Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL /Procedure - for Transpose and Calculation


SQL /Procedure - for Transpose and Calculation

Author
Message
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
ChrisM@Work (4/9/2013)
sudhirnune (4/9/2013)
Yes they has SAL.


Thanks.

DROP TABLE #Sampledata
CREATE 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 ALL
SELECT 'SUDHIR', 'ADV', 10.3 UNION ALL
SELECT 'SUDHIR', 'ALL_1', 10 UNION ALL
SELECT 'SUDHIR', 'ALL_2', 10 UNION ALL
SELECT 'SUDHIR', 'ALL_3', 10 UNION ALL
SELECT 'MADHAV', 'SAL', 34 UNION ALL
SELECT 'MADHAV', 'ALL_1', 24 UNION ALL
SELECT 'MADHAV', 'BONUS', 10

SELECT
   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 s
OUTER 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'
) x
WHERE s.PAYMENT_TYPE = 'SAL'
ORDER BY s.NAME DESC



Nicely Done Chris.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
sudhirnune
sudhirnune
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 252
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 Data
FOR
SELECT 'Update TEST_TGT set '+ COA_CODE + ' = ' + COA_FORMULA +';'
FROM TEST_FORMULA

OPEN CUR_TEST_FORMULA
DECLARE @Q_SQL NVARCHAR(MAX)

FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQL
While @@FETCH_STATUS = 0
BEGIN

EXECUTE (@Q_SQL);

FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQL
END
CLOSE CUR_TEST_FORMULA
DEALLOCATE CUR_TEST_FORMULA

Need help to Update the procedure which will Update the Data in the Cursor and insert the detaisl to the TEST_TGT
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8949 Visits: 19009
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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8949 Visits: 19009
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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
sudhirnune
sudhirnune
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 252
Data in the Test Formula is

TGT_PAYMENT_TYPE   PAYMENT_FORMULA
SAL    SAL-ADV
COMM    ALL_1+ALL_2-ALL_3
BONUS    SAL * 1.1
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8949 Visits: 19009
sudhirnune (4/9/2013)
Data in the Test Formula is

TGT_PAYMENT_TYPE   PAYMENT_FORMULA
SAL    SAL-ADV
COMM    ALL_1+ALL_2-ALL_3
BONUS    SAL * 1.1


There'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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
sudhirnune
sudhirnune
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 252
there are 3 formula....!

SAL = SAL-ADV
COMM    = ALL_1+ALL_2-ALL_3
BONUS    = SAL * 1.1
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8949 Visits: 19009
sudhirnune (4/9/2013)
there are 3 formula....!

SAL = SAL-ADV
COMM    = ALL_1+ALL_2-ALL_3
BONUS    = SAL * 1.1


Did 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 Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
sudhirnune
sudhirnune
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 252
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8949 Visits: 19009
sudhirnune (4/9/2013)
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.


That's straightforward enough. But first, can you explain a little why the query must be dynamic?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For 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 Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search