February 10, 2009 at 1:25 pm
I will have a list of values that I need to subtract 2 values from a group. In the group will only be 2 rows at most (maybe just 1). I need to subtract the TotalQTy in row 1 from row 2.
here is the code I use when I narrow it down to one group.
declare @myTable table
( myId integer,
acctNumber nvarchar(100),
MName nvarchar(256),
SName nvarchar(256),
os char(25),
maName char(10),
TotalQty integer
)
insert into @myTable select myID=1,account,MName, Sname,OS,MaName, sum(Qty) as TotalQty
from tbl1
where Os = 'N' And account is not null And
MName is not null And Sname is not null
group by account,MName, Sname,OS,MaName,
order by account,MName, Sname,OS,MaName,
insert into @myTable select myID=2,account,MName, Sname,OS,MaName, sum(Qty) as TotalQty
from tbl1
where Os = 'Y' And account is not null And
MName is not null And Sname is not null
group by account,MName, Sname,OS,MaName,
order by account,MName, Sname,OS,MaName,
select convert (varchar (10), myId) as myId, TotalQty from @myTable where myId in (1,2)
union all
select 'Difference', a.TotalQty - b.TotalQty
from @myTable a
inner join @myTable b
on a.myId = 1
and b.myId = 2
now is it possible to loop through a table and pull the 2 records out?
for example using the folowing Select I will give the output.
select account,MName, Sname,OS,MaName, sum(Qty) as TotalQty
from tbl1
where account is not null And
MName is not null And Sname is not null
group by account,MName, Sname,OS,MaName,
order by account,MName, Sname,OS,MaName,
Acct Mname Sname OS MaName TotalQty
1111 Test STest N xx 5
1111 Test STest Y xx 4
2222 Pop SPop N yy 6
2222 Pop SPop Y yy 8
3333 TTT STTT N zz 3
1111 UUU SUUU N cc 10
1111 UUU SUUU Y cc 6
Ok so for 1111 Test the result would be 1
for 2222 Pop -2, 3333 TTT = 3, 1111 UUU = 4
Does this make any sense?
Thanks
Q
February 10, 2009 at 4:38 pm
Should be something like this:
SELECT ISNULL(T1.account, T2.account) Acct, ISNULL(T1.MName, T2.MName) M_Name,
ISNULL(T1.Sname, T2.Sname) S_Name, T.TotalQty - T2.TotalQty Diff
FROM (
select account, MName, Sname, sum(Qty) as TotalQty
from tbl1
where OS = 'N'
and account is not null And MName is not null And Sname is not null
group by account,MName, Sname) T1
FULL OUTER JOIN (
select account, MName, Sname, sum(Qty) as TotalQty
from tbl1
where OS = 'Y'
and account is not null And MName is not null And Sname is not null
group by account,MName, Sname) T2
ON T1.account = T2.account AND T1.MName = T2.MName AND T1.Sname = T2.Sname
order by Acct, M_Name, S_Name
Sorry cannot test because you did not provide DDL for the table and sctipt to populate the data.
But I hope you get the idea.
_____________
Code for TallyGenerator
February 10, 2009 at 5:15 pm
works great i just added isnull(T1.Total,0) - isnull(T2.Total,0) Diff.
thanks so much for the help.
February 11, 2009 at 7:34 am
If I was going to insert these into a different table how would I go about getting the PrevTotal?
like this
col1 col2 col3 totalqty prevqty
xxxx yyyy zzzz 13 0
xxxx yyyy zzzz 15 13
So i am getting col1, col2, col3, totalqty from the query in the above post and I am inserting them into a new table.
February 11, 2009 at 9:11 am
I think i figured it out using an update after the insert.
February 11, 2009 at 9:47 am
Qnavry,
Would you mind posting your solution? I'm interested in seeing how you captured the PrevTotal using an update after the insert statement.
Thanks,
Lu.
February 11, 2009 at 11:20 am
update p1 set p1.Previous_tot = p2.current_tot from Totals p1
join Totals p2 on p2.mid = p1.mid and p2.account = p1.accountand p2.sname = p1.sname and p2.mname = p1.mname and left(p2.Date,11) = left(getdate()-1,11)
where Left(p1.date,11) = left(GetDate(),11)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply