Delivered on Time

Question

Delivered on Time

goldsct

Grasshopper

Points: 14
More actions
July 14, 2005 at 2:35 pm

#167584

I am trying to calculate whether an order has been delivered on time to a store.
Each store accepts deliveries on specific days of the week.
Example:
Store1 Mon
Store2 Tues
An order could be placed for store1 on a thurs but not delivered until tues, therefore not on time.
Table structure not decided upon yet.
Does anyone have any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can't really help you without the structure and sample data. Have a look at DateDiff, between, , DateAdd in the books online for a start.

goldsct Grasshopper Points: 14 More actions · Answer 2

hope this helps

CREATE TABLE store

(

storeno varchar(3) NULL,

delday varchar(3) NULL

) ON [PRIMARY]

insert into store values ('01','Tue')

create table orders

( store varchar(2) null,

orderno varchar(3) null,

orderdate varchar(10) null,

deldate varchar(10) null)

insert into orders values ('01','123','20050608','20050610')

insert into orders values ('01','124','20050608','20050615')

Orders must be ready for delivery, deldate, by the next tues after the order date.

order 123 is placed on a wed, 20050608, and is redy for delivery on a fri, 20050610. It is therefore 'ontime'

order 124 is placed on a wed, 20050608, and is ready for delivery on wed, 20050615. Not 'ontime'

ND-215452 SSCrazy Points: 2037 More actions · Answer 3

Not sure why your store.delday does not contain the actual date. I would much easier to query.

Anyway, hope this would help.

CREATE TABLE #magic (day_no int, wd char(3))

GO

insert #magic select 1,'sun'

insert #magic select 2,'mon'

insert #magic select 3,'tue'

insert #magic select 4,'wed'

insert #magic select 5,'thu'

insert #magic select 6,'fri'

insert #magic select 7,'sat'

select o.*,s.*

, CASE when datediff(dd,o.orderdate, o.deldate) > 7 then 'Not on time'

when (datediff(dd,o.orderdate, o.deldate)) <= 7 and (dateadd(dd, (datepart(dw,o.orderdate) - m.day_no) * -1, dateadd(dd,7,o.orderdate))) > o.deldate then 'on time'

ELSE 'Not on time'

END

from orders o

, store s

, #magic m

where s.storeno = o.store

and m.wd = s.delday