Sample Data from StartKPA table:
I wrote the following query:
SELECT *
FROM
(
SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) BETWEEN 70 AND 80 THEN '70-80'
WHEN (EndKPA*100) BETWEEN 65 AND 70 THEN '65-70'
WHEN (EndKPA*100) BETWEEN 62 AND 65 THEN '62-65'
WHEN (EndKPA*100) BETWEEN 60 AND 62 THEN '60-62'
ELSE '<60' END as Aantallen
FROM StartKPA
WHERE BookInventoryDate > '20180429'
) as Aantallen
PIVOT
(
COUNT(EndKpa)
FOR [BookInventoryDate] IN ([20180430], [20180501], [20180502])
) AS pvt
ORDER BY Aantallen DESC
With the following results (Sorry couldn't get in in a table here):
The problem is that I want dynamic dates. So every day a new date will be added, and the 20180502 isn't showed because there is no data. So I want the results in the above table, but everyday a new day will be added (and no zero results like above). I don't want to add a new date every day manually to the query.
There are some posts about it already, but I couldn't get it to work:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
;with cte (datelist, maxdate) as
(
select min(BookInventoryDate) datelist, max(BookinventoryDate) maxdate
from StartKPA
union all
select dateadd(dd, 1, datelist), maxdate
from cte
where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10),
datelist, 120))
from #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Aantallen, BookinventoryDate, ' + @cols + ' from
(
SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) BETWEEN 70 AND 80 THEN '70-80'
WHEN (EndKPA*100) BETWEEN 65 AND 70 THEN '65-70'
WHEN (EndKPA*100) BETWEEN 62 AND 65 THEN '62-65'
WHEN (EndKPA*100) BETWEEN 60 AND 62 THEN '60-62'
ELSE '<60' END as Aantallen
FROM #tempDates d
LEFT JOIN StartKPA kpa on d.datelist=kpa.bookinventorydate
WHERE BookInventoryDate > '20180429'
) x
pivot
(
count(EndKpa)
for PivotDate in (' + @cols + ')
) p '
execute(@query)
I got a few error messages, tried to change things but I don't understand it enough to get it working.
Can someone help? Thanks!