title: I am wanting 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
body: <p>I want the &quot;May 1&quot; of invoice amount column to be dynamic based on the <code>bcd.processeddate</code> '5/1/2020 between '5/1/2021'  A year worth of data so for example if I choose <code>bcd.processeddate</code> 5/15/2020 to 5/15/2021, the columns should say <code>May 15 invoice amount</code>, <code>june 15 invoice amount</code> and so on.</p>
  <p>Can someone guide me in the right direction?</p>
  <pre><code>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 &quot;May 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=6 then (invoiceAmount) else 0 end) as &quot;Jun 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=7 then (invoiceAmount) else 0 end) as &quot;Jul 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=8 then (invoiceAmount) else 0 end) as &quot;Aug 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=9 then (invoiceAmount) else 0 end) as &quot;Sep 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=10 then (invoiceAmount) else 0 end) as &quot;Oct 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=11 then (invoiceAmount) else 0 end) as &quot;Nov 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2020 and month(processeddate)=12 then (invoiceAmount) else 0 end) as &quot;Dec 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2021 and month(processeddate)=1 then (invoiceAmount) else 0 end) as &quot;Jan 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2021 and month(processeddate)=2 then (invoiceAmount) else 0 end) as &quot;Feb 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2021 and month(processeddate)=3 then (invoiceAmount) else 0 end) as &quot;Mar 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2021 and month(processeddate)=4 then (invoiceAmount) else 0 end) as &quot;Apr 1 invoiceAmount&quot;,
         sum(case when year(processeddate)=2021 and month(processeddate)=5 then (invoiceAmount) else 0 end) as &quot;May 1 invoiceAmount&quot;
  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 &gt;= '5/1/2020' and bcd.processeddate&lt; '5/1/2021' and MemberID&lt;&gt;'' 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
  </code></pre>
  <p><a href="https://i.stack.imgur.com/NXYIv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NXYIv.png" alt="enter image description here" /></a></p>