Aging Calculation for Items, Based on Status

Question

Post reply

Aging Calculation for Items, Based on Status

homebrew01

SSC Guru

Points: 55548
More actions
August 26, 2010 at 1:15 pm

#237245

I'm stuck, but I know this can't be too hard.
I have warehouse transactions that are used to calculate how long an item has been in the warehouse (aging), but only if they are a certain status.
[font="Courier New"]Table:
Inv_Warehouse
Part# Char(20)
Date Datetime
Status Char(30)
Seq# int
For instance
Part# Date Status Unique Seq#
------ --------- -------- ---------
1 Aug 1 InHouse 1
1 Aug 2 Active 2
1 Aug 3 Delivered 3
2 Aug 1 InHouse 4
2 Aug 2 Active 5
2 Aug 3 Delivered 6
2 Aug 4 Returned 7
3 Aug 1 InHouse 8
3 Aug 2 Active 9
3 Aug 3 Repair 10[/font]
Aging is calculated from the most recent code that is: 'InHouse', 'Delivered' or 'Returned'
so ignore other status codes such as 'Active' & 'Repair' even if they are more recent.
So, Assuming today's date is Aug 10:
Part 1 age is 7 ('Delivered' Difference from Aug 10 to Aug 3)
Part 2 age is 6 ('Returned' Difference from Aug 10 to Aug 4)
Part 3 age is 9 ('InHouse Difference from Aug 10 to Aug 1)
I've been messing with sub-queries and trying to use max(date) or max(Seq#) but can't seem to get it right.
Thoughts ?

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like the following code?

(Side note: untested due to the lack of ready to use sample data)

SELECT Part,DATEDIFF(dd,MAX(DATE),'20100810') AS age

FROM Inv_Warehouse

WHERE Status IN('InHouse', 'Delivered', 'Returned')

GROUP BY Part

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I think that gives me the right dates, but is there a way to add more fields to the select ? Seems like I have to add them to the 'group by' also, which changes the result to include multiple rows per part due to the different status codes.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Maybe it would help if you'd post table def and sample data in a ready to use format as well as expected result. Would save those volunteers around a lot of time if we could stop guessing.... :unsure:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

homebrew01 (8/26/2010)
I'm stuck, but I know this can't be too hard.
I have warehouse transactions that are used to calculate how long an item has been in the warehouse (aging), but only if they are a certain status.
[font="Courier New"]Table:
Inv_Warehouse
Part# Char(20)
Date Datetime
Status Char(30)
Seq# int
For instance
Part# Date Status Unique Seq#
------ --------- -------- ---------
1 Aug 1 InHouse 1
1 Aug 2 Active 2
1 Aug 3 Delivered 3
2 Aug 1 InHouse 4
2 Aug 2 Active 5
2 Aug 3 Delivered 6
2 Aug 4 Returned 7
3 Aug 1 InHouse 8
3 Aug 2 Active 9
3 Aug 3 Repair 10[/font]
Aging is calculated from the most recent code that is: 'InHouse', 'Delivered' or 'Returned'
so ignore other status codes such as 'Active' & 'Repair' even if they are more recent.
So, Assuming today's date is Aug 10:
Part 1 age is 7 ('Delivered' Difference from Aug 10 to Aug 3)
Part 2 age is 6 ('Returned' Difference from Aug 10 to Aug 4)
Part 3 age is 9 ('InHouse Difference from Aug 10 to Aug 1)
I've been messing with sub-queries and trying to use max(date) or max(Seq#) but can't seem to get it right.
Thoughts ?

Heh... you've been around a long time and you know the rules... see the first link in my signature below. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

LutzM (8/26/2010)
Maybe it would help if you'd post table def and sample data in a ready to use format as well as expected result. Would save those volunteers around a lot of time if we could stop guessing.... :unsure:

Yeah, I shoulda known better:

CREATE TABLE [dbo].[Inv_Warehouse_Sample](

[_id] [bigint] NOT NULL,

[_ResourceGuid] [uniqueidentifier] NOT NULL,

[Serial_Number] [nvarchar](50) NULL,

[Status] [nvarchar](50) NULL,

[Date] [datetime] NULL

) ON [PRIMARY]

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Insert Into Inv_Warehouse_Sample Select _id=728307, _ResourceGuid='DF103277-6BF3-4773-BEDC-8FD35DA3EC72', Serial_Number='NG3520TA0211', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=938127, _ResourceGuid='DF103277-6BF3-4773-BEDC-8FD35DA3EC72', Serial_Number='NG3520TA0211', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1655261, _ResourceGuid='DF103277-6BF3-4773-BEDC-8FD35DA3EC72', Serial_Number='NG3520TA0211', Status='Customer Account', Date='2009-12-16 13:52:42.000'

Insert Into Inv_Warehouse_Sample Select _id=4628207, _ResourceGuid='849436F8-076F-43D2-8003-FB37C887A49F', Serial_Number='NG3520TA0207', Status='In from Customer', Date='2010-07-28 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=899985, _ResourceGuid='D67AB5AC-AF7B-45BC-9566-BC3D7D64C7B0', Serial_Number='NG3520TA0217', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=901175, _ResourceGuid='D67AB5AC-AF7B-45BC-9566-BC3D7D64C7B0', Serial_Number='NG3520TA0217', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1664973, _ResourceGuid='D67AB5AC-AF7B-45BC-9566-BC3D7D64C7B0', Serial_Number='NG3520TA0217', Status='On Order', Date='2009-12-16 13:52:42.000'

Insert Into Inv_Warehouse_Sample Select _id=2255870, _ResourceGuid='D67AB5AC-AF7B-45BC-9566-BC3D7D64C7B0', Serial_Number='NG3520TA0217', Status='Customer Account', Date='2009-12-16 13:52:42.000'

Insert Into Inv_Warehouse_Sample Select _id=918529, _ResourceGuid='1A510EB9-2782-43DD-BE69-BF80371117EF', Serial_Number='NG3520TA3164', Status='Return to Customer', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1591215, _ResourceGuid='1A510EB9-2782-43DD-BE69-BF80371117EF', Serial_Number='NG3520TA3164', Status='Return to Customer', Date='2009-12-16 16:35:45.000'

Insert Into Inv_Warehouse_Sample Select _id=3018389, _ResourceGuid='D906FCA1-2C04-468A-929A-F3ECDA8C8205', Serial_Number='NG3520TA2821', Status='Return to Customer', Date='2010-02-22 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=245154, _ResourceGuid='6BB733AE-FF67-4646-AA17-35B2FE9667B8', Serial_Number='NG3520TA0213', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1610501, _ResourceGuid='6BB733AE-FF67-4646-AA17-35B2FE9667B8', Serial_Number='NG3520TA0213', Status='Customer Account', Date='2009-12-16 13:52:42.000'

Insert Into Inv_Warehouse_Sample Select _id=3766319, _ResourceGuid='4979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBF', Serial_Number='NG3520TA0216', Status='In from Customer', Date='2010-05-05 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=3976454, _ResourceGuid='4979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBF', Serial_Number='NG3520TA0216', Status='On Order', Date='2010-05-27 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=3977165, _ResourceGuid='4979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBF', Serial_Number='NG3520TA0216', Status='RT', Date='2010-05-27 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=4170935, _ResourceGuid='4979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBF', Serial_Number='NG3520TA0216', Status='Return to Customer', Date='2010-06-15 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=4256358, _ResourceGuid='4979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBF', Serial_Number='NG3520TA0216', Status='Return to Customer', Date='2010-06-23 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=4628197, _ResourceGuid='4979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBF', Serial_Number='NG3520TA0216', Status='In from Customer', Date='2010-07-28 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=241593, _ResourceGuid='9D61F0B1-8FD2-44A0-B2CC-35E48E28CD21', Serial_Number='NG3520TA3210', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=295310, _ResourceGuid='9D61F0B1-8FD2-44A0-B2CC-35E48E28CD21', Serial_Number='NG3520TA3210', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1799874, _ResourceGuid='9D61F0B1-8FD2-44A0-B2CC-35E48E28CD21', Serial_Number='NG3520TA3210', Status='On Order', Date='2009-12-16 13:50:19.000'

Insert Into Inv_Warehouse_Sample Select _id=2224127, _ResourceGuid='9D61F0B1-8FD2-44A0-B2CC-35E48E28CD21', Serial_Number='NG3520TA3210', Status='Customer Account', Date='2009-12-16 13:50:19.000'

Insert Into Inv_Warehouse_Sample Select _id=815633, _ResourceGuid='F661C0B2-B948-4234-A91D-BBEEBEDFF43A', Serial_Number='NG3520TA0165', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1140377, _ResourceGuid='F661C0B2-B948-4234-A91D-BBEEBEDFF43A', Serial_Number='NG3520TA0165', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=2059438, _ResourceGuid='F661C0B2-B948-4234-A91D-BBEEBEDFF43A', Serial_Number='NG3520TA0165', Status='On Order', Date='2009-12-16 13:47:12.000'

Insert Into Inv_Warehouse_Sample Select _id=2597022, _ResourceGuid='F661C0B2-B948-4234-A91D-BBEEBEDFF43A', Serial_Number='NG3520TA0165', Status='Customer Account', Date='2009-12-16 13:47:12.000'

Insert Into Inv_Warehouse_Sample Select _id=542476, _ResourceGuid='D2008E0B-8FDA-494C-AE3C-74ECBA00978E', Serial_Number='NG3520TA0227', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1357536, _ResourceGuid='D2008E0B-8FDA-494C-AE3C-74ECBA00978E', Serial_Number='NG3520TA0227', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1920024, _ResourceGuid='D2008E0B-8FDA-494C-AE3C-74ECBA00978E', Serial_Number='NG3520TA0227', Status='Customer Account', Date='2009-12-16 13:52:42.000'

Insert Into Inv_Warehouse_Sample Select _id=611672, _ResourceGuid='5CDD657D-7F24-4A50-858A-83BD52666A57', Serial_Number='NG3520TA2826', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=638119, _ResourceGuid='5CDD657D-7F24-4A50-858A-83BD52666A57', Serial_Number='NG3520TA2826', Status='In from Customer', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1730491, _ResourceGuid='5CDD657D-7F24-4A50-858A-83BD52666A57', Serial_Number='NG3520TA2826', Status='In from Customer', Date='2009-12-16 14:40:57.000'

Insert Into Inv_Warehouse_Sample Select _id=3672482, _ResourceGuid='5CDD657D-7F24-4A50-858A-83BD52666A57', Serial_Number='NG3520TA2826', Status='Return to Customer', Date='2010-04-26 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=878772, _ResourceGuid='1D716EDD-7C77-4034-8CB1-B7EDB443175D', Serial_Number='NG3520TA0372', Status='On Order', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1122886, _ResourceGuid='1D716EDD-7C77-4034-8CB1-B7EDB443175D', Serial_Number='NG3520TA0372', Status='Return to Customer', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=1706679, _ResourceGuid='1D716EDD-7C77-4034-8CB1-B7EDB443175D', Serial_Number='NG3520TA0372', Status='Return to Customer', Date='2009-12-16 16:35:48.000'

Insert Into Inv_Warehouse_Sample Select _id=970886, _ResourceGuid='102CF428-FF54-4494-A609-CA788B70F727', Serial_Number='NG3520TA0279', Status='Customer Account', Date='2009-12-16 00:00:00.000'

Insert Into Inv_Warehouse_Sample Select _id=2259608, _ResourceGuid='102CF428-FF54-4494-A609-CA788B70F727', Serial_Number='NG3520TA0279', Status='Customer Account', Date='2009-12-16 13:51:37.000'

-- based on Lutz's reply:

SELECT DATEDIFF(dd,MAX(DATE),getdate()) AS age, [serial_number]

FROM [Inv_Warehouse_Sample]

WHERE Status IN ('In from Customer', 'Tech Pick-Up', 'Return to Customer','Customer Account','Disposed','Lost')

and date >= '2010-01-01'

GROUP BY [serial_number]

order by [serial_number]

Results look good, but want to show all fields

AgeSerial_Number

--- -------------

29NG3520TA0207

29NG3520TA0216

185NG3520TA2821

122NG3520TA2826

-- Current 'Attempt' to show all fields

select Datediff(day,W.date,GetDate()) as Age, W.*

from [Inv_Warehouse_Sample] W

WHERE(EXISTS

(SELECT DATEDIFF(dd,MAX(DATE),getdate()) AS age, [serial_number]

FROM [Inv_Warehouse_Sample]

WHERE Status IN ('In from Customer', 'Tech Pick-Up', 'Return to Customer','Customer Account','Disposed','Lost')

and date >= '2010-01-01'

and W._id = _id

GROUP BY [serial_number]))

order by [serial_number], date

Current Results: Note duplicate Serial_Number in the list

AGE _ID_ResourceGuidSerial_NumberStatusDate

-----------------------------------------------------------------------------------------

294628207849436F8-076F-43D2-8003-FB37C887A49FNG3520TA0207In from Customer2010-07-28 00:00:00.000

11337663194979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBFNG3520TA0216In from Customer2010-05-05 00:00:00.000

7241709354979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBFNG3520TA0216Return to Customer2010-06-15 00:00:00.000

6442563584979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBFNG3520TA0216Return to Customer2010-06-23 00:00:00.000

2946281974979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBFNG3520TA0216In from Customer2010-07-28 00:00:00.000

1853018389D906FCA1-2C04-468A-929A-F3ECDA8C8205NG3520TA2821Return to Customer2010-02-22 00:00:00.000

12236724825CDD657D-7F24-4A50-858A-83BD52666A57NG3520TA2826Return to Customer2010-04-26 00:00:00.000

Desired Results: Most Recent Serial_Number from list of 'Status' codes

AGE _ID_ResourceGuidSerial_NumberStatusDate

-----------------------------------------------------------------------------------------

294628207849436F8-076F-43D2-8003-FB37C887A49FNG3520TA0207In from Customer2010-07-28 00:00:00.000

2946281974979BA7F-4A0F-417E-BAEB-B7AE6C0DDBBFNG3520TA0216In from Customer2010-07-28 00:00:00.000

1853018389D906FCA1-2C04-468A-929A-F3ECDA8C8205NG3520TA2821Return to Customer2010-02-22 00:00:00.000

12236724825CDD657D-7F24-4A50-858A-83BD52666A57NG3520TA2826Return to Customer2010-04-26 00:00:00.000

drew.allen SSC Guru Points: 76985 More actions · Answer 6

If you want to return fields that you don't want to group on, a CTE with Row_Number() is often a good approach.

I changed your Table to a Table variable, but the logic is the same.

WITH Ordered_Inv AS (

SELECT DateDiff(Day, Date, GetDate()) AS Age, _id, _ResourceGuid

, Serial_Number, Status, Date

, Row_Number() OVER( PARTITION BY Serial_Number ORDER BY Date DESC ) AS Row

FROM @Inv_Warehouse_Sample

WHERE Status IN ('In from Customer', 'Tech Pick-Up', 'Return to Customer','Customer Account','Disposed','Lost')

and date >= '2010-01-01'

)

SELECT Age, _id, _ResourceGuid, Serial_Number, Status, Date

FROM Ordered_Inv

WHERE Row = 1

ORDER BY Serial_Number

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA