Solved

T-SQL syntax to create aging columns for order data

Posted on 2008-06-17
7
743 Views
Last Modified: 2012-05-05
I need help creating columns for the number of days since an order has been place, I cannot figure out how to code the query for the number of days since the order has been place. Ex: I have 37 total orders for a customer that have not shipped, 1 order is 0-1 day old and the rest are +5 days. I need my query to return results like the below example:

bill_to_code    bill_to_name                   0-1 day 2 day 3 day 4 day +5 day TotalOrder
--------------- ------------------------------ ------- ----- ----- ----- ------ -----------
100115          INGRAM MICRO CONSIGNED         1       0     0     0     36     37



Below is my query and the data it returns:

SELECT t_ord.bill_to_code,
t_ord.bill_to_name,
COUNT(DISTINCT t_ord.order_number) AS [TotalOrder],
CONVERT(nvarchar(10),t_ord.order_date,112) AS [OrderDate]
FROM t_order_detail tod
INNER JOIN t_order t_ord ON tod.order_id = t_ord.order_id
INNER JOIN t_item_master tim ON tod.item_master_id = tim.item_master_id
WHERE  t_ord.type_id <=5 AND
t_ord.status NOT IN ('S','X')
AND t_ord.bill_to_code = '100115'
GROUP BY t_ord.bill_to_code, t_ord.bill_to_name , CONVERT(nvarchar(10),t_ord.order_date,112)
ORDER BY bill_to_code


bill_to_code    bill_to_name                   TotalOrder  OrderDate
--------------- ------------------------------ ----------- ----------
100115          INGRAM MICRO CONSIGNED         5           20080516
100115          INGRAM MICRO CONSIGNED         2           20080520
100115          INGRAM MICRO CONSIGNED         1           20080604
100115          INGRAM MICRO CONSIGNED         28          20080605
100115          INGRAM MICRO CONSIGNED         1           20080617

Im sure it is some nested query or union to get the result I want but I just cannot get the syntax correct.

Thank you,
Larry Bye
0
Comment
Question by:larrybye
  • 3
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21807550
nothing complex:
SELECT t_ord.bill_to_code,
t_ord.bill_to_name,
COUNT(DISTINCT t_ord.order_number) AS [TotalOrder]
, SUM(CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) <= 1 THEN 1 ELSE 0 END) [0-1 day]
, SUM(CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) = 2 THEN 1 ELSE 0 END) [2 day]
, SUM(CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) = 3 THEN 1 ELSE 0 END) [3 day]
, SUM(CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) = 4 THEN 1 ELSE 0 END) [4 day]
, SUM(CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) >= 5 THEN 1 ELSE 0 END) [5+ day]
FROM t_order_detail tod
INNER JOIN t_order t_ord ON tod.order_id = t_ord.order_id
INNER JOIN t_item_master tim ON tod.item_master_id = tim.item_master_id
WHERE  t_ord.type_id <=5 AND
t_ord.status NOT IN ('S','X')
AND t_ord.bill_to_code = '100115'
GROUP BY t_ord.bill_to_code, t_ord.bill_to_name 
ORDER BY bill_to_code

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 21808821
Not a nested, nor a union, just a group by and a case statement....

Because you are just looking at order headers, have taken out the order detail lines so the counts do not reflect detail lines, just the headers... Maybe that is where you thought you might need a subquery of some description...


SELECT o.bill_to_code, o.bill_to_name, COUNT(*) AS [TotalOrders],
, SUM(CASE WHEN DATEDIFF(day, o.order_date, getdate()) <= 1 THEN 1 ELSE 0 END) [0-1 day]
, SUM(CASE WHEN DATEDIFF(day, o.order_date, getdate()) = 2 THEN 1 ELSE 0 END) [2 day]
, SUM(CASE WHEN DATEDIFF(day, o.order_date, getdate()) = 3 THEN 1 ELSE 0 END) [3 day]
, SUM(CASE WHEN DATEDIFF(day, o.order_date, getdate()) = 4 THEN 1 ELSE 0 END) [4 day]
, SUM(CASE WHEN DATEDIFF(day, o.order_date, getdate()) >= 5 THEN 1 ELSE 0 END) [5+ day]

FROM t_order o

WHERE  o.type_id <=5
AND o.status NOT IN ('S','X')
AND o.bill_to_code = '100115'

GROUP o.BY bill_to_code, o.bill_to_name
ORDER BY bill_to_code

0
 

Author Comment

by:larrybye
ID: 21812308
I am returning data from the other tables but did not include them in the example since the data returned is correct and formatting it to display nice on the web was not going very good.

Using the code you've provide returns the sum of the detail rows for the order date field; I need the distinct count of the order number returns (sum of "days" columns should equal the TotalOrder column:

New Code for aging columns added to my query returns:

bill_to_code    bill_to_name                   0-1 day 2 day 3 day 4 day +5 day TotalOrder
--------------- ------------------------------ ------- ----- ----- ----- ------ -----------
100115          INGRAM MICRO CONSIGNED         2       0     0     0     130     37

The order with a date of 20080617 had 2 line items and the other 36 orders have a total of a 130 line items.

The code is a huge step in the right direction.

---------------------------------------------------------------------------------------------------------
Here is the complete data returned from my query with the new code fro aging columns:

bill_to_code    bill_to_name                   0-1 day 2 day 3 day 4 day +5 day TotalOrder
--------------- ------------------------------ ------- ----- ----- ----- ------ -----------
100115          INGRAM MICRO CONSIGNED         2       0     0     0     130     37


AverageOrder     TotalOrderAmt     OrderLines  Pieces                    ShipTo    SKUs
--------------------- --------------------- -----------     ---------------------- ----------- -----------
2471.4368             91443.16              132             3539                      5              35
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:larrybye
ID: 21814475
OK, by changing the code provided slightly Ive been able to get the correct results returned but with one warning:

COUNT(distinct CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) <= 1 THEN t_ord.order_number ELSE NULL END) [0-1 day],
COUNT(distinct CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) = 2 THEN t_ord.order_number ELSE NULL END) [2 day],
COUNT(distinct CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) = 3 THEN t_ord.order_number ELSE NULL END) [3 day],
COUNT(distinct CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) = 4 THEN t_ord.order_number ELSE NULL END) [4 day],
COUNT(distinct CASE WHEN DATEDIFF(day, t_ord.order_date, getdate()) >= 5 THEN t_ord.order_number ELSE NULL END) [5+ day]


bill_to_code    bill_to_name                   0-1 day 2 day 3 day 4 day +5 day TotalOrder
--------------- ------------------------------ ------- ----- ----- ----- ------ -----------
100115          INGRAM MICRO CONSIGNED         1       0     0     0     36     37
Warning: Null value is eliminated by an aggregate or other SET operation.

Ive tried the ISNULL function but it returns the same error. Any ideas on how to resolve this?

Thank you again
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21814544
Yeah, using NULL is not a good idea... there are some settings that will turn off that warning message, but if running in a script or procedure will turn the warning into an error and it will then fail...

You obviously haven't tried my posting because the previous outputs shows a count of lines...  Why not try it ?

0
 

Author Comment

by:larrybye
ID: 21821403
Mark,
I stated in my first reply I am returning data from those tables but did not include that information in my example; So that being said your code will not work as is because it will return a count that includes the detail lines when I tried it with the complete query I posted.

An alternative would be to use your code an insert the results into a temp table and then join it to the detail and item master table but I would like to avoid having to do that if possible.

Again the code you have given is a huge step in getting me to the result I am trying to achieve. Just need to figure a way to handle the warning message.

Thanks again
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21822444
Was just answering your original posting with the sample output that you said you needed to achieve. In that example output there was no need to link to detail items. So my code does not use detail lines. Not sure why you think it does, or, why you think it will reflect counts of detail lines. It was the entire basis of my posting - ie no details, and suggest thaat there is a huge misunderstanding of what was posted.

If you need different results to the results that you say you want, then it would help to provide the real outcomes, not something else...

Now combining detailed line information (which is still not apparent in any of your sample outputs), and provide summarised data from just the header level is never going to aggregate properly if trying to show both on the same line / row.

Now, that warning can be controlled via various settings (such as SET ANSI_NULLS, SET ANSI_WARNINGS) but they will bite you - by either failing to count properly, or,
if included in a batch or stored proc, can still cause a "hard" error instead of a warning message. That escalation is a new feature in 2005.

So, would strongly suggest you please post the real outcomes/ requirement, otherwise you will continue to post reasons as to why any of our suggestions will not work for you. In the meantime, I am also fascinated as to why you think my first posting contains anything remotely to do with detail lines.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now