Home » Developer & Programmer » Reports & Discoverer » Date issue
Date issue [message #285420] Tue, 04 December 2007 08:22 Go to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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 #285455 is a reply to message #285420] Tue, 04 December 2007 13:40 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

... delivered_date' falls between 'pickup_date'+ 5
It is as if you've said that "date falls between today". Between 'pickup_date + 5' and what? Where's another boundary?

By the way, where do Reports Builder or Discoverer come into game here? Would you like your question to be moved to another forum (SQL one, for example)?
Re: Date issue [message #285532 is a reply to message #285420] Wed, 05 December 2007 00:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20894
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 
In another words, shipments have the following non-working days:
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.
Re: Date issue [message #286111 is a reply to message #285420] Thu, 06 December 2007 08:34 Go to previous message
brintha
Messages: 28
Registered: August 2007
Junior Member
Thank You so much for helping me out.

Regards,
Brintha
Previous Topic: Printing on Preprinted 8.5" x 7" continuous paper
Next Topic: Date Calculation in Discoverer Report
Goto Forum:
  


Current Time: Mon Dec 05 08:45:26 CST 2016

Total time taken to generate the page: 0.08109 seconds