January 19, 2011 at 5:03 am
Hi guys
I'm starting to delve into the advanced TSQL world and could use some assistance.
Basically I am trying to write a query that gives me all employees who have changed job stream as a percentage of total employees. This sounds easy but what I’m struggling with is that an employee is linked to a stream through their department. So:
So what we have is a Job table, which lists the employee ID and their Department. You can have several rows for the same employee ID if they have changed departments, so for example:
EmpidDept
001IT
001Sales
002Investment Mgmt
The query would be easy for me if I were just tracking whether they changed departments (in the same table). However even though a user might have changed departments, we are only interested if that department is in a different stream. You can have several departments in the same stream, so if the user has changed departments, but remains in the same stream, I don’t want to know.
So there is another table called Stream which maps Departments to Streams, such as:
DeptStream
ITNoStream
Investment MgmtTax Private Client Services
So I need a query that returns all of the employees that have changed stream.
Help please!!
Thanks 🙂
Doodles
January 19, 2011 at 5:19 am
Please read the first article referenced in my signature on how to describe the issue more detailed and with ready to use sample data.
It will be much more helpful if we can run some sample code and compare our solution to an expected result.
As a bonus you'll get tested code...
January 19, 2011 at 5:39 am
Sorry, hope this is more helpful. The code below creates two sample tables, Job and Stream and populates them with some sample records.
USE [MIStg]
GO
/****** Object: Table [dbo].[Stream] Script Date: 01/19/2011 12:32:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stream](
[DeptID] [nchar](10) NULL,
[Stream] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Stream] ([DeptID], [Stream]) VALUES (N'ITDEPT ', N'NOSTREAM ')
INSERT [dbo].[Stream] ([DeptID], [Stream]) VALUES (N'SALDEPT ', N'STREAM1 ')
INSERT [dbo].[Stream] ([DeptID], [Stream]) VALUES (N'FAC ', N'STREAM2 ')
INSERT [dbo].[Stream] ([DeptID], [Stream]) VALUES (N'HRDEPT ', N'STREAM3 ')
INSERT [dbo].[Stream] ([DeptID], [Stream]) VALUES (N'MARDEPT ', N'STREAM4 ')
/****** Object: Table [dbo].[Job] Script Date: 01/19/2011 12:32:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Job](
[EmpID] [nchar](10) NULL,
[JobStartDate] [date] NULL,
[JobEndDate] [date] NULL,
[DeptID] [nchar](10) NULL,
[LocationID] [nchar](10) NULL,
[Active] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'001 ', CAST(0xDF300B00 AS Date), CAST(0x4C320B00 AS Date), N'ITDEPT ', N'LON ', 1)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'001 ', CAST(0x4D320B00 AS Date), NULL, N'MARDEPT ', N'LON ', 1)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'002 ', CAST(0x5B310B00 AS Date), NULL, N'HRDEPT ', N'GAT ', 0)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'003 ', CAST(0x712F0B00 AS Date), NULL, N'HRDEPT ', N'GAT ', 1)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'003 ', CAST(0x712F0B00 AS Date), NULL, N'FAC ', N'LON ', 1)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'004 ', CAST(0x042E0B00 AS Date), NULL, N'SALDEPT ', N'BIR ', 1)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'005 ', CAST(0x65300B00 AS Date), NULL, N'SALDEPT ', N'SOU ', 1)
INSERT [dbo].[Job] ([EmpID], [JobStartDate], [JobEndDate], [DeptID], [LocationID], [Active]) VALUES (N'006 ', CAST(0x9B2E0B00 AS Date), NULL, N'FAC ', N'LON ', 1)
I came up with a query that would allow me to retrieve all the employees that had changed departments (within same table).
SELECT COUNT(DISTINCT a.EmpID) FROM Job a
INNER JOIN Job c ON
a.EmpID = c.EmpID
AND a.DeptID != c.DeptID
WHERE a.Active = 1
Where I get stuck is where I need to retrieve employees that have changed streams (in a separate table).
Thanks!!
January 19, 2011 at 6:54 am
Do you mean something like this
SELECT COUNT(DISTINCT j.EmpID)
FROM dbo.Job j
JOIN dbo.Stream s ON s.DeptID=j.DeptID
JOIN dbo.Job j2 ON j2.EmpID=j.EmpID
JOIN dbo.Stream s2 ON s2.DeptID=j2.DeptID
WHERE j.Active = 1
AND s.Stream<>s2.Stream
Far away is close at hand in the images of elsewhere.
Anon.
January 19, 2011 at 10:10 am
That's exactly what i mean!! Just couldn't think of the logic...
Thanks so much for the response & taking the time to read through my post!
Doodles <-- loves forum bodhisattvas 🙂
January 19, 2011 at 10:17 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply