March 23, 2014 at 8:10 am
Hi Friends i have small doubt in sql server please tell me how to solve issue in sql server
table :emp
Type | Code | Billed Amount | Expected Amount | PaymentAmount | LineitemAdj
A | CPT-A |100.00 | 100.00 | 50.00 | 00.00
B | CPT-A |100.00 | 100.00 | 25.00 | 0.00
P | CPT-A |100.00 | 100.00 | 20.00 | 0.00
A | CPT-B |200.00 | 200.00 | 150.00 | 00.00
B | CPT-B |200.00 | 200.00 | 15.00 | 0.00
P | CPT-B |200.00 | 200.00 | 20.00 | 0.00
based on above table data i need output like below
Type | Code | Billed Amount | Expected Amount | PaymentAmount | LineitemAdj
A | CPT-A |100.00 | 100.00 | 50.00 | 00.00
B | CPT-A |100.00 | 100.00 | 25.00 | 0.00
P | CPT-A |100.00 | 100.00 | 20.00 | 0.00
Adj | CPT-A |100.00 | 100.00 | 0.00 | 5.00
A | CPT-B |200.00 | 200.00 | 150.00 | 00.00
B | CPT-B |200.00 | 200.00 | 15.00 | 0.00
P | CPT-B |200.00 | 200.00 | 20.00 | 0.00
Adj | CPT-B |200.00 | 200.00 | 0.00 | 15.00
please tell me query how to achive this sistuvaion in sql server .
March 23, 2014 at 9:05 am
are your billed amounts and payment amounts/adjustments in separate tables?
can you provide more details on the table structure (DDL create statememts)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2014 at 9:47 am
based on group wise we need display new row(adjust) . all information in one table only.
here billamount-sum(paymentamount) and then get adjust new row records based on code wise.
March 23, 2014 at 10:27 am
would help if you could provide DDL...(do you know how to do this?.....create table statement / insert data statement)
what is the significance of the "TYPE" code...you provide "A","B","P"...does this mean there will only be three rows per "CODE"?
I would still prefer to see billing and payments in separate tables....are you able to modify the design?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2014 at 10:41 am
Is this what you are looking for?
create table dbo.BillTest (
TypeID char(3),
Code char(5),
BilledAmount decimal(12,2),
ExpectedAmount decimal(12,2),
PaymentAmount decimal(12,2),
LineItemAdj decimal(12,2)
);
insert into dbo.BillTest(TypeID,Code,BilledAmount,ExpectedAmount,PaymentAmount,LineItemAdj)
values ('A','CPT-A',100.00,100.00,50.00,0),
('B','CPT-A',100.00,100.00,25.00,0),
('P','CPT-A',100.00,100.00,20.00,0),
('A','CPT-B',200.00,200.00,150.00,0),
('B','CPT-B',200.00,200.00,15.00,0),
('P','CPT-B',200.00,200.00,20.00,0);
with Payments as (
select
Code,
BilledAmount,
ExpectedAmount,
sum(PaymentAmount) TotalPayment
from
dbo.BillTest
group by
Code,
BilledAmount,
ExpectedAmount
), Adjustments as (
select
'Adj' as TypeID,
Code,
BilledAmount,
ExpectedAmount,
0 as PaymentAmount,
BilledAmount - TotalPayment as LineItemAdj
from
Payments
)
insert into dbo.BillTest
select * from Adjustments;
select * from dbo.BillTest
order by
Code,
case when TypeID = 'Adj' then 1 else 0 end,
TypeID;
drop table dbo.BillTest;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply