January 24, 2013 at 7:35 pm
I am trying to figure out how to get results from a transaction table that will blend the results of prior transactions with the current transaction. This is for tracking the amount of wine that was aged in barrels and barrels are classified by year (year of manufacture).
A very short example of my transaction table looks like this:
tmpWkkKeytmpHdrIDtmpTransDatetmpQtytmpCompQtywobBblYearwobPctDateInBblsDateOutBbls
215472218092011-10-12 16:54:26.0002900149.999620075.172402011-10-12NULL
215472218092011-10-12 16:54:26.0002900649.9973200822.413702011-10-12NULL
215472218092011-10-12 16:54:26.0002900449.9988200915.517202011-10-12NULL
215472218092011-10-12 16:54:26.00029001049.9972201036.206802011-10-12NULL
215472218092011-10-12 16:54:26.0002900599.9984201120.689602011-10-12NULL
221816218092011-11-21 15:13:16.000-500-100200720.000002011-10-122011-11-21
221816218092011-11-21 15:13:16.000-500-350200870.000002011-10-122011-11-21
221816218092011-11-21 15:13:16.000-500-50200910.000002011-10-122011-11-21
The first 5 transcation rows are components of the same 2900 gallon transaction, split between barrel years 2007 - 2011. 5.1724% went into 2007 barrels, 22.4137% went into 2008 barrels, and so on.
The next 3 transaction rows are the result of pumping 500 gallons out of some of these barrels, but only barrel years 2007 - 2009 were used.
What I need is sql that gives me the results as follows. The -500 transaction needs to return what was left in all 5 barrel years with the resulting percents and combined qty for each barrel year:
tmpWkkKeytmpHdrIDtmpTransDateCombinedQtywobBblYearwobPctDateInBblsDateOutBbls
2154722180910/12/11 4:54 PM149.999620075.172410/12/2011NULL
2154722180910/12/11 4:54 PM649.9973200822.413710/12/2011NULL
2154722180910/12/11 4:54 PM449.9988200915.517210/12/2011NULL
2154722180910/12/11 4:54 PM1049.9972201036.206810/12/2011NULL
2154722180910/12/11 4:54 PM599.9984201120.689610/12/2011NULL
2218162180911/21/11 3:13 PM49.999620072.08332410/12/201111/21/2011
2218162180911/21/11 3:13 PM299.9973200812.4999310/12/201111/21/2011
2218162180911/21/11 3:13 PM399.9988200916.6666810/12/201111/21/2011
2218162180911/21/11 3:13 PM1049.9972201043.7500410/12/2011NULL
2218162180911/21/11 3:13 PM599.9984201125.0000210/12/2011NULL
but no matter what I try, I can't seem to get the last 2 rows returned for barrel years 2010 and 2011, and I haven't even attempted the calculation of the resulting percents since I can't seem to get all the rows I need.
We're currently on SQL Server 2008 R2 but I experimented with SQL Server 2012 running totals code but with no success:
SELECTA.tmpWkkKey,
A.tmpHdrID,
A.tmpTransDate,
CombinedQty = SUM(A.tmpCompQty) OVER (PARTITION BY A.tmpHdrID, A.wobBblYear ORDER BY A.tmpTransDate),
A.wobBblYear,
A.wobPct,
A.DateInBbls,
A.DateOutBbls
FROMdbo.TestBblPcts A
ORDER BY A.tmpTransDate
If there is a way to do this in SQL Server 2008 R2, I'd very much appreciate some assistance. If SQL Server 2012 offers a better solution, I'm open to that, too. Here is some sql to create the test transaction table that I'm working with:
CREATE TABLE [dbo].[TestBblPcts](
[tmpWkkKey] [int] NOT NULL,
[tmpHdrID] [int] NOT NULL,
[tmpTransDate] [datetime] NOT NULL,
[tmpQty] [float] NOT NULL,
[tmpCompQty] [float] NULL,
[wobBblYear] [varchar](4) NULL,
[wobPct] [varchar](7998) NULL,
[DateInBbls] [date] NULL,
[DateOutBbls] [date] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.TestBblPcts
SELECT 215472,21809,'Oct 12 2011 4:54PM',2900,150,2007,5.17240,'2011-10-12',NULL
UNION SELECT 215472,21809,'Oct 12 2011 4:54PM',2900,649.997,2008,22.41370,'2011-10-12',NULL
UNION SELECT 215472,21809,'Oct 12 2011 4:54PM',2900,449.999,2009,15.51720,'2011-10-12',NULL
UNION SELECT 215472,21809,'Oct 12 2011 4:54PM',2900,1050,2010,36.20680,'2011-10-12',NULL
UNION SELECT 215472,21809,'Oct 12 2011 4:54PM',2900,599.998,2011,20.68960,'2011-10-12',NULL
UNION SELECT 221816,21809,'Nov 21 2011 3:13PM',-500,-100,2007,20.00000,'2011-10-12','2011-11-21'
UNION SELECT 221816,21809,'Nov 21 2011 3:13PM',-500,-350,2008,70.00000,'2011-10-12','2011-11-21'
UNION SELECT 221816,21809,'Nov 21 2011 3:13PM',-500,-50,2009,10.00000,'2011-10-12','2011-11-21'
Thanks much,
Gina
January 24, 2013 at 9:16 pm
I am no oenophile but...
Perhaps something like this will get you on track?
;WITH InBBLS AS (
SELECT *
FROM TestBblPcts
WHERE DateOutBbls IS NULL),
OutBBLs AS (
SELECT *
FROM TestBblPcts
WHERE DateOutBbls IS NOT NULL)
SELECT a.*
FROM InBBLS a
UNION ALL
SELECT
[tmpWkkKey]=MAX(a.tmpWkkKey) OVER (PARTITION BY b.tmpHdrID)
,b.[tmpHdrID]
,b.[tmpTransDate]
,MIN(ISNULL(b.[tmpQty], 0)+ISNULL(a.[tmpQty], 0)) OVER (PARTITION BY b.tmpHdrID)
,ISNULL(b.[tmpCompQty],0)+ISNULL(a.[tmpCompQty],0)
,b.[wobBblYear]
,100*(ISNULL(b.[tmpCompQty],0)+ISNULL(a.[tmpCompQty],0))/
MIN(ISNULL(b.[tmpQty], 0)+ISNULL(a.[tmpQty], 0)) OVER (PARTITION BY b.tmpHdrID)
,b.[DateInBbls]
,a.[DateOutBbls]
FROM OutBBLS a
RIGHT JOIN InBBLS b ON a.tmpHdrID = b.tmpHdrID AND a.wobBblYear = b.wobBblYear
ORDER BY tmpWkkKey, wobBblYear
I didn't validate every column in the results for the 221816 tmpWkkKey group, but I think you can figure out how to fix them if any aren't right.
Not sure if this will work when you're including multiple bbl out transactions. Would need more set up data to check.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 25, 2013 at 10:06 am
Thanks, Dwain. Let me see what I can do with this.
Gina
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply