April 27, 2010 at 6:36 pm
Hi, I have the following Table definition with sample data. In the following table, Customer Product & Date are key fields
Table One
Customer Product Date SALE
X A 01/01/2010 YES
X A 02/01/2010 YES
X A 03/01/2010 NO
X A 04/01/2010 NO
X A 05/01/2010 YES
X A 06/01/2010 NO
X A 07/01/2010 NO
X A 08/01/2010 NO
X A 09/01/2010 YES
X A 10/01/2010 YES
X A 11/01/2010 NO
X A 12/01/2010 YES
In the above table, I need to find the N or > N consecutive records where there was no sale, Sale value was 'NO' For example, if N is 2, the the result set would return the following
Customer Product Date SALE
X A 03/01/2010 NO
X A 04/01/2010 NO
X A 06/01/2010 NO
X A 07/01/2010 NO
X A 08/01/2010 NO
Can someone help me with a SQL query to get the desired results? I am using SQL Server 2005. I started to try using ROW_NUMBER() AND PARTITION clauses but no luck. Thanks for any help
April 27, 2010 at 7:08 pm
So you want to ignore November 1st because it was a singleton?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2010 at 7:22 pm
Yes, That's correct.
If the N takes a value 3, then the months March and April should be ignored too.
April 27, 2010 at 10:04 pm
Hi there,
Hope this helps. This ain't the best solution though. I'll try to find a better one if I have the time.
Declare @tbl Table
(
id int identity,
customer nvarchar(10),
product nvarchar(10),
[date] datetime,
sale nvarchar(10)
)
Insert into @tbl select 'X','A','01/01/1800','FAKE'
Insert into @tbl -- insert your data here, ive provided your sample
select 'X','A','01/01/2010','YES'
union all select 'X','A','02/01/2010','YES'
union all select 'X','A','03/01/2010','NO'
union all select 'X','A','04/01/2010','NO'
union all select 'X','A','05/01/2010','YES'
union all select 'X','A','06/01/2010','NO'
union all select 'X','A','07/01/2010','NO'
union all select 'X','A','08/01/2010','NO'
union all select 'X','A','09/01/2010','YES'
union all select 'X','A','10/01/2010','YES'
union all select 'X','A','11/01/2010','NO'
union all select 'X','A','12/01/2010','YES'
Insert into @tbl select 'X','A','01/01/1800','FAKE'
select * from @tbl
delete from @tbl
where id in (
select t2.id
from @tbl t1
inner join @tbl t2 on (t1.id=t2.id+1)
inner join @tbl t3 on (t1.id=t3.id+2)
where t1.sale<>'no'
and t2.sale='no'
and t3.sale<>'no')
or sale<>'no'
select * from @tbl
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
April 27, 2010 at 10:10 pm
Hi there,
Fix my last query, no need for a temp table anymore (just imagine the temp table here is your table)
Declare @tbl Table
(
id int identity,
customer nvarchar(10),
product nvarchar(10),
[date] datetime,
sale nvarchar(10)
)
Insert into @tbl
select 'X','A','01/01/2010','YES'
union all select 'X','A','02/01/2010','YES'
union all select 'X','A','03/01/2010','NO'
union all select 'X','A','04/01/2010','NO'
union all select 'X','A','05/01/2010','YES'
union all select 'X','A','06/01/2010','NO'
union all select 'X','A','07/01/2010','NO'
union all select 'X','A','08/01/2010','NO'
union all select 'X','A','09/01/2010','YES'
union all select 'X','A','10/01/2010','YES'
union all select 'X','A','11/01/2010','NO'
union all select 'X','A','12/01/2010','YES'
select * from @tbl
select * from @tbl
where id not in (
select t2.id
from @tbl t2
left outer join @tbl t1 on (t1.id-1=t2.id)
left outer join @tbl t3 on (t2.id=t3.id+1)
where ISNULL(t1.sale,'')<>'no'
and t2.sale='no'
and ISNULL(t3.sale,'')<>'no')
and sale='no'
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
April 27, 2010 at 10:12 pm
This is a miss post sorry. cant delete
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
April 28, 2010 at 2:44 am
Here's another parameterized version. Thanks to Quatrei.X for the test script.
Declare @tbl Table
(
customer nvarchar(10),
product nvarchar(10),
[date] datetime,
sale nvarchar(10),
primary key (customer, product, [date])
)
Insert into @tbl -- insert your data here, ive provided your sample
select 'X','A','20091201','NO'
union all select 'X','A','20100101','YES'
union all select 'X','A','20100201','YES'
union all select 'X','A','20100301','NO'
union all select 'X','A','20100401','NO'
union all select 'X','A','20100501','YES'
union all select 'X','A','20100601','NO'
union all select 'X','A','20100701','NO'
union all select 'X','A','20100801','NO'
union all select 'X','A','20100901','YES'
union all select 'X','A','20101001','YES'
union all select 'X','A','20101101','NO'
union all select 'X','A','20101201','YES'
union all select 'X','A','20110101','NO'
union all select 'X','A','20110201','NO'
union all select 'X','A','20110301','NO'
select * from @tbl
DECLARE @cnt INT
SET @cnt = 3
SELECT
*
FROM
(
SELECT
customer, product, [date], sale, groupID,
COUNT(*) OVER (PARTITION BY customer, product, sale, groupID) AS groupCnt
FROM
(
SELECT
customer, product, [date], sale,
ROW_NUMBER() OVER (PARTITION BY customer, product ORDER BY [date])
- ROW_NUMBER() OVER (PARTITION BY customer, product, sale ORDER BY [date]) AS groupID
FROM
@tbl
) T1
) T2
WHERE
sale = 'NO' AND groupCnt >= @cnt
ORDER BY
customer, product, [date]
April 28, 2010 at 9:02 am
Thanks so much Everyone.
The query suggested by Peter works flawless.
Thanks once again.
Javid
April 29, 2010 at 7:44 am
For anyone reading this thread in future that would prefer range results (as below) here's another method.
customer product from_date to_date
X A 2010-06-01 00:00:00.000 2010-08-01 00:00:00.000
X A 2011-01-01 00:00:00.000 2011-03-01 00:00:00.000
DECLARE @MinimumGroupCount INTEGER;
SET @MinimumGroupCount = 3;
WITH NoSale
AS (
SELECT *
FROM @tbl
WHERE sale = 'NO'
),
Starts
AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY T1.customer, T1.product ORDER BY T1.date),
*
FROM NoSale T1
WHERE NOT EXISTS
(
SELECT *
FROM NoSale T2
WHERE T2.customer = T1.customer
AND T2.product = T1.product
AND T2.date = DATEADD(MONTH, -1, T1.date)
)
),
Ends
AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY T1.customer, T1.product ORDER BY T1.date),
*
FROM NoSale T1
WHERE NOT EXISTS
(
SELECT *
FROM NoSale T2
WHERE T2.customer = T1.customer
AND T2.product = T1.product
AND T2.date = DATEADD(MONTH, +1, T1.date)
)
)
SELECT S.customer,
S.product,
from_date = S.date,
to_date = E.date
FROM Starts S
JOIN Ends E
ON E.rn = S.rn
AND E.customer = S.customer
AND E.product = S.product
WHERE DATEDIFF(MONTH, S.date, E.date) >= @MinimumGroupCount - 1
ORDER BY
S.customer,
S.product,
from_date;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 28, 2014 at 1:30 am
Peter Brinkhaus (4/28/2010)
Here's another parameterized version. Thanks to Quatrei.X for the test script.
...
Peter, your solution is brilliant! You're a d**n genius! It saved my day - thanks for your contribution! 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply