weekly sql query

[mdarsot]

I have below query

set linesize 150
set pagesize 2000
set wrap off
spool filetemp.lst


select
id AS userid,
date_release as outputdate
SUM(counts) AS quanitity

from
counts_table
where
(id like '2%')
and date_release between '02-MAR-09' and '08-MAR-09'

group by
id
/

I am query for record counts between the two specified dates inputed above. I have to keep changing dates to query another week. Is there a way it will give me counts for all the week from jan-2008 till jan-2009 where week starts from Mon to Sun.

thanks

 

[taupirho]

Try changing your query to something like

select
id AS userid,
date_release as outputdate
SUM(counts) AS quanitity

from
counts_table
where
(id like '2%')
and date_release >= '01-jan-08'
and date_release <'01-jan-09'
group by id,to_char(date_release,'IW')



In order to understand recursion, you must first understand recursion.

 

[mdarsot]

wow that works great thanks. I want to expand on this.

The ID field contains data like this

ID
--
10U
10UT1
10UT39
15A
15O


Currently when i output ID using the sql you provided it does show me sum by week however each like field is show differently. For exampl

week count for 10U
week count for 10UT

I want to combine like ID and show the sum as long as first two letters of ID is same for exampe

10U, 10UT1, 10UT39 should be combine as 10 (first two digit) is common.

 

[mdarsot]

select
id AS userid,
counts as quantity
date_release as outputdate
SUM(counts) AS quanitity

from
counts_table
where
(id like '2%')
and date_release >= '01-jan-08'
and date_release <'01-jan-09'
group by id,to_char(date_release,'IW')


Output is like this. How can i combine the output for ID field as long as first two digit (10 in below case) is same.

10U 10000
10UT1 12000
10UT29 17000

 

[mdarsot]

one more thing and it will show me weeknumber in the output.

 

[HarleyQuinn]

Use a substring to select the first two characters in the select and the group by clause.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[mdarsot]

i did substr function it just cutsoff the ID field does not combine it.

 

[taupirho]

Try this,

select
substr(id,1,2) AS userid,
to_char(date_release,'IW') as week_no,
date_release as outputdate
SUM(counts) AS quanitity

from
counts_table
where
(id like '2%')
and date_release >= '01-jan-08'
and date_release <'01-jan-09'
group by substr(id,1,2),to_char(date_release,'IW')



In order to understand recursion, you must first understand recursion.

 

[HarleyQuinn]

In the group by clause as well?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[mdarsot]

Amazing thanks

Last thing. Can the week number be shown as 02-Mar-2008 to 08-Mar-2009 instead of just week #

 

[taupirho]

Thats slightly trickier. Have a go yourself and see how far you get. Come back if you hit a wall




In order to understand recursion, you must first understand recursion.

 

[mdarsot]

THanks for your help guys.

I am getting the output i need. Only issue is the output columns are wrapping around on screen and in the spool file. How do i stop it from wrapping around and output continously in straight line all the select fields.

 

[IanWaterman]

YOU could calculate week start date on fly

date_release - to_number(to_char(date_release, 'D')) week_start

This will give you Sunday before

and saturday after will be

(date_release - to_number(to_char(date_release, 'D')))+7 week_end

YOu will then need to Char these and concatenate

Ian

 

[mdarsot]

Thanks

ANy help with wrap around. I dont want all the fields to wrap around mess up my output.

 

[Turkbear]

Hi,
Try increasing the line size and set the pagesize to 0.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mdarsot]

ok one more addition.

I want to get one more field which will be a calculated field in output. The calculation will be date_release + 7 days. and output at the end of report.




select
substr(id,1,2) AS userid,
to_char(date_release,'IW') as week_no,
date_release as outputdate
SUM(counts) AS quanitity

from
counts_table
where
(id like '2%')
and date_release >= '01-jan-08'
and date_release <'01-jan-09'
group by substr(id,1,2),to_char(date_release,'IW')

 

[taupirho]

just add date_release+7 to the list of columns you are selecting



In order to understand recursion, you must first understand recursion.

 

[mdarsot]

Thanks

I did that but then it is complaining about group clause missing. and when put group clause it is grouping everythign by return date which (release+7) which i dont want.

Output i want is like below

id release date counts return date
10 12-Mar-2009 1249 20-Mar-2009

 

[taupirho]

If you can, please post the exact query you are running and its resultant (error) output when run in a sqlplus session.



In order to understand recursion, you must first understand recursion.

 

[mdarsot]

select
substr(output_svycode_NM,1,2) as SI,
SUM(PROCESSING_BATCHSIZE_QTY) AS LOADS,
to_char(batch_release_dt,'IW') WK
from
PARTS_DATABSE
where
(OUTPUT_SVYCODE_NM like '29%')
and batch_release_dt >='02-FEB-09'
and batch_release_dt <'27-DEC-09'
group by
substr(OUTPUT_SVYCODE_NM,1,2),
to_char(batch_release_dt,'IW')


This gives me result like following

SI LOADS WK
-- ----------- --
29 377068 06
29 512180 07
29 177249 08
29 257117 09
29 391693 10
29 242608 11


What i would like is something like below

SI Week_Sent LOADS WK Expected Return (sent +8days)
-- ---------- ------ ----- ---------------
29 Feb2-Feb8 377068 06 Feb10-Feb16
29 Feb9-Feb15 512180 07 Feb17-Feb23
29 Feb16-Feb22 177249 08 Feb24-Mar02
29 Feb23-Mar01 257117 09 Mar03-Mar09
29 Mar02-Mar08 391693 10 Mar10-Mar16
29 Mar09-Mar15 442608 11 Mar17-Mar22