Next Day

Question

Post reply

Next Day

b_boy

SSCertifiable

Points: 5145
More actions
August 18, 2008 at 9:40 am

#197052

Hello
I have a table that consist of order placed by customers which also includes the shipdate.
I want to write a query that pulls out records of cusotmer who have requested for next day delivery, can anyone help

Viewing 7 posts - 1 through 6 (of 6 total)

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

Adam Warne Old Hand Points: 380 More actions · Answer 1

You'll obviously have to replace with your column names....

SELECT

*

FROM

Orders O

WHERE

DATEDIFF(dd,O.Date_Order_Placed,O.Delivery_Date) <= 1

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

When a where clause has any function using column values as parameters, a comparison of the returned values to a constant disallows the usage of any indexing unless the table has a persistent computed column using that function that has been indexed.

Instead of

DATEDIFF(dd,O.Date_Order_Placed,O.Delivery_Date)

Recommend when there is an index on Date_Order_Placed:

O.Date_Order_Placed = DATEADD(dd,-1,O.Delivery_Date)

When there is an index on Delivery_Date:

O.Delivery_Date = DATEADD(dd,1,O.Date_Order_Placed)

Or have a compute column:

Alter table X

Add Order_Notice_Days as DATEDIFF(dd,Date_Order_Placed,Delivery_Date)

PERSISTENT

go

OR, if using 2008, use a filtered index

CREATE unique INDEX SalesOrderHeader_Open

on Sales.SalesOrderHeader

(Status, SalesOrderID)

where Status in (1,2,3)

SQL = Scarcely Qualifies as a Language

Adam Warne Old Hand Points: 380 More actions · Answer 3

Good point on the SARG.

As an afterthought, if Date_Order_Placed is a datetime and delivery date just a date it should be:

O.Delivery_Date <= DATEADD(dd,1,O.Date_Order_Placed)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

If both columns are datetimes containing only dates (time part = 00:00:00.000), then this will do it.

where O.Delivery_Date-O.Date_Order_Placed >= convert(datetime,'19000102')

b_boy SSCertifiable Points: 5145 More actions · Answer 5

And if i would like to find out orders placed by the a customer within a 7 day period will i use:

DATEDIFF(dd,T_OrderHeader.OrderDate,GETDATE()) BETWEEN 1 AND 7)

Adam Warne Old Hand Points: 380 More actions · Answer 6

I'd use

O.Delivery_Date <= DATEADD(dd,7,O.Date_Order_Placed)

But both would work.