Home Forums SQL Server 2008 T-SQL (SS2K8) T-SQL: Aggregate and/or conditional subquery on where clause RE: T-SQL: Aggregate and/or conditional subquery on where clause

  • May 31, 2016 at 11:06 am

    #1882220

    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)