I am trying to sum the values for each day of the month within a fiscal year, I am converting Oracle code to SQL and it's not coming out right, could someone look at the code and assist.
DECLARE @p_startmonth int
DECLARE @p_startyear int
DECLARE @p_endmonth int
DECLARE @p_endyear int
DECLARE @p_site_code nvarchar(3)
SET @p_startyear = '2016'--DATEPART(Year,CONVERT(date,'2019-12-01'))
SET @p_startmonth = '10'--DATEPART(Month,CONVERT(date,'2019-12-01'))
SET @p_endyear = '2016'--DATEPART(YEAR,DATEADD(month,-1,'2019-12-31'))
SET @p_endmonth = '12'--DATEPART(MONTH,DATEADD(month,-1,'2019-12-31'))
set @p_site_code = 'GDW'
SELECT
CONVERT(INT,datepart(DD,observ_date)) AS [Day],
CASE WHEN MONTH(observ_date)>=10 THEN
YEAR(observ_date)+1
ELSE YEAR(observ_date) END AS financial_year,
sum(case DATEPART(MONTH, observ_date) when '10' then ROUND(value,0) end) oct,
sum(case DATEPART(MONTH,observ_date) when '11' then ROUND(value,0) end) nov,
sum(case DATEPART(MONTH,observ_date) when '12' then ROUND(value,0) end) dec,
sum(case DATEPART(MONTH,observ_date) when '01' then ROUND(value,0) end) jan,
sum(case DATEPART(MONTH,observ_date) when '02' then ROUND(value,0) end) feb,
sum(case DATEPART(MONTH,observ_date) when '03' then ROUND(value,0) end) mar,
sum(case DATEPART(MONTH,observ_date) when '04' then ROUND(value,0) end) apr,
sum(case DATEPART(MONTH,observ_date) when '05' then ROUND(value,0) end) may,
sum(case DATEPART(MONTH,observ_date) when '06' then ROUND(value,0) end) jun,
sum(case DATEPART(MONTH,observ_date) when '07' then ROUND(value,0) end) jul,
sum(case DATEPART(MONTH,observ_date) when '08' then ROUND(value,0) end) aug,
sum(case DATEPART(MONTH,observ_date) when '09' then ROUND(value,0) end) sep
FROM HARDB.NUM_DAILY
WHERE (site_code = @p_site_code )
and
OBSERV_DATE >= cast(cast(year(dateadd(YYYY,+111,@p_startyear)) as varchar(4)) +
'-10-01' as date ) and
OBSERV_DATE < cast( cast(year(dateadd(YYYY, +112,@p_startyear)) as varchar(4)) +
'-09-01' as date )
group by OBSERV_DATE
order by day asc
June 8, 2020 at 8:59 pm
If you have a Calendar table that's similar to one you would use for a Date dimension in DAX/SSAS Tabular, you could just specify the start and end dates of each fiscal month in the table and use it to summarize your data.
June 8, 2020 at 9:05 pm
What seems to be the problem?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 8, 2020 at 9:08 pm
We don't have a table for dates, this is a poorly designed database. I have to figure the fiscal year based off observ_date it goes oct through sept
June 8, 2020 at 9:14 pm
The data returned doesn't look sum it properly from what I am seeing in the data, for example oct, nov dec, jan isn't showing data as in the attachment.
June 8, 2020 at 9:23 pm
So to get it to show them day 1 of each month, totals and day 2, day 3 do I need to group by something special within the report? because the day aren't correlating 1-31 with the sum, it's displaying data like shown in this attachment.
June 8, 2020 at 9:37 pm
You've got a wad of things wrong with your code. Can you please tell us what the data types for the "observ_date" and "value" and "@p_site_code' columns are?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2020 at 9:49 pm
Yes - you need to group the results. A couple of notes:
I would modify this so it is based solely on the Fiscal Year to be displayed. Start with that and you get this:
DECLARE @fiscalYear int = 2016;
DECLARE @p_startdate datetime2(0) = datefromparts(@fiscalYear - 1, 10, 1)
, @p_enddate datetime2(0) = datefromparts(@fiscalYear, 10, 1);
Your criteria would then be:
WHERE site_code = @p_site_code
AND OBSERV_DATE >= @p_start_date
AND OBSERV_DATE < @p_end_date
The full query would then be:
SELECT [Day] = DAY(OBSERV_DATE)
, financial_year = @fiscalYear
, oct = sum(iif(month(OBSERV_DATE) = 10, round(value,0), 0)
, nov = sum(iif(month(OBSERV_DATE) = 11, round(value,0), 0)
, dec = sum(iif(month(OBSERV_DATE) = 12, round(value,0), 0)
, jan = sum(iif(month(OBSERV_DATE) = 1, round(value,0), 0)
, feb = sum(iif(month(OBSERV_DATE) = 2, round(value,0), 0)
, mar = sum(iif(month(OBSERV_DATE) = 3, round(value,0), 0)
, apr = sum(iif(month(OBSERV_DATE) = 4, round(value,0), 0)
, may = sum(iif(month(OBSERV_DATE) = 5, round(value,0), 0)
, jun = sum(iif(month(OBSERV_DATE) = 6, round(value,0), 0)
, jul = sum(iif(month(OBSERV_DATE) = 7, round(value,0), 0)
, aug = sum(iif(month(OBSERV_DATE) = 8, round(value,0), 0)
, sep = sum(iif(month(OBSERV_DATE) = 9, round(value,0), 0)
FROM HARDB.NUM_DAILY
WHERE site_code = @p_site_code
AND OBSERV_DATE >= @p_start_date
AND OBSERV_DATE < @p_end_date
GROUP BY
DAY(OBSERV_DATE)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 8, 2020 at 10:05 pm
A part of the problem is doing the rounding inside the sum. I recommend that the rounding be done on the sum rather than inside it for both reasons of performance and for accuracy.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2020 at 10:09 pm
A part of the problem is doing the rounding inside the sum. I recommend that the rounding be done on the sum rather than inside it for both reasons of performance and for accuracy.
Good catch - I probably wouldn't round the results and instead just cast/convert to a numeric data type with a greater precision and leave the actual 'rounding' to the application when it is displayed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 8, 2020 at 10:11 pm
When I use your code I get errors all over , states sum function requires 1 arg, and doesn't recognize OBSERV_DATE, yes I figured to let the report do the rounding, I had converted Oracle to SQL using a tool and that's how it converted it.
SELECT TO_NUMBER(TO_CHAR(observ_date,'DD')) observ_day,
sum(decode(TO_CHAR(observ_date,'MM'),'10',ROUND(value))) oct,
sum(decode(TO_CHAR(observ_date,'MM'),'11',ROUND(value))) nov,
sum(decode(TO_CHAR(observ_date,'MM'),'12',ROUND(value))) dec,
sum(decode(TO_CHAR(observ_date,'MM'),'01',ROUND(value))) jan,
sum(decode(TO_CHAR(observ_date,'MM'),'02',ROUND(value))) feb,
sum(decode(TO_CHAR(observ_date,'MM'),'03',ROUND(value))) mar,
sum(decode(TO_CHAR(observ_date,'MM'),'04',ROUND(value))) apr,
sum(decode(TO_CHAR(observ_date,'MM'),'05',ROUND(value))) may,
sum(decode(TO_CHAR(observ_date,'MM'),'06',ROUND(value))) jun,
sum(decode(TO_CHAR(observ_date,'MM'),'07',ROUND(value))) jul,
sum(decode(TO_CHAR(observ_date,'MM'),'08',ROUND(value))) aug,
sum(decode(TO_CHAR(observ_date,'MM'),'09',ROUND(value))) sep
FROM num_daily
WHERE site_code = :p_site_code
AND shef_code = :p_shef_code
AND observ_date BETWEEN :p_start_date
AND :p_end_date
GROUP BY TO_NUMBER(TO_CHAR(observ_date,'DD'))
ORDER BY TO_NUMBER(TO_CHAR(observ_date,'DD'))
Sorry - since I don't have those tables or any sample data I wasn't able to validate the code. I missed the closing parenthesis on the sum...
SELECT [Day] = DAY(OBSERV_DATE)
, financial_year = @fiscalYear
, oct = sum(iif(month(OBSERV_DATE) = 10, round(value,0), 0))
, nov = sum(iif(month(OBSERV_DATE) = 11, round(value,0), 0))
, dec = sum(iif(month(OBSERV_DATE) = 12, round(value,0), 0))
, jan = sum(iif(month(OBSERV_DATE) = 1, round(value,0), 0))
, feb = sum(iif(month(OBSERV_DATE) = 2, round(value,0), 0))
, mar = sum(iif(month(OBSERV_DATE) = 3, round(value,0), 0))
, apr = sum(iif(month(OBSERV_DATE) = 4, round(value,0), 0))
, may = sum(iif(month(OBSERV_DATE) = 5, round(value,0), 0))
, jun = sum(iif(month(OBSERV_DATE) = 6, round(value,0), 0))
, jul = sum(iif(month(OBSERV_DATE) = 7, round(value,0), 0))
, aug = sum(iif(month(OBSERV_DATE) = 8, round(value,0), 0))
, sep = sum(iif(month(OBSERV_DATE) = 9, round(value,0), 0))
FROM HARDB.NUM_DAILY
WHERE site_code = @p_site_code
AND OBSERV_DATE >= @p_start_date
AND OBSERV_DATE < @p_end_date
GROUP BY
DAY(OBSERV_DATE)
I based my code on what you provided - and if your table does not have the column OBSERV_DATE then I don't see how your code would have worked.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2020 at 1:02 am
I've attached a screen shot of the table definitions.
Ya know... If you were to post an actual CREATE TABLE bit of code for the pertinent columns for this problem to help us help you, I'd be happy to generate a multi-year shedload of test data to show you some possibilities of making your life a whole lot easier. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply