How to insert Select Query Result into a PIVOT

Viewed 307

I m using SQL SERVER 2012.

Query:1

SELECT *
FROM (
    SELECT Insurance, ChargeValue, CreatedDate
    FROM dailychargesummary
    WHERE MonthName='June 2017'
) m
PIVOT (
    SUM(ChargeValue)
    FOR CreatedDate IN ([06/22/2017], [06/23/2017],[06/30/2017])
) n

Output of above query is looks like below:

enter image description here

Now I m hard coding all the dates of a month inside the Pivot Query such as 06/01/2017, 06/02/2017, etc., After searching in the Google, I got the following query to display all the dates of a given month number.

Query 2:

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))

Output looks like below:

enter image description here

Can anyone please guide me how to use the Query2 inside the pivot in Query1 to automate the dates.

1 Answers
Related