Need help in T-sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need help in T-sql

Need help in T-sql

Rate Topic

Display Mode

Topic Options

Author

Message

avinashily

Posted Tuesday, September 11, 2012 10:37 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:49 AM
Points: 57, Visits: 185

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
ID Comm_date_A Commission_A Type_A
1 10-Sep-12 50 FIXED
2 10-Sep-12 60 VARIABLE

Table B
ID Comm_date_B Commission_B
1 10-Oct-12 30
1 10-Oct-13 40
1 10-Oct-14 50
2 10-Oct-12 10
2 10-Oct-13 20
2 10-Oct-14 30

Table C
ID Percentage
1 0.1
2 0.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.

ID Comm_date amount commission rate
2 10-Oct-12 100 10 33.333
2 10-Oct-13 66.667 20 33.333
2 10-Oct-14 33.334 30 33.333

ID Comm_date amount commission rate
1 10-Sep-12 100 50 100

please can anyone tell me the T-sql for achieving this..

Post #1357780

subbareddy542

Posted Wednesday, September 12, 2012 12:56 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:18 PM
Points: 832, Visits: 610

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

Post #1357807

avinashily

Posted Wednesday, September 12, 2012 2:12 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:49 AM
Points: 57, Visits: 185

Thanks a ton...

Post #1357832

« Prev Topic | Next Topic »

Permissions