Consecutive drops - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Consecutive drops

Author

Topic

vinaynaran
Starting Member

9 Posts

Posted - 2011-06-03 : 08:13:03

Dear all,
I have data in sql table like below
CUSTNO Daily Sale Date Target
M0004013 0 03/05/2011 £154.42
M0004013 382.61 04/05/2011 £154.42
M0004013 66.4 05/05/2011 £154.42
M0004013 -96.46 06/05/2011 £154.42
M0004013 250.73 07/05/2011 £154.42
M0004013 187.14 09/05/2011 £154.42
M0004013 45.48 10/05/2011 £154.42
M0004013 446.85 11/05/2011 £154.42
M0004013 126.9 12/05/2011 £154.42
M0004013 -186.23 13/05/2011 £154.42
M0004013 63.52 14/05/2011 £154.42
M0004013 6.86 16/05/2011 £154.42
M0004013 90.49 17/05/2011 £154.42
M0004013 -17.07 18/05/2011 £154.42
M0004013 40.6 19/05/2011 £154.42
M0004013 51.48 20/05/2011 £154.42
M0004013 403.03 21/05/2011 £154.42
M0004013 183.62 23/05/2011 £154.42
M0004013 260.1 24/05/2011 £154.42
M0004013 518.02 25/05/2011 £154.42
M0004013 -217.55 26/05/2011 £154.42
M0004013 2.19 27/05/2011 £154.42
M0004013 240.59 28/05/2011 £154.42
M0004013 204.3 31/05/2011 £154.42

I want out put like below
(Output criteria should be a Daily Sale consecutively less for 3 days than Target)

CUSTNO Daily Sale Date Target
M0004013 -186.23 13/05/2011 £154.42
M0004013 63.52 14/05/2011 £154.42
M0004013 6.86 16/05/2011 £154.42
M0004013 90.49 17/05/2011 £154.42
M0004013 -17.07 18/05/2011 £154.42
M0004013 40.6 19/05/2011 £154.42

Please can you help me to build query in sql server 2008.

Thanks

Vinay

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 08:24:31

Why is 12/05/2011 not part of the result?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinaynaran
Starting Member

9 Posts

Posted - 2011-06-03 : 08:30:21

I want to compare always only 3days like 3rd to 5th then 6th to 9th then 10th to 12th and so on...

Vinay

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 08:33:27

Then why is 13/05/2011 there?

If you just wanted the consecutive entries then
with cte as
(
select *, seq = row_number() over (order by date)
from tbl
) ,
cte2 as
(
select d1=c1.date, d2=c2.date, d3=c3.date
from cte c1
join cte c2
on c1.seq = c2.seq + 1
and c1.sale < c1.target
and c2.sale < c2.target
join cte c3
on c2.seq = c3.seq + 1
and c3.sale < c3.target
)
select t.*
from tbl t
join cte2 c
on c.date = t.d1
or c.date = t.d2
or c.date = t.d3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinaynaran
Starting Member

9 Posts

Posted - 2011-06-03 : 09:26:27

Hi Nigel,

It's doesn't work.

On 13th(-186.23),14th(63.52) and 15th(6.86) consecutively less then Target(154) and same thing happened for next three days.
And I want only those record which consecutively three days less then target.

Thanks
Vinay

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 09:30:02

You don't have an entry for the 15th in your data. 6.86 is for the 16th

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinaynaran
Starting Member

9 Posts

Posted - 2011-06-03 : 09:52:35

Yes you are right. 15th was Sunday.
Vinay

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 10:10:22

declare @tbl table (date datetime, sale int, target int)
insert @tbl values
('20110503',1,154)
,('20110504',200,154)
,('20110505',1,154)
,('20110506',1,154)
,('20110507',200,154)
,('20110509',200,154)
,('20110510',1,154)
,('20110511',200,154)
,('20110512',1,154)
,('20110513',1,154)
,('20110514',1,154)
,('20110516',1,154)
,('20110517',1,154)
,('20110518',1,154)
,('20110519',1,154)
,('20110520',1,154)
,('20110521',200,154)
,('20110523',200,154)
,('20110524',200,154)
,('20110525',200,154)

;with cte as
(
select *, seq = row_number() over (order by date)
from @tbl
) ,
cte2 as
(
select d1=c1.date, d2=c2.date, d3=c3.date
from cte c1
join cte c2
on c1.seq = c2.seq + 1
and c1.sale < c1.target
and c2.sale < c2.target
join cte c3
on c2.seq = c3.seq + 1
and c3.sale < c3.target
)
select distinct t.*
from @tbl t
join cte2 c
on t.date = c.d1
or t.date = c.d2
or t.date = c.d3
order by date

result
date sale target
----------------------- ----------- -----------
2011-05-12 00:00:00.000 1 154
2011-05-13 00:00:00.000 1 154
2011-05-14 00:00:00.000 1 154
2011-05-16 00:00:00.000 1 154
2011-05-17 00:00:00.000 1 154
2011-05-18 00:00:00.000 1 154
2011-05-19 00:00:00.000 1 154
2011-05-20 00:00:00.000 1 154

I still don't know why you want 13th and not 12th. They both look like part of a sequence of 3 if you ignore sundays - neither if you don't.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinaynaran
Starting Member

9 Posts

Posted - 2011-06-03 : 11:37:41

Hi Nigel,
It's working but it's not give me what I want.
Actually I want to check first 3 consecutive days then next 3 days and so on...(see below remark)
in short I want to display records if the customer daily sales consecutive 3 days less then target.

CUSTNO Daily Sale Date Target Remark
M0004013 0 03/05/2011 154 Daily Sales not less than 154 for consecutive 3 days
M0004013 382.61 04/05/2011 154
M0004013 66.4 05/05/2011 154

M0004013 -96.46 06/05/2011 154 Daily Sales not less than 154 for consecutive 3 days
M0004013 250.73 07/05/2011 154
M0004013 187.14 09/05/2011 154

M0004013 45.48 10/05/2011 154 Daily Sales not less than 154 for consecutive 3 days
M0004013 446.85 11/05/2011 154
M0004013 126.9 12/05/2011 154

M0004013 -186.23 13/05/2011 154 Daily Sales less than 154 for consecutive 3 days
M0004013 63.52 14/05/2011 154
M0004013 6.86 16/05/2011 154

M0004013 90.49 17/05/2011 154 Daily Sales less than 154 for consecutive 3 days
M0004013 -17.07 18/05/2011 154
M0004013 40.6 19/05/2011 154

M0004013 51.48 20/05/2011 154 Daily Sales not less than 154 for consecutive 3 days
M0004013 403.03 21/05/2011 154
M0004013 183.62 23/05/2011 154

M0004013 260.1 24/05/2011 154 Daily Sales not less than 154 for consecutive 3 days
M0004013 518.02 25/05/2011 154
M0004013 -217.55 26/05/2011 154

M0004013 2.19 27/05/2011 154 Daily Sales not less than 154 for consecutive 3 days
M0004013 240.59 28/05/2011 154
M0004013 204.3 31/05/2011 154

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 11:55:21

So you want to group into partitions of 3 consecutive days starting with the first date and check the 3 dates and output all of the dates in the goup if they are all below target. Odd requirement but

declare @tbl table (date datetime, sale int, target int)
insert @tbl values
('20110503',1,154)
,('20110504',200,154)
,('20110505',1,154)
,('20110506',1,154)
,('20110507',200,154)
,('20110509',200,154)
,('20110510',1,154)
,('20110511',200,154)
,('20110512',1,154)
,('20110513',1,154)
,('20110514',1,154)
,('20110516',1,154)
,('20110517',1,154)
,('20110518',1,154)
,('20110519',1,154)
,('20110520',1,154)
,('20110521',200,154)
,('20110523',200,154)
,('20110524',200,154)
,('20110525',200,154)

;with cte as
(
select *, seq = row_number() over (order by date)
from @tbl
) ,
cte2 as
(
select *, grp = (seq-1)/3 from cte
)
select * from cte2
where grp in
(select grp
from cte2
where sale < target
group by grp
having COUNT(*) = 3
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinaynaran
Starting Member

9 Posts

Posted - 2011-06-03 : 12:35:49

It's perfect. Thanks you very much Nigel for your great help.

vinay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources