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)
 Summarizing calendar

Author  Topic 

Stumbler
Starting Member

23 Posts
Posted - 2007-09-14 : 06:29:38
Does anyone have a clue how to summarize the data in the following way?

CREATE TABLE history (
Plant char(2) NOT NULL
EmployeeID varchar(5) NOT NULL
Date datetime NOT NULL
Sequence int NOT NULL
Code varchar(4)
Hours int NULL
Shift varchar(1) NULL

INSERT INTO history (Plant,EmployeeID,Date,Sequence,Code,Hours,Shift)
SELECT 'AA','82519','22/01/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','23/01/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','24/01/2007',1,'100',800,'1' UNION ALL
SELECT 'AA','82519','25/01/2007',1,'100',800,'1' UNION ALL
SELECT 'AA','82519','26/01/2007',1,'100',800,'1' UNION ALL
SELECT 'AA','82519','27/01/2007',1,'4283',0,'R' UNION ALL
SELECT 'AA','82519','28/01/2007',1,'4283',0,'R' UNION ALL
SELECT 'AA','82519','29/01/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','30/01/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','31/01/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','1/02/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','2/02/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','3/02/2007',1,'4283',0,'R' UNION ALL
SELECT 'AA','82519','4/02/2007',1,'4283',0,'R' UNION ALL
SELECT 'AA','82519','5/02/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','6/02/2007',1,'100',800,'0' UNION ALL
SELECT 'AA','82519','7/02/2007',1,'100',800,'0' UNION ALL

SELECT * from history

Expected Result
Plant EmployeeID Date_frm Date_to Shift Code NofDays
AA 82519 22/01/2007 26/01/2007 0 100 5
AA 82519 27/01/2007 28/01/2007 R 4283 2
AA 82519 29/01/2007 02/02/2007 0 100 5
AA 82519 03/02/2007 07/02/2007 R 4283 2

With shift being the shift that comes with the Date_frm

Thanks in advance!

Hans

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-14 : 07:18:29
[code]DECLARE @History TABLE (Plant CHAR(2), EmployeeID VARCHAR(5), Date DATETIME, Sequence INT, Code VARCHAR(4), Hours INT, Shift VARCHAR(1))

SET DATEFORMAT DMY

INSERT @History
SELECT 'AA', '82519', '22/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '23/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '24/01/2007', 1, '100', 800, '1' UNION ALL
SELECT 'AA', '82519', '25/01/2007', 1, '100', 800, '1' UNION ALL
SELECT 'AA', '82519', '26/01/2007', 1, '100', 800, '1' UNION ALL
SELECT 'AA', '82519', '27/01/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '28/01/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '29/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '30/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '31/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '1/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '2/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '3/02/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '4/02/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '5/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '6/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '7/02/2007', 1, '100', 800, '0'

;WITH Stage (Plant, EmployeeID, Date, Code, Shift, RowID)
AS (
SELECT Plant,
EmployeeID,
Date,
Code,
Shift,
ROW_NUMBER() OVER (PARTITION BY Plant, EmployeeID ORDER BY Date)
FROM @History
), Yak (Plant, EmployeeID, Date, Code, Shift, RowID, Grp)
AS (
SELECT Plant,
EmployeeID,
Date,
Code,
Shift,
RowID,
0
FROM Stage
WHERE RowID = 1

UNION ALL

SELECT s.Plant,
s.EmployeeID,
s.Date,
s.Code,
s.Shift,
s.RowID,
CASE
WHEN s.Code = y.Code AND s.Shift = y.Shift THEN y.Grp
ELSE y.Grp + 1
END
FROM Stage AS s
INNER JOIN Yak AS y ON y.Plant = s.Plant AND y.EmployeeID = s.EmployeeID
WHERE y.RowID = s.RowID - 1
)

SELECT Plant,
EmployeeID,
MIN(Date) AS Date_frm,
MAX(Date) AS Date_to,
MIN(Shift) AS Shift,
MIN(Code) AS Code,
DATEDIFF(DAY, MIN(Date), MAX(Date)) AS NofDays
FROM Yak
GROUP BY Plant,
EmployeeID,
Grp
ORDER BY Plant,
EmployeeID,
Grp[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Stumbler
Starting Member

23 Posts
Posted - 2007-09-14 : 07:38:26
When I copy paste solution into the query analyser I get the following error messages:

Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'WITH'.
Server: Msg 195, Level 15, State 1, Line 31
'ROW_NUMBER' is not a recognized function name.

Can you please help a newbie?

Hans
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-14 : 07:46:42
This is a SQL Server 2005 forum, so I assumed you noticed that.
Hence I gave a SQL Server 2005 solution.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-14 : 07:55:49
[code]-- Prepare sample data just to mimic your environment which I don't have access to
DECLARE @History TABLE (Plant CHAR(2), EmployeeID VARCHAR(5), Date DATETIME, Sequence INT, Code VARCHAR(4), Hours INT, Shift VARCHAR(1))

SET DATEFORMAT DMY

INSERT @History
SELECT 'AA', '82519', '22/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '23/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '24/01/2007', 1, '100', 800, '1' UNION ALL
SELECT 'AA', '82519', '25/01/2007', 1, '100', 800, '1' UNION ALL
SELECT 'AA', '82519', '26/01/2007', 1, '100', 800, '1' UNION ALL
SELECT 'AA', '82519', '27/01/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '28/01/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '29/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '30/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '31/01/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '1/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '2/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '3/02/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '4/02/2007', 1, '4283', 0, 'R' UNION ALL
SELECT 'AA', '82519', '5/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '6/02/2007', 1, '100', 800, '0' UNION ALL
SELECT 'AA', '82519', '7/02/2007', 1, '100', 800, '0'

-- Stage the data and from here is the code you want to run
CREATE TABLE #Stage
(
Plant CHAR(2),
EmployeeID VARCHAR(5),
Date DATETIME,
Code VARCHAR(4),
Shift VARCHAR(1),
Grp INT
)

INSERT #Stage
(
Plant,
EmployeeID,
Date,
Code,
Shift
)
SELECT Plant,
EmployeeID,
Date,
Code,
Shift
FROM @History

CREATE CLUSTERED INDEX IX_Stage ON #Stage (Plant, EmployeeID, Date)

DECLARE @Grp INT,
@Plant CHAR(2),
@EmployeeID VARCHAR(5),
@Code VARCHAR(4),
@Shift VARCHAR(1)

SELECT TOP 1 @Plant = Plant,
@EmployeeID = EmployeeID,
@Code = Code,
@Shift = Shift,
@Grp = 1
FROM #Stage
ORDER BY Plant,
EmployeeID,
Date

UPDATE #Stage
SET @Grp = Grp = CASE
WHEN @Plant = Plant AND @EmployeeID = EmployeeID AND @Code = Code AND @Shift = Shift THEN @Grp
ELSE @Grp + 1
END,
@Plant = Plant,
@EmployeeID = EmployeeID,
@Code = Code,
@Shift = Shift

-- Show the expected output
SELECT Plant,
EmployeeID,
MIN(Date) AS Date_frm,
MAX(Date) AS Date_to,
MIN(Shift) AS Shift,
MIN(Code) AS Code,
DATEDIFF(DAY, MIN(Date), MAX(Date)) AS NofDays
FROM #Stage
GROUP BY Plant,
EmployeeID,
Grp
ORDER BY Plant,
EmployeeID,
Grp

-- Do some cleanup
DROP TABLE #Stage[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Stumbler
Starting Member

23 Posts
Posted - 2007-09-14 : 07:57:10
My mistake.

Do I repost in other forum or are you willing to give a solution here for 2000?

Hans
Go to Top of Page

Stumbler
Starting Member

23 Posts
Posted - 2007-09-14 : 07:58:30
Ah you did already
Thanks heaps

Hans
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site