CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Thilina Akalanka
Thilina Akalanka

Reputation: 163

SQL Year summary starting from given month

I have the following SQL query which Display 12 months of summary report from the given date. Output shows starting from January to December for whatever the given date.
I want the outcome to start from the given date's month.

If the given date is '2016-05-01' I want the output to be like this

May 16 |June 16| July 16| ........... | Jan 17 | Feb 17 | March 17 | April 17 |

How can I achieve this?
Can some one suggest? 

SELECT Name,SUM(Amount) AS PremiumTot,TotType,
sum(case when month(Dates) =  1 then Tot else 0 end) Jan,
sum(case when month(Dates) =  2 then Tot else 0 end) Feb,
sum(case when month(Dates) =  3 then Tot else 0 end) March,
sum(case when month(Dates) =  4 then Tot else 0 end) April,
sum(case when month(Dates) =  5 then Tot else 0 end) May,
sum(case when month(Dates) =  6 then Tot else 0 end) June,
sum(case when month(Dates) =  7 then Tot else 0 end) July,
sum(case when month(Dates) =  8 then Tot else 0 end) Aug,
sum(case when month(Dates) =  9 then Tot else 0 end) Sep,
sum(case when month(Dates) =  10 then Tot else 0 end) Oct,
sum(case when month(Dates) =  11 then Tot else 0 end) Nov,
sum(case when month(Dates) =  12 then Tot else 0 end) Dece

FROM
(
  SELECT InvoiceMasterID,Dates ,Name,CompanyCommission AS Tot ,0 AS Expences,Amount,1 as TotType
  FROM CommissionView

  UNION ALL

SELECT InvoiceMasterID,Dates,Name, 0 AS Tot ,-AgentCommission AS Expences,Amount,2 as TotType
  FROM CommissionViewCredit
) a
  WHERE Dates between @fromDates AND Datesadd(yy,1,@fromDates)
  GROUP BY Name,TotType

Upvotes: 0

Views: 284

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

Seems, you want to pivot data. So, use PIVOT table!

If you want to create dynamic columns from given date, use CTE (Common Table Expressions)!

--declare variables for given date range
DECLARE @startDate DATE = '2016-05-01'
DECLARE @endDate DATE = DATEADD(mm,11,@startDate)
--declare variable to store months as: [month1], [month2], [etc.] 
DECLARE @Months VARCHAR(1000) = ''
--use cte to create range of dates
;WITH MyDates AS
(
    SELECT @startDate AS MyDate
    UNION ALL
    SELECT DATEADD(mm,1,MyDate) AS MyDate 
    FROM MyDates
    WHERE MyDate<@endDate 
)
SELECT @Months = STUFF((SELECT '],['  + CONVERT(VARCHAR(7), MyDate, 121)
                       FROM MyDates
                       FOR XML PATH('')), 1, 2, '') + ']'

--PRINT @Months: 
-- prints: [2016-05],[2016-06], ... ,[2017-04]

DECLARE @qry NVARCHAR(MAX) = ''

SET @qry = N'SELECT ' + @Months +
' FROM ( ' +
     ' SELECT CONVERT(VARCHAR(7), Dates, 121) AS MyDate, CompanyCommission AS Tot ' +
     'FROM CommissionView ' +
') AS DT ' +
'PIVOT (SUM(Tot) FOR MyDate IN(' + @Months + ')) AS PT'
EXEC (@qry)

For further information, please see:
Dynamic PIVOT
Pivots with dynamic columns
CAST and CONVERT

Good luck!

Upvotes: 3

Related Questions