Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL syntax to create aging columns for order data

Posted on 2008-06-17
7
Medium Priority
?
758 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 143

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 750 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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