asked on

SQL Query Help

In the attached query I specify that if the "DueDate" falls on a weekend THEN DueDate + 1 OR + 2...

I need to ALSO make sure that Weekends are not counted when Adding Dates:

Consider the following Result Data:
JobDate RushNo DueDate RushType
2008-06-05 00:00:00      1001      2008-06-16 00:00:00      Standard Delivery (10 Day)
2008-06-04 00:00:00      1001      2008-06-16 00:00:00      Standard Delivery (10 Day)
2008-06-09 00:00:00      1005      2008-06-16 00:00:00      5 Day Expedite
2008-06-09 00:00:00      1005      2008-06-16 00:00:00      5 Day Expedite
2008-06-10 00:00:00      1012      2008-06-16 00:00:00      4 Day Expedite
2008-06-10 00:00:00      1012      2008-06-16 00:00:00      4 Day Expedite
2008-06-06 00:00:00      1001      2008-06-16 00:00:00      Standard Delivery (10 Day)
2008-06-12 00:00:00      1013      2008-06-16 00:00:00      3 Day Expedite

Notice how JobDates 2008-06-09 (5 Day Expedites) are supposed to Fall on Saturday. The query's logic, makes it fall on Monday 2008-06-16.

So far so good.

However notice all the 10 Day expedites: They count the weekends. How can I alter my query, not to count the weekends.

SELECT J.JobNo, W. Witness, W.Units as 'Pages', J.JobDate, RM.RushNo,
CASE RM.RushNo
 WHEN 1001 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 10, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 12, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 11, J.JobDate)
   ELSE DATEADD (dd, 10, J.JobDate)
  END
 WHEN 1005 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 5, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 7, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 6, J.JobDate)
   ELSE DATEADD (dd, 5, J.JobDate)
  END
 WHEN 1010 THEN
  CASE DATEPART(WEEKDAY, J.JobDate)  
   WHEN 7 THEN DATEADD (dd, 2, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 1, J.JobDate)
   ELSE J.JobDate
  END
 WHEN 1012 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 4, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 6, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 5, J.JobDate)
   ELSE DATEADD (dd, 4, J.JobDate)
  END
 WHEN 1013 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 3, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 5, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 4, J.JobDate)
   ELSE DATEADD (dd, 3, J.JobDate)
  END
 WHEN 1014 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 2, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 4, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 3, J.JobDate)
   ELSE DATEADD (dd, 2, J.JobDate)
  END
 WHEN 1016 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 7, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 9, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 8, J.JobDate)
   ELSE DATEADD (dd, 7, J.JobDate)
  END
 WHEN 1017 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 8, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 10, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 9, J.JobDate)
   ELSE DATEADD (dd, 8, J.JobDate)
  END
 WHEN 1018 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 9, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 11, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 10, J.JobDate)
   ELSE DATEADD (dd, 9, J.JobDate)
  END
 WHEN 1019 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 1, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 3, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 2, J.JobDate)
   ELSE DATEADD (dd, 1, J.JobDate)
  END
 WHEN 1020 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 6, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 8, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 7, J.JobDate)
   ELSE DATEADD (dd, 6, J.JobDate)
  END
END AS DueDate1,
RM.RushType, C.DisplayName as 'Contact', F.FirmName
FROM Jobs J JOIN Witnesses W ON J.JobNo = W.JobNo
            JOIN WitnessesParties WP ON W.WitNo = WP.WitNo
            JOIN Resources R ON R.RsrcNo = W.RsrcNo
            JOIN RushMST RM ON WP.RushNo = RM.RushNo
            JOIN Invoices I ON I.InvNo = WP.InvNo
            JOIN Contacts C ON I.SoldContactNo = C.ContactNo
            JOIN Firms F ON C.FirmNo = F.FirmNo
WHERE 
CASE RM.RushNo
 WHEN 1001 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 10, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 12, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 11, J.JobDate)
   ELSE DATEADD (dd, 10, J.JobDate)
  END
 WHEN 1005 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 5, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 7, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 6, J.JobDate)
   ELSE DATEADD (dd, 5, J.JobDate)
  END
 WHEN 1010 THEN
  CASE DATEPART(WEEKDAY, J.JobDate)  
   WHEN 7 THEN DATEADD (dd, 2, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 1, J.JobDate)
   ELSE J.JobDate
  END
 WHEN 1012 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 4, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 6, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 5, J.JobDate)
   ELSE DATEADD (dd, 4, J.JobDate)
  END
 WHEN 1013 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 3, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 5, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 4, J.JobDate)
   ELSE DATEADD (dd, 3, J.JobDate)
  END
 WHEN 1014 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 2, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 4, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 3, J.JobDate)
   ELSE DATEADD (dd, 2, J.JobDate)
  END
 WHEN 1016 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 7, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 9, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 8, J.JobDate)
   ELSE DATEADD (dd, 7, J.JobDate)
  END
 WHEN 1017 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 8, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 10, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 9, J.JobDate)
   ELSE DATEADD (dd, 8, J.JobDate)
  END
 WHEN 1018 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 9, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 11, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 10, J.JobDate)
   ELSE DATEADD (dd, 9, J.JobDate)
  END
 WHEN 1019 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 1, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 3, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 2, J.JobDate)
   ELSE DATEADD (dd, 1, J.JobDate)
  END
 WHEN 1020 THEN
  CASE DATEPART(WEEKDAY, DATEADD(dd, 6, J.JobDate))  
   WHEN 7 THEN DATEADD (dd, 8, J.JobDate)
   WHEN 1 THEN DATEADD (dd, 7, J.JobDate)
   ELSE DATEADD (dd, 6, J.JobDate)
  END
END = CASE DATEPART(WEEKDAY, DATEADD(dd,1,CONVERT(VARCHAR,GETDATE(),101)))
       WHEN 7 THEN DATEADD(dd,3,CONVERT(VARCHAR,GETDATE(),101))
       WHEN 1 THEN DATEADD(dd,2,CONVERT(VARCHAR,GETDATE(),101))
       ELSE DATEADD(dd,1,CONVERT(VARCHAR,GETDATE(),101))
      END
AND
W.RsrcNo IN (SELECT R.RsrcNo FROM Resources R Where RsrcType = 405)
ORDER BY J.JobNo, J.JobDate

Open in new window

dosth

can you tell me what days it has to be

2008-06-05 00:00:00 1001 2008-06-16 00:00:00 Standard Delivery (10 Day)
2008-06-04 00:00:00 1001 2008-06-16 00:00:00 Standard Delivery (10 Day)

pzozulka

ASKER

So for example, a 10 Day Standard Delivery for a JobDate 2008-06-05 should be 2008-06-19 ( 10 Business Days)...

For 2008-06-04 it should be 2008-06-18.

So ultimate result should be JobDate + Delivery Type = Cannot Fall on a WeekEnd (My query already considers this)

AND

JobDate + Delivery Type = Cannot count WeekEnds...Must count XX Business Days.

ASKER CERTIFIED SOLUTION

dosth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Take a look in this site: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

Good luck