How can i select all dates between date range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

How can i select all dates between date range?

Author

Topic

rahulpatel112
Starting Member

4 Posts

Posted - 2012-06-15 : 09:07:52

I have one SQL Table with 2 columns as below

Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL

Now There are 2 Records in Table

1-1-2012, 5
1-3-2012, 7

Output of Result should be as below if i give date range StartDate as 1-1-2012 and EndDate as 1-15-2012

1-1-2012 5
1-2-2012 0
1-3-2012 7
1-4-2012 0
1-5-2012 0
1-6-2012 0
.
.
.
1-15-2012 0

Means Query should return all the dates of given range with Quantity and if no entry in Table then 0 for Quantity.

How to Do it? Please suggest with Query

R.J.PATEL

Sandips
Starting Member

5 Posts

Posted - 2012-06-15 : 09:28:51

DECLARE @TBL_DATE TABLE
(
ProductionDate DATETIME,
Quantity INT
)

INSERT INTO @TBL_DATE
(
ProductionDate,
Quantity
)
SELECT '2012/06/06', 10 UNION
SELECT '2012/06/10', 9 UNION
SELECT '2012/06/15', 8 UNION
SELECT '2012/06/20', 25

DECLARE @MAXDATE AS DATE
DECLARE @MINDATE AS DATE

SELECT @MAXDATE = MAX(ProductionDate) , @MINDATE = MIN(ProductionDate)
FROM @TBL_DATE;

WITH CTE_DATE
AS
(
SELECT @MINDATE AS DATEVALUE
UNION ALL
SELECT DATEADD(DD, 1, DATEVALUE) FROM CTE_DATE
WHERE DATEADD(DD, 1, DATEVALUE) <= @MAXDATE
)

SELECT DATEVALUE,
CASE WHEN TBL_DATE.Quantity IS NULL
THEN 0
ELSE TBL_DATE.Quantity
END AS Quantity
FROM CTE_DATE
LEFT JOIN @TBL_DATE TBL_DATE
ON CAST(TBL_DATE.ProductionDate AS DATE) = CTE_DATE.DATEVALUE

“Normalize ’till it hurts, then denormalize
’till it works.”

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources