How to extract continuous data.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to extract continuous data.

Author

Topic

c2
Starting Member

2 Posts

Posted - 2008-08-11 : 02:04:18

My table tblattendance have below data;

Date Employee Code Name Status
8/4/2008 0:00 3 A absent
8/1/2008 0:00 1 B absent
8/2/2008 0:00 1 B absent
8/3/2008 0:00 1 B absent
8/10/2008 0:00 1 B absent
8/4/2008 0:00 9 C absent
8/4/2008 0:00 5 G absent
8/8/2008 0:00 5 G absent
8/9/2008 0:00 5 G absent
8/10/2008 0:00 5 G absent

I want to make a query or store procedure that only show records who is absent continuously 3 days i.e. (day 1,2,3) not randomly i.e. (day 4,8,9,10).

Any help will be highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 02:10:57

[code]SELECT t.Name
FROM YourTable t
OUTER APPLY(SELECT Top 1 Date
FROm YourTable
WHERE Name <> t.Name
AND Date > t.Date
ORDER BY Date) tmp
OUTER APPLY(SELECT Top 1 Date
FROm YourTable
WHERE Name <> t.Name
AND Date < t.Date
ORDER BY Date DESC) tmp1
WHERE t.Date>tmp1.Date
AND t.Date<tmp.Date
GROUP BY t.Name
HAVING DATEDIFF(d,MIN(t.Date),MAX(t.Date))>=3[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 04:26:22

Visakh, shouldn't the OUTER APPLYs get information from same Employee?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 04:29:32

quote:
Originally posted by Peso

Visakh, shouldn't the OUTER APPLYs get information from same Employee?

E 12°55'05.25"
N 56°04'39.16"

the OUTER APPLY were to get the change of employee details that heppen just before and just after the current group.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 04:31:07

or did you mean this?

SELECT [Name]
FROM @test t
CROSS APPLY(SELECT Date
            FROm @test
            WHERE Name = t.Name
            AND Date = dateadd(d,1,t.Date)) tmp
CROSS APPLY(SELECT Date
            FROm @test
            WHERE Name = t.Name
            AND Date = dateadd(d,-1,t.Date)) tmp1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 04:37:14

First of all, there are 5 columnheaders in the sample data but only 4 columns with sample data.
But I think OP want FOR EVERY EMPLOYEE who has been continously absent for three days or more.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 05:44:48

[code]DECLARE @Sample TABLE (Date DATETIME, Employee INT, Code CHAR)

INSERT @Sample
SELECT '8/4/2008', 3, 'A' UNION ALL
SELECT '8/1/2008', 1, 'B' UNION ALL
SELECT '8/2/2008', 1, 'B' UNION ALL
SELECT '8/3/2008', 1, 'B' UNION ALL
SELECT '8/10/2008', 1, 'B' UNION ALL
SELECT '8/4/2008', 9, 'C' UNION ALL
SELECT '8/4/2008', 5, 'G' UNION ALL
SELECT '8/8/2008', 5, 'G' UNION ALL
SELECT '8/9/2008', 5, 'G' UNION ALL
SELECT '8/10/2008', 5, 'G'[/code]
Also asked and answered on SQLServerCentral.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources