September 11, 2012 at 10:37 pm
hi,
i am having data in 3 tables need to join the data and get the result set, when getting there is a column which should be calculated from other column dynamically.
example
Table A
IDComm_date_ACommission_AType_A
110-Sep-12 50 FIXED
210-Sep-12 60 VARIABLE
Table B
IDComm_date_BCommission_B
110-Oct-12 30
110-Oct-13 40
110-Oct-14 50
210-Oct-12 10
210-Oct-13 20
210-Oct-14 30
Table C
IDPercentage
10.1
20.333
and the result data set should be like this
if type_a is fixed then the comm_date and commission should be taken from table a or else it should take the values from table B.
column rate is calculated from table C percentage*100
column amount should be like first line should be 100 then after it should get subtracted from the rate and get displayed.
IDComm_date amount commission rate
210-Oct-12 100 10 33.333
210-Oct-13 66.667 20 33.333
210-Oct-14 33.334 30 33.333
IDComm_date amount commission rate
110-Sep-12 100 50 100
please can anyone tell me the T-sql for achieving this..
September 12, 2012 at 12:56 am
PLS TRY BELOW CODE...
DECLARE @TA TABLE(ID INT,Comm_date_A VARCHAR(20), Commission_A INT,Type_A VARCHAR(20))
INSERT INTO @TA
SELECT 1,'10-Sep-12',50,'FIXED'
union all
select 2,'10-Sep-12',60,'VARIABLE'
DECLARE @TB TABLE(ID INT,Comm_date_B VARCHAR(20),Commission_B INT)
INSERT INTO @TB
SELECT 1,'10-Oct-12',30 UNION ALL SELECT 1,'10-Oct-13',40 UNION ALL SELECT 1,'10-Oct-14',50 UNION ALL
SELECT 2,'10-Oct-12',10 UNION ALL SELECT 2,'10-Oct-13',20 UNION ALL SELECT 2,'10-Oct-14',30
DECLARE @TC TABLE(ID INT,Percentage DECIMAL(10,5))
INSERT INTO @TC
SELECT 1,0.1 UNION ALL SELECT 2,0.333;
WITH A
AS(SELECT DISTINCT T1.ID,CASE WHEN T1.Type_A='FIXED' then Comm_date_A else T2.Comm_date_B end Comm_date,CASE WHEN T1.Type_A='FIXED' then Commission_A else T2.Commission_B end Commission,T3.Percentage*100 rate,DENSE_RANK() OVER(PARTITION BY T1.ID ORDER BY CASE WHEN T1.Type_A='FIXED' then Comm_date_A else T2.Comm_date_B end) RANK
FROM @TA T1
LEFT JOIN @TB T2
ON T1.ID=T2.ID
LEFT JOIN @TC T3
ON T1.ID=T3.ID)
SELECT ID,Comm_date,CASE WHEN K.RANK=1 THEN 100 ELSE 100-(SELECT SUM(A1.rate) FROM A A1 WHERE K.ID=A1.ID AND K.RANK>A1.RANK) END amount ,Commission,rate FROM A K
September 12, 2012 at 2:12 am
Thanks a ton...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply