Problem in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in Query

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-08-05 : 00:44:08

Hello Experts,

Currently in my table data is coming in this way. A single SO# have multiple INV NO and INV Amt.


SO #	ORC Amt	InvNo	InvAmt	NetAmt
25	200	11	50	50-200
25	200	22	60	0
25	200	33	70	0
25	200	44	80	0
27	70	90	60	60-70
28	30	77	40	40-30
28	30	88	50	0
		99	100	100-0
		101	200	200-0
		102	300	200-0

Now see the NetAMt column, i want to calculate in this manner.

A single SO# have ORC Amount unique.If a single so contains multiple
invoices then on firs row the calc should be InvAmt-ORC Amt, and others invoices of that SO# should be get zero.

If any invoice does not have so#, then it remains as INvAmt.

Is there any query to do this stuff.

Please help me.

regards,
AKM

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 01:07:03

"A single SO# have ORC Amount unique."

What happens if a SO# does not have ORC amount unique?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-08-05 : 01:17:39

Thanx for the reply.

SO# can have ORC amt or may not be.
If so# contain ORC Amt then a single so have same orc amt for multiple inoices,please see my sample data.

If SO# doesnt have ORC Amt then the net amt should be inv amt only for first row as per the sample data.

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 01:24:09

So you are saying SO# if has ORC Amt it always will be unique for that particular SO# ?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-08-05 : 01:45:23

Yes Idera...correct

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 02:16:32

[code]
declare @tbl as table(SO# int,ORCAmt int,InvNo int,InvAmt int)
insert into @tbl
select 25, 200, 11, 50 union all
select 25, 200, 22, 60 union all
select 25, 200, 33, 70 union all
select 25, 200, 44, 80 union all
select 27, 70, 90, 60 union all
select 28, 30, 77, 40 union all
select 28, 30, 88, 50 union all
select NUll, 30, 88, 50

select *,case when rid=1 and SO# Is not null then InvAmt-OrcAmt
when rid=1 and SO# Is null then InvAmt
else 0
end as NetAmt from
(
select *,ROW_NUMBER()over(partition by SO# order by SO#) as rid from @tbl
)t
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-08-05 : 05:23:50

Thanks idera..i will check it now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources