January 13, 2011 at 5:11 am
Hi guys,
I'm having a lot of trouble with this query so thought I would get some help here.
I have a table of employee history and which department, branch and job they worked at a particular time.
Like this:
EmployeeID___Department____Branch_______Job________ StartDate___________ EndDate
1234_____________30_________ 4________ Job1______ 1 June 2010______ 15 June 2010
1234_____________30_________ 4________ Job2______ 16 June 201______ 31 July 2010
1234_____________30_________ 4________ Job3______ 1 Aug 2010______ 17 Aug 2010
1234_____________100_________ 6_______ Job4______ 18 Aug 2010______ 12 Sep 2010
1234_____________100_________6 ________Job5______13 Sep 2010______ 3 Nov 2010
1234_____________30_________ 4________ Job6______ 4 Nov 2010______31 Dec 2010
What I want to do is to get only the very first start date and the very last end date for a particular department/branch group. So the data would look like:
EmployeeID____Department____ Branch____StartDate______ EndDate
1234____________30____________4____1 June 2010____ 17 Aug 2010
1234____________100____________6___18 Aug 2010 ____ 3 Nov 2010
1234____________ 30____________4____4 Nov 2010_____31 Dec 2010
So from 1 June 2010 to 17 Aug 2010 employee worked at Dep 30 Branch 4
Then went to Department 100 Branch 6 on 18 Aug 2010 and stayed there til 3 Nov 2010
And finally moved back to Dep 30 Brnach 4 on 4 Nov 2010 to 31 Dec 2010.
Now I am not allowed to use cursors in my environment, but can't quite figure out how to formulate such a query without one. Is it possible to do this query without cursors say for example using NTILE or DENSE_RANK or even a CTE?
Assistance greatly appreciated!
January 13, 2011 at 5:29 am
January 13, 2011 at 5:41 am
You can try this:
SELECT
EmployeeID,
Department,
Branch,
MIN(StartDate) AS StartDate,
MAX(EndDate) AS EndDate
FROM TABLE_NAME
GROUP BY EmployeeID, Department, Branch
Regards,
Iulian
January 15, 2011 at 8:39 pm
Thanks Dave,
Your suggestion worked beautifully!
But now I am faced with another problem. I want to use the CTE as part of a Merge statement in order to update Slowly Changing Dimensions in my database.
My query is as follows:
DECLARE @Calendar TABLE
(
dt SMALLDATETIME NOT NULL ,
isWeekday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
DECLARE @dt SMALLDATETIME
SET @dt = '20000101'
WHILE @dt < '20300101'
BEGIN
INSERT @Calendar(dt) SELECT @dt
END
;WITH CTE(BSB, CC, IDSAL,NM_FNM, NM_SNM, FullName,StartDate,EndDate, GroupingBy)
AS
(
SELECT BSB, CC, IDSAL,NM_FNM, NM_SNM, FullName,StartDate,EndDate, Cal.dt + ROW_NUMBER() OVER(PARTITION BY BSB, CC ORDER BY Cal.dt DESC) as GroupingBy
FROM dbo.Source, @Calendar as Cal
WHERE Cal.dt between StartDate and EndDate
)
INSERT INTO [MyDatabase].[dbo].[dimension]
(
[IDSAL]
,[BSB]
,[CC]
,[StartDate]
,[EndDate]
,[NM_FNM]
,[NM_SNM]
,[FullName]
,HashByte
)
SELECT [IDSAL]
,[BSB]
,[CC]
,[StartDate]
,[EndDate]
,[NM_FNM]
,[NM_SNM]
,[FullName]
,HashByte
FROM
(
merge [MyDatabase].[dbo].[dimension] as DimensionTable
using
(
SELECT SortedTable.IDSAL, SortedTable.BSB, SortedTable.CC, MIN(StartDate) AS StartDate, MAX(EndDate) AS EndDate, NM_FNM, NM_SNM, FullName
FROM SortedTable
GROUP BY SortedTable.GroupingBy, SortedTable.IDSAL, SortedTable.BSB , SortedTable.CC,NM_FNM, NM_SNM, FullName
) AS DataStore
ON (
DataStore.IDSAL = DimensionTable.IDSAL
)
WHEN MATCHED AND (
DataStore.BSB <> DimensionTable.BSB
)
THEN
Update SET DimensionTable.EndDate = DataStore.EndDate
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
BSB, CC, IDSAL,NM_FNM, NM_SNM, FullName,StartDate, EndDate, HashByte
)
VALUES
(
DataStore.BSB
,DataStore.CC
,DataStore.IDSAL
,DataStore.NM_FNM
,DataStore.NM_SNM
,DataStore.FullName
,DataStore.StartDate
,DataStore.EndDate
)
OUTPUT
$action AS actionperformed
,DataStore.BSB
,DataStore.CC
,DataStore.IDSAL
,DataStore.NM_FNM
,DataStore.NM_SNM
,DataStore.FullName
,DataStore.StartDate
,DataStore.EndDate
)
) AS TargetOutput (
actionperformed
,BSB
,CC
,IDSAL
,NM_FNM
,NM_SNM
,FullName
,StartDate
,EndDate
) WHERE actionperformed = 'UPDATE'
AND IDSAL IS NOT NULL
AND StartDate IS NOT NULL;
However, it keeps coming up with the error: Msg 5329, Level 15, State 1, Line 27
Windowed functions are not allowed in the SELECT clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
It seems that I am not allowed to use the ROW_NUMBER() function in this way???
Why would it not allow me to do this and is there an alternative to what I am trying to achieve?
Thanks.
January 16, 2011 at 4:23 am
Dave Ballantyne (1/13/2011)
Try this article
I tried using quirky update.Any comments on this method ?
http://www.sqlservercentral.com/Forums/Topic1031000-203-4.aspx
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply