SQLTeam.com | Weblogs | Forums

T-SQL: Aggregate and/or conditional subquery on where clause


#1

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!

Below is the T-SQL to create the tables with sample data

CREATE TABLE [dbo].[Item](
[ItemNo] nvarchar NULL,
[Desc] nvarchar 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 NULL,
[ItemNo] nvarchar NULL,
[Warehouse] nvarchar 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 NULL,
[TransType] nvarchar 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)


#2

Maybe this:

with cte_lothist(transno,rn)
  as (select b.transno
            ,row_number() over(partition by b.lotno
                               order by case
                                           when b.transtype='Shipment'
                                           then 0
                                           else 1
                                         end
                                        ,b.transdate desc
                              )
             as rn
        from dbo.lot as a
             inner join dbo.lothist as b
                     on b.lotno=a.lotno
                    and b.transtype in ('Shipment','Receipt')
       where a.currentqty>0
     )
select * /* specify which fields you want to see */
  from dbo.item as a
       inner join dbo.lot as b
               on b.itemno=a.itemno
       inner join dbo.lothist as c
               on c.lotno=b.lotno
       inner join cte_lothist as d
               on d.transno=c.transno
              and d.rn=1
;

#3

Hello bitsmed,

Thank you very much for your reply. I took your code and adapted and added the fields I wanted, removed the transno and replaced with the lotnum because I realized there was no transno on that table, the lotnum is the primary key. I also replaced 'shipment' and 'receipt' with their respective values on the database 4 (shipment) and 1 (receipt). I then ran the code, but I'm getting all kinds of values for the transaction types, the resultset does not only limit it to 4 or 1. Also I'm getting records with CurrentQty of 0 being returned and the transaction date should be greater than 90 days from current date.

with cte_lothist(lotum, rn)
as (select b.lotnum, row_number() over(partition by b.lotnum
order by case
when b.transtype=4
then 0
else 1
end
,b.transdate desc
)
as rn
from dbo.lot as a
inner join dbo.lothist as b
on b.LOTNUM=a.LOTNUM
and b.transtype in (4,1)
where a.QTYAVAIL>0
)
select b.LOTNUM LotNo, a.FMTITEMNO ItemNo, a.[DESC], b.LOCATION Warehouse, b.QTYAVAIL CurrentQty , b.ASSETCOST Cost,
dbo.fConvertDate(c.TRANSDATE) TransDate, c.TransType, c.QTY TransQty
from dbo.item as a
inner join dbo.lot as b
on b.ITEMNUM=a.ITEMNO
inner join dbo.lothist as c
on c.LOTNUM=b.LOTNUM
inner join cte_lothist as d
on d.lotum=c.LOTNUM
and d.rn=1


#4

Please provide:

  • description of the new/correct tables
  • sample data from the new/correct tables

Also, I forgot to limit according to the 90 day rule. To achieve this, add this where statement right after "and d.rn=1":

 where datediff(day,c.transdate,cast(current_timestamp as date))>90

#5

Hello bitsmed,
Here's the script I generated using the some sample actual data from the live database:

USE [Test]
GO
/****** Object: Table [dbo].[ITEM] Script Date: 6/7/2016 7:59:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ITEM](
[ItemNo] char NOT NULL,
[Desc] char NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[LOT] Script Date: 6/7/2016 7:59:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOT](
[LotNo] char NOT NULL,
[ItemNo] char NOT NULL,
[Warehouse] char NOT NULL,
[CurrentQty] [decimal](19, 4) NOT NULL,
[Cost] [decimal](19, 3) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[LOTHIST] Script Date: 6/7/2016 7:59:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOTHIST](
[LotNo] char NOT NULL,
[TransType] [smallint] NOT NULL,
[TransDate] [decimal](9, 0) NOT NULL,
[TransQty] [decimal](19, 4) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10APPLDIC19851 ', N'APPLES DICED WATER IMP ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10APPLSAU17653 ', N'APPLESAUCE UNSWEETENED IMP ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10APPLSLI19738 ', N'APPLES SLICED WATER SOLID PACK IMP ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10APPLSWE19861 ', N'APPLESAUCE SWEETENED ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10APRIHAL19862 ', N'APRICOTS HALVED LS ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEANPIN13783 ', N'BEANS PINTO LOW SODIUM ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEANPIN17661 ', N'BEANS PINTO ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEANVEG15762 ', N'BEANS VEGETARIAN LOW SODIUM ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEETDIC12977 ', N'BEETS DICED ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEETSLI16786 ', N'BEETS SLICED LARGE ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEETSLI19451 ', N'BEETS SLICED FANCY ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEETWHO11834 ', N'BEETS WHOLE 100 CT 32653 ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEETWHO15757 ', N'BEETS WHOLE 100 CT ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BEETWHO15770 ', N'BEETS WHOLE 60/80 ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BLACBEA11023 ', N'BEANS BLACK ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10BLACBEA17659 ', N'BEANS BLACK LOW SODIUM ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10CARRLAR11081 ', N'CARROTS SLICED LARGE ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10CORNCHI19332 ', N'CORN VPAC CHIPOTLE SPICY ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10FRUICOC19863 ', N'FRUIT COCKTAIL XLS ')
INSERT [dbo].[ITEM] ([ItemNo], [Desc]) VALUES (N'10FRUIMIX15295 ', N'FRUIT MIX LS IMP ')
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'004344701ALCA0114 ', N'10CARRLAR11081 ', N'HARCA1', CAST(50.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005261001ALCA0114 ', N'10CARRLAR11081 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005542501TEQU0114 ', N'10BLACBEA11023 ', N'HARCA1', CAST(21.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005542501TEQU0120 ', N'10BLACBEA11023 ', N'HARCA1', CAST(65.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005783301TEQU0110 ', N'10BLACBEA11023 ', N'HARCA1', CAST(85.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005814505SEFO0114 ', N'10BEETDIC12977 ', N'TRX ', CAST(45.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005831904SEFO0114 ', N'10BEETWHO11834 ', N'HARCA1', CAST(69.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005936701ALCA0114 ', N'10CARRLAR11081 ', N'BSPTX1', CAST(87.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005965001TEQU0110 ', N'10BEANPIN13783 ', N'TRX ', CAST(321.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023501HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(11.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023501HAYU0110 ', N'10FRUIMIX15295 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023601HAYU0110 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023601HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(36.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023701HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023801HAYU0110 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023901HAYU0110 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(54.0000 AS Decimal(19, 4)), CAST(2365.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023901HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023902HAYU0110 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(97.0000 AS Decimal(19, 4)), CAST(3121.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078301HAYU0110 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078301HAYU0110 ', N'10FRUIMIX15295 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078501HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(98.0000 AS Decimal(19, 4)), CAST(1123.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078601HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078701HAYU0110 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(100.0000 AS Decimal(19, 4)), CAST(3233.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078901HAYU0110 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(150.0000 AS Decimal(19, 4)), CAST(4545.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006079001HAYU0110 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006079201HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006080101TEQU0126 ', N'10BEANPIN13783 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006083901NOOA0126 ', N'10BEANPIN13783 ', N'TRX ', CAST(35.0000 AS Decimal(19, 4)), CAST(1125.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084001NOOA0126 ', N'10BEANPIN13783 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084101NOOA0126 ', N'10BEANPIN13783 ', N'HARCA1', CAST(151.0000 AS Decimal(19, 4)), CAST(1212.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084201NOOA0126 ', N'10BEANPIN13783 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084301NOOA0126 ', N'10BEANPIN13783 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006097101HAYU0110 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(55.0000 AS Decimal(19, 4)), CAST(221.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135701HAYU0110 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135701HAYU0110 ', N'10FRUIMIX15295 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135801HAYU0110 ', N'10FRUIMIX15295 ', N'TRX ', CAST(42.0000 AS Decimal(19, 4)), CAST(1212.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135802LUFO0210 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135901LUFO0210 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135901LUFO0210 ', N'10FRUIMIX15295 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(365.0000 AS Decimal(19, 4)), CAST(1541.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 ', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 ', N'10FRUIMIX15295 ', N'TRX ', CAST(32.0000 AS Decimal(19, 4)), CAST(2330.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136002LUFO0210 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(78.0000 AS Decimal(19, 4)), CAST(1212.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136002LUFO0210 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(12.0000 AS Decimal(19, 4)), CAST(233.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136101LUFO0210 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(251.0000 AS Decimal(19, 4)), CAST(12122.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136201LUFO0210 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(112.0000 AS Decimal(19, 4)), CAST(1200.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136301LUFO0210 ', N'10FRUIMIX15295 ', N'BSPTX1', CAST(254.0000 AS Decimal(19, 4)), CAST(12122.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006154701LUFO0210 ', N'10FRUIMIX15295 ', N'ALFMD1', CAST(133.0000 AS Decimal(19, 4)), CAST(2311.000 AS Decimal(19, 3)))
INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006155102SEFO0114 ', N'10BEETWHO15757 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'004344701ALCA0114 ', 1, CAST(20120203 AS Decimal(9, 0)), CAST(135.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'004344701ALCA0114 ', 11, CAST(20141211 AS Decimal(9, 0)), CAST(-135.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005261001ALCA0114 ', 1, CAST(20120203 AS Decimal(9, 0)), CAST(77.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005261001ALCA0114 ', 4, CAST(20120328 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005261001ALCA0114 ', 4, CAST(20120427 AS Decimal(9, 0)), CAST(-21.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005542501TEQU0114 ', 4, CAST(20120221 AS Decimal(9, 0)), CAST(-49.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005542501TEQU0120 ', 1, CAST(20120203 AS Decimal(9, 0)), CAST(49.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005542501TEQU0120 ', 11, CAST(20120211 AS Decimal(9, 0)), CAST(-49.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005783301TEQU0110 ', 1, CAST(20120302 AS Decimal(9, 0)), CAST(112.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005783301TEQU0110 ', 4, CAST(20120306 AS Decimal(9, 0)), CAST(-8.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005783301TEQU0110 ', 4, CAST(20120307 AS Decimal(9, 0)), CAST(-104.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005814505SEFO0114 ', 1, CAST(20120411 AS Decimal(9, 0)), CAST(18.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005814505SEFO0114 ', 4, CAST(20120411 AS Decimal(9, 0)), CAST(-18.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005831904SEFO0114 ', 1, CAST(20120416 AS Decimal(9, 0)), CAST(53.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005831904SEFO0114 ', 4, CAST(20130718 AS Decimal(9, 0)), CAST(-1.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005831904SEFO0114 ', 4, CAST(20130821 AS Decimal(9, 0)), CAST(-52.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 1, CAST(20120723 AS Decimal(9, 0)), CAST(952.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20121210 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130212 AS Decimal(9, 0)), CAST(-20.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130213 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130213 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130221 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130307 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20160601 AS Decimal(9, 0)), CAST(-84.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130314 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130314 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130408 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20160405 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130416 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130429 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130429 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130508 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20130528 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 ', 4, CAST(20160602 AS Decimal(9, 0)), CAST(-36.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005965001TEQU0110 ', 1, CAST(20120815 AS Decimal(9, 0)), CAST(952.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005965001TEQU0110 ', 4, CAST(20120815 AS Decimal(9, 0)), CAST(-952.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20130419 AS Decimal(9, 0)), CAST(-71.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20130809 AS Decimal(9, 0)), CAST(-42.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20160607 AS Decimal(9, 0)), CAST(-184.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 1, CAST(20121024 AS Decimal(9, 0)), CAST(1000.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121101 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121106 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20160501 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121109 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121126 AS Decimal(9, 0)), CAST(-23.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121210 AS Decimal(9, 0)), CAST(-280.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121212 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20160401 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20121227 AS Decimal(9, 0)), CAST(-29.0000 AS Decimal(19, 4)))
INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ', 4, CAST(20130107 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))


#6

Ok, so transid was removed and transdate changed from date to decimal (why???).

I took the liberty of changing the "dbo.fconvertdate" as I'm unsure what exactly this function does.
So I ended up with this query (which should work with the sample data you provided):

with cte_lothist(lotno,transtype,transdate,transqty,rn)
  as (select b.lotno
            ,transtype
            ,transdate
            ,transqty
            ,row_number() over(partition by b.lotno
                               order by case
                                           when b.transtype=4
                                           then 0
                                           else 1
                                         end
                                        ,b.transdate desc
                              )
             as rn
        from dbo.lot as a
             inner join dbo.lothist as b
                     on b.lotno=a.lotno
                    and b.transtype in (1,4)
       where a.currentqty>0
     )
select b.lotno
      ,a.itemno
      ,a.[desc]
      ,b.warehouse
      ,b.currentqty
      ,b.cost
      ,cast(stuff(stuff(c.transdate,5,0,'-'),8,0,'-') as date) as transdate
      ,c.transtype
      ,c.transqty
  from dbo.item as a
       inner join dbo.lot as b
               on b.itemno=a.itemno
       inner join cte_lothist as c
               on c.lotno=b.lotno
              and c.rn=1
 where b.currentqty>0
   and datediff(day
               ,cast(stuff(stuff(c.transdate,5,0,'-'),8,0,'-') as date)
               ,cast(current_timestamp as date)
               )
       >90
;

Ps.: In your second post in this thread, you refer fields which are not in your sample data (lotnum, fmtitemno, location, qtyavail and assetcost). Again -> why???


#7

Hello bitsmed,

Sorry for the confusion. Basically, the date is stored as a decimal in the database, I have no idea why, it's a Sage accounting system. So I created that function to convert it to an actual date type. I'm going to try your code and let you know. Thanks a lot.


#8

Hey bitsmed,

Your code works great on the sample data and I think we're very close. I just ran into something that I wasn't aware in the live database. There are some cases in which a lot # is duplicated in the LOT table for the same ItemNo, but different warehouses. In those instances, the code seems to select the wrong warehouse for the correct transaction. I hope that makes sense. I"m leaving work, but I'll try to provide sample data tomorrow morning. Thanks a lot again for your help.