Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL /Procedure - for Transpose and Calculation Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 09, 2013 4:31 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 8:36 PM Points: 1,117, Visits: 1,560
 ChrisM@Work (4/9/2013)sudhirnune (4/9/2013)Yes they has SAL.Thanks.`DROP TABLE #SampledataCREATE TABLE #Sampledata (NAME VARCHAR(25), PAYMENT_TYPE VARCHAR(10), PAYMENT DECIMAL(5,2))INSERT INTO #Sampledata (NAME, PAYMENT_TYPE, PAYMENT)SELECT 'SUDHIR', 'SAL', 30.3 UNION ALLSELECT 'SUDHIR', 'ADV', 10.3 UNION ALLSELECT 'SUDHIR', 'ALL_1', 10 UNION ALLSELECT 'SUDHIR', 'ALL_2', 10 UNION ALLSELECT 'SUDHIR', 'ALL_3', 10 UNION ALLSELECT 'MADHAV', 'SAL', 34 UNION ALLSELECT 'MADHAV', 'ALL_1', 24 UNION ALLSELECT 'MADHAV', 'BONUS', 10SELECT s.NAME, [SAL] = s.Payment-x.ADV, [BONUS1] = (s.Payment-x.ADV) * 1.1, -- BONUS from formula [BONUS2] = x.BONUS, -- BONUS from Target Table [COMM] = x.[COMM+] - x.[COMM-]FROM #Sampledata sOUTER APPLY ( SELECT [ADV] = SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END), [COMM+] = SUM(CASE WHEN PAYMENT_TYPE IN ('ALL_1','ALL_2') THEN PAYMENT ELSE 0 END), [COMM-] = SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END), [BONUS] = SUM(CASE WHEN PAYMENT_TYPE = 'BONUS' THEN PAYMENT ELSE 0 END) FROM #Sampledata si WHERE si.NAME = s.NAME AND si.PAYMENT_TYPE <> 'SAL') xWHERE s.PAYMENT_TYPE = 'SAL'ORDER BY s.NAME DESC`Nicely Done Chris. Vinu VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1440252
 Posted Tuesday, April 09, 2013 4:46 AM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 6:40 AM Points: 86, Visits: 210
 CODE 1:DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.PAYMENT_TYPE) FROM TEST_DATA_SRC c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')SET @Cols2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+PAYMENT_TYPE+'],0) AS ['+PAYMENT_TYPE+']' FROM TEST_DATA_SRC GROUP BY PAYMENT_TYPE FOR XML PATH('')),2,8000)set @query = 'SELECT name, ' + @cols2 + ' from ( select name , payment , payment_type from TEST_DATA_SRC ) x pivot ( max(payment) for payment_type in (' + @cols + ') ) p 'execute(@query)Which helps me to pivot the Data and provide the needed outpout.Below is teh Part of Stored proc whcih will Update the Data based on the Formula from the TEST_FORMULA:Procedure:----------DECLARE CUR_TEST_FORMULA CURSOR--Get the Update Query created for executing the same on the Fact DataFOR SELECT 'Update TEST_TGT set '+ COA_CODE + ' = ' + COA_FORMULA +';' FROM TEST_FORMULAOPEN CUR_TEST_FORMULADECLARE @Q_SQL NVARCHAR(MAX)FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLWhile @@FETCH_STATUS = 0BEGINEXECUTE (@Q_SQL);FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQLENDCLOSE CUR_TEST_FORMULADEALLOCATE CUR_TEST_FORMULANeed help to Update the procedure which will Update the Data in the Cursor and insert the detaisl to the TEST_TGT
Post #1440260
 Posted Tuesday, April 09, 2013 5:22 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 6,286, Visits: 12,104
 How many rows does the table TEST_FORMULA contain?Can you post a few rows please? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1440270
 Posted Tuesday, April 09, 2013 5:24 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 6,286, Visits: 12,104
 vinu512 (4/9/2013)...Nicely Done Chris.Thank you, Vinu. Insufficient information yet to determine whether or not it will be useful... “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1440271
 Posted Tuesday, April 09, 2013 5:27 AM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 6:40 AM Points: 86, Visits: 210
 Data in the Test Formula isTGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1
Post #1440273
 Posted Tuesday, April 09, 2013 5:42 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 6,286, Visits: 12,104
 sudhirnune (4/9/2013)Data in the Test Formula isTGT_PAYMENT_TYPE PAYMENT_FORMULASAL SAL-ADVCOMM ALL_1+ALL_2-ALL_3BONUS SAL * 1.1There's only one formula in the table? Are you expecting more? If so, can you show how they might look? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1440277
 Posted Tuesday, April 09, 2013 6:34 AM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 6:40 AM Points: 86, Visits: 210
 there are 3 formula....!SAL = SAL-ADVCOMM = ALL_1+ALL_2-ALL_3BONUS = SAL * 1.1
Post #1440289
 Posted Tuesday, April 09, 2013 6:36 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 6,286, Visits: 12,104
 sudhirnune (4/9/2013)there are 3 formula....!SAL = SAL-ADVCOMM = ALL_1+ALL_2-ALL_3BONUS = SAL * 1.1Did you test the query I posted above somewhere? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1440290
 Posted Tuesday, April 09, 2013 6:41 AM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 6:40 AM Points: 86, Visits: 210
 Chris. I agree with your Query. My problem is that i need to make it Dynamic.I can not use the Static way of implimenting this.Can you please help me with the Stored proc which can update the data in the Cursor baed on my Formula.I am looking forward for options to update my Procedure whcih i pasted in one of the posts.
Post #1440291
 Posted Tuesday, April 09, 2013 6:44 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 6,286, Visits: 12,104