Sum Counter for a Week

Question

Post reply

Sum Counter for a Week

varunkum

Default port

Points: 1479
More actions
June 8, 2011 at 8:54 am

#241044

NET_RATE_DLY is checking the day of the week today and calculating the sum, but i want sum of rates from Monday through Saturday for this week.
NET_RATE_SUN ends up with a zero because today is 8th June. But want it to go to 11th June and calculate the sum, basically future date. We have future date rates in our table so that shouldn't be a problem.
I need some idea.Thanks.
SELECT cRR.ROUTE, cRR.PUBLICATION,
sum(
DECODE(to_char(SYSDATE,'D')
,2,cRR.RATE_MONDAY
,3,cRR.RATE_TUESDAY
,4,cRR.RATE_WEDNESDAY
,5,cRR.RATE_THURSDAY
,6,cRR.RATE_FRIDAY
,7,cRR.RATE_SATURDAY
,0) NET_RATE_DLY
,
sum(
DECODE(to_char(SYSDATE,'D')
,1,cRR.RATE_SUNDAY
,0) NET_RATE_SUN
FROM CIRC.carrier_ROUTE cRR
GROUP BY
cRR.ROUTE, cRR.PUBLICATION

Viewing 7 posts - 1 through 6 (of 6 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

A table script, some sample data and the expected output would help a lot.

Take a look at the article linked in my signature line and find out how to help us help you.

-- Gianluca Sartori

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Filtering should be done in WHERE clause and not in SELECT.

General structure should looks like:

SELECT

list-of-colums-to-be-returned

FROM

list-of-tables

WHERE

conditions-for-filtering-and-access

;

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

PaulB-TheOneAndOnly (6/8/2011)
Filtering should be done in WHERE clause and not in SELECT.
General structure should looks like:
SELECT
list-of-colums-to-be-returned
FROM
list-of-tables
WHERE
conditions-for-filtering-and-access
;

Ummmm.... You've never done simple Cross-Tabs where ALL the filtering is done in the SELECT list?

--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)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Jeff Moden (6/9/2011)
PaulB-TheOneAndOnly (6/8/2011)
Filtering should be done in WHERE clause and not in SELECT.
General structure should looks like:
SELECT
list-of-colums-to-be-returned
FROM
list-of-tables
WHERE
conditions-for-filtering-and-access
;
Ummmm.... You've never done simple Cross-Tabs where ALL the filtering is done in the SELECT list?

I used the word "should" Jeff. 😉 In my view best practices call for filtering/access to be specified in WHERE clause, there is where a sleeppy DBA oncall will look when paged at 2:30AM in the morning 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

PaulB-TheOneAndOnly (6/9/2011)
I used the word "should" Jeff. 😉 In my view best practices call for filtering/access to be specified in WHERE clause, there is where a sleeppy DBA oncall will look when paged at 2:30AM in the morning 😀

Heh... don't use semantics on me, Paul. I expect better than that from you. 😉

So far as best practices go, this is nothing but an Oracle method for doing simple a Cross-Tab or Pivot and will not be a cause for waking a DBA at 2:30 in the morning. Tell me the following article, which has a huge amount of filtering in the SELECT list, violates best practices...

http://www.sqlservercentral.com/articles/T-SQL/63681/

--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)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Jeff Moden (6/9/2011)
PaulB-TheOneAndOnly (6/9/2011)
I used the word "should" Jeff. 😉 In my view best practices call for filtering/access to be specified in WHERE clause, there is where a sleeppy DBA oncall will look when paged at 2:30AM in the morning 😀
Heh... don't use semantics on me, Paul. I expect better than that from you. 😉

😀 you made my day man!

Jeff Moden (6/9/2011)So far as best practices go, this is nothing but an Oracle method for doing simple a Cross-Tab or Pivot and will not be a cause for waking a DBA at 2:30 in the morning. Tell me the following article, which has a huge amount of filtering in the SELECT list, violates best practices...
http://www.sqlservercentral.com/articles/T-SQL/63681/

Well... how should I put it. I respect so much the author of referenced article 😉 I prefer not to start a war on such a sensitive and subjective matter - when all i's are dotted and all t's are crossed it goes down to personal preferences 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.