February 21, 2017 at 7:54 am
Hi All
Bit of a mental block, probably from looking at this a bit to much, so any assistance is greatly appreciated.
Attached is some sample data. expected results and my work in progress so far.
I'm after a grand running total based on the apportionments.
So for ID 4, I need the sum of everything from SeqNo 4 to 70
For ID 1309, I need the sum of everything from SeqNo 3 to 52, 54 to 64, 66 to 68, 70 to 71, 73 to 75
Each batch for the sum is started at the row before with its apportionment title and 100%, with the very last row of all apportionments being "Non Apportioned Expenditure"
If a user adds a new Charge to an apportionment then the SeqNo's will remain consecutive, and any other apportion SeqNo's will increment, like a row_number, eg for ID 1309, user may add a new charge under "Schedule - All Tenants", which will mean the values will change to 3-53, 55-64, 67-69, 71-72, 74-76.
Thanks
Ant
February 21, 2017 at 9:04 am
Ant
Try this. It can probably be simplified and/or prettified, but it's a start. And it seems to return the correct results. The Apportion and Nominal columns don't appear to have any effect on the results, so I left them out.
WITH Nulled AS (
SELECT
ID
, CASE Description
WHEN 'Charge' THEN NULL
ELSE Description
END AS NulledTitle
, SeqNo
, Budget
FROM #Temp
)
, Counts AS (
SELECT
ID
, NulledTitle
, SeqNo
, Budget
, COUNT(NulledTitle) OVER (
PARTITION BY ID
ORDER BY SeqNo
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS TitleNo
FROM Nulled
)
, Totals AS (
SELECT
ID
, TitleNo
, MAX(NulledTitle) AS TitleName
, SUM(Budget) AS BudgetSum
FROM Counts
GROUP BY
ID
, TitleNo
)
, NotPivoted AS (
SELECT
ID
, TitleNo
, TitleName
, BudgetSum
FROM Totals
WHERE TitleName IS NOT NULL
AND TitleName <> 'Non Apportioned Expenditure'
)
, Pivoted AS (
SELECT DISTINCT
ID
, CASE WHEN TitleNo = 1 THEN MAX(TitleName) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Desc1
, CASE WHEN TitleNo = 1 THEN MAX(BudgetSum) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Budget1
, CASE WHEN TitleNo = 2 THEN MAX(TitleName) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Desc2
, CASE WHEN TitleNo = 2 THEN MAX(BudgetSum) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Budget2
, CASE WHEN TitleNo = 3 THEN MAX(TitleName) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Desc3
, CASE WHEN TitleNo = 3 THEN MAX(BudgetSum) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Budget3
, CASE WHEN TitleNo = 4 THEN MAX(TitleName) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Desc4
, CASE WHEN TitleNo = 4 THEN MAX(BudgetSum) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Budget4
, CASE WHEN TitleNo = 5 THEN MAX(TitleName) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Desc5
, CASE WHEN TitleNo = 5 THEN MAX(BudgetSum) OVER (PARTITION BY ID, TitleNo) ELSE NULL END AS Budget5
FROM NotPivoted
)
SELECT
ID
, MAX(Desc1)
, MAX(Budget1)
, MAX(Desc2)
, MAX(Budget2)
, MAX(Desc3)
, MAX(Budget3)
, MAX(Desc4)
, MAX(Budget4)
, MAX(Desc5)
, MAX(Budget5)
FROM Pivoted
GROUP BY ID;
John
February 21, 2017 at 9:15 am
Excellent thanks John, greatly appreciated as always.
February 21, 2017 at 9:49 am
Here's another option. I hope that it helps.
SELECT t.ID,
MAX( CASE WHEN d.RowNo = 1 THEN d.Description END) AS Desc1,
SUM( CASE WHEN d.RowNo = 1 THEN t.Budget END) AS Budget1,
MAX( CASE WHEN d.RowNo = 2 THEN d.Description END) AS Desc2,
SUM( CASE WHEN d.RowNo = 2 THEN t.Budget END) AS Budget2,
MAX( CASE WHEN d.RowNo = 3 THEN d.Description END) AS Desc3,
SUM( CASE WHEN d.RowNo = 3 THEN t.Budget END) AS Budget3,
MAX( CASE WHEN d.RowNo = 4 THEN d.Description END) AS Desc4,
SUM( CASE WHEN d.RowNo = 4 THEN t.Budget END) AS Budget4,
MAX( CASE WHEN d.RowNo = 5 THEN d.Description END) AS Desc5,
SUM( CASE WHEN d.RowNo = 5 THEN t.Budget END) AS Budget5
FROM #Temp t
JOIN (
SELECT *,
LEAD(SeqNo) OVER(PARTITION BY Id ORDER BY SeqNo) NextSeqNo,
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY SeqNo) RowNo
FROM #Temp
WHERE Description <> 'Charge') d ON t.ID = d.ID AND t.SeqNo > d.SeqNo AND t.SeqNo < d.NextSeqNo
GROUP BY t.ID
ORDER BY t.ID
February 21, 2017 at 7:04 pm
anthony.green - Tuesday, February 21, 2017 7:54 AMHi AllBit of a mental block, probably from looking at this a bit to much, so any assistance is greatly appreciated.
Attached is some sample data. expected results and my work in progress so far.
I'm after a grand running total based on the apportionments.
So for ID 4, I need the sum of everything from SeqNo 4 to 70
For ID 1309, I need the sum of everything from SeqNo 3 to 52, 54 to 64, 66 to 68, 70 to 71, 73 to 75Each batch for the sum is started at the row before with its apportionment title and 100%, with the very last row of all apportionments being "Non Apportioned Expenditure"
If a user adds a new Charge to an apportionment then the SeqNo's will remain consecutive, and any other apportion SeqNo's will increment, like a row_number, eg for ID 1309, user may add a new charge under "Schedule - All Tenants", which will mean the values will change to 3-53, 55-64, 67-69, 71-72, 74-76.
Thanks
Ant
Bydefinition, a table must have a key. Yet you post things that cannever, ever have a key, because all the columns are NULL.
Yourinsanely large “description_<nothing in particular>†seemsto be a code or type of transaction. We both know that your choice ofan insanely large VARCHAR(50) involved no research or planningwhatsoever. If you done it right. By the way there would be a checkconstraint limiting this column to the valid values that could fit init.
Thereis no such crap as a “sequence_nbr†in in RDBMS; it has to be a“<something in particular>_seqâ€, such as invoices, checks,whatever. You might want to look up the create sequence statement inSQL. If this actually is a sequence.
Whydo you think budget is a valid data element name? Is it a type code?A quantity? What?
Whydo you think “apportion†(of what?) is a valid data element name?It looks to be a quantity, but it lacks what ISO 11179 calls anattribute property.
Finally,“nominal†is an adjective, not even a noun! I also see that it ishuge. Were these the defaults from an old ACCESS or Xbase schema?
Myguess is that the key is (generic_id, something_seq), but I have noconstraints or proof that this guess is correct.
Mostof the work in SQL is done in the DDL, so I would suggest you throwout what you have got and start over with the correct design. Thislooks like someone transcribed a series of notes of a yellow pad intoan improper table declaration. Would you like to try again?
Bydefinition, a table must have a key. Yet you post things that cannever, ever have a key, because all the columns are NULL.
Yourinsanely large “description_<nothing in particular>†seemsto be a code or type of transaction. We both know that your choice ofan insanely large VARCHAR(50) involved no research or planningwhatsoever. If you done it right. By the way there would be a checkconstraint limiting this column to the valid values that could fit init.
Thereis no such crap as a “sequence_nbr†in in RDBMS; it has to be a“<something in particular>_seqâ€, such as invoices, checks,whatever. You might want to look up the create sequence statement inSQL. If this actually is a sequence.
Whydo you think budget is a valid data element name? Is it a type code?A quantity? What?
Whydo you think “apportion†(of what?) is a valid data element name?It looks to be a quantity, but it lacks what ISO 11179 calls anattribute property.
Finally,“nominal†is an adjective, not even a noun! I also see that it ishuge. Were these the defaults from an old ACCESS or Xbase schema?
Myguess is that the key is (generic_id, something_seq), but I have noconstraints or proof that this guess is correct.
Mostof the work in SQL is done in the DDL, so I would suggest you throwout what you have got and start over with the correct design. Thislooks like someone transcribed a series of notes of a yellow pad intoan improper table declaration. Would you like to try again?
Please post DDL and follow ANSI/ISO standards when asking for help.
February 22, 2017 at 1:43 am
Thanks as always Luis
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply