Best Way -CASE,function,subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Best Way -CASE,function,subquery?

Author

Topic

nerdygirl61
Starting Member

21 Posts

Posted - 2011-12-20 : 14:22:21

I am trying to figure out the best way to do this, there is a simplified sample of the data below.
For the records in the MainFile I need to find the corresponding MasterStaff record using the CEID and PayPeriod, and create a record for each FRN/AllocPCT. I am not sure of the best way to do this. There are more fields and tables involved, but those will be easy once I get the bones figured out. I thought about using a WHILE but haven’t done that in SQL, and didn’t know if I should use a count or check to see if the allocPct is 0. I thought about doing a CASE and passing the parameters to a function and adding to the table using a function, I’m assuming I don’t have to return a value(?). Any input would be appreciated.

MainFile

CEID PayPeriod Dollars
1111 201112 25.00
1112 201112 250.00
1113 201112 50.00

MasterStaff

CEID PayPeriod FRN1 AllocPct1 FRN2 AllocPct2 FRN3 AllocPct3
1111 201112 3313 0.75 3312 0.25 NULL 0.00
1112 201112 3313 0.50 4400 0.25 4413 0.25
1113 201112 4413 100.00 NULL 0.00 NULL 0.00

ReportFile (Output)

CEID PayPeriod FRN1 ProRateDollars
1111 201112 3313 18.75
1111 201112 3312 6.25
1112 201112 3313 125.00
1112 201112 4400 62.50
1112 201112 4413 62.50
1113 201112 4413 50.00

toddbkc
Starting Member

9 Posts

Posted - 2011-12-20 : 14:59:31

I would try using a UNION ALL statement, like this:

/* FRN1 populated */
select CEID,PayPeriod,FRN1,AllocPct1
from MainFile
INNER JOIN MasterStaff
...
UNION ALL
select CEID,PayPeriod,FRN2,AllocPct2
from MainFile
INNER JOIN MasterStaff

where masterstaff.FRN2 IS NOT NULL

...
UNION ALL
select CEID,PayPeriod,FRN3,AllocPct3
from MainFile
INNER JOIN MasterStaff

where masterstaff.FRN3 IS NOT NULL

Does this help?

TODD

nerdygirl61
Starting Member

21 Posts

Posted - 2011-12-20 : 17:23:29

Yes it does!

You are awesome, thank you, Karen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources