December 8, 2009 at 11:47 am
create table #x
(phno int,
Description varchar(20),
qty int,
amt money,
type varchar(30))
insert into #x values(123,'abc',1,60,'new')
insert into #x values(123,'abc',-1,-60,'new')
insert into #x values(123,'abc',1,60,'new')
insert into #x values(123,'def',1,60,'discount')
insert into #x values(123433,'abc',-1,-100,'new')
insert into #x values(123,'ghi',1,60,'EMP')
insert into #x values(1223,'acb',1,60,'Existing')
insert into #x values(1223,'acb',-1,-60,'Existing')
insert into #x values(1223,'acb',1,60,'Existing')
insert into #x values(1223,'def',1,60,'discount')
insert into #x values(1223,'ghi',1,60,'EMP')
insert into #x values(1423,'abc',1,60,'new')
insert into #x values(1423,'def',1,60,'DISCOUNT')
insert into #x values(345,'abc',1,130,'NEW')
insert into #x values(345,'abc',-1,-50,'NEW')
insert into #x values(345,'def',-1,-10,'discount')
insert into #x values(3455,'acb',-1,-140,'Existing')
insert into #x values(3455,'def',-1,-40,'DISCOUNT')
insert into #x values(3435,'acb',-1,-30,'Existing')
insert into #x values(3435,'ghi',-1,-30,'EMP')
insert into #x values(345,'acb',1,35,'EXISTING')
insert into #x values(345,'def',1,10,'DISCOUNT')
insert into #x values(345,'ghi',1,5,'EMP')
insert into #x values(567,'jkl',1,89,'add')
insert into #x values(567,'lov',1,89,'add1')
insert into #x values(678,'jkl',-1,-24,'add')
insert into #x values(678,'lov',-1,-24,'add')
select * from #x
alter table #x
add total int
--to get the ones with new phno
select [phno],amt,type into #new from #x
where type = 'NEW'
--to get their respective discount and emp for new phnos
select a.[phno],sum(a.amt) as amt into #new1 from #x a(nolock)
inner join #new b(nolock) ON a.[phno]= B.[phno]
where a.type in ('DISCOUNT','EMP')
group by a.[phno]
---taking the sum from both
update #x
set total = b.toal from #x a
inner join
(
select a.[phno],sum( a.amt + b.amt) as total
from #new a(nolock) left join #new1 b(nolock) ON a.[phno]= B.[phno]
group by a.[phno] )
on a.phno = b.phno
---I need to calculate the totals for phno ....but i have a rule for that
total for New phno = (new+discount+emp)
total for existing phno = (existing+discount+emp)
total for add phno = (add+add1)
i have a scenario here if we have
insert into #x values(345,'abc',1,130,'NEW')
insert into #x values(345,'abc',-1,50,'NEW')
insert into #x values(345,'def',-1,10,'discount')
so it should add both 130-50 = 80
and for this we have discount 10 sp it should be 80 +10 = 90...
i shall put down waht i have tried ...
my problem is if i dont have any discount or emp for a new account i am unable to sum jus the new amt
like
insert into #x values(123433,'abc',1,-100,'new')
so i am unable get -100 in final result it says null as it doesnt have any disc or emp
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 8, 2009 at 12:20 pm
Please post a question somewhere that explains what you are asking people.
Also, you need to try and solve this. From the code I'm not sure if you have or if you are repeating something that someone asked you. It is hard to read when you don't have sentences with capitals or periods, and you haven't formatted the code. You can highlight the code and click one of the links to the left (when posting) that says code="sql" and it will help format it.
December 8, 2009 at 1:28 pm
I did Steve so hopefully you can understand it now so tell me what am i doing is correct ?? or am i in the wrong way ..??
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 8, 2009 at 1:56 pm
Don't write inside the code. Write the specific question at the end, or beginning. It's easier to read then I'm not sure exactly what you want.
Do you need an aggregation by phone number? Or for new separate from existing? The way you wrote this you're rambling along, and I think that may be part of your problem.
It seems like you need to sum a product, the qty * amt. Is that not correct? Why does the "new" or "existing" come into it if you are summing them both the same?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply