0

I want the "May 1" of invoice amount column to be dynamic based on the bcd.processeddate '5/1/2020 between '5/1/2021' A year worth of data so for example if I choose bcd.processeddate 5/15/2020 to 5/15/2021, the columns should say May 15 invoice amount, june 15 invoice amount and so on.

Can someone guide me in the right direction?

select 
       case when cagp.amb_memberid is not null then cagp.amb_memberid else case when cap.amb_memberid is not null then cap.amb_memberid else ca.amb_memberid end end as [ParentID],
       case when cagp.amb_memberid is not null then cagp.name else case when cap.amb_memberid is not null then cap.name else ca.name end end as [ParentName],
       bcd.MemberID, bcd.MemberStationCode, ca.name,
    
       sum(case when year(processeddate)=2020 and month(processeddate)=5 then (invoiceAmount) else 0 end) as "May 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=6 then (invoiceAmount) else 0 end) as "Jun 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=7 then (invoiceAmount) else 0 end) as "Jul 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=8 then (invoiceAmount) else 0 end) as "Aug 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=9 then (invoiceAmount) else 0 end) as "Sep 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=10 then (invoiceAmount) else 0 end) as "Oct 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=11 then (invoiceAmount) else 0 end) as "Nov 1 invoiceAmount",
       sum(case when year(processeddate)=2020 and month(processeddate)=12 then (invoiceAmount) else 0 end) as "Dec 1 invoiceAmount",
       sum(case when year(processeddate)=2021 and month(processeddate)=1 then (invoiceAmount) else 0 end) as "Jan 1 invoiceAmount",
       sum(case when year(processeddate)=2021 and month(processeddate)=2 then (invoiceAmount) else 0 end) as "Feb 1 invoiceAmount",
       sum(case when year(processeddate)=2021 and month(processeddate)=3 then (invoiceAmount) else 0 end) as "Mar 1 invoiceAmount",
       sum(case when year(processeddate)=2021 and month(processeddate)=4 then (invoiceAmount) else 0 end) as "Apr 1 invoiceAmount",
       sum(case when year(processeddate)=2021 and month(processeddate)=5 then (invoiceAmount) else 0 end) as "May 1 invoiceAmount"
from serviceconnection_Hist bcd 
left outer join CRM_Account ca
       on bcd.MemberID=ca.amb_memberid and ca.statecodename='Active' and ca.statecode=0 and ca.statuscode=1
left outer join CRM_Account cap on ca.parentaccountid=cap.accountid and cap.statecode=0 and cap.statuscode=1
left outer join CRM_Account cagp on cap.parentaccountid=cagp.accountid and cagp.statecode=0 and cagp.statuscode=1
where bcd.processeddate >= '5/1/2020' and bcd.processeddate< '5/1/2021' and MemberID<>'' and ca.statecode=0 and ca.statuscode=1
group by 
       case when cagp.amb_memberid is not null then cagp.amb_memberid else case when cap.amb_memberid is not null then cap.amb_memberid else ca.amb_memberid end end,
       case when cagp.amb_memberid is not null then cagp.name else case when cap.amb_memberid is not null then cap.name else ca.name end end,
       bcd.MemberID, bcd.MemberStationCode, ca.name
order by 1, 2, bcd.MemberStationCode, bcd.MemberID

enter image description here

Holger Just
  • 52,918
  • 14
  • 115
  • 123
Smartguy21
  • 41
  • 5
  • 1
    What exactly is your question? – Holger Just Jul 14 '21 at 17:51
  • Ok, I think you want to pivot your data dynamically based on process data which means your column names must be generated dynamically based on data. also the pivot functionality varies by RDBMS so we would need to know what platform you're using and what version. kinda like this: but thereis is already pivoted... https://stackoverflow.com/questions/2247751/dynamic-columns-sql-server-months-as-columns This is also know as Transform, or crosstab and it looks like it is dynamic in nature Based on image: it's MS SQL Server... – xQbert Jul 14 '21 at 19:32
  • https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query is a dynamic pivot, you just need to adjust for your query and handle the formatting of date on process date and adding your column label the way you want. in SQL Server. – xQbert Jul 14 '21 at 19:36
  • Holger Just, I want the date in my columns to be dynamic, right now i have them hardcoded – Smartguy21 Jul 14 '21 at 19:59
  • xQbert I am using Microssoft SQL server Management Studio, Version 15.0.18384.0 – Smartguy21 Jul 14 '21 at 20:00

0 Answers0