datelist between two dates - 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

SQL Server 2012 Forums

Transact-SQL (2012)

datelist between two dates

Author

Topic

Jamuna
Starting Member

7 Posts

Posted - 2013-06-03 : 09:50:50

declare @Start VARCHAR(20) = '02/05/2013',
@End VARCHAR(20) = '10/05/2013'

;WITH DateList
AS
(
SELECT CONVERT(DATETIME,'02/05/2013',103) [Date]
UNION ALL
SELECT CONVERT(DATETIME,[Date],103) +1 FROM DateList WHERE [Date] <CONVERT(DATETIME,'10/05/2013',103)
)
SELECT [Date], CONVERT(VARCHAR,[Date],103) AS ListofDate, DATENAME(DW,[Date]) 'DayName'
,
COALESCE(SUM(DetailsTable.DetailQuantity),0) QTY

FROM DateList
LEFT JOIN DetailsTable
LEFT JOIN MasterTable ON DetailsTable.ID = MasterTable.ID on CONVERT(DATETIME,MasterTable.MDate,103)=CONVERT(DATETIME,[Date],103)

This query will list dates between a given date range,but in between some dates are missing,Can anybody help me displaying all dates between the daterange?I'm using SQL server 2012 .

My output is like this:
02/05/2013
03/05/2013
04/05/2013
05/05/2013
06/05/2013
08/05/2013
10/05/2013
.
.Two dates are missing after joining it with my mastertable

Thanks
Mary

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-03 : 10:45:27

How do we know if dates are missing? Can you post sample data (few records) in consumable format (in form of insert statments) and the desired ouput for that data?

Cheers
MIK

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-06-03 : 12:22:29

Your outer joins are confused.

Also:
1. Use a number table instead of recursion. (Google number/tally table)
2. Do not do needless casts.

Depending on your view of RIGHT JOINs vs nested JOINS:

either


DECLARE @Start datetime = '20130502', @End datetime = '20130510';

SELECT @Start + N.number AS [Date]
	,DATENAME(dw, @Start + N.number) AS [DayName]
	,COALESCE(SUM(D.DetailQuantity), 0) AS QTY
FROM MasterTable M
	JOIN DetailsTable D
		ON M.ID = D.ID
	RIGHT JOIN [master].dbo.spt_values N
		ON N.[type] = 'P'
			AND N.number <= DATEDIFF(d, @Start, @End)
			AND DATEADD(d, DATEDIFF(d, 0, M.MDate), 0) = @Start + N.number;


DECLARE @Start datetime = '20130502', @End datetime = '20130510';

SELECT @Start + N.number AS [Date]
	,DATENAME(dw, @Start + N.number) AS [DayName]
	,COALESCE(SUM(D.DetailQuantity), 0) AS QTY
FROM [master].dbo.spt_values N
	LEFT JOIN
	(
		MasterTable M
			JOIN DetailsTable D
				ON M.ID = D.ID
	)
		ON @Start + N.number = DATEADD(d, DATEDIFF(d, 0, M.MDate), 0)
WHERE  N.[type] = 'P'
	AND N.number <= DATEDIFF(d, @Start, @End);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 23:57:56

why do you need the convert logic in join? whats the datatype of MasterTable.MDate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources