Sum days for fiscalyear

Question

Post reply

Sum days for fiscalyear

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Okay, I am not going to try to write any code until I have a good understanding of what you are attempting to accomplish.

First you are converting from Oracle to T-SQL, correct? This means you have tables and data in a MS SQL Server database, correct?

If the answer to both above questions is yes, then please post the DDL (CREATE TABLE statement) for the table HARDB.NUM_DAILY in the MS SQL Server database. Post sample data, not production data, that is representative of the problem domain. In this instance 4 or 5 rows of data for each month in the fiscal year. Also clearly define what the fiscal year is, for instance Fiscal Year 2019 begins on 2018-10-01 and ends on 2019-09-30. Then, using the sample data, show us what the expected output should be when the code is run.

I honestly believe that what you are attempting to accomplish can be done in a fairly simple manner, but since we can't see what you actually see, we need you to show us in a way we can easily create a sandbox in SQL Server we can use to develop a solution.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Jeffrey Williams wrote:

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.

The rounding still takes place in the wrong spot. Sergiy actually posted what is wrong that in his article this morning although he didn't include the bit of performance impact that also has on the process.

See the last sentence in the conclusion of his article, which is the most appropriate for this rounding problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

Jeff Moden wrote:

The rounding still takes place in the wrong spot. Sergiy actually posted what is wrong that in his article this morning although he didn't include the bit of performance impact that also has on the process.

See the last sentence in the conclusion of his article, which is the most appropriate for this rounding problem.

I don't disagree - but the original oracle query performed the rounding at the same level. First step for migration is to get the *same* results with the same set of data - once you have that you can then consider changing/improving the code for correctness.

I would definitely document the incorrect code and at least attempt to get a fix, but for QA purposes where the resulting reports/views/calculations/etc... must balance I don't see how that could be accomplished if the code is not going to produce the same results.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Understood and agreed but, like was said in an article this morning, "There's nothing more permanent than a temporary fix". It's truly unfortunate that that made the same mistake in Oracle. Heh... hopefully, me bitching about it will prompt the OP to bring serious guns to bear on the issue and they'll fix it before it becomes permanent code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Jeff Moden wrote:

cbrammer1219 wrote:

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. 😀

Seriously... it'll take just 10 minutes of your time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions