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 Tuesday, April 9, 2013 7:18 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
Because formula are different for Different Scenario

I need to make it dinamic so that based on the Scenario Formula should be Take and Calculated.

SCENARIO - TGT_PAYMENT_TYPE - PAYMENT_FORMULA
NORMAL - SAL - SAL-ADV
NORMAL - COMM - ALL_1+ALL_2-ALL_3
NORMAL - BONUS - SAL * 1.1
MONTH - SAL - SAL
MONTH - COMM - ALL_1+ALL_2
YEARLY - BONUS - SAL+ALL_1+ALL_2
Post #1440305
Posted Tuesday, April 9, 2013 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 7,231, Visits: 13,711
sudhirnune (4/9/2013)
Because formula are different for Different Scenario

I need to make it dinamic so that based on the Scenario Formula should be Take and Calculated.

SCENARIO - TGT_PAYMENT_TYPE - PAYMENT_FORMULA
NORMAL - SAL - SAL-ADV
NORMAL - COMM - ALL_1+ALL_2-ALL_3
NORMAL - BONUS - SAL * 1.1
MONTH - SAL - SAL
MONTH - COMM - ALL_1+ALL_2
YEARLY - BONUS - SAL+ALL_1+ALL_2


How many scenarios will you have? I see 3: normal, month and yearly.


“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 #1440306
Posted Tuesday, April 9, 2013 7: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
yes and might increase at any point of time.
Post #1440309
Posted Tuesday, April 9, 2013 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 7,231, Visits: 13,711
sudhirnune (4/9/2013)
yes and might increase at any point of time.


So to summarise, you want to take three or four very simple queries and convert them into a series of statements which are so complicated and convoluted that you need help to write them - at the expense of performance, maintainability and any other criteria which you may choose to distinguish a good query from a less well written one. Are you really sure you want to do this?


“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 #1440331
Posted Tuesday, April 9, 2013 7:59 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 I understand that, but need of time I need to complete it as per the Request.
Post #1440339
Posted Tuesday, April 9, 2013 8:13 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
I will try to Explain you the Scenario and need Suggestion:

1. I have te table which is Similar to TEST_SRC for which will tipically has 4000 to 5000 Payment types, along with the County.

2. I need has a TEST_FORMULA which will be having Country.

3. We need to generate the data which will have the Payment Types as columns.

4. We need to now Update Couple of Column based on the Formula defined in the TEST_FORMULA.

Please need your help to guidance, which will be help to understand the process / Procedure whcih will help to achive the Above Steps
Post #1440352
Posted Tuesday, April 9, 2013 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 7,231, Visits: 13,711
sudhirnune (4/9/2013)
I will try to Explain you the Scenario and need Suggestion:

1. I have te table which is Similar to TEST_SRC for which will tipically has 4000 to 5000 Payment types, along with the County.

2. I need has a TEST_FORMULA which will be having Country.

3. We need to generate the data which will have the Payment Types as columns.

4. We need to now Update Couple of Column based on the Formula defined in the TEST_FORMULA.

Please need your help to guidance, which will be help to understand the process / Procedure whcih will help to achive the Above Steps


Here's what I recommend you do. While you only have one calculation one test case with 3 calculations, write one query to deal with it - similar to or the same as the one I've written for you above. Continue to write queries for new cases until you have five or six test cases with different sets of columns and different calculations, at which point you will also have a representative sample set of data and queries to post here.
This approach will provide you with a far more clear picture of the whole process, and will also provide us with sample data and calculations to build a robust solution for you.


“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 #1440403
Posted Tuesday, April 9, 2013 1:42 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 all for helping on this: below is procedure which does the Trick

Create procedure USP_TEST
@NAME NVARCHAR(20)
AS
BEGIN
IF object_id(N'tempdb..##TEMP_TEST') is not null
DROP table ##TEMP_TEST;
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX),
@V_NAME AS NVARCHAR(20);

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 0 SAL_CACL,0 BONUS_CALC,0 COMM_CALC,name,' + @cols2 + ' into ##TEMP_TEST from
(
select name
, payment
, payment_type
from TEST_DATA_SRC
where name = '''+ @NAME + '''
) x
pivot
(
max(payment)
for payment_type in (' + @cols + ')
) p '


exec sys.sp_executesql @query--,N'@CUR_DATA cursor output',@CUR_DATA output

DECLARE CUR_TEST_FORMULA CURSOR
--Get the Update Query created for executing the same on the Fact Data
FOR
SELECT 'Update ##TEMP_TEST set '+ TGT_PAYMENT_TYPE + ' = ' + PAYMENT_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

print @Q_SQL;
EXECUTE (@Q_SQL);

FETCH NEXT FROM CUR_TEST_FORMULA INTO @Q_SQL
END
CLOSE CUR_TEST_FORMULA
DEALLOCATE CUR_TEST_FORMULA
Post #1440539
Posted Tuesday, April 9, 2013 7:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
I think you need to stop converting the source data to an excel-like pivot table and do your calculations inline.
It will make things much simpler.

Unfortunately, you did not provide a test sample, so fo my demonstration I used the one made up by Chris.
I changed a small part there - added a PayDate column: I belive you have something like this in your table.

CREATE TABLE #Sampledata (
PayDate smalldatetime,
NAME VARCHAR(25),
PAYMENT_TYPE VARCHAR(10),
PAYMENT DECIMAL(5,2)
)

You may embed all your formula variations into a single view:

SELECT 
DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0) PayMonth, s.NAME,
SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ADV' THEN PAYMENT ELSE 0 END) [NORMAL-SAL],
SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) - SUM(CASE WHEN PAYMENT_TYPE = 'ALL_3' THEN PAYMENT ELSE 0 END) [NORMAL-COMM],
SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) *1.1 [NORMAL-BONUS],
SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) [MONTH-SAL],
SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [MONTH-COMM],
SUM(CASE WHEN PAYMENT_TYPE = 'SAL' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_1' THEN PAYMENT ELSE 0 END) + SUM(CASE WHEN PAYMENT_TYPE = 'ALL_2' THEN PAYMENT ELSE 0 END) [YEARLY-BONUS]
FROM #Sampledata s
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, PayDate) , 0), s.NAME

Than you may just select appropriate columns from the view for appropriate cases.

You may chose using dynamic SQL for selecting only required columns from this view, but I'd suggest to move this part to UI/Report module.
That's what they are for.
Post #1440627
Posted Wednesday, April 10, 2013 1:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 7,231, Visits: 13,711
sudhirnune (4/9/2013)
Thanks all for helping on this: below is procedure which does the Trick

Create procedure USP_TEST
@NAME NVARCHAR(20)
AS
BEGIN
IF object_id(N'tempdb..##TEMP_TEST') is not null
DROP table ##TEMP_TEST;
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX),
@V_NAME AS NVARCHAR(20);

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 0 SAL_CACL,0 BONUS_CALC,0 COMM_CALC,name,' + @cols2 + ' into ##TEMP_TEST from
(
select name
, payment
, payment_type
from TEST_DATA_SRC
where name = '''+ @NAME + '''
) x
pivot
(
max(payment)
for payment_type in (' + @cols + ')
) p '


exec sys.sp_executesql @query--,N'@CUR_DATA cursor output',@CUR_DATA output

DECLARE CUR_TEST_FORMULA CURSOR
--Get the Update Query created for executing the same on the Fact Data
FOR
SELECT 'Update ##TEMP_TEST set '+ TGT_PAYMENT_TYPE + ' = ' + PAYMENT_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

print @Q_SQL;
EXECUTE (@Q_SQL);

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


What will be consuming the result sets from this stored procedure?


“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 #1440672
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse