CODEWITHSUNDEEP
sql-servert-sqlpivot
General Chad
General Chad

Reputation: 465

How can I use PIVOT to simplify or modernize this SQL statement?

Below I have as SQL statement (that is actually generated dynamically) where I create 5 columns. The first is the client name, the second, third and fourth are the count of records for each month and the 5th is the difference between the most recent month and the previous month.

SELECT ClientName AS 'Reporting',  
    (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate='20170101') 
  AS '20170101', 
    (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate='20170201') 
  AS '20170201', 
    (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate='20170301') 
  AS '20170301', 
       (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate='20170301')
      -(SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate='20170201') 
  AS 'Difference' 

from FRU -- Removed: left outer join UMP on FRU.UnitID=UMP.UnitID 
  group by FRU.ClientName, FRU.ClientID 
  order by ClientName

This seems like a great time to practice my PIVOT skills but I don't have any. Can you convert this to use PIVOT so I can learn from you?

For added amaze-value, here's the actual SQL that I use to generate the above statement. If you can convert this, I will owe you one ambiguously unidentifiable item or service.

I use @coreSQL because I use that portion several more times with slight modifications to @qry.

    DECLARE @qry nvarchar(max)

  --In modern SQL I use format(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0),'yyyyMMdd')
  --In crappy old 2008 SQL:
  DECLARE @OneMonth varchar(8) = replace(convert(varchar, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0), 111), '/', '')
  DECLARE @TwoMonths varchar(8) = replace(convert(varchar, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0), 111), '/', '')
  DECLARE @ThreeMonths varchar(8) = replace(convert(varchar, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0), 111), '/', '')
  DECLARE @coreSQL varchar(max)

  SET @coreSQL = ' (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate=''' + @ThreeMonths + ''') AS ''' + @ThreeMonths + ''','
  SET @coreSQL = @coreSQL + ' (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate=''' + @TwoMonths + ''') AS ''' + @TwoMonths + ''','
  SET @coreSQL = @coreSQL + ' (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate=''' + @OneMonth + ''') AS ''' + @OneMonth + ''','
  SET @coreSQL = @coreSQL + ' (SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate=''' + @OneMonth + ''')-(SELECT count(UMP.INDX) from UMP where ClientID=FRU.ClientID and LogActivityDate=''' + @TwoMonths + ''') AS ''Difference'''

  -- Raw results
  SET @qry = 'SELECT ClientName AS ''Reporting'', '
  SET @qry = @qry+@coreSQL
  SET @qry = @qry + ' from FRU group by FRU.ClientName, FRU.ClientID order by ClientName'

  EXECUTE sp_Executesql @qry

Upvotes: 0

Views: 73

Answers (3)

Paurian
Paurian

Reputation: 1402

SELECT
  ClientName,
  [20170101], [20170201], [20170301], [20170301] - [20170201] AS 'Difference'
FROM
  (
    SELECT FRU.ClientName, UMP.LogActivityDate, COUNT(1) AS Counted
    FROM FRU
    FULL JOIN UMP
      ON FRU.UnitID = UMP.UnitID AND
         FRU.ClientID = UMP.ClientID
    GROUP BY
      ClientName, LogActivityDate
  ) AS SourceTable
PIVOT
  (
    SUM(Counted)
    FOR LogActivityDate IN ([20170101], [20170201], [20170301])
  ) AS PivotTable

Upvotes: 1

Stéphane CLEMENT
Stéphane CLEMENT

Reputation: 372

PIVO is not needed :

SELECT
  ClientName AS 'Reporting',  
  SUM(CASE WHEN UMP.LogActivityDate='20170101' THEN 1 ELSE 0 END) AS '20170101', 
  SUM(CASE WHEN UMP.LogActivityDate='20170102' THEN 1 ELSE 0 END) AS '20170102', 
  SUM(CASE WHEN UMP.LogActivityDate='20170103' THEN 1 ELSE 0 END) AS '20170103', 
  SUM(CASE WHEN UMP.LogActivityDate='20170103' THEN 1 WHEN UMP.LogActivityDate='20170103' THEN -1 ELSE 0 END) AS 'Difference', 
  from FRU
  left outer join UMP on (FRU.UnitID=UMP.UnitID AND ClientID=FRU.ClientID)
  group by FRU.ClientName, FRU.ClientID 
  order by ClientName

Hope it will helps you...

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Your outer query only gets you the client name. The join with UMP seems completely superfluous.

I don't see how PIVOT can help here, by the way. I'd use conditional aggregation:

select 
  fru.clientname as "Reporting",
  coalesce(u.cnt20170101, 0) as "20170101",
  coalesce(u.cnt20170201, 0) as "20170201",
  coalesce(u.cnt20170301, 0) as "20170301",
  coalesce(u.cnt20170301, 0) - coalesce(u.cnt20170201, 0) as "difference"
from fru
left join
(
  select
    clientid, 
    count(case when logactivitydate = '20170101' then 1 end) as cnt20170101,
    count(case when logactivitydate = '20170201' then 1 end) as cnt20170201,
    count(case when logactivitydate = '20170301' then 1 end) as cnt20170301
  from ump
  group by clientid
) u on u.clientid = fru.clientid;

BTW: Single quotes are for string literals, double quotes are for alias names. (But as this is SQL Server you may have to replace them by non-standard brackets, e.g. as [Reporting].

Upvotes: 0

Related Questions