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.
| 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/
|
|
|
|
|
|
|
How many Friday in between given date