June 20, 2012 at 12:17 pm
I have a sql server situation where I do not know what to use, I am new to sql server and do not know how construct it.My data is as such
Account datetrans uniqueID TranAmount TranBal
AAA111 4/2/2012 1001 50.00 65.00
AAA111 4/30/2012 1002 10.00 65.00
AAA111 4/30/2012 1003 5.00 65.00
AAA111 4/30/2012 1004 10.00 65.00
AAA111 4/30/2012 1005 10.00 65.00
what i need to do is sum the TranAmount till it equals the tranbal so i would need the first 3 records then to pull out the record ID 1003.
June 20, 2012 at 12:24 pm
Something like this?
with CTE as (
select
*,
(select
sum(TranAount)
from
table inner
where
inner.UniqueId <= outer.uniqueid
) agg
from
table outer
)
select
*
from
CTE
where
agg = TranBal
In SQL Server 2012 I would have used framing.
June 20, 2012 at 1:52 pm
according to BOL this should work with SQL2008..
select *
, SUM ( TranAmount ) over ( partition by Account order by Account, datetrans, uniqueID ) as RunTranAmount
from yourtable
order by Account, datetrans, uniqueID
however, on my SQL2008R2DE SP1 CU7 (10.50.2817.0) it doesn't :crying:
( I upgraded this morning and have to double check for bug )
edited...
This one works :w00t:
;
with cteRNK
as (
select *
, rank() over ( partition by Account order by Account, datetrans, uniqueID ) as RNK
from #t
)
select T.Account
, T.datetrans
, T.uniqueID
, T.TranAmount
, T.TranBal
, SUM(R.TranAmount) RunTranAmount
from cteRNK T
inner join cteRNK R
on R.Account = T.Account
and R.RNK <= T.RNK
group by T.Account
, T.datetrans
, T.uniqueID
, T.TranAmount
, T.TranBal
order by T.Account
, T.datetrans
, T.uniqueID
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2012 at 3:51 pm
It would have helped us respond to you quicker if you'd kindly posted a table create and data insertion SQL. It would also have cleared up any assumptions about the data types eg.
CREATE TABLE [dbo].[sss](
[Account] [varchar](50) NOT NULL,
[datetrans] [datetime] NOT NULL,
[uniqueID] [int] NOT NULL,
[TranAmount] [numeric](18, 2) NOT NULL,
[TranBal] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO
insert into sss (account, dateTrans, uniqueID, TranAmount, TranBal) values
('AAA111', '2012-4-2', 1001, 50.00, 65.00),
('AAA111', '2012-4-30', 1002, 10.00, 65.00),
('AAA111', '2012-4-30', 1003, 5.00, 65.00),
('AAA111', '2012-4-30', 1004, 10.00, 65.00),
('AAA111', '2012-4-30', 1005, 10.00, 65.00)
I haven't bother to create a clustered index on the uniqueID column but good practice says you should.
It's also a good idea to use date formats that are not country or culture specific.
Anyway, here's the way I would do it without using cursors ;-);
declare
@maxID int = 0,
@id int = 0,
@runningTotal numeric(18,2) = 0,
@tranBal numeric(18,2) = 1
select @maxID = MAX(uniqueID) from dbo.sss
while (@runningTotal <> @tranBal) and (@id <> @maxID)
begin
select top 1 @id = uniqueID , @runningTotal += TranAmount, @tranBal = tranBal
from dbo.sss
where @id < uniqueID
order by uniqueID asc
end
select @id accountID
This loop gives you the option to do any further processing required.
If the uniqueID is really all you want you can simply execute this
select uniqueID
from dbo.sss s
where (
select SUM(TranAmount) runningTotal
from dbo.sss
where uniqueID <= s.uniqueID) = TranBal
Regards
D.
June 20, 2012 at 4:07 pm
Derek Robinson (6/20/2012)
It would have helped us respond to you quicker if you'd kindly posted a table create and data insertion SQL. It would also have cleared up any assumptions about the data types eg.
CREATE TABLE [dbo].[sss](
[Account] [varchar](50) NOT NULL,
[datetrans] [datetime] NOT NULL,
[uniqueID] [int] NOT NULL,
[TranAmount] [numeric](18, 2) NOT NULL,
[TranBal] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO
insert into sss (account, dateTrans, uniqueID, TranAmount, TranBal) values
('AAA111', '2012-4-2', 1001, 50.00, 65.00),
('AAA111', '2012-4-30', 1002, 10.00, 65.00),
('AAA111', '2012-4-30', 1003, 5.00, 65.00),
('AAA111', '2012-4-30', 1004, 10.00, 65.00),
('AAA111', '2012-4-30', 1005, 10.00, 65.00)
I haven't bother to create a clustered index on the uniqueID column but good practice says you should.
It's also a good idea to use date formats that are not country or culture specific.
Anyway, here's the way I would do it without using cursors ;-);
declare
@maxID int = 0,
@id int = 0,
@runningTotal numeric(18,2) = 0,
@tranBal numeric(18,2) = 1
select @maxID = MAX(uniqueID) from dbo.sss
while (@runningTotal <> @tranBal) and (@id <> @maxID)
begin
select top 1 @id = uniqueID , @runningTotal += TranAmount, @tranBal = tranBal
from dbo.sss
where @id < uniqueID
order by uniqueID asc
end
select @id accountID
This loop gives you the option to do any further processing required.
If the uniqueID is really all you want you can simply execute this
select uniqueID
from dbo.sss s
where (
select SUM(TranAmount) runningTotal
from dbo.sss
where uniqueID <= s.uniqueID) = TranBal
Regards
D.
FYI, your code fails if dateformat is set to dmy:
set dateformat dmy;
go
CREATE TABLE [dbo].[sss](
[Account] [varchar](50) NOT NULL,
[datetrans] [datetime] NOT NULL,
[uniqueID] [int] NOT NULL,
[TranAmount] [numeric](18, 2) NOT NULL,
[TranBal] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO
insert into dbo.sss (account, dateTrans, uniqueID, TranAmount, TranBal) values
('AAA111', '2012-4-2', 1001, 50.00, 65.00),
('AAA111', '2012-4-30', 1002, 10.00, 65.00),
('AAA111', '2012-4-30', 1003, 5.00, 65.00),
('AAA111', '2012-4-30', 1004, 10.00, 65.00),
('AAA111', '2012-4-30', 1005, 10.00, 65.00)
GO
select * from dbo.sss;
go
drop table dbo.sss;
go
June 20, 2012 at 4:09 pm
The following will work with both mdy and dmy:
set dateformat mdy;
go
CREATE TABLE [dbo].[sss](
[Account] [varchar](50) NOT NULL,
[datetrans] [datetime] NOT NULL,
[uniqueID] [int] NOT NULL,
[TranAmount] [numeric](18, 2) NOT NULL,
[TranBal] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO
insert into dbo.sss (account, dateTrans, uniqueID, TranAmount, TranBal) values
('AAA111', '20120402', 1001, 50.00, 65.00),
('AAA111', '20120430', 1002, 10.00, 65.00),
('AAA111', '20120430', 1003, 5.00, 65.00),
('AAA111', '20120430', 1004, 10.00, 65.00),
('AAA111', '20120430', 1005, 10.00, 65.00)
GO
select * from dbo.sss;
go
drop table dbo.sss;
go
set dateformat dmy;
go
CREATE TABLE [dbo].[sss](
[Account] [varchar](50) NOT NULL,
[datetrans] [datetime] NOT NULL,
[uniqueID] [int] NOT NULL,
[TranAmount] [numeric](18, 2) NOT NULL,
[TranBal] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
GO
insert into dbo.sss (account, dateTrans, uniqueID, TranAmount, TranBal) values
('AAA111', '20120402', 1001, 50.00, 65.00),
('AAA111', '20120430', 1002, 10.00, 65.00),
('AAA111', '20120430', 1003, 5.00, 65.00),
('AAA111', '20120430', 1004, 10.00, 65.00),
('AAA111', '20120430', 1005, 10.00, 65.00)
GO
select * from dbo.sss;
go
drop table dbo.sss;
go
set dateformat mdy;
go
June 20, 2012 at 4:12 pm
Thanks for improving the code, Lynn. It's 11 pm here in the UK and I'm using that as an excuse.
June 20, 2012 at 8:22 pm
How about something just a tad more interesting?
CREATE TABLE #sss
([Account] [varchar](50) NOT NULL,
[datetrans] [datetime] NOT NULL,
[uniqueID] [int] NOT NULL,
[TranAmount] [numeric](18, 2) NOT NULL,
[TranBal] [numeric](18, 2) NOT NULL
) ON [PRIMARY]
insert into #sss (account, dateTrans, uniqueID, TranAmount, TranBal)
values
('AAA111', '20120402', 1001, 50.00, 65.00),
('AAA111', '20120430', 1002, 10.00, 65.00),
('AAA111', '20120430', 1003, 5.00, 65.00),
('AAA111', '20120430', 1004, 10.00, 65.00),
('AAA111', '20120430', 1005, 10.00, 65.00)
DECLARE @RTRow NUMERIC(18, 2) = 0.00
;WITH s AS (
SELECT account, dateTrans, uniqueID, TranAmount, TranBal
,n=ROW_NUMBER() OVER (ORDER BY uniqueID)
FROM #sss)
UPDATE s
SET uniqueID = uniqueID
,@RTRow = CASE WHEN @RTRow + TranAmount = TranBal THEN uniqueID
WHEN @RTRow + TranAmount > TranBal THEN @RTRow
ELSE @RTRow + TranAmount END
SELECT ID2MatchTranBal=CAST(@RTRow AS INT)
DROP TABLE #sss
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply