T-SQL: Aggregate and/or conditional subquery on where clause
-
Hello Everyone,
Thanks in advance for reading my post and for your help. I'm trying to get a list of inventory lots that haven't had shipments in over 90 days based on the transaction date in the lot history table.
Background info/conditions:
*There are 3 tables I'm working with: Item, Lot and LotHist
*CurrentQty from the LOT table has to be > 0
*There are only two transaction types to be considered: Shipment, Receipt (only in the absence of shipment transactions for a particular lot)
*TransDate from LOTHIST table has to be > 90 days from current date (we'll use 5/31/16 as the today's date)
*There could be multiple receipts/shipments for a particular lot. For example, if there are many different shipments/receipts transactions for a particular lot, I'm only interested in evaluating the most recent transaction Max(TransDate) for that particular lot. If the most recent shipment transaction is older than 90 days, then the record would be selected
*There could be transactions for a lot, that only have receipt transaction types, meaning no shipments have been made for that lot. In this case and only this case where no shipment transactions are present for a lot, then the most recent transaction Max(TransDate) would be evaluated and if greater than 90 days, then it would be selected. An example of this is the transaction for lot # KGKO which only has receipt transactions on the LOTHIST table.
I have included a "Desired Query Results" I put together manually which illustrates the result of this logic.
I'm very puzzled on how to approach this. I look forward to reading your approach. Thanks again!
SELECT LOT.LotNo, ITEM.ItemNo, ITEM.[DESC], LOT.Warehouse, LOT.CurrentQty, LOT.Cost, LOTHIST.TransDate, LOT.TransType, LOTHIST.TransQtyFROM LOT INNER JOIN ITEM ON LOT.ItemNo = ITEM.ItemNo
INNER JOIN LOTHIST ON LOTHIST.LotNo = LOT.LotNo
WHERE LOTHIST.TransType = 'Shipment' OR LOTHIST.TransType = 'Receipt' AND LOT.CurrentQty > 0 AND (LOTHIST.TransDate) > GETDATE()- 90)
-
I'm a little confused on your requirements.
Is this correct:
LOT must be more recent than 90 days in past
Most recent SHIPMENT must be earlier than 90 days in past
Is that what it boils down to?
If so, there are a number of ways to do that. The simplest is using Not Exists and a sub-query of the shipments.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Hello SSChampion,
I'm sorry for the confusion. The result set I'm looking for, should include only lots with transaction dates older than 90 days. Also only interested in transaction type of "Shipment" for a lot and receipt only if there are no shipment transactions for each particular lot.
The sample date I'm using for the current date is: 5/31/16 so 90 days prior would be 3/2/16, so the selections would be based on dates older than: 3/2/16
I hope this clarifies your question.
I included a link to a picture with the sample tables/data, are you able to see it?
-
Please ALWAYS include create table statements and all relevant sample data and your expected output from said data. That way we can be sure to write a query that covers all of your needs, and ask intelligent questions if we are uncertain.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Hello Kevin,
Sorry about that, I'm new to this type of forum. Here is the code.
CREATE TABLE [dbo].[Item](
[ItemNo] [nvarchar](255) NULL,
[Desc] [nvarchar](255) NULL
)
INSERT [dbo].[Item] ([ItemNo], [Desc]) VALUES (N'10-LB-Tomato', N'10 Lb Bag of Tomatos')
INSERT [dbo].[Item] ([ItemNo], [Desc]) VALUES (N'12-LB-Bean', N'12 LB Bag of Beans')
INSERT [dbo].[Item] ([ItemNo], [Desc]) VALUES (N'6-LB-Rice', N'7 Lb Bag of Rice')
CREATE TABLE [dbo].[Lot](
[LotNo] [nvarchar](255) NULL,
[ItemNo] [nvarchar](255) NULL,
[Warehouse] [nvarchar](255) NULL,
[CurrentQty] [float] NULL,
[Cost] [money] NULL
)
INSERT [dbo].[Lot] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'ABWC', N'10-LB-Tomato', N'Chigaco', 125, 754.0000)
INSERT [dbo].[Lot] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'KGKO', N'12-LB-Bean', N'Los Angeles', 215, 1250.0000)
INSERT [dbo].[Lot] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'TYWU', N'6-LB-Rice', N'New York', 345, 1725.0000)
CREATE TABLE [dbo].[LotHist](
[TransNo] [float] NULL,
[LotNo] [nvarchar](255) NULL,
[TransType] [nvarchar](255) NULL,
[TransDate] [date] NULL,
[TransQty] [float] NULL
)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (1, N'ABWC', N'Receipt', '2016-01-01',15)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (3, N'ABWC', N'Shipment', '2016-02-03',14)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (2, N'ABWC', N'Shipment', '2016-02-11',32)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (4, N'ABWC', N'Receipt', '2016-02-12',23)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (7, N'KGKO', N'Receipt', '2016-01-01',33)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (5, N'KGKO', N'Receipt', '2016-02-04',25)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (8, N'KGKO', N'Receipt', '2016-02-07',41)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (9, N'TYWU', N'Receipt', '2016-01-02',23)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (11, N'TYWU', N'Shipment','2016-01-22',36)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (10, N'TYWU', N'Shipment', '2016-02-20',24)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (14, N'TYWU', N'Shipment', '2016-02-22',85)
INSERT [dbo].[LotHist] ([TransNo], [LotNo], [TransType], [TransDate], [TransQty]) VALUES (13, N'TYWU', N'Receipt', '2016-02-25',14)
-
Even though you said that you attached desired results, I'm not seeing them. Would the following work?
;
WITH LotHistOrdered AS (
SELECT *, ROW_NUMBER()OVER(PARTITION BY lh.LotNo ORDER BY lh.TransType DESC, lh.TransDate DESC, lh.TransNo DESC) AS rn
FROM @LotHist lh
)
SELECT LOT.LotNo, ITEM.ItemNo, ITEM.[DESC], LOT.Warehouse, LOT.CurrentQty, LOT.Cost, LOTHIST.TransDate, lothist.TransType, LOTHIST.TransQty
FROM @LOT lot
INNER JOIN @ITEM item
ON LOT.ItemNo = ITEM.ItemNo
INNER JOIN LotHistOrdered lothist
ON LOTHIST.LotNo = LOT.LotNo
WHERE lothist.rn = 1
AND (LOTHIST.TransType = 'Shipment'
ORLOTHIST.TransType = 'Receipt' AND LOT.CurrentQty > 0 AND (LOTHIST.TransDate) > GETDATE()- 90
)
It uses a CTE with a ROW_NUMBER to sort the records in order of relevance and then selects only the most relevant row. (I'm not sure that I got the correct order of relevance.)
Drew
PS: I prefer to use temp tables or table variables for sample data, because I don't want to clutter up my dev environment if I forget to DROP them.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hello Drew,
Thanks for your reply. I see that your code starts with:
;
WITH
I'm getting an error saying that the variable, is some of your code missing?
Also, I included the image with all the tables and the results table. It's a little image icon on the bottom of the post, I think depending on your browser it may be clickable or not, you may have to right click on it and select open image in another tab.
-
marcossuriel (5/31/2016)
Hello Drew,Thanks for your reply. I see that your code starts with:
;
WITH
I'm getting an error saying that the variable, is some of your code missing?
Also, I included the image with all the tables and the results table. It's a little image icon on the bottom of the post, I think depending on your browser it may be clickable or not, you may have to right click on it and select open image in another tab.
I had code to set up the table variables. I did not include that, because I figured that you would want to rewrite this for your actual tables.
I also think that your error message got truncated.
When attaching desired results, it's best to set it up using SELECT statements in the same manner as you set up SELECT statements for your sample data. The image was showing up as missing, so I didn't try to open it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hello Drew,
I replaced the variables with my table names (which are saved and contain data) on my SQL Server, but it does not work. I get "Invalid object name" for all of them in the code.
Also, I didn't know how to include the results in code, because that's what I'm trying to figure out, how to write the query to produce the results I want.
I think the best bet would be to open the picture I attached so you can see the entire scenario, including a results table.
-
marcossuriel (5/31/2016)
Hello Drew,I replaced the variables with my table names (which are saved and contain data) on my SQL Server, but it does not work. I get "Invalid object name" for all of them in the code.
Also, I didn't know how to include the results in code, because that's what I'm trying to figure out, how to write the query to produce the results I want.
I think the best bet would be to open the picture I attached so you can see the entire scenario, including a results table.
Since you didn't provide your modified code, it's impossible for anyone to determine where the issue might be.
Use the table value constructor or a series of SELECT/UNION ALL statements to hard-code the desired results, just like you hard-coded the sample date. You don't need to write a query to derive the desired results from the sample data in order to hard-code the desired results.
The best bet is to make it as easy as possible for people to work on your problem. If you provide code that people can cut and paste into SSMS that creates the sample data and the desired results, they are far more likely to work on it. People cannot cut and paste a graphic into SSMS to get your desired results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hello Drew,
Thank you so much for sticking around to help, I'm learning SQL as well as how to use the forum as I go. I realized I made a mistake, your code runs just fine with the hard coded table names. It's very close to producing the results I'm looking for. It produces 2 records from the sample hard coded data I provided. It should output 3 records, one of those being a receipt record. I'm going to try to hard code a select statement that produces the 3 records I'm looking for. I will post it in the next reply. In the meantime here's the changed code:
WITH LotHistOrdered AS (SELECT *, ROW_NUMBER()OVER(PARTITION BY lh.LotNo ORDER BY lh.TransType DESC, lh.TransDate DESC, lh.TransNo DESC) AS rn
FROM LotHist lh
)
SELECT LOT.LotNo, ITEM.ItemNo, ITEM.[DESC], LOT.Warehouse, LOT.CurrentQty, LOT.Cost, LOTHIST.TransDate, lothist.TransType, LOTHIST.TransQty
FROM LOT
INNER JOIN ITEM
ON LOT.ItemNo = ITEM.ItemNo
INNER JOIN LotHistOrdered lothist
ON LOTHIST.LotNo = LOT.LotNo
WHERE lothist.rn = 1
AND (LOTHIST.TransType = 'Shipment'
ORLOTHIST.TransType = 'Receipt' AND LOT.CurrentQty > 0 AND (LOTHIST.TransDate) > GETDATE()- 90
)
-
desired results for you
+-----------------------------------------------------------------------------------------------------------------------+¦ DESIRED QUERY RESULTS ¦
¦-----------------------------------------------------------------------------------------------------------------------¦
¦ LotNo ¦ ItemNo ¦ Desc ¦ Warehouse ¦ CurrentQty ¦ Cost ¦ TransDate ¦ TransType ¦ TransQty ¦
¦-------+--------------+----------------------+-------------+------------+-----------+-----------+-----------+----------¦
¦ ABWC ¦ 10-LB-Tomato ¦ 10 Lb Bag of Tomatos ¦ Chicago ¦ 125 ¦ $754.00 ¦ 2/11/2016 ¦ Shipment ¦ 32 ¦
¦-------+--------------+----------------------+-------------+------------+-----------+-----------+-----------+----------¦
¦ KGKO ¦ 12-LB-Bean ¦ 12 LB Bag of Beans ¦ Los Angeles ¦ 215 ¦ $1,250.00 ¦ 2/7/2016 ¦ Receipt ¦ 41 ¦
¦-------+--------------+----------------------+-------------+------------+-----------+-----------+-----------+----------¦
¦ TYWU ¦ 6-LB-Rice ¦ 7 Lb Bag of Rice ¦ New York ¦ 345 ¦ $1,725.00 ¦ 2/22/2016 ¦ Shipment ¦ 85 ¦
+-----------------------------------------------------------------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Hey SSCrazy,
Thank you so much, that's exactly the results I'm looking for. If I could just get the query's logic to do this work it would be amazing. Thanks a lot guys for your help.
-
I think the following code
(LOTHIST.TransDate) > GETDATE()- 90
should be
(LOTHIST.TransDate) < GETDATE()- 90
Also, the parens around LOTHIST.TransDate are completely unnecessary.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Oh WOW this works PERFECTLY!!!! Thank you sooooo much Drew, and the other guys for your input. I have been breaking my head with this for weeks before posting it. I really really really appreciate it, you have no idea 🙂
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply