Grouping By Date When Used In Case

I’ve got a working query where I can export to Excel and pivot myself to not be grouped by the fldTrancationDateTime column and that is working fine as a short term solution.

Is the only option here to use SQL pivot so I can only group by the Product and have each date range populated? Instead of one row for each date? Or am I going about coding this incorrectly?

declare @Date Date
set @Date = GETDATE()
print(@Date)
select T.fldProductCode, P.fldProductDescription, count(fldTransactionID), 
case when datediff(d,cast(T.fldTransactionDateTime as date),@Date ) <= 7 
    then count(fldTransactionID) else 0 end as [0-7 Days],
case when datediff(d,cast(T.fldTransactionDateTime as date),@Date ) > 7 and datediff(d,cast(T.fldTransactionDateTime as date),@Date ) <= 14
    then count(fldTransactionID) else 0 end as [7-14 Days],
case when datediff(d,cast(T.fldTransactionDateTime as date),@Date ) > 14 and datediff(d,cast(T.fldTransactionDateTime as date),@Date ) <= 21
    then count(fldTransactionID) else 0 end as [14-21 Days],
case when datediff(d,cast(T.fldTransactionDateTime as date),@Date ) > 21 and datediff(d,cast(T.fldTransactionDateTime as date),@Date ) <= 28
    then count(fldTransactionID) else 0 end as [21-28 Days],
case when datediff(d,cast(T.fldTransactionDateTime as date),@Date ) > 28 
    then count(fldTransactionID) else 0 end as [28+ Days]
 from [transaction] T
join product P on P.fldProductCode = T.fldProductCode
where T.fldTransactionSold = 0
group by  P.fldProductDescription,T.fldProductCode, cast(fldTransactionDateTime as DATE)

Answer

I am guessing you want conditional aggregation:

select T.fldProductCode, P.fldProductDescription, 
       count(fldTransactionID), 
       sum(case when datediff(day, T.fldTransactionDateTime, @Date) <= 7 
                then 1 else 0
           end) as [0-7 Days],
       sum(case when datediff(day, T.fldTransactionDateTime, @Date) between 8 and 14 
                then 1 else 0
           end) as [7-14 Days],
       sum(case when datediff(day, T.fldTransactionDateTime, @Date) between 15 and 21
                then 1 else 0
           end) as [14-21 Days],
       sum(case when datediff(day, T.fldTransactionDateTime, @Date) between 22 and 28
                then 1 else 0
           end) as [22-28 Days],

       sum(case when datediff(day, T.fldTransactionDateTime, @Date) > 28 
                then 1 else 0
           end) as [21-28 Days],
from [transaction] T join product
     P
     on P.fldProductCode = T.fldProductCode
where T.fldTransactionSold = 0
group by P.fldProductDescription, T.fldProductCode;;

The case expression is the argument to the sum() rather than the other way around.