Pivot table not displaying date values

simflex1 · November 29, 2023, 6:54pm

Hi everyone,

The code below is not displaying all the values in pivot table.

For instance, we are trying the year or years an individual has submitted his or here report.

Everything displays the values correctly except the value for dateCreated field is all NULL.

Any ideas what could be wrong?

Thanks in advance

ALTER PROCEDURE [dbo].[uspGetPivotedData]

AS
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy')) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT employeeName,empTitle,email, ' + @cols + ' from 
            (
                select employeeName
                    , empTitle
,email
                    , dateCreated,sourceincome
                from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
                inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
           ) x
            pivot 
            (
                 max(sourceincome)
                for dateCreated in (' + @cols + ')
            ) p ORDER BY ' + @cols + ' DESC '

EXECUTE sp_executesql @query

SqlHippo · November 30, 2023, 5:09pm

select employeeName
    ,  empTitle
    ,  email
    ,  dateCreated
    ,  sourceincome
from       SourceDetails 
inner join Employees     on SourceDetails.employeeID = Employees.EmployeeID
inner join DateDetails   on SourceDetails.employeeID = DateDetails.employeeID

Without the PIVOT, does this query produce values in the dateCreated and sourceIncome columns?

SqlHippo · November 30, 2023, 5:13pm

Ah, here's the problem. You're formatting the dateCreated as only the year for @cols, but not doing the same thing in the query.

for FORMAT(dateCreated, 'yyyy') in (' + @cols + ')

SqlHippo · November 30, 2023, 5:15pm

Also, I don't think this is going to do what you're hoping it will do.

`ORDER BY ' + @cols + ' DESC '`

simflex1 · November 30, 2023, 5:40pm

First of all, thank you so much for your response.

I checked a few times earlier but no response.

So, I was coming to update the post with more info hoping that would help, then I saw your response.

Here is sample data that gets displayed when we run the code posted:

employeeName	empTitle	email	2018	2019	2022	2023
Jane Doe	Senior Assistant Attorney	jdoe@hotmail.com	NULL	NULL	NULL	NULL
Karen Wise	Internal Auditor	kwise@yahoo.com	NULL	NULL	NULL	NULL
Angel Marist	Director, Library	Amarist@gmail.com	NULL	NULL	NULL	NULL
Kevin Wiseman	Library Director	kwiseman@yahoo.com	NULL	NULL	NULL	NULL
Major Bates	Director	mbates@bing.com	NULL	NULL	NULL	NULL
May Baker	PRINCIPAL AUDITOR	mbaker@outlook.com	NULL	NULL	NULL	NULL

As you can see, there are values for EmployeeName, title and email address but the value of dateCreated is NULL and that's the issue.

Yes, I agree, the Order by near the bottom is actually useless and I should have removed it before posting.

Sorry about that.

simflex1 · November 30, 2023, 6:25pm

Sorry again @SqlHippo ,
Yes, it produces pretty much all the date values dating back to 2018 when this apply went LIVE.:

dateCreated
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/16/2018
3/17/2018
3/18/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/19/2018
3/20/2018
3/20/2018
3/20/2018
3/21/2018
3/22/2018
3/26/2018
3/26/2018
3/26/2018
3/27/2018
3/28/2018
3/28/2018
3/29/2018
3/29/2018
3/30/2018
3/30/2018
3/30/2018
4/2/2018
4/2/2018
4/2/2018
4/2/2018
4/3/2018
4/3/2018
4/3/2018
4/3/2018
4/5/2018
4/6/2018
4/6/2018
4/9/2018
4/10/2018
4/12/2018
4/17/2018
4/17/2018
4/19/2018
4/19/2018
4/19/2018
4/19/2018
4/19/2018
4/20/2018
4/20/2018
4/20/2018
4/20/2018
4/20/2018
4/21/2018
4/23/2018
4/23/2018
4/23/2018
4/23/2018
4/23/2018
4/25/2018
4/26/2018
4/27/2018
4/27/2018
4/27/2018
4/28/2018
4/28/2018
4/30/2018
4/30/2018
4/30/2018
4/30/2018
5/1/2018
5/2/2018
5/3/2018
5/3/2018
5/3/2018
5/3/2018
5/3/2018
5/3/2018
5/3/2018
5/4/2018
5/4/2018
5/4/2018
5/4/2018
5/4/2018
5/4/2018
5/4/2018
5/8/2018
5/10/2018
5/11/2018
5/15/2018
5/15/2018
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/21/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/22/2019
3/23/2019
3/25/2019
3/25/2019
3/25/2019
3/25/2019
3/25/2019
3/25/2019
3/25/2019
3/25/2019
3/25/2019
3/26/2019
3/26/2019
3/27/2019
4/3/2019
4/3/2019
4/3/2019
4/8/2019
4/8/2019
4/10/2019
4/11/2019
4/15/2019
4/15/2019
4/15/2019
4/15/2019
4/19/2019
4/21/2019
4/21/2019
4/21/2019
4/21/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/22/2019
4/23/2019
4/23/2019
4/23/2019
4/23/2019
4/23/2019
4/23/2019
4/23/2019
4/23/2019
4/24/2019
4/24/2019
4/24/2019
4/24/2019
4/24/2019
4/25/2019
4/26/2019
4/26/2019
4/26/2019
4/28/2019
4/28/2019
4/28/2019
4/29/2019
4/29/2019
4/29/2019
4/29/2019
4/29/2019
4/29/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
4/30/2019
5/1/2019
5/2/2019
5/2/2019
5/2/2019
5/3/2019
5/6/2019
4/15/2022
3/15/2022
8/31/2023
9/1/2023
9/1/2023
9/14/2023
9/18/2023
4/19/2022
8/31/2023
8/31/2023
9/1/2023
9/1/2023
9/5/2023
2/1/2023
9/1/2023
9/1/2023
9/17/2023
9/1/2023

simflex1 · November 30, 2023, 6:35pm

Well, I don't understand this at all.

One is used as column header
FORMAT(dateCreated, 'yyyy') in (' + @cols + ')

and the other is used as values. Should it really matter?

If yes, any ideas how to work around it?

I am going to try and format the date field in query to yyyy to see it makes any difference.

SqlHippo · December 1, 2023, 1:00am

Change this line:

to this:

for FORMAT(dateCreated, 'yyyy') in (' + @cols + ')

I haven't tested it, but the point of my comment is that you are formatting dateCreated as YYYY when you build the @cols string. You ALSO need to format it in the pivot definition or you are effectively saying for '2019-04-30' in ([2018], [2019], [2020], etc...) when what you want is for '2019' in ([2018], [2019], [2020], etc...).

simflex1 · December 1, 2023, 2:32am

Thank you again @SqlHippo for your help.

I tried your suggestion but got an error that says, 'incorrect syntax near '(' and points to line 23 and line 23 starts with the select employeename... right below the "("
(
select employeeName <-- line 23 starts here.
, empTitle

However, I think the error has to do with this line:
for Format(dateCreated , 'yyyy') in (' + @cols + ')
The error is gone without this line.

Any ideas how it could be formatted to work and without the error?

I am still playing around and doing some googling to see what I can find to help.

Thanks again for your assistance.

SqlHippo · December 1, 2023, 3:02pm

The other place you can change it is here:

Replace dateCreated there with FORMAT(dateCreated, 'YYYY') AS yearCreated
Then change for dateCreated in (' + @cols + ') to for yearCreated in (' + @cols + ')

simflex1 · December 1, 2023, 4:01pm

Oh my goodness, you are so creative!

I have not made the change yet but that solutions looks glorious.

Be back with update.

Thanks so much.

simflex1 · December 1, 2023, 4:24pm

Ok, there are still some issues to work out.

First, this -> FORMAT(dateCreated, 'YYYY') AS yearCreated still did not work; giving similar error.

I guess is because this code is dynamic in nature and for the most part, beings and ends with single quote (').
As a result, any item with single quotes within the single quotes generates an error.

To work around that, I changed it to:

YEAR(dateCreated) AS yearCreated

Because of that, the error went away and the values displayed.

However, the @cols var is still not producing expected results.

It is now producing either NULL or 0 (zero).

Examining the results further, it shows that if a record, say Jane Doe, did not submit any information in 2022, it displays NULL and that is find.

However, if Jane Doe submitted a record in 2022, instead of showing the date, it shows 0.
So, not sure where else to look for solutions.

SqlHippo · December 1, 2023, 4:45pm

If you use https://dbfiddle.uk, create a sample table, fill it with a few rows of sample data, and run the query against it, then you can share the link here and we can work on resolving it. Otherwise, it will take a very long time to dial in just the right specific fixes your code needs.

simflex1 · December 1, 2023, 6:21pm

Ok,
let me work on that immediately.

simflex1 · December 1, 2023, 7:09pm

Sorry, I realize I am a sqlZebra, not at the Hippo level and that fiddle is not very intuitive.

I have a time finding where and how to do anything there.

I could provide the scripts here ( create table and insert data scripts) and you can just load them and work with code I posted.

Please let me know if this is an adequate alternative.

Thank you.

SqlHippo · December 1, 2023, 7:24pm

You can, but using dbfiddle.uk is pretty easy.

simflex1 · December 1, 2023, 8:24pm

Ok, you are right it is really easy. I just didn't know how it worked until your example.

I have created the tables and inserted rows, just for 2 years only (2018 and 2019).

I think that if it works here, it should work for more years.

There is just one problem though.

When inserting values into the dateCreated field, I had something like this:
'2018-04-19 10 12-29-133' but kept getting an error that:
# The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I had to change the data type to nvarchar(max) and it inserted successfully.
I had wanted to see I made an error in inserting the dates but it inserted and I had no way of know what it looked like.

Please let me know if I had to reinsert data into the dateDetails table.

Thank you soooo much for your time and assistance.

SqlHippo · December 1, 2023, 9:18pm

Share the dbfiddle link.

simflex1 · December 1, 2023, 9:41pm

Oh duh!

My inexperience using fiddle (from anywhere) clearly shows.

I created the tables, inserted the records and closed the screen thinking there is a way you find out.

SIGH!

I will have to redo it, perhaps on Monday.

I am pretty spent today.

So sorry; I didn't know.

simflex1 · December 4, 2023, 6:59pm

hi SqlHippo,

First my most sincere apologies for wasting your time. I FOUND the problem.

it was there all along but I did not see it.

At the time I was developing this stored procedure, one of the requirements was for users to also disclose their annual salaries (sounceincome).

This requirement was later dropped earlier this year but I completed forgot to update the stored proc till it is needed now.

I had to change from:

            pivot 
            (
                 max(sourceincome)  <--this
                for yearCreated in (' + @cols + ')
            ) p '

to:

        pivot 
        (
             max(yearCreated) <-- this
            for yearCreated in (' + @cols + ')
        ) p ORDER BY ' + @cols + ' DESC '

Now, it displays the correct year value for a given year column.

I am truly sorry for sending you on a goose chase.

I still need one additional help.

the values display as:

2018    2019     2022    2023
2018    2019     NULL    NULL
NULL    NULL     NULL    2023

As you can see from the sample above, there are two results for each year.

In 2018, there are two results, 2018 and NULL, same with 2019.
In 2022, the two results are NULL and in 2023, it was NULL and 2023.

My last question about this hopefully, is there a way to use a CASE statement or IF statement on the pivot to replace actual value with the value 'Submitted" and if the value is NULL, replace NULL with 'No Data"?

Is this possible?