January 23, 2014 at 6:39 am
I have the following query which produces a running total.
WITH CTE_rownr
AS (
SELECT extractdate
, dwPalletId
, szPalletNumber
, dwFormsetIdFK
, SUM(quantity) as quantity
, ROW_NUMBER() OVER (
PARTITION BY dwPalletId ORDER BY dwPalletId
, extractdate
) AS rn
,daysinstock
FROM FSPalletStockHistory_extract
GROUP BY extractdate, dwPalletId, szPalletNumber, dwFormsetIdFK, daysinstock
)
SELECT extractdate,
dwPalletId,
szPalletNumber,
(
SELECT SUM(quantity)
FROM CTE_rownr
WHERE dwPalletId = base.dwPalletId AND rn <= base.rn
) AS quantity,
dwFormsetIdFK,
daysinstock
FROM CTE_rownr base;
How can I get this to output to a table please?
January 23, 2014 at 8:35 am
Here is one way that I was just working on when I read your post. :w00t:
Remember, INSERT INTO when the table exists. Use SELECT INTO when it does not and you want to create a new one automatically.
INSERT INTO
[Code="other"]
WITH tab AS (
bla bla
)
INSERT INTO [YOUR TABLE] (
[COLUMNS...]
)
SELECT * FROM tab
[/code]
SELECT INTO
; WITH UNIQUEID([ID], [Name]) AS
(
SELECT DISTINCT
[ID]
,[Name]
FROM #TempTable_2
WHERE [Type] = '1'
)
SELECT
a.[ID]
,NEWID() AS [NEWID]
,a.[GroupID]
INTO #TempTable_3
FROM UNIQUEID a
--INSERT INTO COMBINED IF YOU NEEDED
INSERT INTO [YourTable]
(
[COLUMNS...]
)
SELECT
[COLUMNS...]
FROM #TempTable_3
...
Soooo, you could do this
[Code="other"]
WITH CTE_rownr
AS (
SELECT extractdate
, dwPalletId
, szPalletNumber
, dwFormsetIdFK
, SUM(quantity) as quantity
, ROW_NUMBER() OVER (
PARTITION BY dwPalletId ORDER BY dwPalletId
, extractdate
) AS rn
,daysinstock
FROM FSPalletStockHistory_extract
GROUP BY extractdate, dwPalletId, szPalletNumber, dwFormsetIdFK, daysinstock
)
INSERT INTO [YOUR TABLE] (
[COLUMNS...]
)
SELECT extractdate,
dwPalletId,
szPalletNumber,
(
SELECT SUM(quantity)
FROM CTE_rownr
WHERE dwPalletId = base.dwPalletId AND rn <= base.rn
) AS quantity,
dwFormsetIdFK,
daysinstock
FROM CTE_rownr base;
[/code]
OR
SELECT SUM(quantity)
FROM CTE_rownr
WHERE dwPalletId = base.dwPalletId AND rn <= base.rn
) AS quantity,
dwFormsetIdFK,
daysinstock
INTO #TempTable_1
FROM CTE_rownr base;
January 23, 2014 at 5:26 pm
phingers (1/23/2014)
I have the following query which produces a running total.
WITH CTE_rownrAS (
SELECT extractdate
, dwPalletId
, szPalletNumber
, dwFormsetIdFK
, SUM(quantity) as quantity
, ROW_NUMBER() OVER (
PARTITION BY dwPalletId ORDER BY dwPalletId
, extractdate
) AS rn
,daysinstock
FROM FSPalletStockHistory_extract
GROUP BY extractdate, dwPalletId, szPalletNumber, dwFormsetIdFK, daysinstock
)
SELECT extractdate,
dwPalletId,
szPalletNumber,
(
SELECT SUM(quantity)
FROM CTE_rownr
WHERE dwPalletId = base.dwPalletId AND rn <= base.rn
) AS quantity,
dwFormsetIdFK,
daysinstock
FROM CTE_rownr base;
How can I get this to output to a table please?
Before you go any farther, if you've got lots of rows this is going to be pretty slow because of the triangular join you're doing. You probably should take a look at this article by SQL MVP Jeff Moden: Solving the Running Total and Ordinal Rank Problems[/url]
There's also a new method available in SQL 2012 that's quite fast (by Wayne Sheffield): Running totals in “Denali” CTP3[/url]
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 24, 2014 at 12:45 am
Thanks dwain.c - I used xsevensinzx because it is a one off operation. Yes it is slow but scheduled overnight it hadn't got in the way of anything else.
The article you referred to its very good and I will use that in future.
January 24, 2014 at 12:52 am
phingers (1/24/2014)
Thanks dwain.c - I used xsevensinzx because it is a one off operation. Yes it is slow but scheduled overnight it hadn't got in the way of anything else.The article you referred to its very good and I will use that in future.
Excellent idea! Wait for it to break in the middle of the night so your DBA can be called in to fix it. :w00t:
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 24, 2014 at 3:18 pm
Before you go any farther, if you've got lots of rows this is going to be pretty slow because of the triangular join you're doing. You probably should take a look at this article by SQL MVP Jeff Moden: Solving the Running Total and Ordinal Rank Problems[/url]
... If I may add:
A good article about Triangular Joins[/url] by Jeff Moden.
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply