CODEWITHSUNDEEP
sqlsql-server
ASH
ASH

Reputation: 20302

Tyring to pivot based on Date field

I have 2 fields that I want to pull into a query, plus a deal_balance (Decimal), and AsOfDate (Date). I tried two different ways; neither worked but I think both are close. The only catch is that the AsOfDate is the last working day of each month. I'm not sure how to convert that into an Integer (1,2,etc) or an abbreviated month's name (Jan,Feb,etc.)

select Contact_ID, TB_Line,
  sum(case when [AsOfDate] = 1 then [DEAL_BALANCE] else 0 end) MonthJan,
  sum(case when [AsOfDate] = 2 then [DEAL_BALANCE] else 0 end) MonthFeb,
  sum(case when [AsOfDate] = 3 then [DEAL_BALANCE] else 0 end) MonthMar,  
  sum(case when [AsOfDate] = 4 then [DEAL_BALANCE] else 0 end) MonthApr,
  sum(case when [AsOfDate] = 5 then [DEAL_BALANCE] else 0 end) MonthMay,
  sum(case when [AsOfDate] = 6 then [DEAL_BALANCE] else 0 end) MonthJun,
  sum(case when [AsOfDate] = 7 then [DEAL_BALANCE] else 0 end) MonthJul,
  sum(case when [AsOfDate] = 8 then [DEAL_BALANCE] else 0 end) MonthAug,
  sum(case when [AsOfDate] = 9 then [DEAL_BALANCE] else 0 end) MonthSep,
  sum(case when [AsOfDate] = 10 then [DEAL_BALANCE] else 0 end) MonthOct,
  sum(case when [AsOfDate] = 11 then [DEAL_BALANCE] else 0 end) MonthNov,
  sum(case when [AsOfDate] = 12 then [DEAL_BALANCE] else 0 end) MonthDec
from [TBL_HIST]
group by Contact_ID, TB_Line;


SELECT *
FROM
(
  SELECT Contact_ID, TB_Line, AsOfDate, Deal_Balance
  FROM [TBL_HIST]
) src
pivot
(
  SUM(DEAL_BALANCE)
  for AsOfDate in (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
) piv;

I am using SQL Server 2008.

Upvotes: 0

Views: 40

Answers (2)

ASH
ASH

Reputation: 20302

I just got the other version working!!

SELECT *
FROM
(
  SELECT Contact_ID, TB_Line, Deal_Balance, DATENAME(Month,[AsOfDate]) AS TheDate
  FROM [TBL_HIST]
) AS P
PIVOT
(
  SUM(DEAL_BALANCE) for TheDate in (January, February, March, April, May, June, July, August, September, October, November, December)
) AS PV;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Just use the month() function:

select Contact_ID, TB_Line,
       sum(case when month([AsOfDate]) = 1 then [DEAL_BALANCE] else 0 end) MonthJan,
       sum(case when month([AsOfDate]0 = 2 then [DEAL_BALANCE] else 0 end) MonthFeb,
       . . .

Upvotes: 1

Related Questions