SQL /Procedure - for Transpose and Calculation

Question

Post reply

SQL /Procedure - for Transpose and Calculation

sudhirnune

SSCrazy

Points: 2342
More actions
April 8, 2013 at 8:10 pm

#292124

Hi Team,
I have a Complex requirement.
Source:
--------
NAME PAYMENT_TYPE PAYMENT
SUDHIR SAL 30.3
SUDHIR ADV 10.3
SUDHIR ALL_1 10
SUDHIR ALL_2 10
SUDHIR ALL_3 10
MADHAV SAL 34
MADHAV ALL_1 24
MADHAV BONUS 10
Formula:
--------
TGT_PAYMENT_TYPE PAYMENT_FORMULA
SAL SAL-ADV
COMM ALL_1+ALL_2-ALL_3
BONUS SAL * 1.1
Target Table:
------------
NAME SAL BONUS COMM
SUDHIR 20 0 10
MADHAV 34 10 24
Please need help to code SQL Query / Package which will take the Formula on from the table Formula and apply on Source before pushing it to target.
Thanks & Regards,
Sudhir Nune.

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Mansfield SSCrazy Points: 2396 More actions · Answer 1

Use UNPIVOT to turn each PAYMENT_TYPE into its own column. See the following link.

Then your calculation should be easy 🙂

sudhirnune SSCrazy Points: 2342 More actions · Answer 2

Thanks for the Reply, If you observe my Example, the Payment Type is not Static for all the user and payment Type has nearly 5000 types 🙁

Writing Pivot is a Challenge with out clear understanding.

Also by Writing Pivot I am restricting the Payment types.

When Ever a new payment come's in I need to Append it to the query.

Please check if any such scenarios occured for any of you ???

Mansfield SSCrazy Points: 2396 More actions · Answer 3

There is no need to hard-code each payment type in a pivot query. Dynamic SQL can take care of that.

sudhirnune SSCrazy Points: 2342 More actions · Answer 4

Thanks for the above input.

Now I am stuck to write a Dynamic SQL which will inter update the Data in the cursor, as per teh data format needed.

1. Formula is fetched into a Cursor form the Formula table.

2. Decla one more cursore which will fetch the data from the SQL Prepared to fetch the pivot data.

2. For loop is used to use the Formula data.

3. Need to write a SLQ Query whcih will update the target Columns SAL, BON, COM based on the formula.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Is there one payment type which is common to all employees? For instance, 'SAL'? This could be very easy ...

^{“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

sudhirnune SSCrazy Points: 2342 More actions · Answer 6

the broblem is that I have multiple of such in my Scenarion

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

I understand that you have multiple payment types, and an employee may have several rows with different payment types.

Do ALL employees have at least one row with payment type of 'SAL'?

^{“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

sudhirnune SSCrazy Points: 2342 More actions · Answer 8

sudhirnune

SSCrazy

Points: 2342

April 9, 2013 at 3:25 am

#1604732

Yes they has SAL.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

^{“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

vinu512 SSCoach Points: 15729 More actions · Answer 10

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[/url] 😉

sudhirnune SSCrazy Points: 2342 More actions · Answer 11

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 SSC Guru Points: 186127 More actions · Answer 12

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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

sudhirnune SSCrazy Points: 2342 More actions · Answer 14

Data in the Test Formula is

TGT_PAYMENT_TYPEPAYMENT_FORMULA

SAL SAL-ADV

COMM ALL_1+ALL_2-ALL_3

BONUS SAL * 1.1