July 9, 2009 at 5:17 am
Hi,
I have a query with a StartDate and EndDate parameters. If I select 2009/07/01(startDate) and 2009/07/07(EndDate), this would be 7 Days(being days 1 - 7) but I would like to display 1 - 6, even if there is no details specified on a certain day. My result so far is(with the above date range) :
Date Txns_Successful
2009/07/0345
2009/07/0433
2009/07/0519
2009/07/0621
2009/07/0724
But would like it to be displayed like this:
Day Date Txns_Successful
0 2009/07/01 0
1 2009/07/02 0
2 2009/07/03 45
3 2009/07/04 33
4 2009/07/05 19
5 2009/07/06 21
6 2009/07/07 24
My Query is as follows:
SELECT (CAST (CAST (RIGHT ('0' + CAST(YEAR([DateTimeStamp]) AS VARCHAR(4)),4) + '/' +
RIGHT('0' + CAST((MONTH([DateTimeStamp])) AS VARCHAR(2)),2) + '/' +
RIGHT('0' + CAST(DAY([DateTimeStamp]) AS VARCHAR(2)),2) AS CHAR(10))AS VARCHAR(10))) as Date,
COUNT(*) as Txns_Successful
FROM DBNAME.dbo.TABLENAME_Main CU
INNER JOIN DBNAME.dbo.TABLENAME1 CNU on CU.FromMSISDN = CNU.MSISDN
WHERE ResultReason = 'Successful' and ([DateTimeStamp]
between '2009/07/01 00:00:00' AND '2009/07/07 23:59:59')
GROUP BY (CAST (CAST (RIGHT ('0' + CAST(YEAR([DateTimeStamp]) AS VARCHAR(4)),4) + '/' +
RIGHT('0' + CAST((MONTH([DateTimeStamp])) AS VARCHAR(2)),2) + '/' +
RIGHT('0' + CAST(DAY([DateTimeStamp]) AS VARCHAR(2)),2) AS CHAR(10))AS VARCHAR(10)))
Any assistance would be fantastic!!!
Thank you!
July 9, 2009 at 6:57 am
It's hard to say without really seing the structure of the tables and it's data. However, try using a LEFT OUTER JOIN on the table with the date.
Eli
July 9, 2009 at 8:41 am
You can use either a Numbers/Tally table or a Calendar Table
(Plenty of examples of both on this site)
Here is an example of how to approach the problem with an inline numbers table:
DECLARE @StartDate datetime
,@EndDate datetime
SELECT @StartDate = '20090701'
,@EndDate = '20090707'
IF @StartDate > @EndDate
SELECT @StartDate = @EndDate
,@EndDate = @StartDate
-- code to get Dates and DayNo
SELECT N.N - 1 AS DayNo
,@StartDate + N.N - 1 AS TxnsDate
FROM
(
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
) N (N)
WHERE N.N = @StartDate
AND DateTimeStamp < @EndDate + 1
GROUP BY DATEADD(d, DATEDIFF(d, 0, DateTimeStamp), 0)
-- now combine the tow to get the result
SELECT D1.DayNo, D1.TxnsDate
,COALESCE(D2.Txns_Successful, 0) AS Txns_Successful
FROM
(
SELECT N.N - 1 AS DayNo
,@StartDate + N.N - 1 AS TxnsDate
FROM
(
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
) N (N)
WHERE N.N = @StartDate
AND DateTimeStamp < @EndDate + 1
GROUP BY DATEADD(d, DATEDIFF(d, 0, DateTimeStamp), 0)
) D2
ON D1.TxnsDate = D2.TxnsDate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply