Weekly Count - 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

General SQL Server Forums

New to SQL Server Programming

Weekly Count

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2010-02-16 : 16:02:18

Hi,

In the below query I am trying to get a weekly count. The week starts Mon -Sun
What the query is doing while calculating the count is it is taking one day before the start date and end date.

Eg: Result set:
start date end date Count
2/8/2010 2/14/2010 3786

This count in actual is from 2/7/2010 to 2/13/2010.
How do I correct this query so that it gives me the count
from 2/8 to 2/14.

Query

SET DATEFIRST 1 --(Monday - Sunday)
select convert(varchar(11),DATEADD(wk,DATEDIFF(wk,-1,date),0),101) start_date,
convert(varchar(11),dateadd(wk,datediff(wk,-1,date),6),101) end_date,
count(*)
FROM Eligible WITH (NOLOCK)
WHERE id = 20
and date >= '01/01/' + convert(varchar(4),
case when Month(getdate()) = 1 then (year(getdate())-1)
else year(getdate())end)
AND date < getdate()
group by convert(varchar(11),DATEADD(wk,DATEDIFF(wk,-1,date),0),101),
convert(varchar(11),dateadd(wk,datediff(wk,-1,date),6),101)
order by 1

Thanks,
Petronas

acbarberi
Starting Member

11 Posts

Posted - 2010-02-16 : 19:46:25

What? What are you trying to count? Days?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 23:37:55

are you looking at this?


select DATEADD(wk,DATEDIFF(wk,0,date),0) start_date,
dateadd(wk,datediff(wk,0,date),6) end_date,
count(*)
FROM Eligible WITH (NOLOCK)
WHERE id = 20
and date >= '01/01/' + convert(varchar(4),
case when Month(getdate()) = 1 then (year(getdate())-1) 
else year(getdate())end)
AND date < getdate()
group by DATEADD(wk,DATEDIFF(wk,0,date),0),
dateadd(wk,datediff(wk,0,date),6)
order by 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Petronas
Posting Yak Master

134 Posts

Posted - 2010-02-17 : 09:42:57

Thanks Visakh16..
what the query is doing is it is displaying the correct days as start date and end date for the week (monday -sunday) in the result set. But when it is counting for the count it is taking one day previous (Sunday-saturday) and displaying that count. I don't know how to fix it. Your help would be greatly appreciated.

Thanks much,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 09:47:54

quote:
Originally posted by Petronas

Thanks Visakh16..
what the query is doing is it is displaying the correct days as start date and end date for the week (monday -sunday) in the result set. But when it is counting for the count it is taking one day previous (Sunday-saturday) and displaying that count. I don't know how to fix it. Your help would be greatly appreciated.

Thanks much,
Petronas

whats your datefirst setting? i see you've set it as 1 then it should only consider start as Monday and end as Sunday

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Petronas
Posting Yak Master

134 Posts

Posted - 2010-02-17 : 09:54:51

Thanks Visakh16, even after commenting out out the Set Datefirst 1 I still get the same results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:03:43

can you show some sample data from your table and then explain the discrepancy in the count?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Petronas
Posting Yak Master

134 Posts

Posted - 2010-02-17 : 10:54:23

Here is the data from the tables:
The below is the count for each day:
2/1/2010 637
2/2/2010 586
2/3/2010 573
2/4/2010 535
2/5/2010 554
2/6/2010 541
2/7/2010 544
2/8/2010 543
2/9/2010 498
2/10/2010 563
2/11/2010 528
2/12/2010 584
2/13/2010 567
2/14/2010 581
2/15/2010 595

The query is pulling the below data:
12/28 1/03 1,837
1/04 1/10 4,683
1/11 1/17 4,973
1/18 1/24 4,189
1/25 1/31 4,108
2/01 2/07 4,177
2/08 2/14 3,827
2/15 2/21 1,690

If you check the number from 2/8-2/14 does not match will the daily count , it is showing the count from 2/7-2/13.

Thanks so much for your help,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:56:00

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Petronas
Posting Yak Master

134 Posts

Posted - 2010-02-17 : 11:27:56

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 11:37:29

[code]
SELECT start_date,end_date,COALESCE(t1.Cnt,0)
FROM
(select DATEADD(wk,DATEDIFF(wk,0,date),0) start_date,
dateadd(wk,datediff(wk,0,date),6) end_date
FROM Eligible WITH (NOLOCK)
WHERE id = 20
and date >= '01/01/' + convert(varchar(4),
case when Month(getdate()) = 1 then (year(getdate())-1)
else year(getdate())end)
AND date < getdate()
group by DATEADD(wk,DATEDIFF(wk,0,date),0),
dateadd(wk,datediff(wk,0,date),6)
)t
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM Eligible
WHERE date BETWEEN t.start_date
AND t.end_date
)t1
order by 1[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Petronas
Posting Yak Master

134 Posts

Posted - 2010-02-18 : 10:14:20

Hi Visakh16,

Thanks for your query. I tried it and it is giving me inflated results.
Below is the result set:

01/04/2010 01/10/2010 5732
01/11/2010 01/17/2010 6601
01/18/2010 01/24/2010 5282
01/25/2010 01/31/2010 5543
02/01/2010 02/07/2010 5436
02/08/2010 02/14/2010 5034
02/15/2010 02/21/2010 1721
12/28/2009 01/03/2010 800

Thanks for all your help. Appreciate it .
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 10:21:44

quote:
Originally posted by Petronas

Hi Visakh16,

Thanks for your query. I tried it and it is giving me inflated results.
Below is the result set:

01/04/2010 01/10/2010 5732
01/11/2010 01/17/2010 6601
01/18/2010 01/24/2010 5282
01/25/2010 01/31/2010 5543
02/01/2010 02/07/2010 5436
02/08/2010 02/14/2010 5034
02/15/2010 02/21/2010 1721
12/28/2009 01/03/2010 800

Thanks for all your help. Appreciate it .
Petronas

Sorry from now on if you need more help you need to post some sample data and then explain what you want. Unless I know what are your business rules I cant make out how to do calculations to give correct count for you.

See guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources