Urgent: 8 Consecutive days worked
-
Hi All,
I have table with columns:
[ASSOCIATEID]
[LASTNM]
[FIRSTNM]
[STRNBR]
[DPTNBR]
[TYPE]
[DateWorked] [datetime] NULL,
[HoursWorked].
Need to report like this :
Associate number
Associate name
Date worked
number of hours worked each day
Selection criteria : Associates worked 8 consecutive days between 9/28/09 and 12/31/09
Thanks in advance....
Thanks,
Kotteesh
-
Please see the first link in my signature for the best way to setup a sample DDL/dataset and to show what results you're looking for from that dataset.
It's much easier to work on a problem like this with sample data and expected results then for us to try to build a set for each person who asks a question. Remember, we're volunteers. The more work you do up front to make our lives easier the more likely one of us is to drop by to help you.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks Craig,
Here is the script to create the source table
----------------------------------------------
USE [TESTDB]
GO
/****** Object: Table [dbo].[CVTEST] Script Date: 06/28/2011 03:29:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CVTEST2](
[ASSOCIATEID] [bigint] NULL,
[LASTNM] [varchar](40) NULL,
[FIRSTNM] [varchar](40) NULL,
[STRNBR] [varchar](4) NULL,
[DPTNBR] [varchar](4) NULL,
[TYPE] [varchar](40) NULL,
[DateWorked] [datetime] NULL,
[HoursWorked] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------------------------------------------
Data to be inserted
INSERT INTO CVTEST2
VALUES (100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-09-28 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES(100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-09-29 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES(100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-09-30 14:00:25.000',7.0)
INSERT INTO CVTEST2
VALUES (100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-10-01 14:00:25.000',10.0)
INSERT INTO CVTEST2
VALUES (100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-10-02 14:00:25.000',5.0)
INSERT INTO CVTEST2
VALUES (100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-10-03 14:00:25.000',3.0)
INSERT INTO CVTEST2
VALUES (100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-10-04 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES (100549823,'CINTRON','KIMBERLEY',3615,0031,'USFTHOURLY','2009-10-05 14:00:25.000',9.0)
INSERT INTO CVTEST2
VALUES (100458932,'BAY','THOMAS',3615,0031,'USFTHOURLY','2009-09-29 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES (100458932,'BAY','THOMAS',3615,0031,'USFTHOURLY','2009-09-29 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES (100458932,'BAY','THOMAS',3615,0031,'USFTHOURLY','2009-09-29 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES (100458932,'BAY','THOMAS',3615,0031,'USFTHOURLY','2009-09-29 14:00:25.000',8.0)
INSERT INTO CVTEST2
VALUES (100458932,'BAY','THOMAS',3615,0031,'USFTHOURLY','2009-09-29 14:00:25.000',8.0)
---------------------------------------
Expected result: Associates worked between 09/29/2009 and 12/31/2009 for eight consecutive days.
In this example, result should be:
100549823CINTRONKIMBERLEY28-Sep-20098
100549823CINTRONKIMBERLEY29-Sep-20098
100549823CINTRONKIMBERLEY30-Sep-20097
100549823CINTRONKIMBERLEY1-Oct-2009 10
100549823CINTRONKIMBERLEY2-Oct-20095
100549823CINTRONKIMBERLEY3-Oct-20093
100549823CINTRONKIMBERLEY4-Oct-20098
100549823CINTRONKIMBERLEY5-Oct-20099
-
WITH CTE1 AS (SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked],
CONVERT(VARCHAR(8),DATEADD(Day,-ROW_NUMBER() OVER(PARTITION BY [ASSOCIATEID] ORDER BY [DateWorked]),[DateWorked]),112) AS rnDiff
FROM CVTEST2
WHERE [DateWorked] >= '20090928' AND [DateWorked] < '20100101'),
CTE2 AS (
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked],
COUNT(*) OVER(PARTITION BY [ASSOCIATEID],rnDiff) AS cn
FROM CTE1)
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked]
FROM CTE2
WHERE cn>=8
ORDER BY [ASSOCIATEID],[DateWorked];
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi Mark,
I'm not getting fruitful results with the query.
Query Used:
WITH CTE1 AS (
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked],
CONVERT(VARCHAR(8),DATEADD(Day,-ROW_NUMBER() OVER(PARTITION BY [ASSOCIATEID] ORDER BY [DateWorked]),
[DateWorked]),112) AS rnDiff
FROM CVTEST
WHERE [DateWorked] >= '20090928' AND [DateWorked] < '20091231'),
CTE2 AS (
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked],
COUNT(*) OVER(PARTITION BY [ASSOCIATEID],rnDiff) AS cn
FROM CTE1)
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],[HoursWorked]
FROM CTE2
WHERE cn>=8
ORDER BY [ASSOCIATEID],[DateWorked];
------------------------
Results Sample: If you see the highlighted rows are not matching the requirement of 8 Consecutive days.
100620244ISHAM DAVID H2009-10-27 00:00:00.0008
100620244ISHAM DAVID H2009-10-28 00:00:00.00010
100620244ISHAM DAVID H2009-10-28 00:00:00.00010
100620244ISHAM DAVID H2009-10-31 00:00:00.0008
100620244ISHAM DAVID H2009-11-01 00:00:00.0008
100620244ISHAM DAVID H2009-11-01 00:00:00.0008
100620244ISHAM DAVID H2009-11-02 00:00:00.0008
100620244ISHAM DAVID H2009-11-22 00:00:00.0008
100620244ISHAM DAVID H2009-11-23 00:00:00.0008
100620244ISHAM DAVID H2009-11-23 00:00:00.0008
100620244ISHAM DAVID H2009-11-24 00:00:00.0007
100620244ISHAM DAVID H2009-11-27 00:00:00.0008
100620244ISHAM DAVID H2009-11-27 00:00:00.0008
100620244ISHAM DAVID H2009-11-28 00:00:00.0007
100620244ISHAM DAVID H2009-12-02 00:00:00.0008
100620244ISHAM DAVID H2009-12-03 00:00:00.0008
100620244ISHAM DAVID H2009-12-03 00:00:00.0008
100620244ISHAM DAVID H2009-12-04 00:00:00.0008
The Date worked is not continuous 8 days as it breaks a day (means the associate haven't worked 8 consecutive days and it should not get reported.
Please help me here...
-
Try this
WITH CTE1 AS (
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],
CONVERT(VARCHAR(8),
DATEADD(Day,-DENSE_RANK() OVER(PARTITION BY [ASSOCIATEID] ORDER BY [DateWorked]),[DateWorked]),
112) AS rnDiff
FROM CVTEST
WHERE [DateWorked] >= '20090928' AND [DateWorked] < '20091231'),
CTE2 AS (
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked],
MIN([DateWorked]) OVER(PARTITION BY [ASSOCIATEID],rnDiff) AS minDt,
MAX([DateWorked]) OVER(PARTITION BY [ASSOCIATEID],rnDiff) AS maxDt
FROM CTE1)
SELECT [ASSOCIATEID],[LASTNM],[FIRSTNM],[DateWorked]
FROM CTE2
WHERE DATEDIFF(day,minDt,maxDt)>=7
ORDER BY [ASSOCIATEID],[DateWorked];
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Mark... This worked like a charm.
Thanks a bunch:-)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply