For a few days more than 0 query how to write?

The following table
Field name AA date
a 6 2013-10-13
b 2 2013-10-13
c 3 2013-10-13

a 2 2013-10-12
b -1 2013-10-12
c 1 2013-10-12

a 3 2013-10-11
b 3 2013-10-11
c -3 2013-10-11

a 0 2013-10-10
b 2 2013-10-10
c 3 2013-10-10

The results you want:
1, Only check for 3 consecutive days of more than 0 records
2, Only check for 2 consecutive days of more than 0 records
3, Only check for 1 consecutive days of more than 0 records

4, Query 2 consecutive days or more than 0 records


Thank you

Started by Luke at November 17, 2013 - 8:52 AM

Don't know is it right? It means
create table
 #A(Name varchar(10),AA int ,[date]datetime);
 insert #A
 SELECT 'a',6 ,'2013/10/13'
union all select  'b',2,'2013/10/13'
union all select  'c',3,'2013/10/13'

union all select  'a',2,'2013/10/12'
union all select  'b',-1,'2013/10/12'
union all select  'c',1,'2013/10/12'

union all select  'a',3,'2013/10/11'
union all select  'b',3,'2013/10/11'
union all select  'c',-3,'2013/10/11'

union all select  'a',0,'2013/10/10'
union all select  'b',2,'2013/10/10'
union all select  'c',3,'2013/10/10'

select * from #A
;with cte as (
select row_number() over(order by getdate())as rn,* from #A
)

select * from cte  a
where  (select AA from cte b where b.rn =a.rn -1 )>0 and (select AA from cte c where c.rn =a.rn -2 )>0 and a.AA>0

drop table #A

Posted by Hale at November 28, 2013 - 8:55 AM

WITH a1 ([name],[AA],[date]) AS
(
SELECT 'a',6,'2013-10-13' UNION ALL
SELECT 'b',2,'2013-10-13' UNION ALL
SELECT 'c',3,'2013-10-13' UNION ALL
SELECT 'a',2,'2013-10-12' UNION ALL
SELECT 'b',-1,'2013-10-12' UNION ALL
SELECT 'c',1,'2013-10-12' UNION ALL
SELECT 'a',3,'2013-10-11' UNION ALL
SELECT 'b',3,'2013-10-11' UNION ALL
SELECT 'c',-3,'2013-10-11' UNION ALL
SELECT 'a',0,'2013-10-10' UNION ALL
SELECT 'b',2,'2013-10-10' UNION ALL
SELECT 'c',3,'2013-10-10'
)
,a2 AS
(
SELECT *,DATEADD(dd,-(ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [date])), [date]) date2
FROM a1 
WHERE [AA]>0
)
,a3 AS
(
SELECT [name],date2
FROM a2
GROUP BY [name],date2
HAVING COUNT(*)=3--1, Only check for 3 consecutive days of more than 0 records
--HAVING COUNT(*)=2--2, Only check for 2 consecutive days of more than 0 records
--HAVING COUNT(*)=1--3, Only check for 1 consecutive days of more than 0 records
--HAVING COUNT(*)>=2--4, Query 2 consecutive days or more than 0 records
)
SELECT a2.[name],a2.[AA],A2.[date]
FROM a2
INNER JOIN a3 ON a2.[name]=a3.[name] AND a2.date2=a3.date2
ORDER BY a2.[name],A2.[date]

Posted by Abraham at December 12, 2013 - 9:24 AM

Can be replaced with VB language?

Posted by Luke at December 26, 2013 - 9:55 AM

Do not quite understand, for example, 3 days more than 0 records, refers to the 11, 12, 13 consecutive 3 days

Or just 11,11,11 this 3 days the same.

Posted by Ignatz at January 01, 2014 - 10:51 AM

If the above SQL to meet the requirements of your words, a stored procedure, VB direct call

Posted by Janet at January 12, 2014 - 10:57 AM

3 is greater than 0 of relative field AA

Posted by Luke at January 18, 2014 - 10:59 AM

Oh, I know that is greater than 0 is the field of AA ha, what I mean is continuous, is continuous 3 days is the value of No. 11, No. 12, No. 13, or No. 11, No. 11, No. 11, the same day 3

Posted by Ignatz at January 26, 2014 - 11:48 AM

Is the name of name is the same person, such as a for 3 consecutive days,
So for several days for the same person, do not know that there is no clear, ha ha

Posted by Luke at February 07, 2014 - 12:04 PM


create table
 #A(Name varchar(10),AA int ,[date]datetime);
 insert #A
 SELECT 'a',6 ,'2013/10/13'
union all select  'b',2,'2013/10/13'
union all select  'c',3,'2013/10/13'

union all select  'a',2,'2013/10/12'
union all select  'b',-1,'2013/10/12'
union all select  'c',1,'2013/10/12'

union all select  'a',3,'2013/10/11'
union all select  'b',3,'2013/10/11'
union all select  'c',-3,'2013/10/11'

union all select  'a',0,'2013/10/10'
union all select  'b',2,'2013/10/10'
union all select  'c',3,'2013/10/10'

select * from #A

;with cte as (
select row_number() Over (partition by name order by [date]) as RN,* from #A
)

select a.* from cte a 
cross apply ( select* from cte b
where a.Name =b.Name  and  a.rn =b.rn  
and (select AA from cte d where d.rn =b.rn -1 and d.Name =b.Name )>0 
and (select AA from cte e where e.rn =b.rn -2 and e.Name =b.Name  )>0 
and b.AA >0
)ab

drop table #A

Posted by Hale at February 09, 2014 - 12:29 PM