March 5, 2013 at 5:16 am
Hi ,
I have a table which holds the employee details :
/*******************************************************************/
/****** Object: Table [dbo].[empleaves] Script Date: 05-03-2013 17:42:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[empleaves](
[empid] [int] NULL,
[leavedate] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (1, CAST(0xCE360B00 AS Date))
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (1, CAST(0xD0360B00 AS Date))
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xD0360B00 AS Date))
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBA360B00 AS Date))
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBB360B00 AS Date))
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBC360B00 AS Date))
GO
INSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (3, CAST(0xC9360B00 AS Date))
GO
/*******************************************************************/
I need a query which will report the leaves of employees as :
empidStartingDateEndingDateNoOfDays
128-02-201328-02-20131
102-03-201302-03-20131
202-03-201302-03-20131
208-02-201310-02-20133
323-02-201323-02-20131
How can I achieve this .?
March 5, 2013 at 5:43 pm
You can use the staggered row number approach to get this:
CREATE TABLE #empleaves(
[empid] [int] NULL,
[leavedate] [date] NULL
) ON [PRIMARY]
GO
INSERT #empleaves ([empid], [leavedate]) VALUES (1, CAST(0xCE360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (1, CAST(0xD0360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xD0360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xBA360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xBB360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (2, CAST(0xBC360B00 AS Date))
INSERT #empleaves ([empid], [leavedate]) VALUES (3, CAST(0xC9360B00 AS Date))
;WITH GroupDates AS (
SELECT empid, leavedate
,rd=CAST(leavedate AS DATETIME)-ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate)
FROM #empleaves)
SELECT empid, StartingDate=MIN(leavedate), EndingDate=MAX(leavedate)
,NoOfDays=1+DATEDIFF(day, MIN(leavedate), MAX(leavedate))
FROM GroupDates
GROUP BY empid, rd
ORDER BY empid, StartingDate
DROP TABLE #empleaves
Jeff Moden doesn't call it that in his article, but here is where it is described: Group Islands of Contiguous Dates[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 5, 2013 at 6:10 pm
Well, Dwain, I was about to suggest the data should be stored with some sort of identifying element as to indicate whether or not the date indicates a leave or a return, lest one be tempted to do something like this...
WITH Dates AS
(
SELECT
empid,
leavedate,
Rownum = CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0
THEN 2
ELSE 1
END,
instance = ((ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) -
CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0
THEN 2
ELSE 1
END)/2) + 1
FROM empleaves
), Dates2 AS
(
SELECT
empid,
instance,
LeaveDate = MAX(CASE WHEN Rownum = 1 THEN leavedate ELSE NULL END),
ReturnDate = MAX(CASE WHEN Rownum = 2 THEN leavedate ELSE NULL END)
FROM Dates
GROUP BY empid, instance
)
SELECT
*,
NoOfDays = DATEDIFF(d,leavedate,returndate)
FROM Dates2
ORDER BY empid, instance
However, it looks like I am getting rustier the longer I dwell in the mainframe world. Anyhow, I still think a data structure like this still leaves a whole lot of room for error. For example, what would happen if someone (or some process) did not enter a return date, so what is really two consecutive dates appear to be a leave and return date, but in reality are two leave dates? Once again, thanks for the showing, me anyway, a new trick.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 5, 2013 at 6:13 pm
dwain.c (3/5/2013)
Jeff Moden doesn't call it that in his article, but here is where it is described: Group Islands of Contiguous Dates[/url]
Ah yes, I do remember reading that now. Thank you Jeff.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 5, 2013 at 6:30 pm
Greg Snidow (3/5/2013)
Well, Dwain, I was about to suggest the data should be stored with some sort of identifying element as to indicate whether or not the date indicates a leave or a return, lest one be tempted to do something like this...
WITH Dates AS
(
SELECT
empid,
leavedate,
Rownum = CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0
THEN 2
ELSE 1
END,
instance = ((ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) -
CASE WHEN ROW_NUMBER() OVER (PARTITION BY empid ORDER BY leavedate) % 2 = 0
THEN 2
ELSE 1
END)/2) + 1
FROM empleaves
), Dates2 AS
(
SELECT
empid,
instance,
LeaveDate = MAX(CASE WHEN Rownum = 1 THEN leavedate ELSE NULL END),
ReturnDate = MAX(CASE WHEN Rownum = 2 THEN leavedate ELSE NULL END)
FROM Dates
GROUP BY empid, instance
)
SELECT
*,
NoOfDays = DATEDIFF(d,leavedate,returndate)
FROM Dates2
ORDER BY empid, instance
However, it looks like I am getting rustier the longer I dwell in the mainframe world. Anyhow, I still think a data structure like this still leaves a whole lot of room for error. For example, what would happen if someone (or some process) did not enter a return date, so what is really two consecutive dates appear to be a leave and return date, but in reality are two leave dates? Once again, thanks for the showing, me anyway, a new trick.
You are welcome Greg. I just love that trick and found that it has other applications than just contiguous dates (for example take a look at the 4th link in my signature on pattern splitting).
I agree there are dangers in this data structure. One for example is that mine doesn't cover is if the leave is contiguous over periods where the employee has the day off (e.g., Saturdays and Sundays).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 5, 2013 at 9:45 pm
Thanks a lot dwain.. It's working 🙂
March 5, 2013 at 9:47 pm
Thanks a ton everyone.. I am jumping to the pool. Lot of things pending.. Thanks again... 🙂
March 5, 2013 at 11:57 pm
You are right. This structure is risky. But fortunately , this lies in the old DB from which we are migrating data to a new beautiful one 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply