SQL Pivot table not returining expected results-CodePudding

This is the first time I have had to use a SQL pivot table, so I'm not very proficient in getting results with the method, my pivot table is not returning the results I expected and I don't know why...

Here is my instruction:

"Create a sql that will show the total number create [ddateCreated] of patients per branch for each month for 2020 - 2021. Display Required fields BranchName , Year , Jan , Feb, Mar, Apr, May , Jun , Jul ,Aug , Sept , Oct , Nov , Dec."

so I came up with the below query:

select * from (

  select datename(month, b.dDateCreated) as [Month] ,
  sName as BranchName ,
  datename(Year, b.dDateCreated) as [Year],
  p.ipkPatientID from Branch b
  inner join Patients p
  on p.ifkBranchID=b.ipkBranchID
  where (b.dDateCreated BETWEEN '2020-01-01 00:00:00.000'AND '2021-12-31 23:59:59.999')

  

  ) as Src
  pivot(
  
  count(ipkPatientID)
   for [Month] in ([Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sept],
[Oct],
[Nov],
[Dec])
  
  ) as Pivot_Table

Here are the results:

BranchName                   Year   Jan Feb Mar Apr May Jun Jul Aug Sept    Oct Nov Dec
Cdldttd dd Fhdftwt wndfplpgy 2020   0   0   0   0   0   0   0   0      0    0   0   0
Ddjdlppmdnt                  2020   0   0   0   0   14  0   0   0      0    0   0   0
Ddpn Cdhpnfp                 2020   0   0   0   0   0   0   0   0      0    0   0   0
dlmfdn Lpnw Lfchtdnwnhg      2020   0   0   0   0   0   0   0   0      0    0   0   0
dlmfdn Lpnw Mwffkdng         2020   0   0   0   0   0   0   0   0      0    0   0   0
Dnhwwnjflld                  2020   0   0   0   0   0   0   0   0      0    0   0   0
dthpwd                       2020   0   0   0   0   0   0   0   0      0    0   0   0
fnc and wttpcfwtdt           2020   0   0   0   0   5   0   0   0      0    0   0   0
Fwdhfd Gldn                  2020   0   0   0   0   0   0   0   0      0    0   0   0
Hdwhdwt pfffcd               2020   0   0   0   0   0   0   0   0      0    0   0   0
Hpmd jftft                   2020   0   0   0   0   91  0   0   0      0    0   0   0

I can't understand why it shows 0's everywhere? i have tried to refactor this query mulptiple times but I cant come right... Also note how there is little data in the Month of May, for some odd reason.

Look at the below query, which is the source of the pivot table:

select datename(month, b.dDateCreated) as [Month] ,
  sName as BranchName ,
  datename(Year, b.dDateCreated) as [Year],
  p.ipkPatientID from Branch b
  inner join Patients p
  on p.ifkBranchID=b.ipkBranchID
  where (b.dDateCreated BETWEEN '2020-01-01 00:00:00.000'AND '2021-12-31 23:59:59.999')

These results look correct?

and there is data for every month... So how would I count the patient id's for every month and display it?

CodePudding user response：

Pretty sure this will correct your issue

Select * 
 From (
      select left(datename(month, b.dDateCreated),3) as [Month] ,   -- Notice Jan,Feb,Mar
             sName as BranchName ,
             datename(Year, b.dDateCreated) as [Year],
             p.ipkPatientID 
       from  Branch b
       inner join Patients p on p.ifkBranchID=b.ipkBranchID
       where b.dDateCreated BETWEEN '2020-01-01 00:00:00.000'AND '2021-12-31 23:59:59.997'
  ) as Src
  Pivot( count(ipkPatientID) for [Month] in ([Jan],
                                             [Feb],
                                             [Mar],
                                             [Apr],
                                             [May],
                                             [Jun],
                                             [Jul],
                                             [Aug],
                                             [Sep],  -- Notice Sep not Sept
                                             [Oct],
                                             [Nov],
                                             [Dec] ) ) as Pivot_Table

CodePudding user response：

I would recommend pivoting with conditional aggregation rather than using the vendor-specific pivot query. I find that the pivot syntax is less intuitive ; it is also less flexible, and of course not easily portable across databases.

Using conditional aggregation, and starting from your existing working query, you would pivot like so:

select b.sName as BranchName, x.[Year],
    sum(case when x.[Month] = 'Jan' then 1 else 0 end) as Jan
    sum(case when x.[Month] = 'Feb' then 1 else 0 end) as Feb
    -- ... repeat for the following months...
    sum(case when x.[Month] = 'Dec' then 1 else 0 end) as Dec
from Branch b
inner join Patients p on p.ifkBranchID = b.ipkBranchID
cross apply ( values (datename(year, b.dDateCreated), datename(month, b.dDateCreated) ) d([Month], [Year])
where b.dDateCreated >= '2020-01-01' and b.dDateCreated < '2022-01-01'
group by b.ifkBranchID, b.sName, x.[Year]

Side notes:

filtering dates is often simpler than with half-open intervals than with between
you probably want to group by the primary key of the branch table (which I assumed is ifkBranchID) ; different branches may have the same name.
I used outer apply to compute the datenames just once rather than repeating the expressions all over the queqry
it is good practice to prefix all columns in the query with the table they belong to