[Thinus du Pisanie]

Hey you guys i'm struggling with sql statment that i want to run i have the following tables

1. Employee
Emp_ID
Emp_Name
Emp_Surname

2. Gap
Gap_ID
Emp_ID
Item_Name

I have a Insert statment that exstracts data from 5 tables and run checks that insert the data into gap table.

Now when i run an individual gap analysis the gap table truncate and i get only one Person's info wich is correct.

Bv. 1 Emp111 Grade 12
2 Emp111 It Diploma

ect.

If i run a company gap analysis i get all the employee's gaps witch is also correct

Bv. 1 Emp111 Grade 12
2 Emp111 It Diploma
3. Emp112 It Diploma
4. Emp112 Grade 12
5. Emp113 Grade 10
6. Emp113 It Diploma

This gives me the employee details per gap.

[SELECT lms_Gap_WL.*, lms_Employee.Employee_Name, lms_Employee.Employee_Initials, lms_Employee.Employee_Surname, FROM lms_Gap_WL INNER JOIN lms_Employee ON dbo.lms_Employee.Employee_ID = @EmpID]

In Report
Jan du Plessis

Grade 12
It Doiploma

What i want is to count all the employee's that have a gap in the diffrent item_name's.

Than get their information (Details like statement on top)

Than i want to remove all the duplicates

But the catch is i want the information to display like this in a report

Item Name Employees counted +
Employee Names

bv. 1 Grade 12 15 +
Jan
Koos
Sannie

2 It Diploma 12 +
Jan
Koos
Piet

I have absolutly no idea how to start or what to do. Any help will be apprisiated really.



Than remove all the duplicates

[BobRodes]

It's a little hard for me to understand what you're saying, because you don't really explain what a "gap" is. However , try changing your inner join to a right join. this will return records where employees have no item name, with NULL in the item name field. You can then count all the fields where item name is null.

This post has been edited by BobRodes: 18 May 2012 - 10:48 AM

[Thinus du Pisanie]

Sorry let my refraze a little

The tables i am using is:

1. Employee
Emp_ID
Emp_Name
Emp_Surname

2. Education
EducID
Education_Name

3. Qualification
QuaID
Qua_Name

4. Gap
Gap_ID
Emp_ID
Item_Code
Item_Name

I have a Insert statment that exstracts data from Qualification and education tables and run checks that insert the data into gap table.

The Gap table is the table that identify the gaps the employee have in his/her job profile. If she is a Developer she need sertain qualifications to be one, as the company evolve she need to update the qualifications and the gap table shows the company witch courses she need or qualifications she need to upgrade her profile.

Now when i run an individual gap analysis the gap table truncate and i get only one Person's info wich is correct.

Bv. 1 Emp111 Educ001(EducationID) Grade 12
2 Emp111 Qualif001(Qualification ID) It Diploma

ect.

Note that it is the same employee

If i run a company gap analysis i get all the employee's gaps witch is also correct

Gap ID EmpID Itemcode Item Name

Bv. 1 Emp111 Educ001 Grade 12
2 Emp111 Qualif001 It Diploma
3. Emp112 Qualif001 It Diploma
4. Emp112 Educ001 Grade 12
5. Emp113 Educ002 Grade 10
6. Emp113 Qualif001 It Diploma

This gives me the employee details per gap.

SELECT lms_Gap_WL.*, lms_Employee.Employee_Name, lms_Employee.Employee_Initials, lms_Employee.Employee_Surname, FROM lms_Gap_WL INNER JOIN lms_Employee ON dbo.lms_Employee.Employee_ID = @EmpID

Now i want to print a report the report per individual gap out of the gap table looks like this :


Jan du Plessis

Item Code Item Name

Educ001 Grade 12
Qualif001 It Doiploma

What i want is to count all the employee's that have a gap in the diffrent item_name's.

Than get their information like below

SELECT lms_Gap_WL.*, lms_Employee.Employee_Name, lms_Employee.Employee_Initials, lms_Employee.Employee_Surname, FROM lms_Gap_WL INNER JOIN lms_Employee ON dbo.lms_Employee.Employee_ID = @EmpID

Than i want to remove all the duplicates, the duplicates is in this example the It diploma and grade 12.

But the catch is i want the information to display like this in a report

Item Code Item Name Employees counted Employee Names

Qualif001 It Doiploma 2 (employees need this course) Jan
Koos
Piet

Educ001 Grade 12 2 (employees need this course) Jan
Koos

Educ002 Grade 10 1 (employees need this course) Piet




I have absolutly no idea how to start or what to do. Any help will be apprisiated really.



Hope you understand now sorry for the half phrasing.

[BobRodes]

Ok, I begin to see what you are up to, but still not quite clear. Is a "gap" a particular education or qualification unit that an employee doesn't have and is required to have for a job that he's interested in?

Also, how do you decide what are "duplicates", and why do you have two different tables, one for education and one for qualification?

[baavgai]

I was trying to avoid this one. Was hoping someone would let you down easy...

SQL returns result sets. Period. You ask a question, you get an answer. That result set has NO formatting.

You want a report writer. Report writers take result sets and make them pretty. You can apply all kinds for display rules and layout to your heart's content. Don't ask me to recommend one, because they all suck. It looks like you're using MS SQL; just use that one.

That said...

You can customize the result set. MS SQL has temp tables; we'll use those:

-- mimicking your data
create table #Emp(EmpId varchar(6), [Name] varchar(30))
create table #Gap (GapId int, EmpId varchar(6), ItemCode varchar(30), ItemName varchar(30) )

insert into #Gap values(1,'Emp111','Educ001','Grade 12')
insert into #Gap values(2,'Emp111','Qualif001','It Diploma')
insert into #Gap values(3,'Emp112','Qualif001','It Diploma')
insert into #Gap values(4,'Emp112','Educ001','Grade 12')
insert into #Gap values(5,'Emp113','Educ002','Grade 10 ')
insert into #Gap values(6,'Emp113','Qualif001','It Diploma')

insert into #Emp values('Emp111','Jan')
insert into #Emp values('Emp112','Koos')
insert into #Emp values('Emp113','Piet')


select a.ItemCode, a.ItemName, b.EmployeesCounted, a.EmpId
	from #Gap a
	inner join (
		select ItemCode, ItemName, count(*) as EmployeesCounted from #Gap group by ItemCode, ItemName
		) b on a.ItemCode=b.ItemCode and a.ItemName=b.ItemName


ItemCode                       ItemName                       EmployeesCounted EmpId
------------------------------ ------------------------------ ---------------- ------
Educ001                        Grade 12                       2                Emp111
Educ001                        Grade 12                       2                Emp112
Educ002                        Grade 10                       1                Emp113
Qualif001                      It Diploma                     3                Emp113
Qualif001                      It Diploma                     3                Emp111
Qualif001                      It Diploma                     3                Emp112

(6 row(s) affected)


-- add the name instead
select a.ItemCode, a.ItemName, b.EmployeesCounted, c.[Name]
	from #Gap a
	inner join (
		select ItemCode, ItemName, count(*) as EmployeesCounted from #Gap group by ItemCode, ItemName
		) b on a.ItemCode=b.ItemCode and a.ItemName=b.ItemName
	inner join #Emp c
		on a.EmpId=c.EmpId
	order by a.ItemCode, a.ItemName


ItemCode                       ItemName                       EmployeesCounted Name
------------------------------ ------------------------------ ---------------- ------------------------------
Educ001                        Grade 12                       2                Jan
Educ001                        Grade 12                       2                Koos
Educ002                        Grade 10                       1                Piet
Qualif001                      It Diploma                     3                Piet
Qualif001                      It Diploma                     3                Jan
Qualif001                      It Diploma                     3                Koos

(6 row(s) affected)


-- time to make a report
-- use all char, for spacing
create table #Report(Seq int identity(1,1), ItemCode char(30), ItemName char(30), EmployeesCounted char(15), EmpName char(30) )

-- load with the data we want
insert into #Report(ItemCode, ItemName, EmployeesCounted, EmpName)
	select a.ItemCode, a.ItemName, cast(b.EmployeesCounted as varchar), c.[Name]
		from #Gap a
		inner join (
			select ItemCode, ItemName, count(*) as EmployeesCounted from #Gap group by ItemCode, ItemName
			) b on a.ItemCode=b.ItemCode and a.ItemName=b.ItemName
		inner join #Emp c
			on a.EmpId=c.EmpId
		order by b.EmployeesCounted desc, a.ItemCode, a.ItemName, c.[Name]


-- clear out the dups
update #Report
	set ItemCode='', ItemName='', EmployeesCounted=''
	from #Report a
	where seq not in (select min(seq) from #Report group by ItemCode, ItemName)

-- the result
select ItemCode, ItemName, EmployeesCounted, EmpName
	from #Report
	order by seq


ItemCode                       ItemName                       EmployeesCounted EmpName
------------------------------ ------------------------------ ---------------- ------------------------------
Qualif001                      It Diploma                     3                Jan                           
                                                                               Koos                          
                                                                               Piet                          
Educ001                        Grade 12                       2                Jan                           
                                                                               Koos                          
Educ002                        Grade 10                       1                Piet                          

(6 row(s) affected)

If that seems overly complex, it is. Use a report writer.

[Thinus du Pisanie]

Yes it is and he requires it for job he is interested in.

Education runs with other tables that quilification doesn't so i must have two tables.

Duplicates are determned by their item name, if i run a gap analysis for the whole company (all employees) my table is going to have 200 employees with 500 education and quilification Item names and the company wants to see just all the gaps needed

If duplicates isn't remove the company is going to see they need
Grade 12
Grade 12
Grade10
It diploma
Grade 10

If removed
Grade 12 _______________ 2 Employees______Koos
_________________________________Jan
Grade 10 _______________ 1 Employees______Koos
It diploma _______________ 2 Employees______Koos
_________________________________Piet

Hope it's clearer now.

baavgai, on 21 May 2012 - 10:39 AM, said:

I was trying to avoid this one. Was hoping someone would let you down easy...

SQL returns result sets. Period. You ask a question, you get an answer. That result set has NO formatting.

You want a report writer. Report writers take result sets and make them pretty. You can apply all kinds for display rules and layout to your heart's content. Don't ask me to recommend one, because they all suck. It looks like you're using MS SQL; just use that one.

That said...

You can customize the result set. MS SQL has temp tables; we'll use those:

-- mimicking your data
create table #Emp(EmpId varchar(6), [Name] varchar(30))
create table #Gap (GapId int, EmpId varchar(6), ItemCode varchar(30), ItemName varchar(30) )

insert into #Gap values(1,'Emp111','Educ001','Grade 12')
insert into #Gap values(2,'Emp111','Qualif001','It Diploma')
insert into #Gap values(3,'Emp112','Qualif001','It Diploma')
insert into #Gap values(4,'Emp112','Educ001','Grade 12')
insert into #Gap values(5,'Emp113','Educ002','Grade 10 ')
insert into #Gap values(6,'Emp113','Qualif001','It Diploma')

insert into #Emp values('Emp111','Jan')
insert into #Emp values('Emp112','Koos')
insert into #Emp values('Emp113','Piet')


select a.ItemCode, a.ItemName, b.EmployeesCounted, a.EmpId
	from #Gap a
	inner join (
		select ItemCode, ItemName, count(*) as EmployeesCounted from #Gap group by ItemCode, ItemName
		) b on a.ItemCode=b.ItemCode and a.ItemName=b.ItemName


ItemCode                       ItemName                       EmployeesCounted EmpId
------------------------------ ------------------------------ ---------------- ------
Educ001                        Grade 12                       2                Emp111
Educ001                        Grade 12                       2                Emp112
Educ002                        Grade 10                       1                Emp113
Qualif001                      It Diploma                     3                Emp113
Qualif001                      It Diploma                     3                Emp111
Qualif001                      It Diploma                     3                Emp112

(6 row(s) affected)


-- add the name instead
select a.ItemCode, a.ItemName, b.EmployeesCounted, c.[Name]
	from #Gap a
	inner join (
		select ItemCode, ItemName, count(*) as EmployeesCounted from #Gap group by ItemCode, ItemName
		) b on a.ItemCode=b.ItemCode and a.ItemName=b.ItemName
	inner join #Emp c
		on a.EmpId=c.EmpId
	order by a.ItemCode, a.ItemName


ItemCode                       ItemName                       EmployeesCounted Name
------------------------------ ------------------------------ ---------------- ------------------------------
Educ001                        Grade 12                       2                Jan
Educ001                        Grade 12                       2                Koos
Educ002                        Grade 10                       1                Piet
Qualif001                      It Diploma                     3                Piet
Qualif001                      It Diploma                     3                Jan
Qualif001                      It Diploma                     3                Koos

(6 row(s) affected)


-- time to make a report
-- use all char, for spacing
create table #Report(Seq int identity(1,1), ItemCode char(30), ItemName char(30), EmployeesCounted char(15), EmpName char(30) )

-- load with the data we want
insert into #Report(ItemCode, ItemName, EmployeesCounted, EmpName)
	select a.ItemCode, a.ItemName, cast(b.EmployeesCounted as varchar), c.[Name]
		from #Gap a
		inner join (
			select ItemCode, ItemName, count(*) as EmployeesCounted from #Gap group by ItemCode, ItemName
			) b on a.ItemCode=b.ItemCode and a.ItemName=b.ItemName
		inner join #Emp c
			on a.EmpId=c.EmpId
		order by b.EmployeesCounted desc, a.ItemCode, a.ItemName, c.[Name]


-- clear out the dups
update #Report
	set ItemCode='', ItemName='', EmployeesCounted=''
	from #Report a
	where seq not in (select min(seq) from #Report group by ItemCode, ItemName)

-- the result
select ItemCode, ItemName, EmployeesCounted, EmpName
	from #Report
	order by seq


ItemCode                       ItemName                       EmployeesCounted EmpName
------------------------------ ------------------------------ ---------------- ------------------------------
Qualif001                      It Diploma                     3                Jan                           
                                                                               Koos                          
                                                                               Piet                          
Educ001                        Grade 12                       2                Jan                           
                                                                               Koos                          
Educ002                        Grade 10                       1                Piet                          

(6 row(s) affected)

If that seems overly complex, it is. Use a report writer.

baavgai.
I am using a report writer so the main query is to complete my table with the sql queries and then use report writer to simply select the data in the table to display in nice format.

[Thinus du Pisanie]

baavgai

Thank you very very much exactly what i needed and wanted your brilliant.

May I ask if i want to learn these advance sql statment that you wrote with the a.Itemname ect. Where can i get tutorials or learning material for it ?

And thanks again you just inspire me to acive new hights.

[baavgai]

Hmmm... aside from the use of MS SQL style temp tables and "identity" it's basically just straight up SQL. I'd start there. Any SQL book, tutorial, intro should cover the basics.

You should understand the "CRUD" of SQL: INSERT, SELECT, UPDATE, DELETE. Within select, you must understand JOIN and also GROUP BY. Once you have that, a lot of tricks are open to you.

Your single biggest tool for complex stuff is a "subquery." That is, basically, treating the result of one query as if it were a table in another.

There are places where pure SQL just can't do the job. The trick of throwing the result in a temp table and then manipulating it solves this most of the time. For the really ugly, there is are procedural languages, like T-SQL.

[BobRodes]

I'm sorry, I had the idea that you were trying to calculate what the gaps were from the other data presented. Looks like baavgai understood better what you were looking for.

[Thinus du Pisanie]

Good Morning guys I think maybe I am taking a change here but hope that one of you maybe can help me with the following problem if this is possible in any way.

I wrote a vb.net application that connects to my sql 2008 R2 database like this:

Imports System.Data.SqlClient
Imports System.IO
Public Class Prep_Status
[/Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")]
Private Sub Prep_Status_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim currentFolder As String = Directory.GetCurrentDirectory()
' Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA")
Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA")
For Each FI As FileInfo In INF.GetFiles()
Dim filenght As String = FI.Name.Length
Dim sfilename As String = FI.Name
sfilename = sfilename.Remove(filenght - 4)
If FI.Name = sfilename + ".mdf" Then
If sfilename = "master" Then
ElseIf sfilename = "model" Then
ElseIf sfilename = "msdb" Then
ElseIf sfilename = "tempdb" Then
ElseIf sfilename = "ReportServer" Then
ElseIf sfilename = "ReportServerTempDB" Then
ElseIf sfilename = "MSDBData" Then
Else
cboxcompany.Items.Add(sfilename)

End If
End If
Next

Connection string is this :

Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")

Now it works perfectly well and I can call all off my databases in my application now I was wondering can I connect my application to a server via VPN access through my desktop pc? IS this possible?

How it works currently I connect to VPN access and from there remotely connect to my server and do all my database admin through remote desktop.

If someone can help me or let me down easy and say it is not possible I can get some sleep for a change. Would be appreciated.