June 6, 2016 at 10:41 am
Hello Drew,
I was out for a few days. I'm back to work today. I'm sad to report that your solution doesn't produce the correct results when I put it on the production environment. It seemed to work with the few test records, but not on the live database.
I'm getting 260,000 records and all of them show the following:
TransType = "Shipment"
CurrentQty = 0
Cost = 0
I'm not sure what's wrong, the query is exactly the same as the one that works on the sample data.
June 6, 2016 at 1:04 pm
marcossuriel (6/6/2016)
Hello Drew,I was out for a few days. I'm back to work today. I'm sad to report that your solution doesn't produce the correct results when I put it on the production environment. It seemed to work with the few test records, but not on the live database.
I'm getting 260,000 records and all of them show the following:
TransType = "Shipment"
CurrentQty = 0
Cost = 0
I'm not sure what's wrong, the query is exactly the same as the one that works on the sample data.
The problem is that your sample data is not representative of your live data. We can't see your live data, so our code is only as good as the sample data that we have to work with. If you want our help, then you are going to need to supply data that is more representative of your live data.
It also sounds like you don't understand what the code is doing. You should try taking the code apart until you understand what each piece is doing. I would suggest starting with the ROW_NUMBER expression, specifically making sure that the order is specified correctly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 6, 2016 at 1:30 pm
It sounds like the problem is that you're returning rows where current quantity is 0, which violates one of your requirements.
If that's the case, then at least that immediate problem is pretty straightforward. Specifically, you're getting bitten by the fact that AND has higher precedence than OR.
The WHERE clause (without the row number criterion, to save some space) currently looks like this:
LOTHIST.TransType = 'Shipment'
OR
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
If we use parentheses to make the precedence clear, what's being evaluated is this:
LOTHIST.TransType = 'Shipment'
OR
(
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
)
That'll return all the RN=1 rows with a type of 'Shipment', period.
What you want to be evaluated (if I understand you correctly), is this:
(
LOTHIST.TransType = 'Shipment'
OR
LOTHIST.TransType = 'Receipt'
)
AND
(
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
)
Cheers!
EDIT: As an aside, this wasn't a problem in the sample data because there were no lots with a current quantity of 0, so this problem wouldn't have shown up.
June 6, 2016 at 2:05 pm
Jacob Wilkins (6/6/2016)
It sounds like the problem is that you're returning rows where current quantity is 0, which violates one of your requirements.If that's the case, then at least that immediate problem is pretty straightforward. Specifically, you're getting bitten by the fact that AND has higher precedence than OR.
The WHERE clause (without the row number criterion, to save some space) currently looks like this:
LOTHIST.TransType = 'Shipment'OR
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
If we use parentheses to make the precedence clear, what's being evaluated is this:
LOTHIST.TransType = 'Shipment'OR
(
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
)
That'll return all the RN=1 rows with a type of 'Shipment', period.
No, that's not true. The query will return rows where RN=1 and EITHER side of the OR expression is true.
The limiting factor is the ROW_NUMBER, specifically based on the criteria
marcossuriel (5/31/2016)
Receipt (only in the absence of shipment transactions for a particular lot)
The ROW_NUMBER was set up to prioritize Shipments and he is only seeing Shipments, which is why I recommended he start there with his analysis.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 6, 2016 at 2:10 pm
drew.allen (6/6/2016)
Jacob Wilkins (6/6/2016)
It sounds like the problem is that you're returning rows where current quantity is 0, which violates one of your requirements.If that's the case, then at least that immediate problem is pretty straightforward. Specifically, you're getting bitten by the fact that AND has higher precedence than OR.
The WHERE clause (without the row number criterion, to save some space) currently looks like this:
LOTHIST.TransType = 'Shipment'OR
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
If we use parentheses to make the precedence clear, what's being evaluated is this:
LOTHIST.TransType = 'Shipment'OR
(
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
)
That'll return all the RN=1 rows with a type of 'Shipment', period.
No, that's not true. The query will return rows where RN=1 and EITHER side of the OR expression is true.
The limiting factor is the ROW_NUMBER, specifically based on the criteria
marcossuriel (5/31/2016)
Receipt (only in the absence of shipment transactions for a particular lot)The ROW_NUMBER was set up to prioritize Shipments and he is only seeing Shipments, which is why I recommended he start there with his analysis.
Drew
Eh?
I didn't say it will return only rows with RN=1 and transtype='Shipment'.
I said it will return all such rows.
The "period" was to indicate that the current quantity of the lot doesn't matter, as in "This will show all RN=1 rows with a transtype of 'Shipment and current quantity of 0? No, it will return all RN=1 rows with a transtype of 'Shipment',period." His requirement is that any rows returned must be for lots with a current quantity>0, if I understand him correctly.
As you say, there may well be something else going on if indeed no 'Receipt' rows are showing up in the production data, but I focused on the current quantity 0 row aspect first because that directly violates the requirements. Nothing says there absolutely MUST be lots with only 'Receipt' transactions, although that is of course likely.
It could be I'm misunderstanding both of you, but I guess we'll see 🙂
Cheers!
June 6, 2016 at 2:35 pm
I didn't read all this thread, and I'm not going to start now. 😉 But it is REALLY hard to think of a scenario in which this WHERE clause is what is INTENDED:
LOTHIST.TransType = 'Shipment'
OR
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2016 at 2:49 pm
Jacob Wilkins (6/6/2016)
I didn't say it will return only rows with RN=1 and transtype='Shipment'.I said it will return all such rows.
The "period" was to indicate that the current quantity of the lot doesn't matter,
No, the "period" means that nothing else matters, specifically nothing after the OR matters.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 6, 2016 at 2:52 pm
TheSQLGuru (6/6/2016)
I didn't read all this thread, and I'm not going to start now. 😉 But it is REALLY hard to think of a scenario in which this WHERE clause is what is INTENDED:
LOTHIST.TransType = 'Shipment'OR
LOTHIST.TransType = 'Receipt'
AND
LOT.CurrentQty > 0
AND
(LOTHIST.TransDate) > GETDATE()- 90
He used an older version of the code. The comparison on the date field was changed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 6, 2016 at 3:00 pm
drew.allen (6/6/2016)
Jacob Wilkins (6/6/2016)
I didn't say it will return only rows with RN=1 and transtype='Shipment'.I said it will return all such rows.
The "period" was to indicate that the current quantity of the lot doesn't matter,
No, the "period" means that nothing else matters, specifically nothing after the OR matters.
Drew
You have a different typical use of that construction than I do. So be it. I'm happy to discuss that over a beer, but it's of little significance here 🙂
The main point regardless of that particular sentence in my post is that the WHERE clause as originally written only filtered for current quantity being greater than 0 for rows with a TransType of 'Receipt'; a row with a TransType of 'Shipment' would be returned even if the current quantity for the associated lot were 0.
If I understand the OP's requirements correctly, that is mistaken. I offered a fix for that particular problem if it were indeed a problem.
Cheers!
June 6, 2016 at 3:00 pm
Hello Guys,
I'm really sorry for all the confusion this has caused. It is true, I'm not a SQL expert so I don't fully understand what the ROW_NUMBER() expression does yet, even' though I have looked at many examples online. I really appreciate your patience. I'm going to re-create the code with samples of live data and post it as soon as I get a chance. Again thanks a lot for all your help.
June 6, 2016 at 3:03 pm
Hello Drew,
I apologize I didn't make it clearer. The CurrentQty > 0 should apply to the entire recordset whether it is a shipment or receipt.
June 7, 2016 at 9:12 am
Hello Guys,
I generated a sample from the actual live database. Please note that the TransType on the database is stored as follows:
TransType: 4 = Shipment
TransType: 1 = Receipt
I put names instead of the actual data on the previous code for readability. As mentioned before, I don't care about any other transaction types, they shouldn't be included in the result set.
Thank you so much again for all your help, I hope this helps.
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](24) NOT NULL,
[Desc] [char](60) 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](40) NOT NULL,
[ItemNo] [char](24) NOT NULL,
[Warehouse] [char](6) 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](40) 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)))
June 7, 2016 at 12:29 pm
marcossuriel (6/7/2016)
Hello Guys,I generated a sample from the actual live database. Please note that the TransType on the database is stored as follows:
TransType: 4 = Shipment
TransType: 1 = Receipt
I put names instead of the actual data on the previous code for readability. As mentioned before, I don't care about any other transaction types, they shouldn't be included in the result set.
Thank you so much again for all your help, I hope this helps.
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](24) NOT NULL,
[Desc] [char](60) 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](40) NOT NULL,
[ItemNo] [char](24) NOT NULL,
[Warehouse] [char](6) 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](40) 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)))
Thanks! I think I know to tweak the query, but before messing too much with that, could you provide the desired results for this new sample data?
Cheers!
June 7, 2016 at 3:10 pm
Hello Jacob,
I have the results I want from the dataset provide, but not sure how to show them in table view on here.
lotnoitemnodescwarehousecurrentqtycosttransdatetranstypetransqty
005965001TEQU0110 10BEANPIN13783 BEANS PINTO LOW SODIUM TRX 32108/15/20124-952
005814505SEFO0114 10BEETDIC12977 BEETS DICED TRX 4504/11/20124-18
005831904SEFO0114 10BEETWHO11834 BEETS WHOLE 100 CT 32653 HARCA16908/21/20134-52
005542501TEQU0114 10BLACBEA11023 BEANS BLACK HARCA12102/21/20124-49
005542501TEQU0120 10BLACBEA11023 BEANS BLACK HARCA16502/3/2012149
005783301TEQU0110 10BLACBEA11023 BEANS BLACK HARCA18503/7/20124-104
004344701ALCA0114 10CARRLAR11081 CARROTS SLICED LARGE HARCA15002/3/20121135
June 8, 2016 at 9:01 am
marcossuriel (6/7/2016)
Hello Jacob,I have the results I want from the dataset provide, but not sure how to show them in table view on here.
lotnoitemnodescwarehousecurrentqtycosttransdatetranstypetransqty
005965001TEQU0110 10BEANPIN13783 BEANS PINTO LOW SODIUM TRX 32108/15/20124-952
005814505SEFO0114 10BEETDIC12977 BEETS DICED TRX 4504/11/20124-18
005831904SEFO0114 10BEETWHO11834 BEETS WHOLE 100 CT 32653 HARCA16908/21/20134-52
005542501TEQU0114 10BLACBEA11023 BEANS BLACK HARCA12102/21/20124-49
005542501TEQU0120 10BLACBEA11023 BEANS BLACK HARCA16502/3/2012149
005783301TEQU0110 10BLACBEA11023 BEANS BLACK HARCA18503/7/20124-104
004344701ALCA0114 10CARRLAR11081 CARROTS SLICED LARGE HARCA15002/3/20121135
As I suspected, the problem is with the ORDER BY clause in the ROW_NUMBER(). Have you looked at what is being produced there like I suggested?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply