How many Friday in between given date

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

Transact-SQL (2000)

How many Friday in between given date

Author

Topic

great_mamun
Starting Member

14 Posts

Posted - 2010-03-13 : 23:47:54

Dear All,
I have the following sql query,
"SELECT XDATE, DAY_NAME = COUNT(DATENAME(DW, XDATE)) FROM TABLE
WHERE XDATE >= '1/1/2009' AND XDATE <= '1/31/2009' GROUP BY XDATE ORDER BY XDATE"

It will return the following result,
XDATE DAY_NAME
-----------------------------------
2009-01-01 Thursday
2009-01-02 Friday
2009-01-03 Saturday
There is no data on 2009-01-04
2009-01-05 Monday
2009-01-06 Tuesday
.
.
.
2009-01-20 Tuesday
2009-01-21 Wednesday
There is no data on 2009-01-22 &
There is no data on 2009-01-23 &
2009-01-24 Saturday
2009-01-25 Sunday
.
.

But I want the all the dates from 1st January to 31st January whether xdate is present or not.

Please help me regarding above matters

Best Regards,
Abdullah Al Mamun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-14 : 00:26:39

use a CALENDAR table or F_TABLE_DATE and LEFT JOIN to your TABLE


SELECT d.[DATE], DAY_NAME = COUNT(DATENAME(DW, XDATE])) 
FROM   F_TABLE_DATE('20090101', '20090131') d
       LEFT JOIN TABLE t  on d.[DATE] = t.[XDATE]
WHERE  XDATE >= '1/1/2009' AND XDATE <= '1/31/2009' 
GROUP BY d.[DATE] 
ORDER BY d.[DATE]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-14 : 00:31:17

you need to have a calendar table for that. If you don't have it already you need to create one with all date

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-14 : 00:31:44

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources