Count number of days in a table

[StephHansen]

I am running CR 9 against SQL 2000.

I am selecting data from three tables to calculate the time difference between processing time and shipment time. That works fine. I need to then subtract any holidays that may occur between that time period from the difference between proc. time and shipment time.

My critical data includes:

Package.PackageID
Package.BeginTime
Package.ShipTime
Package.MailClass

from this I use:
DateDiff ("h", {Package.BeginTime}, {Package.ShipTime})

to calculate the time between beginning and shipping the package.

I hit a roadblock when I try to subtrack the holidays that occur (if any) between BeginTime and ShipTime. there can be 0 or 1 or 2 or whatever.

what I need is a way to say:



to then multiply that value by 24 and subtract that from my difference in time.

My holiday table ONLY has Holidays in it and the ID for it Select Count(Holiday.HolidayDate) where Holiday.HolidayDate>=Package.BeginTime and Holiday.HolidayDate<=Package.ShipTimeis mailclass, (For example 12/25/03 is repeated for mailclass 0, 1, 2, 3, and 4). That is also a vlaue in my package table, so I need to do the link on that value.

I am doing a detail report that is not grouped. When I have tried to get the number of times dates appear within the records begin and ship times, it stops after it hits one.

Any advice?











Stephanie

 

[nagornyi]

you can get either the number of holidays of the entire duration right in the SQL. Provided that date difference in SQL is calculated in number of days (can be fractional) you may use

select
p.PackageID,
(p.ShipTime - p.BeginTime -
(select count(HolidayDate) from Holiday h
where h.HolidayDate>=p.BeginTime and h.HolidayDate<=p.ShipTime and h.MailClass=p.MailClass)*24
from Package p