Running Total using T-SQL

Question

Post reply

Running Total using T-SQL

Ralph L. Wahlert

SSC-Addicted

Points: 430
More actions
November 10, 2010 at 7:56 am

#228953

I have created a view that combines purchasing data with sales data for items that are bought and sold. I have a date field that stores the date the items were purchased and when the items are sold. The quantities for the purchased items are positive and the sold quantities are shown as negatives.
I would like to write query from this view that would group by the item number and show me a running total of the quantity remaining of the items. In other words, I want to see the quantity purchased followed by the quantity sold with a running total of the quantity remaining.
A basic result set might look like the following:
Item Quantity Date Source Remaining
ABC12 500 10/1/2010 Purchase 500
ABC12 -50 10/2/2010 Sale 450
ABC12 -75 10/4/2010 Sale 375
ABC12 -100 10/6/2010 Sale 275
I think you all get the idea.
I can group by the item number and sort by the date, but how can I create the running total?
Any ideas would be appreciated.
Thanks.
Ralph

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

select item, date, quantity, tot = (select sum(quantity) from tbl t2 where t.item = t2.item and t2.date <= t.date)

from tbl t

order by item, date

That will give the daily totals so if multiple transactions per day then will all have the same total

;with cte as

(

select item, date , seq = row_number() over (partition by itemno order by date from tbl)

)

select item, date, tot = (select sum(quantity) from cte t2 where cte.item = t2.item and t2.seq <= cte.seq

order by seq

Cursors never.
DTS - only when needed and never to control.

Ralph L. Wahlert SSC-Addicted Points: 430 More actions · Answer 2

Thank you Nigel, I will work with this and see how it comes out.

Ralph

WayneS SSC Guru Points: 95460 More actions · Answer 3

You might want to check out this article[/url] for how to perform running totals.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ralph L. Wahlert SSC-Addicted Points: 430 More actions · Answer 4

Ralph L. Wahlert

SSC-Addicted

Points: 430

November 10, 2010 at 10:33 am

#1249044

Thank you Wayne. I'll be sure the read this.

Ralph

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

nigelrivett (11/10/2010)
select item, date, quantity, tot = (select sum(quantity) from tbl t2 where t.item = t2.item and t2.date <= t.date)
from tbl t
order by item, date
That will give the daily totals so if multiple transactions per day then will all have the same total
;with cte as
(
select item, date , seq = row_number() over (partition by itemno order by date from tbl)
)
select item, date, tot = (select sum(quantity) from cte t2 where cte.item = t2.item and t2.seq <= cte.seq
order by seq

Hi Nigel,

You may want to have a look at the following article and find out why inequalities in an aggregated correlated subquery may not be the way to go.

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

Ralph,

You're new here so let me give you a "survivor's" hint. Take a look at the article at the first link in my signature line below. People will usually jump through hoops if you take just a smidge of time to post the table def and some sample data like that article tells you how to.

Anyway, on with the show. As usual, what's happening is documented in the code below...

--=====================================================================================================================

-- Build some sample data. This is the way you should do it when you post a question to make it easier on folks.

-- This is NOT a part of the solution but is an essential part to us being able to demo a solution for you.

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL

DROP TABLE #TestData

;

--===== Create and populate the test table on the fly

SELECT Item,Quantity,Date,Source,Remaining

INTO #TestData

FROM (

SELECT 'ABC12', 500,CAST('10/1/2010' AS DATETIME),'Purchase',NULL UNION ALL

SELECT 'ABC12', -50,CAST('10/2/2010' AS DATETIME),'Sale',NULL UNION ALL

SELECT 'ABC12', -75,CAST('10/4/2010' AS DATETIME),'Sale',NULL UNION ALL

SELECT 'ABC12',-100,CAST('10/6/2010' AS DATETIME),'Sale',NULL

) testdata (Item,Quantity,Date,Source,Remaining)

;

--===== Put a totally bogus clustered index on the data just to prove this works

CREATE CLUSTERED INDEX IX_#TestData ON #TestData (Quantity)

;

--===== Display the test data before we do anything to it

SELECT * FROM #TestData ORDER BY Item,Date,Quantity

;

--=====================================================================================================================

-- This is the solution without the overhead of a Triangular Join. It'll do a million rows in just seconds.

--=====================================================================================================================

--===== Declare some essential variables with obvious names to reflect their use

DECLARE @PrevItem VARCHAR(10),

@PrevBal INT,

@Counter INT

;

--===== It's essential to preset the counter variable. All the others may be calculated during the UPDATE

SELECT @Counter = 1

;

--===== This produces an ordered, running total update. It has a built in fault detector that will let you know if

-- a failure occured. That same fault detector is what makes the ordered update work even when the clustered

-- index is in a totally different order. This type of update is affectionately known as the "Quirky Update"

-- and it's a powerful tool to learn. Special thanks to Paul White and Tom Thompson for the fault detector.

WITH

cteSort AS

(

SELECT Counter = ROW_NUMBER() OVER (ORDER BY Item, Date, Quantity),

Item, Quantity, Date, Source, Remaining

FROM #TestData

)

UPDATE tgt

SET @PrevBal = Remaining = CASE

WHEN tgt.Counter = @Counter

THEN CASE

WHEN tgt.Item = @PrevItem THEN tgt.Quantity + @PrevBal

ELSE tgt.Quantity

END

ELSE 1/0 --Force error if out of sequence

END,

@PrevItem = tgt.Item,

@Counter = @Counter + 1

FROM cteSort tgt WITH (TABLOCKX) --Absolutely essential, we don't want anyone sneaking in while we're updating

OPTION (MAXDOP 1) --Parallelism must be prevented for the serial nature of this update

;

--===== Display the test data before after the running total update

SELECT * FROM #TestData ORDER BY Item,Date,Quantity

;

Heh... obviously, I need to rewrite that running total article just one more time... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ralph L. Wahlert SSC-Addicted Points: 430 More actions · Answer 7

Jeff:

Thanks for lesson on SSC etiquette. I'll read through the article and supply a proper table definition and sample data.

Ralph

Ralph L. Wahlert SSC-Addicted Points: 430 More actions · Answer 8

Jeff:

I have taken your advise and read your article on how to properly request assistance.

My table create code is below, followed by the code to populate the tale with data.

So my question remains: how can I create a running total on the field "Ctns" when sorting by the "SKU" and "Required" fields?

Hopefully this is presented correctly, as I do not wish to seem ungrateful for the help.

I do care about my code.

Ralph

/****** Object: Table [dbo].[_PositionShipping] Script Date: 11/15/2010 12:08:04 ******/

/****** This was scripted out from a select into statement I ran****/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[_PositionShipping](

[PONUMBER] [char](21) NULL,

[DOCDATE] [datetime] NULL,

[VENDORID] [char](15) NULL

) ON [PRIMARY]

SET ANSI_PADDING ON

ALTER TABLE [dbo].[_PositionShipping] ADD [Source] [varchar](8) NOT NULL

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[_PositionShipping] ADD [SKU] [char](31) NOT NULL

SET ANSI_PADDING ON

ALTER TABLE [dbo].[_PositionShipping] ADD [ICO Marks] [varchar](21) NOT NULL

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[_PositionShipping] ADD [Site] [char](11) NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [Ctns] [numeric](19, 5) NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [Kilos/Ctn] [numeric](19, 5) NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [FOB] [numeric](19, 5) NULL

SET ANSI_PADDING ON

ALTER TABLE [dbo].[_PositionShipping] ADD [UOM] [char](9) NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [Required] [datetime] NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [ETD] [datetime] NOT NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [ETA] [varchar](1) NOT NULL

ALTER TABLE [dbo].[_PositionShipping] ADD [InvoiceDate] [datetime] NULL

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[_PositionShipping] ADD [DocNo] [char](21) NULL

GO

SET ANSI_PADDING OFF

GO

Below is the coed to populate the table with data.

--set identity_insert dbo._PositionShipping on

-- No identity in original table

-- Insert into statement with data

insert into dbo._PositionShipping

([PONUMBER],[DOCDATE],[VENDORID],[Source],[SKU],[ICO Marks],[Site],[Ctns],[Kilos/Ctn],[FOB],[UOM],[Required],[ETD],[ETA],[InvoiceDate],[DocNo])

SELECT '2287','Apr 11 2008 12:00AM','COLCAFE','Purchase','ST1 (35-KG) ','','IN-TR COL','446.00000','35.00000','8.85000','kgs ','Aug 29 2008 12:00AM','Aug 29 2008 12:00AM','','Apr 11 2008 12:00AM','2287'union all

SELECT '2229(19-35)','Sep 24 2007 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5(A) (33-KG) ','','F-F','9180.00000','33.00000','5.38000','kgs ','Dec 31 2015 12:00AM','Dec 31 2015 12:00AM','','Sep 24 2007 12:00AM','2229(19-35)'union all

SELECT '2305(7-100)','May 26 2008 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5 (33-KG) ','','F-F','0.00000','33.00000','6.78000','kgs ','Aug 29 2008 12:00AM','Aug 29 2008 12:00AM','','May 26 2008 12:00AM','2305(7-100)'union all

SELECT '2318(1-4)','Aug 4 2008 12:00AM','CCLPRODUCTS','Purchase','CCL-FD/5 (23-KG) ','','F-F','700.00000','23.00000','10.85000','kgs ','Feb 5 2009 12:00AM','Feb 5 2009 12:00AM','','Aug 4 2008 12:00AM','2318(1-4)'union all

SELECT '2292(4-6)','Apr 18 2008 12:00AM','CCLPRODUCTS','Purchase','CCL-AG-BAROK (23-KG) ','','F-F','0.00000','23.00000','7.10000','kgs ','Jan 31 2009 12:00AM','Jan 31 2009 12:00AM','','Apr 18 2008 12:00AM','2292(4-6)'union all

SELECT '2365','Dec 18 2008 12:00AM','COLCAFE','Purchase','PSN-4.14 (DOY-PACKS) ','','IN-TR COL','4.00000','30.00000','30.36000','kgs ','Dec 18 2008 12:00AM','Dec 18 2008 12:00AM','','Dec 18 2008 12:00AM','2365'union all

SELECT '2365','Dec 18 2008 12:00AM','COLCAFE','Purchase','PSN-2M (DOY-PACKS) ','','IN-TR COL','4.00000','30.00000','36.41000','kgs ','Dec 18 2008 12:00AM','Dec 18 2008 12:00AM','','Dec 18 2008 12:00AM','2365'union all

SELECT '2373..','Jan 6 2009 12:00AM','COLCAFE','Purchase','SBUX SKU 1197038 ','','F-F','129.00000','0.99360','78.45000','kgs ','Jan 8 2009 12:00AM','Jan 8 2009 12:00AM','','Jan 6 2009 12:00AM','2373..'union all

SELECT '2373..','Jan 6 2009 12:00AM','COLCAFE','Purchase','SBUX SKU 1197042 ','','F-F','54.00000','2.98080','67.06000','kgs ','Jan 8 2009 12:00AM','Jan 8 2009 12:00AM','','Jan 6 2009 12:00AM','2373..'union all

SELECT '2373..','Jan 6 2009 12:00AM','COLCAFE','Purchase','SBUX SKU 1197037 ','','F-F','63.00000','0.99360','73.01000','kgs ','Jan 8 2009 12:00AM','Jan 8 2009 12:00AM','','Jan 6 2009 12:00AM','2373..'union all

SELECT '2373..','Jan 6 2009 12:00AM','COLCAFE','Purchase','SBUX SKU 1197040 ','','F-F','36.00000','2.98080','61.62000','kgs ','Jan 8 2009 12:00AM','Jan 8 2009 12:00AM','','Jan 6 2009 12:00AM','2373..'union all

SELECT '2350','Nov 14 2008 12:00AM','COLCAFE','Purchase','SCER (15-KG) ','','F-F','0.00000','15.00000','22.32000','kgs ','Nov 14 2008 12:00AM','Nov 14 2008 12:00AM','','Nov 14 2008 12:00AM','2350'union all

SELECT '2350','Nov 14 2008 12:00AM','COLCAFE','Purchase','SCER (15-KG) ','','F-F','0.00000','15.00000','22.74000','kgs ','Nov 14 2008 12:00AM','Nov 14 2008 12:00AM','','Nov 14 2008 12:00AM','2350'union all

SELECT '2501.','Oct 29 2009 12:00AM','IBADESA','Purchase','C1-30(N) AGGLO (23-KG) ','','TX','4200.00000','50.70626','1.75000','lbs ','Oct 29 2009 12:00AM','Oct 29 2009 12:00AM','','Oct 29 2009 12:00AM','2501.'union all

SELECT '2579','Apr 14 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (33-KG) ','','I-TR CCL','540.00000','33.00000','5.25000','kgs ','Apr 14 2010 12:00AM','Apr 14 2010 12:00AM','','Apr 14 2010 12:00AM','2579'union all

SELECT '2583','Apr 19 2010 12:00AM','IBADESA','Purchase','Z-4 AGGLO (23-KG) ','','ORIGIN','2800.00000','50.70626','2.95000','lbs ','Apr 19 2010 12:00AM','Apr 19 2010 12:00AM','','Apr 19 2010 12:00AM','2583'union all

SELECT '2600','May 27 2010 12:00AM','COLCAFE','Purchase','PSN-4.14 (15-KG) ','','IN-TR COL','3724.00000','15.00000','28.81000','kgs ','May 27 2010 12:00AM','May 27 2010 12:00AM','','May 27 2010 12:00AM','2600'union all

SELECT '2600','May 27 2010 12:00AM','COLCAFE','Purchase','PSN-2M (15-KG) ','','IN-TR COL','5465.00000','15.00000','34.51000','kgs ','May 27 2010 12:00AM','May 27 2010 12:00AM','','May 27 2010 12:00AM','2600'union all

SELECT '2596','May 11 2010 12:00AM','COLCAFE','Purchase','PSN-4.14 (15-KG) ','','IN-TR COL','1706.00000','15.00000','28.81000','kgs ','May 11 2010 12:00AM','May 11 2010 12:00AM','','May 11 2010 12:00AM','2596'union all

SELECT '2596','May 11 2010 12:00AM','COLCAFE','Purchase','PSN-2M (15-KG) ','','IN-TR COL','431.00000','15.00000','34.51000','kgs ','May 11 2010 12:00AM','May 11 2010 12:00AM','','May 11 2010 12:00AM','2596'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197040 ','','IN-TR COL','684.00000','2.98080','62.79520','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1178267 - AI ','','IN-TR COL','0.00000','4.27680','59.79060','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197035 ','','IN-TR COL','2875.00000','0.42240','57.12300','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1173888 ','','IN-TR COL','1953.00000','1.42560','60.44120','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197037 ','','IN-TR COL','1197.00000','0.99360','74.21130','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197038 ','','IN-TR COL','1953.00000','0.99360','79.91130','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11002231 ','','IN-TR COL','8316.00000','1.66320','64.88150','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11005174 ','','IN-TR COL','693.00000','0.99360','81.73130','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1173891 ','','IN-TR COL','315.00000','1.42560','66.14120','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197029 ','','IN-TR COL','14950.00000','0.42240','62.82300','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11005175 ','','IN-TR COL','306.00000','2.98080','70.31520','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11005176 ','','IN-TR COL','672.00000','5.75000','60.84740','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197036 ','','IN-TR COL','8050.00000','0.42240','62.82300','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11004067 - CI ','','IN-TR COL','216.00000','4.27680','61.39850','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1197042 ','','IN-TR COL','756.00000','2.98080','68.49520','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1173890 ','','IN-TR COL','882.00000','1.42560','66.14120','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11004041 ','','IN-TR COL','315.00000','1.42560','67.96120','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11004043 - CI ','','IN-TR COL','90.00000','4.27680','61.39850','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 11004729 ','','IN-TR COL','420.00000','1.18800','69.40270','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2592','May 5 2010 12:00AM','COLCAFE','Purchase','SKU 1178264 - CI ','','IN-TR COL','144.00000','4.27680','53.87850','kgs ','May 5 2010 12:00AM','May 5 2010 12:00AM','','May 5 2010 12:00AM','2592'union all

SELECT '2403','Mar 12 2009 12:00AM','COCAM','Purchase','FD7 (32-KG) ','','F-F','140.00000','32.00000','11.62000','kgs ','Mar 12 2009 12:00AM','Mar 12 2009 12:00AM','','Mar 12 2009 12:00AM','2403'union all

SELECT '2387','Jan 28 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-FD/L-BAROK (23-KG) ','','I-TR CCL','2800.00000','23.00000','8.50000','kgs ','Jan 28 2009 12:00AM','Jan 28 2009 12:00AM','','Jan 28 2009 12:00AM','2387'union all

SELECT '2421','Apr 23 2009 12:00AM','COLCAFE','Purchase','SCBB (0.864 KG) ','','IN-TR COL','0.00000','0.86400','34.12000','kgs ','Apr 23 2009 12:00AM','Apr 23 2009 12:00AM','','Apr 23 2009 12:00AM','2421'union all

SELECT '2491','Sep 30 2009 12:00AM','COLCAFE','Purchase','SKU 1173889 ','','ORIGIN','693.00000','1.42560','61.07120','kgs ','Sep 30 2009 12:00AM','Sep 30 2009 12:00AM','','Sep 30 2009 12:00AM','2491'union all

SELECT '2491','Sep 30 2009 12:00AM','COLCAFE','Purchase','SKU 1178265 ','','ORIGIN','2736.00000','4.27680','58.95450','kgs ','Sep 30 2009 12:00AM','Sep 30 2009 12:00AM','','Sep 30 2009 12:00AM','2491'union all

SELECT '2494','Oct 7 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (200G TINS) ','','ORIGIN','4660.00000','2.40000','6.90000','kgs ','Oct 7 2009 12:00AM','Oct 7 2009 12:00AM','','Oct 7 2009 12:00AM','2494'union all

SELECT '2496','Oct 12 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-AG/CG (25-KG) ','','F-F','14000.00000','25.00000','4.65000','kgs ','Oct 12 2009 12:00AM','Oct 12 2009 12:00AM','','Oct 12 2009 12:00AM','2496'union all

SELECT '2498','Oct 12 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-FD/CG (25-KG) ','','F-F','13300.00000','25.00000','7.25000','kgs ','Oct 12 2009 12:00AM','Oct 12 2009 12:00AM','','Oct 12 2009 12:00AM','2498'union all

SELECT '2477(4-77)','Oct 14 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (33-KG). ','','I-TR CCL','18900.00000','33.00000','5.67500','kgs ','Oct 14 2009 12:00AM','Oct 14 2009 12:00AM','','Oct 14 2009 12:00AM','2477(4-77)'union all

SELECT '2477(4-77)','Oct 14 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5 (33-KG). ','','I-TR CCL','29700.00000','33.00000','5.67500','kgs ','Oct 14 2009 12:00AM','Oct 14 2009 12:00AM','','Oct 14 2009 12:00AM','2477(4-77)'union all

SELECT '2477(4-77)','Oct 14 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (25-KG). ','','I-TR CCL','1620.00000','25.00000','5.67500','kgs ','Oct 14 2009 12:00AM','Oct 14 2009 12:00AM','','Oct 14 2009 12:00AM','2477(4-77)'union all

SELECT '2502','Oct 20 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-AG/3C (23-KG) ','','I-TR CCL','11200.00000','23.00000','6.40000','kgs ','Oct 20 2009 12:00AM','Oct 20 2009 12:00AM','','Oct 20 2009 12:00AM','2502'union all

SELECT '2504','Oct 21 2009 12:00AM','COCAM','Purchase','S8E (32-KG) ','','IN-TR COCA','0.00000','32.00000','5.40000','kgs ','Oct 21 2009 12:00AM','Oct 21 2009 12:00AM','','Oct 21 2009 12:00AM','2504'union all

SELECT '2545','Feb 3 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/FC/1 (33-KG) ','','I-TR CCL','2160.00000','33.00000','7.40000','kgs ','Feb 3 2010 12:00AM','Feb 3 2010 12:00AM','','Feb 3 2010 12:00AM','2545'union all

SELECT '2516','Nov 16 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5, TYPE Y (30-KG) ','','F-F','11880.00000','30.00000','5.50000','kgs ','Nov 16 2009 12:00AM','Nov 16 2009 12:00AM','','Nov 16 2009 12:00AM','2516'union all

SELECT '2546','Feb 4 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-FD/5 (23-KG) ','','I-TR CCL','8400.00000','23.00000','6.50000','kgs ','Feb 4 2010 12:00AM','Feb 4 2010 12:00AM','','Feb 4 2010 12:00AM','2546'union all

SELECT '2530','Jan 15 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5(A) (33-KG) ','','F-F','3780.00000','33.00000','5.47000','kgs ','Jan 15 2010 12:00AM','Jan 15 2010 12:00AM','','Jan 15 2010 12:00AM','2530'union all

SELECT '2559','Mar 8 2010 12:00AM','COLCAFE','Purchase','ST1 (35-KG). ','','IN-TR COL','520.00000','35.00000','12.22500','kgs ','Mar 8 2010 12:00AM','Mar 8 2010 12:00AM','','Mar 8 2010 12:00AM','2559'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1173888 ','','IN-TR COL','3150.00000','1.42560','60.23120','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1197028 ','','IN-TR COL','107640.00000','0.42240','56.91300','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1178263 ','','IN-TR COL','3240.00000','4.27680','52.27450','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1178269 ','','IN-TR COL','0.00000','4.14000','54.32480','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1197029 ','','IN-TR COL','76475.00000','0.42240','62.56300','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11002231 ','','IN-TR COL','29421.00000','1.66320','64.62150','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11004258 ','','IN-TR COL','1750.00000','0.66000','55.60030','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11004729 ','','IN-TR COL','2380.00000','1.18800','68.06670','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11002133 ','','IN-TR COL','70.00000','1.42560','66.20670','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1197038 ','','IN-TR COL','315.00000','0.99360','79.65130','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11001152 ','','IN-TR COL','168.00000','5.75000','58.76740','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1178263 - AI ','','IN-TR COL','1944.00000','4.27680','53.88060','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1178263 - CI ','','IN-TR COL','2664.00000','4.27680','53.66850','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11005624 ','','IN-TR COL','5290.00000','0.36960','65.70640','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1173890 ','','IN-TR COL','2961.00000','1.42560','65.88120','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11004065 ','','IN-TR COL','0.00000','1.42560','67.74120','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11004067 ','','IN-TR COL','0.00000','4.27680','59.78450','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11005174 ','','IN-TR COL','189.00000','0.99360','81.51130','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1197037 ','','IN-TR COL','315.00000','0.99360','74.00130','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11001151 ','','IN-TR COL','144.00000','5.75000','53.11740','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11001140 ','','IN-TR COL','24.00000','8.25000','51.32580','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1173891 ','','IN-TR COL','2394.00000','1.42560','65.88120','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11002134 ','','IN-TR COL','0.00000','1.42560','60.55670','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11005664 ','','IN-TR COL','18760.00000','1.18800','67.32270','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 11005665 ','','IN-TR COL','23870.00000','1.18800','61.67270','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2536','Jan 22 2010 12:00AM','COLCAFE','Purchase','SKU 1178264 ','','IN-TR COL','0.00000','4.27680','52.27450','kgs ','Jan 22 2010 12:00AM','Jan 22 2010 12:00AM','','Jan 22 2010 12:00AM','2536'union all

SELECT '2540','Feb 22 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/3 (33-KG) ','','ORIGIN','1620.00000','33.00000','5.95000','kgs ','Jan 29 2010 12:00AM','Jan 29 2010 12:00AM','','Feb 22 2010 12:00AM','2540'union all

SELECT '2541','Jan 29 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (33-KG) ','','I-TR CCL','3240.00000','33.00000','5.25000','kgs ','Jan 29 2010 12:00AM','Jan 29 2010 12:00AM','','Jan 29 2010 12:00AM','2541'union all

SELECT '2542','Feb 1 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/FC/1 (33-KG) ','','I-TR CCL','2160.00000','33.00000','7.40000','kgs ','Feb 1 2010 12:00AM','Feb 1 2010 12:00AM','','Feb 1 2010 12:00AM','2542'union all

SELECT '2543','Feb 1 2010 12:00AM','COLCAFE','Purchase','ST1 (35-KG). ','','IN-TR COL','4160.00000','35.00000','12.22500','kgs ','Feb 1 2010 12:00AM','Feb 1 2010 12:00AM','','Feb 1 2010 12:00AM','2543'union all

SELECT '2544','Feb 3 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (33-KG) ','','I-TR CCL','4320.00000','33.00000','5.25000','kgs ','Feb 3 2010 12:00AM','Feb 3 2010 12:00AM','','Feb 3 2010 12:00AM','2544'union all

SELECT '2597','May 11 2010 12:00AM','COLCAFE','Purchase','PSND-4.14 (15-KG) ','','IN-TR COL','132.00000','15.00000','36.34000','kgs ','May 11 2010 12:00AM','May 11 2010 12:00AM','','May 11 2010 12:00AM','2597'union all

SELECT '2597','May 11 2010 12:00AM','COLCAFE','Purchase','PSN-2M (15-KG) ','','IN-TR COL','198.00000','15.00000','34.51000','kgs ','May 11 2010 12:00AM','May 11 2010 12:00AM','','May 11 2010 12:00AM','2597'union all

SELECT '2598','May 12 2010 12:00AM','COLCAFE','Purchase','SKU 11006899 ','','IN-TR COL','431100.00000','0.80100','15.26640','kgs ','May 12 2010 12:00AM','May 12 2010 12:00AM','','May 12 2010 12:00AM','2598'union all

SELECT '2607','Jun 4 2010 12:00AM','COLCAFE','Purchase','GR3MH (SACO) (320-KG) ','','IN-TR COL','168.00000','320.00000','12.60000','kgs ','Jun 4 2010 12:00AM','Jun 4 2010 12:00AM','','Jun 4 2010 12:00AM','2607'union all

SELECT '2566','Mar 26 2010 12:00AM','COLCAFE','Purchase','ST1 (35-KG). ','','IN-TR COL','520.00000','35.00000','12.22500','kgs ','Mar 26 2010 12:00AM','Mar 26 2010 12:00AM','','Mar 26 2010 12:00AM','2566'union all

SELECT '2568','Mar 30 2010 12:00AM','COCAM','Purchase','S8E (35-KG) ','','IN-TR COCA','545.00000','35.00000','4.85000','kgs ','Mar 30 2010 12:00AM','Mar 30 2010 12:00AM','','Mar 30 2010 12:00AM','2568'union all

SELECT '2568','Mar 30 2010 12:00AM','COCAM','Purchase','SM (32-KG) ','','IN-TR COCA','545.00000','32.00000','4.25000','kgs ','Mar 30 2010 12:00AM','Mar 30 2010 12:00AM','','Mar 30 2010 12:00AM','2568'union all

SELECT '2610','Jun 11 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/FC/1 (33-KG) ','','ORIGIN','540.00000','33.00000','7.40000','kgs ','Jun 11 2010 12:00AM','Jun 11 2010 12:00AM','','Jun 11 2010 12:00AM','2610'union all

SELECT '2614','Jun 16 2010 12:00AM','COLCAFE','Purchase','TJ AGGLO (3.5 OZ/24) ','','IN-TR COL','1400.00000','24.00000','1.68000','Jar ','Jun 16 2010 12:00AM','Jun 16 2010 12:00AM','','Jun 16 2010 12:00AM','2614'union all

SELECT '2614','Jun 16 2010 12:00AM','COLCAFE','Purchase','TJ AGGO-DECAF (3.5 OZ/24) ','','IN-TR COL','1400.00000','24.00000','2.02000','Jar ','Jun 16 2010 12:00AM','Jun 16 2010 12:00AM','','Jun 16 2010 12:00AM','2614'union all

SELECT '2612','Jun 11 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/5 (33-KG) ','','I-TR CCL','540.00000','33.00000','5.25000','kgs ','Jun 11 2010 12:00AM','Jun 11 2010 12:00AM','','Jun 11 2010 12:00AM','2612'union all

SELECT '2518(2A-2B)','Nov 23 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-AG (23-KG) ','','I-TR CCL','500.00000','23.00000','6.00000','kgs ','Jun 22 2010 12:00AM','Jun 22 2010 12:00AM','','Nov 23 2009 12:00AM','2518(2A-2B)'union all

SELECT '2518(2A-2B)','Nov 23 2009 12:00AM','CCLPRODUCTS','Purchase','CCL-FD/5 (23-KG) ','','I-TR CCL','200.00000','23.00000','8.25000','kgs ','Jun 22 2010 12:00AM','Jun 22 2010 12:00AM','','Nov 23 2009 12:00AM','2518(2A-2B)'union all

SELECT '2629','Jul 13 2010 12:00AM','IBADESA','Purchase','C1-30N (30-KG) ','','GULF COAST','0.00000','66.13860','1.80000','lbs ','Jul 13 2010 12:00AM','Jul 13 2010 12:00AM','','Jul 13 2010 12:00AM','2629'union all

SELECT '2631','Jul 23 2010 12:00AM','IBADESA','Purchase','C1-30(N) AGGLO (10-KG) ','','LAREDO, TX','0.00000','22.04620','1.80000','lbs ','Jul 23 2010 12:00AM','Jul 23 2010 12:00AM','','Jul 23 2010 12:00AM','2631'union all

SELECT '2627','Jul 7 2010 12:00AM','BUENDIA','Purchase','STPA (25-KG) ','','USA WH','7320.00000','25.00000','12.65000','kgs ','Jul 7 2010 12:00AM','Jul 7 2010 12:00AM','','Jul 7 2010 12:00AM','2627'union all

SELECT '2627','Jul 7 2010 12:00AM','BUENDIA','Purchase','STPA (25-KG) ','','USA WH','60.00000','25.00000','0.00010','kgs ','Jul 7 2010 12:00AM','Jul 7 2010 12:00AM','','Jul 7 2010 12:00AM','2627'union all

SELECT '2628','Jul 7 2010 12:00AM','BUENDIA','Purchase','ST (35-LB) ','','USA WH','532.00000','15.87575','12.65000','kgs ','Jul 7 2010 12:00AM','Jul 7 2010 12:00AM','','Jul 7 2010 12:00AM','2628'union all

SELECT '2628','Jul 7 2010 12:00AM','BUENDIA','Purchase','ST (35-LB) ','','USA WH','4.00000','15.87575','0.00010','kgs ','Jul 7 2010 12:00AM','Jul 7 2010 12:00AM','','Jul 7 2010 12:00AM','2628'union all

SELECT '2635','Aug 3 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/FC/1 (33-KG) ','','I-TR CCL','2160.00000','33.00000','7.70000','kgs ','Aug 3 2010 12:00AM','Aug 3 2010 12:00AM','','Aug 3 2010 12:00AM','2635'union all

SELECT '2639','Aug 17 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5 (25-KG) ','','I-TR CCL','6300.00000','25.00000','5.88000','kgs ','Aug 17 2010 12:00AM','Aug 17 2010 12:00AM','','Aug 17 2010 12:00AM','2639'union all

SELECT '2630','Jul 16 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5 (25-KG) ','','I-TR CCL','1400.00000','25.00000','5.80000','kgs ','Jul 16 2010 12:00AM','Jul 16 2010 12:00AM','','Jul 16 2010 12:00AM','2630'union all

SELECT '2633','Jul 29 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-SD/DR5(A) (33-KG) ','','F-F','43200.00000','33.00000','5.58000','kgs ','Jul 28 2010 12:00AM','Jul 28 2010 12:00AM','','Jul 29 2010 12:00AM','2633'union all

SELECT '2642','Aug 23 2010 12:00AM','BUENDIA','Purchase','STPA (25-KG) ','','ORIGIN','615.00000','25.00000','13.00000','kgs ','Aug 23 2010 12:00AM','Aug 23 2010 12:00AM','','Aug 23 2010 12:00AM','2642'union all

SELECT '2640','Aug 18 2010 12:00AM','COLCAFE','Purchase','FSCC (1.3608 KG) ','','ORIGIN','2366.00000','1.36080','29.57000','kgs ','Aug 19 2010 12:00AM','Aug 19 2010 12:00AM','','Aug 18 2010 12:00AM','2640'union all

SELECT '2641','Aug 18 2010 12:00AM','COLCAFE','Purchase','EXSCER (200-KG) ','','ORIGIN','42.00000','200.00000','11.34000','kgs ','Aug 19 2010 12:00AM','Aug 19 2010 12:00AM','','Aug 18 2010 12:00AM','2641'union all

SELECT '2643','Aug 23 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-AG/3C (23-KG) ','','ORIGIN','7000.00000','23.00000','7.00000','kgs ','Aug 23 2010 12:00AM','Aug 23 2010 12:00AM','','Aug 23 2010 12:00AM','2643'union all

SELECT '2649','Sep 2 2010 12:00AM','COLCAFE','Purchase','SBC DECAF ','','IN-TR COL','16.00000','8.25000','54.91580','kgs ','Sep 2 2010 12:00AM','Sep 2 2010 12:00AM','','Sep 2 2010 12:00AM','2649'union all

SELECT '2644','Aug 26 2010 12:00AM','COLCAFE','Purchase','SCER (15-KG) ','','ORIGIN','105.00000','15.00000','26.48000','kgs ','Aug 26 2010 12:00AM','Aug 26 2010 12:00AM','','Aug 26 2010 12:00AM','2644'union all

SELECT '2644','Aug 26 2010 12:00AM','COLCAFE','Purchase','SEDR (35-KG) ','','ORIGIN','86.00000','35.00000','16.92000','kgs ','Aug 26 2010 12:00AM','Aug 26 2010 12:00AM','','Aug 26 2010 12:00AM','2644'union all

SELECT '2652','Oct 27 2010 12:00AM','CCLPRODUCTS','Purchase','CCL-FD/1 (23-KG) ','','TBD','70000.00000','23.00000','7.50000','kgs ','Oct 27 2010 12:00AM','Oct 27 2010 12:00AM','','Oct 27 2010 12:00AM','2652'union all

SELECT '2645','Aug 27 2010 12:00AM','COLCAFE','Purchase','TJ AGGLO (3.5 OZ JAR) ','','IN-TR COL','2800.00000','24.00000','1.68000','Jar ','Aug 27 2010 12:00AM','Aug 27 2010 12:00AM','','Aug 27 2010 12:00AM','2645'union all

SELECT '2645','Aug 27 2010 12:00AM','COLCAFE','Purchase','TJ AGGLO (3.5 OZ JAR) ','','IN-TR COL','1400.00000','24.00000','1.68000','Jar ','Aug 27 2010 12:00AM','Aug 27 2010 12:00AM','','Aug 27 2010 12:00AM','2645'union all

SELECT '2645','Aug 27 2010 12:00AM','COLCAFE','Purchase','TJ AGGO-DECAF (3.5 OZ/24) ','','IN-TR COL','1400.00000','24.00000','2.02000','Jar ','Aug 27 2010 12:00AM','Aug 27 2010 12:00AM','','Aug 27 2010 12:00AM','2645'union all

SELECT '2654','Oct 27 2010 12:00AM','COLCAFE','Purchase','ST1 (35-KG) ','','LA','54000.00000','35.00000','7.00000','kgs ','Sep 27 2010 12:00AM','Sep 27 2010 12:00AM','','Oct 27 2010 12:00AM','2654'union all

SELECT '2646','Aug 30 2010 12:00AM','CUTURRA COFFEE','Purchase','ST (RE-WT) ','','TX','23.00000','616.86950','0.00000','lbs ','Aug 30 2010 12:00AM','Aug 30 2010 12:00AM','','Aug 30 2010 12:00AM','2646'union all

SELECT '2647','Aug 31 2010 12:00AM','COLCAFE','Purchase','SCER (15-KG) ','','ORIGIN','200.00000','15.00000','26.59000','kgs ','Aug 31 2010 12:00AM','Aug 31 2010 12:00AM','','Aug 31 2010 12:00AM','2647'union all

SELECT '11982','Sep 1 2010 12:00AM','STARBUCKS','Sale','EXSCER (200-KG) ','3-26-0568 ','ORIGIN','-30.00000','440.92400','6.01000','lbs ','Aug 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11982'union all

SELECT '11976','Sep 1 2010 12:00AM','INGREDIENTSINTL','Sale','S4C (32-KG) ','002/4065/0419 ','LA','-15.00000','70.54784','3.41000','lbs ','Jul 30 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11976'union all

SELECT '11980','Sep 2 2010 12:00AM','AMFOTEK','Sale','SM (32-KG) ','002/4065/0421 ','CHI','-72.00000','70.54784','2.31000','lbs ','Sep 1 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 2 2010 12:00AM','11980'union all

SELECT '11981','Sep 2 2010 12:00AM','COFEXTRACTS','Sale','CCL-SD/FC/1 (33-KG) ','14/420/2604(2) ','LA','-2.00000','72.75246','4.69000','lbs ','Sep 2 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 2 2010 12:00AM','11981'union all

SELECT '7030','Aug 30 2010 12:00AM','ROYAL PACIFIC','Sale','ST (RE-WT) ','03/001/9105 ','TX','-23.00000','616.86950','5.05000','lbs ','Aug 30 2010 12:00AM','Jan 1 1900 12:00AM','','Aug 30 2010 12:00AM','7030'union all

SELECT '11991','Sep 8 2010 12:00AM','EAWEBER','Sale','9557 (32-KG) ','002/4065/0387 ','CHI','-15.00000','70.54784','7.25000','lbs ','Jun 21 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 8 2010 12:00AM','11991'union all

SELECT '11983','Sep 1 2010 12:00AM','STARBUCKS','Sale','SKU 11002231 ','3-26-0410 ','IN-TR COL','-1260.00000','1.00000','109.92700','Case ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11983'union

SELECT '11984','Sep 1 2010 12:00AM','STARBUCKS','Sale','SKU 1178263 - CI ','3-26-0410 ','IN-TR COL','-36.00000','108.00000','2.18200','SKU ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11984'union all

SELECT '11984','Sep 1 2010 12:00AM','STARBUCKS','Sale','SKU 1178265 - CI ','3-26-0410 ','IN-TR COL','-72.00000','108.00000','2.40700','SKU ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11984'union all

SELECT '11984','Sep 1 2010 12:00AM','STARBUCKS','Sale','SKU 1173888 ','3-26-0410 ','IN-TR COL','-63.00000','144.00000','0.61000','SKU ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11984'union all

SELECT '11984','Sep 1 2010 12:00AM','STARBUCKS','Sale','SKU 11002231 ','3-26-0410 ','IN-TR COL','-630.00000','1.00000','109.92700','Case ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 1 2010 12:00AM','11984'union all

SELECT '11985','Sep 2 2010 12:00AM','STARBUCKS','Sale','PSN-2M (15-KG) ','3-26-0629 ','IN-TR COL','-40.00000','1.00000','532.65000','Case ','May 11 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 2 2010 12:00AM','11985'union all

SELECT '11986','Sep 2 2010 12:00AM','STARBUCKS','Sale','PSN-2M (15-KG) ','3-26-0629 ','IN-TR COL','-15.00000','1.00000','532.65000','Case ','May 11 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 2 2010 12:00AM','11986'union all

SELECT '11987','Sep 2 2010 12:00AM','STARBUCKS','Sale','SKU 1178265 - CI ','3-26-0410 ','IN-TR COL','-360.00000','108.00000','2.40700','SKU ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 2 2010 12:00AM','11987'union all

SELECT '11988','Sep 7 2010 12:00AM','CNDC-SBUX','Sale','PSN-2M (15-KG) ','3-26-0491 ','ORIGIN','-9.00000','15.00000','35.51000','kgs ','Sep 2 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 7 2010 12:00AM','11988'union all

SELECT '11988','Sep 7 2010 12:00AM','CNDC-SBUX','Sale','PSN-2M (15-KG) ','3-26-0599 ','ORIGIN','-9.00000','15.00000','35.51000','kgs ','Sep 2 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 7 2010 12:00AM','11988'union all

SELECT '11990','Sep 8 2010 12:00AM','INCASA','Sale','ST1 (35-KG) ','3-26-0168 ','LA','-126.00000','77.16170','6.21000','lbs ','May 11 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 8 2010 12:00AM','11990'union all

SELECT '11994','Sep 2 2010 12:00AM','STARBUCKS','Sale','SKU 11006899 ','23-26-0600 ','IN-TR COL','-3600.00000','6.00000','2.06000','SKU ','May 12 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 2 2010 12:00AM','11994'union all

SELECT '11989','Sep 8 2010 12:00AM','STARBUCKS','Sale','SKU 1197035 ','3-26-0410 ','IN-TR COL','-2185.00000','16.00000','1.54000','SKU ','May 5 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 8 2010 12:00AM','11989'union

SELECT '11992','Sep 8 2010 12:00AM','BRADBARRY','Sale','F3X (32-KG) ','002/4065/0332 ','CHI','-5.00000','70.54784','7.54000','lbs ','Sep 8 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 8 2010 12:00AM','11992'union all

SELECT '11993','Sep 9 2010 12:00AM','BLUEPACIFIC','Sale','TCG1 (50-KG) ','3-26-0638 (TCG1) ','NY','-9.00000','110.23100','6.49000','lbs ','Sep 9 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 9 2010 12:00AM','11993'union all

SELECT '11993','Sep 9 2010 12:00AM','BLUEPACIFIC','Sale','TCG1 (50-KG) ','3-26-0638 (TCG1) ','NY','-16.00000','110.23100','3.25000','lbs ','Sep 9 2010 12:00AM','Jan 1 1900 12:00AM','','Sep 9 2010 12:00AM','11993'

-- No identity in original table

--set identity_insert dbo._PositionShipping off

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Oh my... My apologies. This one slipped through the cracks bigtime. I've got a couple of things I have to do first but I'll be back to this one soon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

Ralph,

Your data is a little bit different than your original example. What do you want the running total to be grouped by?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alex S-483693 SSC Enthusiast Points: 121 More actions · Answer 11

Hi Ralph,

I've had a look at the script Jeff put together and got it to work for my requirements - I have to say it is BRILLIANT!, thanks Jeff.

If I am not wrong, this should work for your requirement. This splits the Running Total by SKU and sorts by Required (date). I assumed this is what you want although you said:

how can I create a running total on the field "Ctns" when sorting by the "SKU" and "Required" fields?

If you did simply want it as a running total of Ctns, regardless of the SKU, then some minor changes are required (but this wouldn't make sense to me from a business context).

This is the version I came up with after creating your _PositionShipping table. The steps are:

to import the data from _PositionShipping into the temp table (or add the RunningTotal column to _PositionShipping),
define your parameters in the correct type and
update / reset the Running Total as you go through the table.

I'm pretty excited about this method (trawling through the article now) and plan to use it in a couple of other slow updates that I have (for example difference between consecutive dollar values).

Alex

--=====================================================================================================================

-- Build some sample data. This is the way you should do it when you post a question to make it easier on folks.

-- This is NOT a part of the solution but is an essential part to us being able to demo a solution for you.

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL

DROP TABLE #TestData

;

--===== Create and populate the test table on the fly

SELECT *, CONVERT([numeric](19, 5), NULL) as 'RunningTotal'

INTO #TestData

FROM [_PositionShipping] a

;

--===== Put a totally bogus clustered index on the data just to prove this works

CREATE CLUSTERED INDEX IX_#TestData ON #TestData (SKU)

;

--===== Display the test data before we do anything to it

SELECT * FROM #TestData ORDER BY SKU, Required

;

--=====================================================================================================================

-- This is the solution without the overhead of a Triangular Join. It'll do a million rows in just seconds.

--=====================================================================================================================

--===== Declare some essential variables with obvious names to reflect their use

DECLARE @PrevItem VARCHAR(100),

@PrevBal [numeric](19, 5),

@Counter INT

;

--===== It's essential to preset the counter variable. All the others may be calculated during the UPDATE

SELECT @Counter = 1

;

--===== This produces an ordered, running total update. It has a built in fault detector that will let you know if

-- a failure occured. That same fault detector is what makes the ordered update work even when the clustered

-- index is in a totally different order. This type of update is affectionately known as the "Quirky Update"

-- and it's a powerful tool to learn. Special thanks to Paul White and Tom Thompson for the fault detector.

WITH

cteSort AS

(

SELECT Counter = ROW_NUMBER() OVER(ORDER BY SKU, Required),

*

FROM #TestData

)

UPDATE tgt

SET @PrevBal = RunningTotal = CASE

WHEN tgt.Counter = @Counter

THEN CASE

WHEN SKU = @PrevItem

THEN tgt.Ctns + @PrevBal

ELSE tgt.Ctns

END

ELSE 1/0 --Force error if out of sequence

END,

@PrevItem = SKU,

@Counter = @Counter + 1

FROM cteSort tgt WITH (TABLOCKX) --Absolutely essential, we don't want anyone sneaking in while we're updating

OPTION (MAXDOP 1) --Parallelism must be prevented for the serial nature of this update

;

--===== Display the test data after the running total update

SELECT * FROM #TestData ORDER BY SKU, Required

;

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 12

Alex S-483693 (11/18/2010)
If I am not wrong, this should work for your requirement. This splits the Running Total by SKU and sorts by Required (date). I assumed this is what you want although you said:
how can I create a running total on the field "Ctns" when sorting by the "SKU" and "Required" fields?

Ok... I'm going to claim that I was tired instead of just stupid. :blush: I'm sorry... I just flat out missed that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

Alex S-483693 (11/18/2010)
I'm pretty excited about this method (trawling through the article now) and plan to use it in a couple of other slow updates that I have (for example difference between consecutive dollar values).

Paul White found a way to build in an error checker that also happens to make it work even better and Tom Thompson improved even that a bit. I'll see if I can find the post in the article for that. Ignore the stuff about the execution plan in the article... I made a mistake. It doesn't hurt anything but it is a mistake. Obviously, a 2nd rewrite is in the works...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 14

The post where Paul White describes the beautiful safety check he wrote is at the following URL: http://www.sqlservercentral.com/Forums/Topic802558-203-3.aspx#BM859559

DO make sure you incorporate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)