November 20, 2012 at 11:03 am
I need to write a query to subtract data from consecutive rows uptil a non zero value is reached.
For
Current Line 8659: it should return 25000-15000
Current Line 12328: it should return 10935-57690
Current Line 13521: it should return 5736.77-5736.77
Current Line 15564: it should return 48000-32000
CurrentLineMappingLineSourceLineAmountRowNumber
8659 8659 7111 25000 1
8659 7111 6100 25000 2
8659 6100 4422 15000 3
12328 12328 9660 10935 1
12328 9660 7763 10935 2
12328 7763 6894 10935 3
12328 6894 5411 10935 4
12328 5411 1792 57690 5
13521 13521 12639 5736.77 1
13521 12639 10783 5736.77 2
13521 10783 8948 5736.77 3
13521 8948 8688 5736.77 4
13521 8688 7213 5736.77 5
13521 7213 7148 5736.77 6
13521 7148 6803 5736.77 7
15564 15564 12247 48000 1
15564 12247 9009 32000 2
Scripts for table:
------------------------
CREATE TABLE [AmountTable](
[CurrentLine] [float] NULL,
[MappingLine] [float] NULL,
[SourceLine] [float] NULL,
[Amount] [float] NULL,
[RowNumber] [float] NULL
) ON [PRIMARY]
GO
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8659, 8659, 7111, 25000, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8659, 7111, 6100, 25000, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8659, 6100, 4422, 15000, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 12328, 9660, 10935, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 9660, 7763, 10935, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 7763, 6894, 10935, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 6894, 5411, 10935, 4)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 5411, 1792, 57690, 5)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 13521, 12639, 5736.77, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 12639, 10783, 5736.77, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 10783, 8948, 5736.77, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 8948, 8688, 5736.77, 4)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 8688, 7213, 5736.77, 5)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 7213, 7148, 5736.77, 6)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 7148, 6803, 5736.77, 7)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (15564, 15564, 12247, 48000, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (15564, 12247, 9009, 32000, 2)
November 20, 2012 at 11:27 am
Assuming that the last rownumber for any currentline is the one to be subtracted:
select a.[CurrentLine], a.amount, b.amount, (a.amount - b.amount) as 'total'
from [AmountTable] a join AmountTable b on a.CurrentLine = b.CurrentLine
and a.RowNumber = 1 and b.RowNumber =
(select MAX(c.rownumber) from AmountTable c where c.CurrentLine = a.CurrentLine)
If the subtractions can be triggered multiple times for any given currentline, then this won't work.
November 20, 2012 at 8:29 pm
Thanks however it may not always be the last one. The subtraction should happen between consecutive ones.
i.e Row 1- Row 2.
If Row 1 - Row 2 =0 then Row2-Row 3
If Row 2 - row 3 = 0 then Row 3- Row 4 and so on.
It should go on until no more rows are encountered.
November 21, 2012 at 2:27 am
This probably isn't what you're looking for either but we'll give it a shot:
;WITH Amounts AS (
SELECT *,
n=ROW_NUMBER() OVER (PARTITION BY CurrentLine ORDER BY RowNumber DESC)
FROM AmountTable)
SELECT CurrentLine
,Amount=SUM(CASE n WHEN 1 THEN -Amount ELSE Amount END)
FROM Amounts
WHERE n < 3
GROUP BY CurrentLine
It would be best to clarify exactly your expected results set as it should appear in the Results pane of SSMS because your requirement isn't particularly clear as stated.
For example, the above query returns this:
CurrentLine Amount
8659 10000
12328 -46755
13521 0
15564 16000
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
November 21, 2012 at 7:07 am
Apologies if I wasnt clear. I have been working on it since last night.
Have reached to some point but need help to proceed.
Please find additional rows in this table to help me explain what I need.
I have written the following query -
select A.CurrentLine,A.MappingLine,A.SourceLine,
A.Amount as AmountA,
B.Amount as AmountB,
A.RowNumber as RowNbrA, B.RowNumber as RowNbrB,
(A.Amount-B.Amount) as [Difrence (AmountA - AmountB)]
from [dbo].[AmountTable] A
Inner join [dbo].[AmountTable] B
on B.RowNumber = A.RowNumber +1 and A.CurrentLine = B.CurrentLine
I need to add another column (say Occurance [indicating teh occurance of non zero difference]) to this resultset indicating the "occurance" of a non zero value (Difference (Amount A-Amount B)) if it exists for each CurrentLine.
So specifically for -
Current Line - 8659
the New Column should say Occurance = 1 (to indicate first occurance) for Diference =1000
Current Line - 8660
the New Column should say Occurance = 1 (to indicate first occurance) for Diference =10000
the New Column should say Occurance = 2 (to indicate 2nd occurance) for Diference =15000
For cases where there is no occurance of non zero difference, it must indicate the last row of rowbnr A for a specific currentline.
Eg. Current Line - 13521
Occurance should be 0 for all rows.
--------------------------------------------------------------------------------------------------------------
The insert script -
truncate table [AmountTable]
GO
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8659, 8659, 7111, 25000, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8659, 7111, 6100, 25000, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8659, 6100, 4422, 15000, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8660, 8659, 7111, 25000, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8660, 7111, 6100, 25000, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8660, 6100, 4422, 15000, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8660, 6100, 4422, 15000, 4)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (8660, 6100, 4422, 0, 5)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 12328, 9660, 10935, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 9660, 7763, 10935, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 7763, 6894, 10935, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 6894, 5411, 10935, 4)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (12328, 5411, 1792, 57690, 5)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 13521, 12639, 5736.77, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 12639, 10783, 5736.77, 2)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 10783, 8948, 5736.77, 3)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 8948, 8688, 5736.77, 4)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 8688, 7213, 5736.77, 5)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 7213, 7148, 5736.77, 6)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (13521, 7148, 6803, 5736.77, 7)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (15564, 15564, 12247, 48000, 1)
INSERT [AmountTable] ([CurrentLine], [MappingLine], [SourceLine], [Amount], [RowNumber]) VALUES (15564, 12247, 9009, 32000, 2)
November 21, 2012 at 11:42 am
This may do what you require?;with origQry as (
select A.CurrentLine,A.MappingLine,A.SourceLine,
A.Amount as AmountA,
B.Amount as AmountB,
A.RowNumber as RowNbrA, B.RowNumber as RowNbrB,
(A.Amount-B.Amount) as [Difrence (AmountA - AmountB)]
from [dbo].[AmountTable] A
Inner join [dbo].[AmountTable] B
on B.RowNumber = A.RowNumber +1 and A.CurrentLine = B.CurrentLine
)
select CurrentLine, [Difrence (AmountA - AmountB)], row_number() over (partition by currentLine order by RowNbrB) occurence, RowNbrB
from origQry
where [Difrence (AmountA - AmountB)] <> 0
union all
select CurrentLine, 0 [Difrence (AmountA - AmountB)], 1, MAX(RowNbrB)
from origQry
group by currentline
having sum([Difrence (AmountA - AmountB)]) = 0
November 21, 2012 at 12:01 pm
This cannot be done reliably because there is nothing in the table to absolutely guarantee the correct order of the data. You must have some column that guarantees the order. It's tempting to rely on the supposed "natural" order but that order could change in a heartbeat if the optimizer thinks it should.
Add a column to sort by first.
Scratch that and my apologies. I missed the column furthest to the right.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2012 at 5:42 pm
How about something like this?
;WITH Amounts AS (
SELECT CurrentLine, Amount, RowNumber, [Difference]=CAST(0 AS FLOAT)
,[Rows Subtracted]=CAST('N/A' AS VARCHAR(13))
FROM AmountTable
WHERE RowNumber = 1
UNION ALL
SELECT a.CurrentLine, a.Amount, a.RowNumber, b.Amount - a.Amount
,CAST(b.RowNumber AS VARCHAR(5)) + ' - ' + CAST(a.RowNumber AS VARCHAR(5))
FROM AmountTable a
INNER JOIN Amounts b
ON a.CurrentLine = b.CurrentLine AND b.RowNumber + 1 = a.RowNumber
)
SELECT CurrentLine, [Difference], [Rows Subtracted]
FROM (
SELECT CurrentLine, Amount, RowNumber, [Difference], [Rows Subtracted]
,n=ROW_NUMBER() OVER (PARTITION BY CurrentLine ORDER BY RowNumber DESC)
FROM Amounts) a
WHERE [Difference] <> 0 OR ([Difference] = 0 AND n = 1)
ORDER BY CurrentLine, RowNumber
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
December 6, 2012 at 11:08 pm
Thanks all for your help. I finally managed it using the same query that I had created.
This query was a part of an SSIS package.
We broadly categorized the conditions -
1. All records having value 0
2. All upto the last one having a 0 value
Used CONDITIONAL SPLIT and routed the rows as required.
For picking the first non zero,
1. Used a column in the query called NonZeroPosition
2. Put 9999999999 for 0s
3. Put the RowNumber for non zero ones.
4. Used aggregate transformation to pick the MIN NonZero Position.
Could manage (not sure if this was the best way to do it...but in the interest of time completed the task) !!...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply