Get the date ranges for constant values.

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)

Get the date ranges for constant values.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-31 : 03:00:42

I have a table with date like this.

InstId --Date ----Readings
--1 ---10/12/2008 --10
--1 ---11/12/2008 --10
--1 ---12/12/2008 --10
--1 ---13/12/2008 --9
--1 ---14/12/2008 --10
--1 ---15/12/2008 --10
--1 ---16/12/2008 --10
--1 ---17/12/2008 --10
--2 ---05/03/2008 --8
--2 ---06/03/2008 --6
--2 ---07/03/2008 --8
--2 ---08/03/2008 --8
--2 ---09/03/2008 --8
--2 ---20/03/2008 --8

Guys I want to get the date ranges instrument wise for which the instrument readings are constant.

For example for instrument 1 the readings are constant i.e 10 from 10/12/2008
till 12/12/2008 & then again it is constant from 14/12/2008 till 17/12/2008.
Same goes for instrument id 2.It is constant from 07/03/2008 till 20/03/2008.
I need to get the output like this.

StartDate EndDate Readings
10/12/2008 12/12/2008 10
14/12/2008 17/12/2008 10
17/03/2008 20/03/2008 8

Thanks for any help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-31 : 09:20:10

do you want to display or disregard the readings where they only occured on one date? ie:

13/12/2008 13/12/2008 9

Your disired output says to disregard those but just wanted to confirm...

Be One with the Optimizer
TG

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-31 : 11:30:12

quote:
Originally posted by TG

do you want to display or disregard the readings where they only occured on one date? ie:

13/12/2008 13/12/2008 9

Your desired output says to disregard those but just wanted to confirm...

Be One with the Optimizer
TG

Thanks for the reply.
Yes the constant readings should be there for more than one day.The one day reading should be disregarded.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-31 : 12:33:33

Here's one way:


declare @t table (InstId int, Date datetime, Readings int)
set dateformat 'dmy'
insert @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8

;with rws (rn, InstID, Date, readings)
as
(
       select row_number() over (partition by InstID order by date)
              ,InstID
              ,Date
              ,readings
       from   @t
)
,rcv (rn, InstID, startDate, endDate, readings, grp)
as
(
       select rn
              ,InstID
              ,convert(datetime, null)
              ,Date
              ,readings
              ,1
       from   rws
       where  rn = 1
       union all
       select rws.rn
              ,rws.InstID
              ,case when rws.InstID = rcv.InstID and rws.readings = rcv.readings then rcv.enddate else null end
              ,rws.date
              ,rws.readings
              ,case when rws.InstID = rcv.InstID and rws.readings = rcv.readings then rcv.grp else rcv.grp+1 end
       from   rws
       join   rcv on rcv.rn+1 = rws.rn and rcv.InstID = rws.InstID
)
select InstID
       ,min(startDate) startDate
       ,max(enddate) enddate
       ,min(readings) readings
from rcv 
where  datediff(day, startDate, enddate) > 0
group by InstID
       ,grp
order by InstID
       ,grp


output:
InstID      startDate               enddate                 readings
----------- ----------------------- ----------------------- -----------
1           2008-12-10 00:00:00.000 2008-12-12 00:00:00.000 10
1           2008-12-14 00:00:00.000 2008-12-17 00:00:00.000 10
2           2008-03-07 00:00:00.000 2008-03-20 00:00:00.000 8

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 13:00:31

Another method:-

SELECT res.StartDate,res.EndDate,res.Readings
FROM
(
SELECT MIN(t.Date) AS StartDate,MAX(t.Date) AS EndDate,t.Readings
FROM Table t
CROSS APPLY (SELECT MIN(Date) AS MinChangeDate
             FROM Table
             WHERE Date >t.Date
             AND Readings <> t.Readings)b
GROUP BY t.InstId,t.Readings,b.MinChangeDate
)res
WHERE DATEDIFF(dd,res.StartDate,res.EndDate)>0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-31 : 18:26:04

I like the CROSS APPLY method. And the statement given works for the sample data but I think you need to add another criteria to the applied subquery in case InstIDs cover similar dates:


---------------------------------------
EDIT:
Here is the sample data I tested with:
declare @t table (InstId int, Date datetime, Readings int)
set dateformat 'dmy'
insert @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8 union all

select 3, '10/12/2008', 11 union all
select 3, '11/12/2008', 11 union all
select 3, '12/12/2008', 11 union all
select 3, '13/12/2008', 4 union all
select 3, '14/12/2008', 11 union all
select 3, '15/12/2008', 11 union all
select 3, '16/12/2008', 11 union all
select 3, '17/12/2008', 11
---------------------------------------

SELECT res.StartDate,res.EndDate,res.Readings
FROM
(
SELECT MIN(t.Date) AS StartDate,MAX(t.Date) AS EndDate,t.Readings
FROM @t t
CROSS APPLY (SELECT MIN(Date) AS MinChangeDate
             FROM @t
             WHERE InstID = t.InstID
              and    Date >t.Date
             AND Readings <> t.Readings)b
GROUP BY t.InstId,t.Readings,b.MinChangeDate
)res
WHERE DATEDIFF(dd,res.StartDate,res.EndDate)>0

Be One with the Optimizer
TG

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-01 : 01:19:18

TG & Vishakh thanks a ton for your help.Both of your solutions are giving the output exactly what I needed.I will be using vishaks solution.Thanks once again vishakh for giving such a good & clean solution.
TG it was good catch to check for the different instid having readings on same date.In 99% of cases it is possible.
Thank you very much.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-01 : 01:37:48

Guys I am causing a little bit more trouble to you.I will be very grateful if you can just give an insight on how you achieved the result.I tried to dig into the solution but could not understand it.I know about the new Apply clause in SQL 2005.But in this particular problem how it works I could not understand.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-01 : 02:54:43

quote:

Originally posted by TG

I like the CROSS APPLY method. And the statement given works for the sample data but I think you need to add another criteria to the applied subquery in case InstIDs cover similar dates:


---------------------------------------
EDIT:
Here is the sample data I tested with:
declare @t table (InstId int, Date datetime, Readings int)
set dateformat 'dmy'
insert @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8 union all

select 3, '10/12/2008', 11 union all
select 3, '11/12/2008', 11 union all
select 3, '12/12/2008', 11 union all
select 3, '13/12/2008', 4 union all
select 3, '14/12/2008', 11 union all
select 3, '15/12/2008', 11 union all
select 3, '16/12/2008', 11 union all
select 3, '17/12/2008', 11
---------------------------------------

SELECT res.StartDate,res.EndDate,res.Readings
FROM
(
SELECT MIN(t.Date) AS StartDate,MAX(t.Date) AS EndDate,t.Readings
FROM @t t
CROSS APPLY (SELECT MIN(Date) AS MinChangeDate
             FROM @t
             WHERE InstID = t.InstID
              and    Date >t.Date
             AND Readings <> t.Readings)b
GROUP BY t.InstId,t.Readings,b.MinChangeDate
)res
WHERE DATEDIFF(dd,res.StartDate,res.EndDate)>0

Be One with the Optimizer
TG

thats true TG. Thanks for the catch

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-01 : 03:23:57

quote:
Originally posted by ayamas

Guys I am causing a little bit more trouble to you.I will be very grateful if you can just give an insight on how you achieved the result.I tried to dig into the solution but could not understand it.I know about the new Apply clause in SQL 2005.But in this particular problem how it works I could not understand.
Thanks.

The inner query will return each row with the min date at which the reading changes for instid by menas of CROSS APPLY onto subquery. CROSS APPLY is used to get the min date for each record when reading changes for its inst id. Thus result for the query will be

InstId --Date ----Readings--MinChangeDate
--1 ---10/12/2008 --10--13/12/2008
--1 ---11/12/2008 --10--13/12/2008
--1 ---12/12/2008 --10--13/12/2008
--1 ---13/12/2008 --9--14/12/2008
--1 ---14/12/2008 --10--05/03/2008
--1 ---15/12/2008 --10--05/03/2008
--1 ---16/12/2008 --10--05/03/2008
...

Now group on InstId,Readings and MinChangeDate and take MIN nad max value of date. then we get

InstId--Readings--MinChange Date--Start--End
1--10--13/12/2008--10/12/2008--12/12/2008
1--9--14/12/2008--13/12/2008--13/12/2008

....
Now you want only ones that span more than 1 days which is what wherecondition does...so you will get final result as
Start--End--Reading
10/12/2008--12/12/2008--10
14/12/2008--17/12/2008--10
....

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-01 : 07:59:12

Thanks vishakh(King of Cross Apply) for your valuable feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources