| Date issue [message #285420] |
Tue, 04 December 2007 08:22  |
brintha
Messages: 28
Registered: August 2007
|
Junior Member |
|
|
Hi,
I have to write a query which fetches the records that satisfies the condition that the 'delivered_date' falls between 'pickup_date'+ 5 and it should not contain should not contain any saturday and sunday and not any holidays that is stored in the holiday field of a table.
If so i have to extend the limit from 5('pickup_date'+ 5) to 'pickup_date+7' (for sat & sun) plus the no of holidays if any. How can i write this query. Please any body help me out in this.
Regards,
Brintha.R
[EDITED by LF: removed empty lines]
[Updated on: Tue, 04 December 2007 13:36] by Moderator Report message to a moderator |
|
|
|
| Re: Date issue [message #285434 is a reply to message #285420] |
Tue, 04 December 2007 09:37  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
|
Could you please provide us with the create table and insert scripts? Then we will be able to help you (better then by just guessing what your data looks like).
|
|
|
|
|
|
| Re: Date issue [message #285532 is a reply to message #285420] |
Wed, 05 December 2007 00:10 |
brintha
Messages: 28
Registered: August 2007
|
Junior Member |
|
|
Thanks for your replies. Im trying to make my explanation as clear as possible.
If this is not the correct forum please do move my query to the correct one.
Shipment_status
Shipment_Id Update_Date Status_Value
Ship_01 11/11/07 Picked up
Ship_01 18/11/07 Delivered
Shipment
Shipment_Id Location_Id
Ship_01 Location_01
Holidays
Location_Id Holiday_Id From Till
Location_01 Holiday_01 13/11/07 13/11/07
The shipment should be delivered within 5 days it has been picked up.
Here 15/11/07 and 16/11/07 are Saturday and Sunday respectively. If 13/11/07 is a holiday then the shipment can be delivered within 8 days after pickup. (Ie,, ignoring holidays)
So the limit (delivered date must fall between) is pick update and pick up date + 5 + no of holidays.
Thanks & Regards,
Brintha
|
|
|
|
| Re: Date issue [message #285709 is a reply to message #285532] |
Wed, 05 December 2007 06:26 |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is your example, modified a little bit for testing purposes:
SQL> select * From holidays;
LOCATION_ID HOLIDAY_ID DATE_FRO DATE_TIL
-------------------- -------------------- -------- --------
location_01 holiday_01 13.11.07 15.11.07
SQL> select * From shipment;
SHIPMENT_ID LOCATION_ID
-------------------- --------------------
ship_01 location_01
ship_02 location_01
ship_03 location_01
SQL> select * From shipment_status order by shipment_id;
SHIPMENT_I UPDATE_D STATUS_VALUE
---------- -------- --------------------
ship_01 11.11.07 picked up
ship_01 23.11.07 delivered
ship_02 01.12.07 picked up
ship_02 25.12.07 delivered
ship_03 08.11.07 picked up
ship_03 13.11.07 delivered
ship_01: 11.11.2007 - 18.11.2007 -> 6 nwd - 3 sat/sun
3 hol
ship_02: 01.12.2007 - 25.12.2007 -> 8 nwd - 8 sat/sun
0 hol
ship_03: 08.12.2007 - 13.11.2007 -> 3 nwd - 2 sat/sun
1 hol
This query will return the desired result (at least, I think so):
SELECT DISTINCT sp.shipment_id
FROM SHIPMENT_STATUS sp,
SHIPMENT_STATUS sd,
(
SELECT x.shipment_id, COUNT(x.datum) cnt_nonworking_days
FROM (
-- number of Saturdays and Sundays
SELECT DISTINCT s1.shipment_id, s1.update_date + LEVEL - 1 datum
FROM SHIPMENT_STATUS s1, SHIPMENT_STATUS s2, SHIPMENT sh, HOLIDAYS h
WHERE s1.shipment_id = s2.shipment_id
AND s1.status_value = 'picked up'
AND s2.status_value = 'delivered'
AND sh.shipment_id = s1.shipment_id
AND sh.location_id = h.location_Id
AND TO_CHAR(s1.update_date + LEVEL - 1, 'd') IN ('6', '7')
CONNECT BY LEVEL <= s2.update_date - s1.update_date + 1
UNION ALL
-- number of holidays between 'picked up' and 'delivered' dates
SELECT DISTINCT z.shipment_id, z.holiday
FROM (SELECT ss1.shipment_id, h.date_from + LEVEL - 1 holiday
FROM HOLIDAYS h, SHIPMENT sh, SHIPMENT_STATUS ss1
WHERE sh.shipment_id = ss1.shipment_id
AND sh.location_id = h.location_id
CONNECT BY LEVEL <= h.date_till - h.date_from + 1
) z, SHIPMENT_STATUS ssf, SHIPMENT_STATUS sst
WHERE ssf.shipment_id = z.shipment_id
AND ssf.shipment_id = sst.shipment_id
AND ssf.status_value = 'picked up'
AND sst.status_value = 'delivered'
AND z.holiday BETWEEN ssf.update_date AND sst.update_Date
) x
GROUP BY x.shipment_id
) y
WHERE y.shipment_id = sp.shipment_id
AND sp.shipment_id = sd.shipment_id
AND sp.status_value = 'picked up'
AND sd.status_value = 'delivered'
AND sd.update_date > sp.update_date + 5 + y.cnt_nonworking_days -- 5 days + nonworking days
ORDER BY sp.shipment_id;
SHIPMENT_I
----------
ship_01
ship_02
I *know* it isn't very nice nor fast solution, but ... can't figure anything more elegant at the moment.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
