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 |
|
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 #636230 is a reply to message #636229] |
Mon, 20 April 2015 09:30 |
|
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 |
|
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 #636252 is a reply to message #636250] |
Mon, 20 April 2015 12:00 |
|
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 #636258 is a reply to message #636256] |
Mon, 20 April 2015 12:16 |
|
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 #636283 is a reply to message #636282] |
Mon, 20 April 2015 14:16 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
anumoses58 wrote on Mon, 20 April 2015 13:33I 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
|
|
|
Goto Forum:
Current Time: Thu Mar 28 08:28:05 CDT 2024
|