Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL /Procedure - for Transpose and Calculation Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 8:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
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.
Post #1440109
Posted Monday, April 8, 2013 8:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
Use UNPIVOT to turn each PAYMENT_TYPE into its own column. See the following link.

Then your calculation should be easy :)
Post #1440111
Posted Monday, April 8, 2013 8:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
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 ???
Post #1440113
Posted Monday, April 8, 2013 8:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
There is no need to hard-code each payment type in a pivot query. Dynamic SQL can take care of that.

Post #1440120
Posted Tuesday, April 9, 2013 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
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.
Post #1440185
Posted Tuesday, April 9, 2013 1:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1440195
Posted Tuesday, April 9, 2013 1:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
the broblem is that I have multiple of such in my Scenarion
Post #1440199
Posted Tuesday, April 9, 2013 1:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1440202
Posted Tuesday, April 9, 2013 3:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
Yes they has SAL.
Post #1440234
Posted Tuesday, April 9, 2013 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1440243
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse