SQL Server 2005 ''grouping'' problem (newbie)
-
Hi all,
I'm attempting to create a sql statement (if possible) that can convert some day (date) based data to date period (ie: from/to date) data.
Consider the following data:
declare @DateTable TABLE(Date datetime primary key, PlaySequenceId int);
declare @Result TABLE (
PlaySequenceId int primary key,
DateFrom datetime,
DateTo datetime);
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-01 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-02 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-03 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-04 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-05 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-06 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-07 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-08 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-09 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-10 00:00:00.000',8)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-11 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-12 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-13 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-14 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-15 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-16 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-17 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-18 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-19 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-20 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-21 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-23 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-24 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-25 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-26 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-27 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-28 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-29 00:00:00.000',2)
select * from @DateTable
I Want to convert the 'runs' of consecutive days for a 'PlaySequenceId' value into a single row in a result set (the @Result table) that shows 'fromDate','toDate','PlaySequenceId'. Dates will be unique, and a 'run' ends when either the 'PlaySequenceId' changes, or the there is a 'gap' in the dates. The above data should produce output like:
FromDate ToDate PlaySequenceId
2007-01-01 00:00:00.000 2007-01-08 00:00:00.000 9
2007-01-09 00:00:00.000 2007-01-09 00:00:00.000 2
2007-01-10 00:00:00.000 2007-01-10 00:00:00.000 8
2007-01-11 00:00:00.000 2007-01-15 00:00:00.000 10
2007-01-16 00:00:00.000 2007-01-21 00:00:00.000 2
2007-01-23 00:00:00.000 2007-01-29 00:00:00.000 2
(NB: The missing date '2007-01-22' causes 2 'runs' of PlaySequenceId value 2)
This seemed easy enough at first glance, but I've found myself 'going in circles'. I'm hoping someone can help.
cheers,
dave
-
Hi Dave,
I have played around with this today and I have found a solution.
Please not that the bigger your table gets you may need to do some optimization. But for now this is a woking solution:
SET NOCOUNT ON
declare @DateTable TABLE(ID INT IDENTITY(1,1) PRIMARY KEY, Date datetime, PlaySequenceId int);
declare @Result TABLE (
PlaySequenceId int ,
DateFrom datetime,
DateTo datetime);
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-01 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-02 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-03 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-04 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-05 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-06 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-07 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-08 00:00:00.000',9)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-09 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-10 00:00:00.000',8)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-11 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-12 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-13 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-14 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-15 00:00:00.000',10)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-16 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-17 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-18 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-19 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-20 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-21 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-23 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-24 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-25 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-26 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-27 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-28 00:00:00.000',2)
insert into @DateTable (Date, PlaySequenceId) VALUES ('2007-01-29 00:00:00.000',2)
DECLARE @VinLoop INT
DECLARE @VinLoopMax INT
DECLARE @VdtStartDate DATETIME
DECLARE @VdtDate DATETIME
DECLARE @VdtPrevDate DATETIME
DECLARE @VinPlayID INT
DECLARE @VinPreviousPlayID INT
DECLARE @VinCounter INT
SELECT @VinLoop = 1
,@VinLoopMax = MAX([ID]) + 1
,@VinPreviousPlayID = 0
,@VdtStartDate = '1900-01-01'
,@VdtPrevDate = '1900-01-01'
,@VinCounter = 0
FROM @DateTable
WHILE (@VinLoop <= @VinLoopMAX)
BEGIN
SELECT
@VinPlayID = [PlaySequenceId]
,@VdtDate = Date
,@VdtStartDate = CASE WHEN @VinLoop = 1 THEN @VdtDate ELSE @VdtStartDate END
FROM @DateTable
WHERE [ID] = @VinLoop
IF ((@VinPlayID != @VinPreviousPlayID) or (@VdtStartDate != DATEADD(d,-@VinCounter,@VdtDate))) AND (@VinLoop != 1)
BEGIN
INSERT INTO @Result(PlaySequenceId,DateFrom ,DateTo) VALUES (@VinPreviousPlayID,@VdtStartDate,@VdtPrevDate)
--OUT OF SEQUENCE OR NEW ID
SELECT @VinPreviousPlayID = @VinPlayID
,@VdtStartDate = @VdtDate
,@VdtPrevDate = @VdtDate
,@VinCounter = 1
END
ELSE
BEGIN
--*****IN SEQUENCE*****'
SELECT @VinPreviousPlayID = @VinPlayID
--,@VdtStartDate = @VdtDate
,@VdtPrevDate = @VdtDate
,@VinCounter = @VinCounter + 1
END
SET @VinLoop = @VinLoop + 1
END
select * from @Result
Hope this helps
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Thanks very much!
cheers,
dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply