T-SQL Help

Question

T-SQL Help

Vandy

SSCommitted

Points: 1827
More actions
November 16, 2006 at 7:06 am

#117621

Hi
I have one table having orderdate and deliverydate Now i need to calculate Delivery performance means how many orders we delivered in 24 hours, 48 hours and 72 hours +.......On monthly bases. Now one problem is if some customer place order on friday and if it deliveried on monday then it's a 24 hour delivery.
Fields of my table are:
custcode,custname,orderno,invoicenumber,orderlineno,orderdate,deliverydate,invoiceprice
Regards

Viewing 15 posts - 1 through 15 (of 15 total)

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

Luke L SSC-Dedicated Points: 35545 More actions · Answer 1

You might try using a calendar table to track workdays or you could just use

datediff(dw,date,date) The dw datepart is weekdays. This won't account for holidays however, hence the suggestion of a calendar table.

To help us help you read this[/url]For better help with performance problems please read this[/url]

sankar soma SSC Enthusiast Points: 117 More actions · Answer 2

sorry to bump in..what is a calendar table..(ignore my ignorance)

Thanks,

Sankar

Luke L SSC-Dedicated Points: 35545 More actions · Answer 3

Basically it's a table that you can add to your database to help with calculating dates.

You can add whatever information you might need. You can pretty easily genereate one and include, hours, days, months, years, and various other date parts, holiday or not etc. This can then be used for joining and date eliminations and such.

To help us help you read this[/url]For better help with performance problems please read this[/url]

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

It's a table with all the dates as the clustered primary key. Then you can add flags for trimesteres, week-ends, holidays, etc.

Luke L SSC-Dedicated Points: 35545 More actions · Answer 5

here's a link to an article that discusses it's use and how to create one etc..

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

To help us help you read this[/url]For better help with performance problems please read this[/url]

sankar soma SSC Enthusiast Points: 117 More actions · Answer 6

Thank you both of you for the explanation.

Regards,

Sankar

Sergiy SSC Guru Points: 110192 More actions · Answer 7

declare @Ordered datetime, @Delivered datetime

select @Ordered = GETDATE(), @Delivered = GETDATE() + 2.7

select datediff(hh, @Ordered, @Delivered) - DATEDIFF(wk, @Ordered, @Delivered)*48

DATEDIFF(wk, ...) returns number of weekends between dates. Does not depend on DATEFIRST settings.

_____________
Code for TallyGenerator

Vandy SSCommitted Points: 1827 More actions · Answer 8

Thanks for reply

Sorry Sergiy, i am a new user of sql.Could you explain me this statement please

@Delivered = GETDATE() + 2.7

Regards

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

It adds 2.7 days to the current datetime.

The best practice is to use the dataadd function but there are exceptions to that .

Vandy SSCommitted Points: 1827 More actions · Answer 10

Thanks Ninja's RGR'us

Sorry for again asking

Is this mean 2 days and 7 hours...?

I want to show my client output in one table like this

Total number of orders 8289

24 hour receipt 8067

48 hour receipt 176

72 hours+ 39

Extremely sorry for asking again, Is it possible to show this output with one query.

Regards

Sergiy SSC Guru Points: 110192 More actions · Answer 11

No, it means 2 days + 0.7*24 hours. "2.7" days.

SELECT COUNT(OrderID) as [Total number of orders],

COUNT(case when ReceiptHours <= 24 then OrderID else NULL end) as [24 hour receipt],

COUNT(case when ReceiptHours > 24 and ReceiptHours <= 48 then OrderID else NULL end) as [48 hour receipt],

COUNT(case when ReceiptHours > 48 and ReceiptHours <= 72 then OrderID else NULL end) as [72 hour receipt],

COUNT(case when ReceiptHours > 72 then OrderID else NULL end) as [72 hours +]

FROM (select OrderID, datediff(hh, TimeOrdered, TimeReceived) - DATEDIFF(wk, TimeOrdered, TimeReceived)*48 as ReceiptHours

FROM <Orders Table>

WHERE <....>

) DT

Orders not received yet not gonna appear in this report. Should they?

But I have to warn you - this query will be very expensive in terms of server resouses and slow.

I would create separate table having data for this report and set up trigger on <Orders Table> to update report table when Order is recorded or TimeReceived is updated.

_____________
Code for TallyGenerator

Vandy SSCommitted Points: 1827 More actions · Answer 12

Thanks a lot Sergiy

I WILL TRY THIS ..........

Kind Regards

Vandy

Vandy SSCommitted Points: 1827 More actions · Answer 13

Here is my query

SELECT COUNT(OrderLineNumber) AS [Total Number Of Orders], COUNT(CASE WHEN DateInvoiced <= SystemOrderDate + 1 THEN OrderLineNumber ELSE NULL

END) AS [24 Hour Receipt], COUNT(CASE WHEN DateInvoiced > Systemorderdate + 1 AND

DateInvoiced <= SystemOrderDate + 2 THEN OrderLinenumber ELSE NULL END) AS [48 Hour Receipt],

COUNT(CASE WHEN DateInvoiced > SystemOrderDate + 2 AND DateInvoiced <= SystemOrderDate + 3 THEN Orderlinenumber ELSE NULL END)

AS [72 Hour Receipt], COUNT(CASE WHEN DateInvoiced > SystemOrderDate + 3 THEN OrderLineNumber ELSE NULL END) AS [72 Hour Plus],

Month

FROM DeliveryPerformanceV

WHERE <----Conditions----->

Group by Month

Sorry for bothering again i am a new user so don't know much about it...

Data Type of date is int(CYYMMDD format) that's why i am adding numbers.

Could anybody please explain how can i add order in 24 hour receipt which are ordered on friday and deliveried on monday. I need to extract that orders from 72 hour receipt also. Other thing we do have orders which are diliveried on same day means on Friday and next means Saturday also.

Thanks

Sergiy SSC Guru Points: 110192 More actions · Answer 14

Stupidiest idea to store date as int(CYYMMDD format)!

How you would explain Server that 20061201 = 20061130 + 1? Not to mention heaps of other problems created by this approach!

You need to convert this "date" into datetime. You may create new computed column with datetime value calculated from that freaky int. If you'll set up index on it this calculations will not affect performance at all.

Than you need to use the formula from my post to calculate hours excluding weekends. You may do it in subquery, as I did, or, again, in computed column in the same table.

Than you may build your SELECT.

_____________
Code for TallyGenerator