Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get continuous date with count Expand / Collapse
Author
Message
Posted Saturday, December 15, 2012 11:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
hi all,
i am having a requirement like this

input

EmpID reportdate reportname noofdays
47 11/29/2012 Thursday 1
47 11/30/2012 Friday 1
47 12/4/2012 Tuesday 1
47 12/5/2012 Wednesday 1
47 12/7/2012 Friday 1
47 12/10/2012 Monday 1
48 11/29/2012 Thursday 1
48 11/30/2012 Friday 1
48 12/4/2012 Tuesday 1
48 12/5/2012 Wednesday 1
48 12/7/2012 Friday 1
48 12/10/2012 Monday 1
48 14/10/2012 Tuesday 1


i need to calculate the leave type for all employees which will be either single, continuous or connecting
if an employee taking leave from friday to monday then the leave count will be 4, if he is taking leave continuous then count should be added and show the total count, if there is a gap between two continuous leave then another entry has to come.
here for empid 47 there is entry for report date 29/11 and 30/11 which is continuous so the output should be in
47,'continuous',2.
another entry is report date 7/12 and 10/11 which is connecting because its from friday to monday so o/p will be 47,'connecting',4.


output
EmpID LeaveType LeaveCount
47 continuous 2
47 continuous 2
47 connecting 4
48 continuous 2
48 continuous 2
48 connecting 4
48 single 1

here it is possible multiple leave type for single employee may come.

any help will be highly appreciated.

Post #1396903
Posted Saturday, December 15, 2012 7:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,945, Visits: 2,900
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats (they were not). Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. We now have to guess at everything, then do your typing for you. Your “no_of_days” column is redundant and improperly formed; it should be leave_cnt. The days of the week are not report names; they are temporal units and can be computed from a date.

CREATE TABLE Employee_Leave
(emp_id INTEGER NOT NULL,
leave_date DATE NOT NULL,
PRIMARY KEY (emp_id, leave_date)

INSERT INTO Employee_Leave
VALUES
(47, '2012-11-29'), (47, '2012-11-30'),
(47, '2012-12-04'), (47, '2012-12-05'),
(47, '2012-12-07'), (47, '2012-12-10'),
(48, '2012-11-29'), (48, '2012-11-30'),
(48, '2012-12-04'), (48, '2012-12-05'), (48, '2012-12-07'), (48, '2012-12-10'),
(48, '2012-10-14');

Why was this DDL and DML so hard you could not type it for us? What you want is this version of a calendar table, with the Julianized business dates. A fifty year table is probably big enough.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
...);

The trick is to assign a single number to each weekend.

INSERT INTO Calendar
VALUES ('2007-04-05', 42),
('2007-04-06', 43), -- good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44),
('2007-04-10', 45); --Tuesday

To compute the business days from Thursday of this week to next
Tuesday:

SELECT (C2.julian_business_nbr – C1.julian_business_nbr) AS business_day_cnt,
DATEDIFF(DAY, C1.cal_date, C2.cal_date) AS calendar_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05',
AND C2.cal_date = '2007-04-10';


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1396955
Posted Sunday, December 16, 2012 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
CELKO (12/15/2012)
Code should be in Standard SQL as much as possible and not local dialect.


That's an absolute load of hooie! This is an SQL Server specific forum and SQL Server specific code is welcomed with open arms.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1396998
Posted Monday, December 17, 2012 3:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
By combining Mr. CELKO's politely supplied DDL and sample data, with the technique described by Jeff Moden in his excellent SQL Spackle article: Group Islands of Contiguous Dates, I can come up with the following:

CREATE TABLE #Employee_Leave
(emp_id INTEGER NOT NULL,
leave_date DATE NOT NULL,
PRIMARY KEY (emp_id, leave_date));

INSERT INTO #Employee_Leave
VALUES
(47, '2012-11-29'), (47, '2012-11-30'), (47, '2012-12-04'), (47, '2012-12-05'),
(47, '2012-12-07'), (47, '2012-12-10'), (48, '2012-11-29'), (48, '2012-11-30'),
(48, '2012-12-04'), (48, '2012-12-05'), (48, '2012-12-07'), (48, '2012-12-10'),
(48, '2012-10-14');

WITH
cteGroupedDates AS
( --=== Find the unique dates and assign them to a group.
-- The group looks like a date but the date means nothing except that adjacent
-- dates will be a part of the same group.
SELECT emp_id,
UniqueDate = leave_date,
DateGroup = DATEADD(dd
,-ROW_NUMBER() OVER (
PARTITION BY emp_id ORDER BY emp_id,leave_date)
,CASE DATEPART(dw, leave_date) WHEN 2 THEN DATEADD(dd, -2, leave_date) ELSE leave_date END )
FROM #Employee_Leave
GROUP BY emp_id,leave_date
)
--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the
-- Start and End dates of each group of contiguous dates. While we're at it,
-- we can also figure out how many days are in each range of days.
SELECT emp_id,
StartDate = MIN(UniqueDate),
EndDate = MAX(UniqueDate),
[Days] = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,
[Type] = CASE WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 = 1 THEN 'Single'
WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 > 1 AND
DATEPART(dw, MIN(UniqueDate)) > DATEPART(dw, MAX(UniqueDate)) THEN 'Connecting'
ELSE 'Continuous' END
FROM cteGroupedDates
GROUP BY emp_id,DateGroup
ORDER BY emp_id,StartDate

DROP TABLE #Employee_Leave


Note that this solution is sensitive to the setting of DATEFIRST, i.e., it assumes the week starts on Sunday.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1397178
Posted Monday, December 17, 2012 7:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
Thank you very much dwain for your kind support.

with all due respect to CELKO going forward i will follow complete process.




Thanks,
Ghanshyam
Post #1397264
Posted Tuesday, December 18, 2012 5:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
hi Dwain,
as per you solution its working fine, but when the no. of leaves are more then 12 its giving wrong data.

here i am giving sample input and output
CREATE TABLE #Employee_Leave
(emp_id INTEGER NOT NULL,
leave_date DATE NOT NULL,
NO_Of_Days float not null,
PRIMARY KEY (emp_id, leave_date,NO_Of_Days));


INSERT INTO #Employee_Leave (Emp_id,Leave_Date,NO_Of_Days) VALUES

(89,'11/1/2012',0.5),
(89,'11/2/2012',1),
(89,'11/5/2012',1),
(89,'11/6/2012',1),
(89,'11/7/2012',1),
(89,'11/8/2012',1),
(89,'11/9/2012',0.5),
(89,'11/10/2012',1),
(89,'11/11/2012',1),
(89,'11/12/2012',1),
(89,'11/13/2012',1),
(89,'11/14/2012',1),
(88,'11/14/2012',1),
(88,'11/15/2012',1),
(88,'11/27/2012',0.5),
(87,'11/27/2012',0.5)

-- calculate leaves
;WITH
cteGroupedDates AS
( --=== Find the unique dates and assign them to a group.
-- The group looks like a date but the date means nothing except that adjacent
-- dates will be a part of the same group.
SELECT emp_id,
UniqueDate = leave_date,
DateGroup = DATEADD(dd
,-ROW_NUMBER() OVER (
PARTITION BY emp_id ORDER BY emp_id,leave_date)
,CASE DATEPART(dw, leave_date) WHEN 2 THEN DATEADD(dd, -2, leave_date) ELSE leave_date END )
FROM #Employee_Leave
GROUP BY emp_id,leave_date
)
--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the
-- Start and End dates of each group of contiguous dates. While we're at it,
-- we can also figure out how many days are in each range of days.
SELECT emp_id,
StartDate = MIN(UniqueDate),
EndDate = MAX(UniqueDate),
[Days] = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,
[Type] = CASE WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 = 1 THEN 'Single'
WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 > 1 AND
DATEPART(dw, MIN(UniqueDate)) > DATEPART(dw, MAX(UniqueDate)) THEN 'Connecting'
ELSE 'Continuous' END
FROM cteGroupedDates
GROUP BY emp_id,DateGroup
ORDER BY emp_id,StartDate

output
----------
emp_id StartDate EndDate Days Type
87 2012-11-27 2012-11-27 1 Single
88 2012-11-14 2012-11-15 2 Continuous
88 2012-11-27 2012-11-27 1 Single
89 2012-11-01 2012-11-12 12 Connecting
89 2012-11-06 2012-11-14 9 Continuous


expected output
----------------
emp_id StartDate EndDate Days Type
87 2012-11-27 2012-11-27 1 Single
88 2012-11-14 2012-11-15 2 Continuous
88 2012-11-27 2012-11-27 1 Single
89 2012-11-01 2012-11-12 14 Connecting




Observation : when the date is exceeding two weeks dategroup is coming 2 for which is creating two records.

hope there is a solution to fix it.


regards,
Ghanshyam
Post #1397707
Posted Tuesday, December 18, 2012 5:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
It will tend to do that the way I fudged the grouping factor.

Just goes to show that thorough test data is the best way to a good solution.

What about those 0.5 days in the latest test data? Do you want something special done with those too?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1397731
Posted Tuesday, December 18, 2012 10:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
No answer to my questions so I'll ignore them.

This ain't pretty (elegant yes) but it seems to do the job.

CREATE TABLE #Employee_Leave
(emp_id INTEGER NOT NULL,
leave_date DATE NOT NULL,
NO_Of_Days float not null,
PRIMARY KEY (emp_id, leave_date,NO_Of_Days));


INSERT INTO #Employee_Leave (Emp_id,Leave_Date,NO_Of_Days) VALUES

(89,'11/1/2012',0.5),
(89,'11/2/2012',1),
(89,'11/5/2012',1),
(89,'11/6/2012',1),
(89,'11/7/2012',1),
(89,'11/8/2012',1),
(89,'11/9/2012',0.5),
(89,'11/10/2012',1),
(89,'11/11/2012',1),
(89,'11/12/2012',1),
(89,'11/13/2012',1),
(89,'11/14/2012',1),
(88,'11/14/2012',1),
(88,'11/15/2012',1),
(88,'11/27/2012',0.5),
(87,'11/27/2012',0.5)

-- calculate leaves
-- Use Jeff Moden's approach to calculate islands of contiguous dates
-- http://www.sqlservercentral.com/articles/T-SQL/71550/
;WITH cteGroupedDates AS (
SELECT emp_id,
UniqueDate = leave_date,
DateGroup = DATEADD(dd
,-ROW_NUMBER() OVER (
PARTITION BY emp_id ORDER BY emp_id,leave_date)
,CASE DATEPART(dw, leave_date)
WHEN 2 THEN DATEADD(dd, -2, leave_date)
ELSE leave_date END )
FROM #Employee_Leave
GROUP BY emp_id,leave_date
),
cteGroupedDates2 AS (
SELECT emp_id,
StartDate = MIN(UniqueDate),
EndDate = MAX(UniqueDate),
[Days] = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,
[Type] = CASE WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 = 1
THEN 'Single'
WHEN DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1 > 1 AND
DATEPART(dw, MIN(UniqueDate)) > DATEPART(dw, MAX(UniqueDate))
THEN 'Connecting'
ELSE 'Continuous' END
FROM cteGroupedDates
GROUP BY emp_id,DateGroup),
-- Above will produce overlapping dates for longer periods so use Itzik Ben-Gan's approach
-- to group the overlapping dates.
-- http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx
C1 AS (
SELECT emp_id, ts, Type2, Type
,e=CASE Type2 WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY emp_id, Type2 ORDER BY EndDate) END
,s=CASE Type2 WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY emp_id, Type2 ORDER BY StartDate) END
FROM cteGroupedDates2
CROSS APPLY (
VALUES (1, StartDate), (-1, EndDate)) a(Type2, ts)
),
C2 AS (
SELECT C1.*
,se=ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY ts, Type2 DESC)
FROM C1),
C3 AS (
SELECT emp_id, ts, Type
,grpnm=FLOOR((ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY ts)-1) / 2 + 1)
FROM C2
WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0)
SELECT emp_id, StartDate=MIN(ts), EndDate=MAX(ts)
,Days=DATEDIFF(day, MIN(ts), DATEADD(day, 1, MAX(ts)))
,Type=MIN(Type)
FROM C3
GROUP BY emp_id, grpnm
ORDER BY emp_id,StartDate

DROP TABLE #Employee_Leave


No guarantees that it will work against any case you throw at it but give it a try.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1398164
Posted Tuesday, December 18, 2012 11:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
Thanks Dwain,
that 0.5 is the amount of leave an employee can take in a day either its half day or a full day.
i will take a try on this solution a give you update.



Regards,
Ghanshyam
Post #1398174
Posted Wednesday, December 19, 2012 3:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 1,567, Visits: 2,390
Jeff Moden (12/16/2012)
CELKO (12/15/2012)
Code should be in Standard SQL as much as possible and not local dialect.


That's an absolute load of hooie! This is an SQL Server specific forum and SQL Server specific code is welcomed with open arms.


Wait a minute, Jeff. Are you saying this is *not* Oracle_DB2_SQLServer_MySQL_PostGRE_ETC_ServerCentral? Jeez, I've been confused for so long.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1398697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse