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

marcossuriel · June 6, 2016, 5:15pm

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)

bitsmed · June 6, 2016, 10:12pm

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
;

marcossuriel · June 7, 2016, 1:03am

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

bitsmed · June 7, 2016, 4:45am

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

marcossuriel · June 7, 2016, 3:05pm

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 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 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005261001ALCA0114 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005542501TEQU0114 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005542501TEQU0120 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005783301TEQU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005814505SEFO0114 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005831904SEFO0114 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'005965001TEQU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023601HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023601HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023701HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023801HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023901HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023901HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006023902HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078301HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078301HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078501HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078601HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078701HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006078901HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006079001HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006079201HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006080101TEQU0126 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006083901NOOA0126 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084001NOOA0126 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084101NOOA0126 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084201NOOA0126 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006084301NOOA0126 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006097101HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135701HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135701HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135801HAYU0110 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135802LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135901LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006135901LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136001LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136002LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136002LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136101LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136201LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006136301LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006154701LUFO0210 INSERT [dbo].[LOT] ([LotNo], [ItemNo], [Warehouse], [CurrentQty], [Cost]) VALUES (N'006155102SEFO0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'004344701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'004344701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005261001ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005261001ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005261001ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005542501TEQU0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005542501TEQU0120 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005542501TEQU0120 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005783301TEQU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005783301TEQU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005783301TEQU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005814505SEFO0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005814505SEFO0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005831904SEFO0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005831904SEFO0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005831904SEFO0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005936701ALCA0114 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005965001TEQU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'005965001TEQU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 INSERT [dbo].[LOTHIST] ([LotNo], [TransType], [TransDate], [TransQty]) VALUES (N'006023501HAYU0110 ')
')
WATER SOLID PACK IMP ')
')
')
')
')
')
')
')
')
')
')
')
')
')
')
')
')
')
', N'10CARRLAR11081 ', N'HARCA1', CAST(50.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10CARRLAR11081 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BLACBEA11023 ', N'HARCA1', CAST(21.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BLACBEA11023 ', N'HARCA1', CAST(65.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BLACBEA11023 ', N'HARCA1', CAST(85.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEETDIC12977 ', N'TRX ', CAST(45.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEETWHO11834 ', N'HARCA1', CAST(69.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10CARRLAR11081 ', N'BSPTX1', CAST(87.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'TRX ', CAST(321.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(11.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(36.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(54.0000 AS Decimal(19, 4)), CAST(2365.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(97.0000 AS Decimal(19, 4)), CAST(3121.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(98.0000 AS Decimal(19, 4)), CAST(1123.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(100.0000 AS Decimal(19, 4)), CAST(3233.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(150.0000 AS Decimal(19, 4)), CAST(4545.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'TRX ', CAST(35.0000 AS Decimal(19, 4)), CAST(1125.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'HARCA1', CAST(151.0000 AS Decimal(19, 4)), CAST(1212.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10BEANPIN13783 ', N'HARCA1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(55.0000 AS Decimal(19, 4)), CAST(221.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'TRX ', CAST(42.0000 AS Decimal(19, 4)), CAST(1212.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(365.0000 AS Decimal(19, 4)), CAST(1541.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'DICOH1', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'TRX ', CAST(32.0000 AS Decimal(19, 4)), CAST(2330.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(78.0000 AS Decimal(19, 4)), CAST(1212.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(12.0000 AS Decimal(19, 4)), CAST(233.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(251.0000 AS Decimal(19, 4)), CAST(12122.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(112.0000 AS Decimal(19, 4)), CAST(1200.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'BSPTX1', CAST(254.0000 AS Decimal(19, 4)), CAST(12122.000 AS Decimal(19, 3)))
', N'10FRUIMIX15295 ', N'ALFMD1', CAST(133.0000 AS Decimal(19, 4)), CAST(2311.000 AS Decimal(19, 3)))
', N'10BEETWHO15757 ', N'TRX ', CAST(0.0000 AS Decimal(19, 4)), CAST(0.000 AS Decimal(19, 3)))
', 1, CAST(20120203 AS Decimal(9, 0)), CAST(135.0000 AS Decimal(19, 4)))
', 11, CAST(20141211 AS Decimal(9, 0)), CAST(-135.0000 AS Decimal(19, 4)))
', 1, CAST(20120203 AS Decimal(9, 0)), CAST(77.0000 AS Decimal(19, 4)))
', 4, CAST(20120328 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20120427 AS Decimal(9, 0)), CAST(-21.0000 AS Decimal(19, 4)))
', 4, CAST(20120221 AS Decimal(9, 0)), CAST(-49.0000 AS Decimal(19, 4)))
', 1, CAST(20120203 AS Decimal(9, 0)), CAST(49.0000 AS Decimal(19, 4)))
', 11, CAST(20120211 AS Decimal(9, 0)), CAST(-49.0000 AS Decimal(19, 4)))
', 1, CAST(20120302 AS Decimal(9, 0)), CAST(112.0000 AS Decimal(19, 4)))
', 4, CAST(20120306 AS Decimal(9, 0)), CAST(-8.0000 AS Decimal(19, 4)))
', 4, CAST(20120307 AS Decimal(9, 0)), CAST(-104.0000 AS Decimal(19, 4)))
', 1, CAST(20120411 AS Decimal(9, 0)), CAST(18.0000 AS Decimal(19, 4)))
', 4, CAST(20120411 AS Decimal(9, 0)), CAST(-18.0000 AS Decimal(19, 4)))
', 1, CAST(20120416 AS Decimal(9, 0)), CAST(53.0000 AS Decimal(19, 4)))
', 4, CAST(20130718 AS Decimal(9, 0)), CAST(-1.0000 AS Decimal(19, 4)))
', 4, CAST(20130821 AS Decimal(9, 0)), CAST(-52.0000 AS Decimal(19, 4)))
', 1, CAST(20120723 AS Decimal(9, 0)), CAST(952.0000 AS Decimal(19, 4)))
', 4, CAST(20121210 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
', 4, CAST(20130212 AS Decimal(9, 0)), CAST(-20.0000 AS Decimal(19, 4)))
', 4, CAST(20130213 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20130213 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
', 4, CAST(20130221 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20130307 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
', 4, CAST(20160601 AS Decimal(9, 0)), CAST(-84.0000 AS Decimal(19, 4)))
', 4, CAST(20130314 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
', 4, CAST(20130314 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
', 4, CAST(20130408 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20160405 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
', 4, CAST(20130416 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20130429 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
', 4, CAST(20130429 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20130508 AS Decimal(9, 0)), CAST(-28.0000 AS Decimal(19, 4)))
', 4, CAST(20130528 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20160602 AS Decimal(9, 0)), CAST(-36.0000 AS Decimal(19, 4)))
', 1, CAST(20120815 AS Decimal(9, 0)), CAST(952.0000 AS Decimal(19, 4)))
', 4, CAST(20120815 AS Decimal(9, 0)), CAST(-952.0000 AS Decimal(19, 4)))
', 4, CAST(20130419 AS Decimal(9, 0)), CAST(-71.0000 AS Decimal(19, 4)))
', 4, CAST(20130809 AS Decimal(9, 0)), CAST(-42.0000 AS Decimal(19, 4)))
', 4, CAST(20160607 AS Decimal(9, 0)), CAST(-184.0000 AS Decimal(19, 4)))
', 1, CAST(20121024 AS Decimal(9, 0)), CAST(1000.0000 AS Decimal(19, 4)))
', 4, CAST(20121101 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20121106 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20160501 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20121109 AS Decimal(9, 0)), CAST(-112.0000 AS Decimal(19, 4)))
', 4, CAST(20121126 AS Decimal(9, 0)), CAST(-23.0000 AS Decimal(19, 4)))
', 4, CAST(20121210 AS Decimal(9, 0)), CAST(-280.0000 AS Decimal(19, 4)))
', 4, CAST(20121212 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20160401 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))
', 4, CAST(20121227 AS Decimal(9, 0)), CAST(-29.0000 AS Decimal(19, 4)))
', 4, CAST(20130107 AS Decimal(9, 0)), CAST(-56.0000 AS Decimal(19, 4)))

bitsmed · June 7, 2016, 4:34pm

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???

marcossuriel · June 7, 2016, 5:31pm

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.

marcossuriel · June 7, 2016, 9:51pm

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.