Convert Mult Cols to Rows & Calc on second table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert Mult Cols to Rows & Calc on second table

Author

Topic

nerdygirl61
Starting Member

21 Posts

Posted - 2011-10-17 : 20:06:18

I want to strip out columns from one table into seperate rows, and do a caculation on them using information stored in a seperate table. I know I can do several queries to sepearate the data, but I am thinking there should be a way to do it in one query. Here is a simplified version of what I am trying to do. Any input would be apreciated, thanks.

I want to see

OutFile
CEID PayPeriod Name FRN Earnings Dollars Benefits
5555 201115 Bob 5591 250.00 150.00 100.00
5555 201115 Bob 5593 125.00 75.00 50.00
5555 201115 Bob 5594 125.00 75.00 50.00
5555 201116 Bob 5591 250.00 150.00 100.00
5555 201116 Bob 5593 250.00 150.00 100.00
5577 201115 Leo 5594 900.00 800.00 100.00
5577 201116 Leo 5594 637.50 600.00 37.50
5577 201116 Leo 5593 212.50 200.00 12.50

I want to left join on tblFP and the calculation on the AllocPCT's and Gross/Dollars/Benefits is as follows - AllocPCT1/100 * GrossEarnings as Earnings, etc.

Below is the table data

tblMs
CEID PayPeriod Name FRN1 AllocPct1 FRN2 AllocPct2 FRN3 AllocPct3
5555 201115 Bob 5591 50 5593 25 5594 25
5555 201116 Bob 5591 50 5593 50
5557 201115 Jane 5593 75 5599 25
5557 201116 Jane 5593 75 5599 25
5577 201115 Leo 5594 100
5577 201116 Leo 5594 75 5593 25

tblFP
CEID PayPeriod GrossEarnings Dollars Benefits
5555 201115 500.00 300.00 200.00
5555 201116 500.00 300.00 200.00
5577 201115 900.00 800.00 100.00
5577 201116 850.00 800.00 50.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:35:33

seeing the output, i think what you need is inner join


SELECT m.CEID, m.PayPeriod, m.Name,
m.FRN,
m.AllocPct/100 * f.GrossEarnings AS Earnings,
m.AllocPct/100 * f.Dollars  AS Dollars,
m.AllocPct/100 * f.Benefits AS Benefits 
FROM (SELECT CEID, PayPeriod, Name, FRN1 AS FRN, AllocPct1 AS AllocPct
      FROM tblMs
      UNION ALL
      SELECT CEID, PayPeriod, Name, FRN2, AllocPct2
      FROM tblMs
      UNION ALL
      SELECT CEID, PayPeriod, Name, FRN3, AllocPct3
      FROM tblMs
     ) m
INNER JOIN tblFP f
ON f.CEID = m.CEID 
AND f.PayPeriod = m.PayPeriod

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nerdygirl61
Starting Member

21 Posts

Posted - 2011-10-18 : 16:44:36

Thank you so much, it works great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:21:43

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources