December 22, 2011 at 7:27 am
Hi there...
I want to display all the days of the month in a SQL statement and then link that day up with data in my table. If there is no data for that day then it must display a null.
My Table looks like this.
DateTime | Count
01 NOV 2005 | 20
02 NOV 2005 | 28
05 NOV 2005 | 2
15 NOV 2006 | 29
I then want the result to look something like this when i pull the select Query (which i am not sure how it must look)
01 Nov 2005 | 20
02 Nov 2005 | 28
03 Nov 2005 | 0
04 Nov 2005 | 0
05 Nov 2005 | 2
06 Nov 2005 | 0
07 Nov 2005 | 0
08 Nov 2005 | 0
09 Nov 2005 | 0
10 Nov 2005 | 0
11 Nov 2005 | 0
12 Nov 2005 | 0
13 Nov 2005 | 0
14 Nov 2005 | 0
15 Nov 2005 | 29
all the way to the end of the month...
Please can you assist in this so that i can solve the report.
Thanks.
Why do people point to their wrists when asking for the time, but don't point to their crotch when they ask where the bathroom is?
December 22, 2011 at 7:44 am
Your reports will query only single month every time or are u planning to find all the distinct months in ur data and create a report ? You input to the query is going to be only one month, right?
December 22, 2011 at 7:57 am
Hi there..
For this kind of task you're going to need a dates table. This simply has every day from a predefined start date, to a predefined end date. So it might be 1901-01-01 to 2040-12-30 for instance. You can then do a left join to the date table to return each date.
something like:
CREATE TABLE #Orders ( ID INTEGER IDENTITY,
Orders INTEGER,
DateID INTEGER)
CREATE TABLE #Dim_Date (ID INTEGER IDENTITY,
Date DATETIME)
INSERT INTO #Orders
SELECT 10,1
UNION ALL SELECT 100,2
UNION ALL SELECT 200,2
UNION ALL SELECT 50,4
UNION ALL SELECT 2,4
INSERT INTO #Dim_Date
SELECT '2011-12-01'
UNION ALL SELECT '2011-12-02'
UNION ALL SELECT '2011-12-03'
UNION ALL SELECT '2011-12-04'
UNION ALL SELECT '2011-12-05'
SELECT d.[Date], ISNULL(SUM(o.Orders),0) AS SumOrders
FROM #Dim_Date d
LEFT JOIN #Orders o ON o.DateID = d.ID
GROUP BY d.[Date]
You can find how to create the table here: http://www.sqlservercentral.com/articles/Date+Manipulation/65195/
December 22, 2011 at 7:58 am
There are two easy ways to do this:
First (and usually best) is have a calendar table. Query that and either join it to the other table(s) or use a sub-query off it to get the data from the other table(s).
select Date,
(select count(*)
from dbo.Orders
where OrderDate = Calendar.Date) as QtyOrders
from dbo.Calendar
where Date between @StartDate and @EndDate
order by Date;
Second (works pretty well) is use a Numbers table to generate an on-the-fly calendar, and use that in place of a calendar table.
select DateAdd(day, Number, @StartDate) as Date
from dbo.Numbers
where Number between 0 and DateDiff(day, @StartDate, @EndDate)
order by Number;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 8:18 am
Check this out :
DECLARE @TempTable TABLE
( InvoiceDate DATETIME , Ct INT )
INSERT INTO @TempTable (InvoiceDate, Ct)
SELECT '01 NOV 2005' , 20
UNION ALL SELECT '02 NOV 2005' , 28
UNION ALL SELECT '05 NOV 2005' , 2
UNION ALL SELECT '15 NOV 2005' , 29
-- Input variable that carries the month for the report should be prepared
DECLARE @InputMonth DATETIME
SET @InputMonth = '01 NOV 2005'
-- The query to do this
--== Create a tally based Month table
; WITH MonthNumbers (N) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23
UNION ALL SELECT 24
UNION ALL SELECT 25
UNION ALL SELECT 26
UNION ALL SELECT 27
UNION ALL SELECT 28
UNION ALL SELECT 29
UNION ALL SELECT 30
),
DaysOfTheMonth ( Days ) AS
(
SELECT CrsAppOp.DaysOfTheMonth
FROM MonthNumbers Base
CROSS APPLY (SELECT DATEADD(DD,N,@InputMonth) )CrsAppOp (DaysOfTheMonth)
WHERE DATEPART(MM ,CrsAppOp.DaysOfTheMonth) = DATEPART(MM,@InputMonth)
)
SELECT CONVERT (VARCHAR(14),DotM.Days,106) [Days]
, ISNULL ( TT.Ct , 0) Ct
FROM @TempTable TT
RIGHT JOIN DaysOfTheMonth DotM
ON TT.InvoiceDate = DotM.Days
December 27, 2011 at 10:44 am
Thank you ColdCoffee.
I will make use of your solution.
😉
Why do people point to their wrists when asking for the time, but don't point to their crotch when they ask where the bathroom is?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply