4

Sample Data from StartKPA table:

enter image description here

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):

enter image description 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!

1

Try this query. I was not able to check the query since I don't have data. If there is an error, please let me know.

SELECT DISTINCT BookInventoryDate FROM StartKPA

DECLARE @colsValues AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename(datesYouWantAsColumns)
         FROM yourTable
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

I'm getting unique dates assuming StartKPA is a table and BookInventoryDate is a field of that table. If'I'm wrong, please change it.

DECLARE @colsValues AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename(BookInventoryDate)
         FROM StartKPA
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max);

SET @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 ('+ @colsValues +')
                ) AS pvt
                ORDER BY Aantallen DESC';

EXECUTE(@query); 
  • Thanks for our input. I get the following error: Msg 102, Level 15, State 1, Line 13 Incorrect syntax near ','. – SqlUser May 2 '18 at 11:37
  • 1
    Thanks, working now :-)! I added WHERE BookInventoryDate > '20180429' though in the first declare statement – SqlUser May 2 '18 at 11:48
  • @SqlUser, does your first query work? All I'm doing is getting the all unique dates columns for that query. So, you don't have to enter it manually. Look at the FOR [BookInventoryDate] IN ('+ @colsValues +') code. – DxTx May 2 '18 at 11:49
1

You should change single quotes 'inside your @query by double single quotes ''.

Keep in mind you're using a recursive query, you must set a correct value for:

OPTION (MAXRECURSION XXXX)
create table StartKPA(BookInventoryDate datetime);
insert into StartKPA values ('20180101'), ('20180108');
GO
2 rows affected
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
option (maxrecursion 0);

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 '

select @query;
GO
SELECT Aantallen, BookinventoryDate, [2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04],[2018-01-05],[2018-01-06],[2018-01-07],[2018-01-08] 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 ([2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04],[2018-01-05],[2018-01-06],[2018-01-07],[2018-01-08])
        ) p 

db<>fiddle here

  • Thanks! But I'm getting this error message: Msg 530, Level 16, State 1, Line 4 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. – SqlUser May 2 '18 at 10:59
  • You're using a recursive query, have a look at OPTION (MAXRECURSION XXXX) I've added it to the answer. – McNets May 2 '18 at 11:03
  • Just having one last error I guess with the incorrect column name 'PivotDate' in the last query. – SqlUser May 2 '18 at 11:17
  • 2
    Instead of a picture of your current data, could you use my fiddle and add this data as INSERT statements? – McNets May 2 '18 at 11:31
  • Sorry, I'm not getting that fiddle – SqlUser May 2 '18 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.