| | SQL query logic
Posted By: Posted Date: September 29, 2010
Points: 0 Category :Sql Server | |
|
|
Hi,
I had suggestions from forums but couldnt give the requirments clearly. classid has ranks with A being minimum and Z being maximum. For a groupid, I have multiple classids. Now I need to update insuranceflag for the MAXIMUM classid for a particular groupid.
For a groupid with maximum classid, I need to grab the debitamount (d1) and keep on multiplying with creditamounts till I hit another
NOT NULL debitamount (d2). Then I need to compare d1 with d2 and update the flag as increase or decrease.
The insurance flag is U (up), D (down) or S (same)
Please have a look at the below sample data and needed result set:
declare @insurance table
(
groupid int,
classid varchar(20),
creditamt varchar(20),
debitamt varchar(20),
insuraceflag varchar(30)
)
insert into @insurance
select 100, 'A', null, 1.234, '
union all
select 100, 'B', 1.005, null, '
union all
select 100, 'C', 1.25, null, '
union all
select 100, 'D', null, 1.5, '
union all
select 101, 'A', null, 1.2, '
union all
select 101, 'B', null, 1.57, '
union all
select 102, 'A', null, 2.4, '
View Complete Post
|
| More Related Resource Links |
| | | |
|