limit query by last 20 days - 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 2005 Forums

Transact-SQL (2005)

limit query by last 20 days

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-10-14 : 04:18:05

I have a query

select useridcv,count(cv) from anp where mydatestage2>getdate()-20 group by useridcv

Now I want to change this to count that last 20 days for each user and not to count a day that has 0 records as a day

so if user 1 has
14-2
13-0
12-1
11-5

when it counts the last 20 days for that user it will not count the day with 0 records and go back an extra day.

How can I do this in a query?

Kabila
Starting Member

33 Posts

Posted - 2009-10-14 : 04:42:26

select useridcv,count(cv) from anp where mydatestage2>getdate()-20 group by useridcv
having count(cv)>0

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-14 : 04:54:25

quote:
Originally posted by Kabila

select useridcv,count(cv) from anp where mydatestage2>getdate()-20 group by useridcv
having count(cv)>0

It will just exclude the records with count is zero in the final result. It will not go back an extra day in case it finds a record with zero count.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 04:56:10

[code]
select *
from
(
select useridcv, count(cv) as cnt,
r = dense_rank() over (partition by useridcv order by dateadd(day, datediff(day, 0, mydatestage2), 0) desc)
from anp
where cv is not null
group by useridcv
) a
where a.r <= 20
[/code]

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-10-14 : 04:56:40

but I need it to go back the extra day if there are no records for a day

is this possible in a query ?
otherwise maybe a stored procedure?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-14 : 12:55:24

quote:
Originally posted by esthera

but I need it to go back the extra day if there are no records for a day

is this possible in a query ?
otherwise maybe a stored procedure?

Only one day? Or do you need to keep going back until you find a day with a value for that User?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-10-14 : 14:03:35

I need it to go back till it finds 20 days for that user

I tried khtan's solution but got
Column 'anp.mydatestage2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 18:43:06

try


select useridcv, count(cv)
from
(
    select useridcv, cv,
           r = dense_rank() over (partition by useridcv order by dateadd(day, datediff(day, 0, mydatestage2), 0) desc)
    from   @anp 
    where  cv is not null
) a
where a.r <= 20
group by useridcv

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-10-15 : 02:36:37

thanks kthan - perfect
is there a simple to add that it also returs how many days its counting (which would be 20 unless that user had less then 20 days with data)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 03:15:32

yes. a.r is the number of days it is counting. Just include that in the select statement


select useridcv, count(cv), max(a.r)
. . . .

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-10-15 : 03:52:38

thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources