SELECT data grouped by WEEK in SQL

I have a working query (ORACLE SQL) that gives me gallons grouped by store number, with gallons summed by type and a percentage column as well. Each store number has a different conversion date from which I sum up the data –

SELECT StoreNbr, 
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END)) AS DECIMAL (5,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ClrntSys IN ('844', '84448', 'GIC')
AND ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY StoreNbr
ORDER BY StoreNbr;

I now need to also sum it up by week, with Sunday being the first day of each week, but I’m having trouble understanding how DATEPART works. Just so I could get a better idea, I tried only to sum up the data for last week using DATEPART examples I’m seeing online, but this doesn’t work. It’s giving me “invalid identifier DATEPART”. –

SELECT DATEPART(week, 5/17/20) AS weekTotal, 
StoreNbr, 
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END)) AS DECIMAL (5,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY DATEPART(week, 5/17/20), StoreNbr
ORDER BY StoreNbr;

What I really need, however, is each week’s data (with Sunday being the beginning of each week) summed up separately going back to each store’s conversion date. Is it possible to do that? Is there something else besides DATEPART that would work better?

Answer

Sorry – just noticed that you said Oracle SQL, and my first answer was for SQL Server! The reason you are getting an error is that DATEPART is not an Oracle function. Instead, you can simply do math on the dates, using a known sunday (prior to a known first date in the DB table) as an anchor date:

SELECT
    '30-DEC-2018' as "Known Sunday,
    trunc((sysdate - to_date('30-DEC-2018')) / 7) as "Week Num",
    to_date('30-DEC-2018') 
        + (trunc((sysdate - to_date('30-DEC-2018')) / 7) * 7)"
FROM
    dual

Advertisement

Answer