Home » SQL & PL/SQL » SQL & PL/SQL » Convert date to weeks in the month and days (Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production Oracle ORACLE PL/SQL V10.1.0.4.2 - Pr)
Convert date to weeks in the month and days [message #636228] Mon, 20 April 2015 09:06 Go to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

SELECT DISTINCT ih.customer_id, 
                patient_name, 
                SUM( 
                CASE 
                                WHEN To_char(ih.invoice_date,'Day') = To_char(:end_date,'Day') THEN id.order_qty
                                ELSE 0 
                END) weeks SUM( 
                CASE 
                                WHEN to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-1),'Mon') THEN id.order_qty 
                                ELSE 0 
                END) last_month, 
                SUM( 
                CASE 
                                WHEN to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-2),'Mon') THEN id.order_qty 
                                ELSE 0 
                END) prev_month 
FROM            tab1 ih, 
                tab2 id, 
                tab4 ip, 
                tab3 vp 
WHERE           id.invoice_number = ih.invoice_number 
AND             id.item_id = vp.product_code 
AND             id.item_id = ip.item_id 
AND             ip.item_type IN ('P') 
AND             ih.customer_id = 'WAD-EX0128' 
AND             ih.invoice_date BETWEEN to_date('01-JAN-2015','DD-MON-YYYY') AND             to_date('31-MAR-2015','DD-MON-YYYY') 
AND             vp.inv_product_type IN ('RBC', 
                                        'LRBC', 
                                        'LPHER', 
                                        'PHER', 
                                        'FFP', 
                                        'FP24', 
                                        'CRYO') 
GROUP BY        ih.customer_id, 
                patient_name

This query gets current month, last month and previous month based on start date and end date. Now I want data by weeks. Help appreciated if someone can help me get data for Jan 2015 4 weeks data by week 1 to week 4 and days mon to sun

Week 1 week 2
mon tue wed thu fri sat sun mo tur wed thu fri sat sun

*BlackSwan formatted & added {code} tags. Please do so yourself in the future.
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

[Updated on: Mon, 20 April 2015 09:14] by Moderator

Report message to a moderator

Re: Convert date to weeks in the month and days [message #636229 is a reply to message #636228] Mon, 20 April 2015 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It would be more understandable if you post the output you now want.

Before, Please How to use [code] tags and make your code easier to read.

Re: Convert date to weeks in the month and days [message #636230 is a reply to message #636229] Mon, 20 April 2015 09:30 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member


Select distinct ih.customer_id,patient_name,
		sum(case when to_char(ih.invoice_date,'Day') = to_char(:end_date,'Day') then id.order_qty else 0 end) weeks
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-1),'Mon') then id.order_qty else 0 end) last_month,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-2),'Mon') then id.order_qty else 0 end) prev_month
from       tab1 ih,
           tab2 id,
           tab4 ip,
           tab3 vp
where id.invoice_number = ih.invoice_number
and id.item_id = vp.product_code
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and ih.customer_id = 'WAD-EX0128'
and   ih.invoice_date between  to_date('01-JAN-2015','DD-MON-YYYY')  and to_date('31-MAR-2015','DD-MON-YYYY')
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name


The above code works fine.

Now required data format.

Expected data

Week 1 Week 2
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Re: Convert date to weeks in the month and days [message #636231 is a reply to message #636230] Mon, 20 April 2015 09:39 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

Sorry

Select distinct ih.customer_id,patient_name,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(to_date(:end_date,'DD-MON-YYYY'),'Mon') then id.order_qty else 0 end) curr_month,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-1),'Mon') then id.order_qty else 0 end) last_month,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(add_months(to_date(:end_date,'DD-MON-YYYY'),-2),'Mon') then id.order_qty else 0 end) prev_month
from       tab1 ih,
           tab2 id,
           tab4 ip,
           tab3 vp
where id.invoice_number = ih.invoice_number
and id.item_id = vp.product_code
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and ih.customer_id = 'WAD-EX0128'
and   ih.invoice_date between  to_date('01-JAN-2015','DD-MON-YYYY')  and to_date('31-MAR-2015','DD-MON-YYYY')
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name


The above code works fine for months. Now if user wants data for 1 moth JAN 2015. by weeks.

Week 1 Week 2
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Re: Convert date to weeks in the month and days [message #636232 is a reply to message #636231] Mon, 20 April 2015 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Now if user wants data for 1 moth JAN 2015. by weeks.
>Week 1 Week 2
>Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat

What happens to the Sunday in Week 2 above?
In which column above should 1 Jan 2015 be reported?
Re: Convert date to weeks in the month and days [message #636233 is a reply to message #636232] Mon, 20 April 2015 09:50 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

Sorry missed sunday

Week 2
Mon Tue Wed Thu Fri Sat Sun
Re: Convert date to weeks in the month and days [message #636234 is a reply to message #636233] Mon, 20 April 2015 10:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sorry, but your requirement is not at all clear.

If I were you, I would have first provided the table creation script, and then insert statements for the data. And, finally, I would show the desired output(of course, as per the rules, isn't it? Wink). If I need help, I must provide the required details.
Re: Convert date to weeks in the month and days [message #636235 is a reply to message #636234] Mon, 20 April 2015 10:08 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

I have attached sample data creation scripts and data in the attachment.
sample_date.txt was attached. In the earlier query I did get data for months. Now I want it to be modified for weeks in the month.
Re: Convert date to weeks in the month and days [message #636237 is a reply to message #636233] Mon, 20 April 2015 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anumoses58 wrote on Mon, 20 April 2015 16:50
Sorry missed sunday

Week 2
Mon Tue Wed Thu Fri Sat Sun


This is NOT formatted.
Explain this result.
How does it come from your data?


Re: Convert date to weeks in the month and days [message #636240 is a reply to message #636237] Mon, 20 April 2015 11:15 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

In my previous query based on invoiced date I for for current month, last month and previous month. But now I am not getting the query to distribute the data by weeks and days. Dont give me the heading but give me order_qty by days of the week.
Re: Convert date to weeks in the month and days [message #636241 is a reply to message #636240] Mon, 20 April 2015 11:24 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

Based on the invoice date we have to get the week and the days
Re: Convert date to weeks in the month and days [message #636242 is a reply to message #636241] Mon, 20 April 2015 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What should be the result for the data you gave?

Re: Convert date to weeks in the month and days [message #636245 is a reply to message #636242] Mon, 20 April 2015 11:47 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

CUSTOMER NAME CURRENT MONTH

WAD-EX0128 ALEXIAN BROTHERS MEDICAL CENTER 515

NOW I WANT THIS TO BE BROKEN BY WEEKS AND DAYS.
Re: Convert date to weeks in the month and days [message #636246 is a reply to message #636245] Mon, 20 April 2015 11:47 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

515 FOR JANUARY
Re: Convert date to weeks in the month and days [message #636247 is a reply to message #636246] Mon, 20 April 2015 11:48 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

If I knew how to write for weeks and days I could tell you the expected data. I need to know how to write the case statement for weeks and days.
Re: Convert date to weeks in the month and days [message #636248 is a reply to message #636247] Mon, 20 April 2015 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 20 April 2015 18:31

What should be the result for the data you gave?


In addition, in a formatted way as explained in the links that have been provided.

Re: Convert date to weeks in the month and days [message #636249 is a reply to message #636248] Mon, 20 April 2015 11:51 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

If I knew how to write query for weeks and days I could tell you the expected data. I need to know how to write the case statement for weeks and days.
Re: Convert date to weeks in the month and days [message #636250 is a reply to message #636249] Mon, 20 April 2015 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't know what should be the result how could you know if the query is correct?

Re: Convert date to weeks in the month and days [message #636252 is a reply to message #636250] Mon, 20 April 2015 12:00 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

Select distinct ih.customer_id,patient_name,
		sum(case when to_char(ih.invoice_date,'Mon')=to_char(to_date(:end_date,'DD-MON-YYYY'),'Mon') then id.order_qty else 0 end) curr_month,
		to_char(ih.invoice_date,'Mon') || ' week' || to_char(ih.invoice_date,'W') as week_data
from       tab1 ih,
           tab2 id,
           tab4 ip,
           tab3 vp
where id.invoice_number = ih.invoice_number
and id.item_id = vp.product_code
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and ih.customer_id = 'WAD-EX0128'
and   ih.invoice_date between  to_date('01-JAN-2015','DD-MON-YYYY')  and to_date('31-JAN-2015','DD-MON-YYYY')
and vp.inv_product_type in ('RBC','LRBC','LPHER','PHER','FFP','FP24','CRYO')
group by ih.customer_id,patient_name,ih.invoice_date


ran this query

Jan 2015 1st week 23 have 3 days (thurs, fri, sat) if we consider sunday as day 1. But what I am doing is gone to week 5. Now I want that to be broken to days.
Re: Convert date to weeks in the month and days [message #636253 is a reply to message #636252] Mon, 20 April 2015 12:01 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

Jan 2015 1st week has 3 days (thurs, fri, sat) if we consider sunday as day 1. But what I am doing is gone to week 5. Now I want that to be broken to days
Re: Convert date to weeks in the month and days [message #636255 is a reply to message #636253] Mon, 20 April 2015 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 20 April 2015 18:50
Michel Cadot wrote on Mon, 20 April 2015 18:31

What should be the result for the data you gave?


In addition, in a formatted way as explained in the links that have been provided.

Re: Convert date to weeks in the month and days [message #636256 is a reply to message #636255] Mon, 20 April 2015 12:13 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

Week 1

Mon Tue Wed Thu Fri Sat Sun
9 7 11 11

Thurs 9
Fri 7
Sat 11
Sun 11
Re: Convert date to weeks in the month and days [message #636257 is a reply to message #636256] Mon, 20 April 2015 12:15 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

week 1 of Jan 2015

thurs 9
Fri 7
Sat 11

Week 2 of Jan 2015

Sun 11
Re: Convert date to weeks in the month and days [message #636258 is a reply to message #636256] Mon, 20 April 2015 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

anumoses58 wrote on Mon, 20 April 2015 19:13
Week 1

Mon Tue Wed Thu Fri Sat Sun
9 7 11 11

Thurs 9
Fri 7
Sat 11
Sun 11


1/ This is not formatted si we can't see which value is in which column
2/ Is this the EXACT output of the query you want? That is the result in columns AND in lines in the same query?

[Updated on: Mon, 20 April 2015 12:16]

Report message to a moderator

Re: Convert date to weeks in the month and days [message #636259 is a reply to message #636257] Mon, 20 April 2015 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anumoses58 wrote on Mon, 20 April 2015 19:15
week 1 of Jan 2015

thurs 9
Fri 7
Sat 11

Week 2 of Jan 2015

Sun 11


Or is this the output you want?

You MUST FIRST decide what output you want BEFORE you can get or write any query.

Re: Convert date to weeks in the month and days [message #636260 is a reply to message #636259] Mon, 20 April 2015 12:18 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

How can I format and send?
Re: Convert date to weeks in the month and days [message #636261 is a reply to message #636260] Mon, 20 April 2015 12:19 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

in the sample data I have included 1 customer for Jan 2015 with invoice dates and quantity. I need to know if I can attach an excel spread sheet for output required?
Re: Convert date to weeks in the month and days [message #636263 is a reply to message #636260] Mon, 20 April 2015 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

anumoses58 wrote on Mon, 20 April 2015 19:18
How can I format and send?


In the same way you format your query.
Just align the columns.

Do NOT attach any file for few lines.

Re: Convert date to weeks in the month and days [message #636266 is a reply to message #636263] Mon, 20 April 2015 12:38 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

CUSTOMER_ID PATIENT_NAME Week 1 Week 2

WAD-EX0128 ALEXIAN BROTHERS MEDICAL CENTER Sun Mon Tue Wed Thu Fri Sat Sun
9 7 11 11

I dont think I was able to put the formatting. Cannot attach excel spreadsheet.
Re: Convert date to weeks in the month and days [message #636268 is a reply to message #636266] Mon, 20 April 2015 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I dont think I was able to put the formatting.


Just read the links Iprovided, even a for 5 years old child is able to do it.
If you can't then leave IT job and stay away from computers.

Re: Convert date to weeks in the month and days [message #636269 is a reply to message #636268] Mon, 20 April 2015 12:43 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

select'WAD-EX0128' as customer, 'ALEXIAN BROTHERS MEDICAL CENTER' as name, 7 as thur, 9 as Fri, 11 as Sat from dual

Re: Convert date to weeks in the month and days [message #636271 is a reply to message #636269] Mon, 20 April 2015 12:44 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

select'WAD-EX0128' as customer, 'ALEXIAN BROTHERS MEDICAL CENTER' as name, null as Sun, null as Mon, null as Tue, null as Wed,7 as thur, 9 as Fri, 11 as Sat from dual

Re: Convert date to weeks in the month and days [message #636272 is a reply to message #636271] Mon, 20 April 2015 12:45 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

You can run this and that is the expected data required from the sample data.
Re: Convert date to weeks in the month and days [message #636274 is a reply to message #636272] Mon, 20 April 2015 12:53 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

SELECT'WAD-EX0128'                      AS customer,       'ALEXIAN BROTHERS MEDICAL CENTER' AS NAME,       0                                 AS Sun,       0                                 AS Mon,       0                                 AS Tue,       0                                 AS Wed,       7                                 AS thur,       9                                 AS Fri,       11                                AS Sat FROM   dual 
Re: Convert date to weeks in the month and days [message #636278 is a reply to message #636274] Mon, 20 April 2015 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is unreadable.

Re: Convert date to weeks in the month and days [message #636282 is a reply to message #636278] Mon, 20 April 2015 13:33 Go to previous messageGo to next message
anumoses58
Messages: 23
Registered: April 2015
Location: Aurora IL USA
Junior Member

I can only do so much to provide information.
Re: Convert date to weeks in the month and days [message #636283 is a reply to message #636282] Mon, 20 April 2015 14:16 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
anumoses58 wrote on Mon, 20 April 2015 13:33
I can only do so much to provide information.


Only took me about 60 seconds to make it readable

SELECT 'WAD-EX0128'                      AS customer,       
       'ALEXIAN BROTHERS MEDICAL CENTER' AS NAME,       
       0                                 AS Sun,       
       0                                 AS Mon,       
       0                                 AS Tue,       
       0                                 AS Wed,       
       7                                 AS thur,       
       9                                 AS Fri,       
       11                                AS Sat 
FROM   dual 
Previous Topic: External Tables
Next Topic: Hierarchical queries with aggregation
Goto Forum:
  


Current Time: Thu Mar 28 08:28:05 CDT 2024