[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

T-SQL syntax to create aging columns for order data

Posted on 2008-06-17
7
Medium Priority
?
756 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

649 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