11 Replies - 456 Views - Last Post: 18 May 2017 - 12:11 PM

#1 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 07:55 AM

Hi,
I have this SQL statement which uses a UNION statement with a number of valid values of a field called Frequency. I've hard coded in the valid values and it works fine. However if a new Frequency were to be introduced I'd have to re-visit the SQL statement.

I've attached a screen shot of the whole SQL, I hope it is self explanatory what I'm looking for.

Thanks,

Jim.
DECLARE @CompanyID int;
SET @CompanyID=1344;
SELECT        co.CompanyName,
			  pc.PCDescr,
			  ec.PCompID,
			  ed.EeRef, ed.Surname + ' ' + ed.Forename as EmployeeName, case when ec.ECalcRate = uec.Rate and ec.ECalcUnits = uec.Units then 'True' else 'False' end as Matched,
              ec.ECalcRate as StarRate, 
			  CASE WHEN uec.Rate IS NULL THEN 0 ELSE uec.Rate END as UnityRate, ec.ECalcUnits as StarUnits, 
			  CASE WHEN uec.Units IS NULL THEN 0 ELSE uec.Units END as UnityUnits, ec.ECalcDue as StarDue, 
			  CASE WHEN uec.Rate IS NULL OR uec.Units IS NULL THEN 0 WHEN pc.PCUnitBased = 'True' THEN uec.Rate * uec.Units ELSE uec.Rate END AS UnityDue,
			  pr.RunDate
			 
FROM            EeComponents as ec
INNER JOIN EeDetails as ed on ed.EeID=ec.EeID
INNER JOIN PayComponents as pc on pc.PCompID=ec.PCompID
INNER JOIN PayrollRuns as pr on pr.RunID=ec.RunID
LEFT JOIN Branchs as br on ed.BranchId=br.BranchID
LEFT JOIN Unity.dbo.Companies AS co ON @CompanyID = co.CompanyID
FULL OUTER JOIN UnityEeComponents as uec on uec.EeRef=ed.EeRef and uec.PCompID=ec.PCompID
WHERE ec.RunID=(SELECT MAX(RunID) from PayrollRuns 
                                  where Frequency=12) ******** I don't want this hard coded Frequency=12 here **********
      AND (ec.ECalcRate=uec.Rate AND ec.ECalcUnits=uec.Units AND ec.ECalcDue<>0
	       AND (CASE WHEN uec.Rate IS NULL THEN 0 ELSE uec.Rate END <>0
                OR CASE WHEN uec.Units IS NULL THEN 0 ELSE uec.Units END <>0)
	       OR (ec.ECalcRate<>CASE WHEN uec.Rate IS NULL THEN 0 ELSE uec.Rate END 
		       OR ec.ECalcUnits<>CASE WHEN uec.Units IS NULL THEN 0 ELSE uec.Units END))

UNION

SELECT        co.CompanyName,
			  pc.PCDescr,
			  ec.PCompID,
			  ed.EeRef, ed.Surname + ' ' + ed.Forename as EmployeeName, case when ec.ECalcRate = uec.Rate and ec.ECalcUnits = uec.Units then 'True' else 'False' end as Matched,
              ec.ECalcRate as StarRate, 
			  CASE WHEN uec.Rate IS NULL THEN 0 ELSE uec.Rate END as UnityRate, ec.ECalcUnits as StarUnits, 
			  CASE WHEN uec.Units IS NULL THEN 0 ELSE uec.Units END as UnityUnits, ec.ECalcDue as StarDue, 
			  CASE WHEN uec.Rate IS NULL OR uec.Units IS NULL THEN 0 WHEN pc.PCUnitBased = 'True' THEN uec.Rate * uec.Units ELSE uec.Rate END AS UnityDue,
			  pr.RunDate

FROM            EeComponents as ec
INNER JOIN EeDetails as ed on ed.EeID=ec.EeID
INNER JOIN PayComponents as pc on pc.PCompID=ec.PCompID
INNER JOIN PayrollRuns as pr on pr.RunID=ec.RunID
LEFT JOIN Branchs as br on ed.BranchId=br.BranchID
LEFT JOIN Unity.dbo.Companies AS co ON @CompanyID = co.CompanyID
FULL OUTER JOIN UnityEeComponents as uec on uec.EeRef=ed.EeRef and uec.PCompID=ec.PCompID
WHERE ec.RunID=(SELECT MAX(RunID) from PayrollRuns 
                                  where Frequency=52)   ******** I don't want this hard coded Frequency=52 here **********
      AND (ec.ECalcRate=uec.Rate AND ec.ECalcUnits=uec.Units AND ec.ECalcDue<>0
	       AND (CASE WHEN uec.Rate IS NULL THEN 0 ELSE uec.Rate END <>0
                OR CASE WHEN uec.Units IS NULL THEN 0 ELSE uec.Units END <>0)
	       OR (ec.ECalcRate<>CASE WHEN uec.Rate IS NULL THEN 0 ELSE uec.Rate END 
		       OR ec.ECalcUnits<>CASE WHEN uec.Units IS NULL THEN 0 ELSE uec.Units END))

ORDER BY pc.PCDescr,ec.PCompID,EmployeeName


This post has been edited by jcborland: 18 May 2017 - 08:41 AM


Is This A Good Question/Topic? 0
  • +

Replies To: A more elegant and dynamic use on a UNION in SQL

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13385
  • View blog
  • Posts: 53,420
  • Joined: 12-June 08

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:16 AM

Missing the code?
Was This Post Helpful? 0
  • +
  • -

#3 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:29 AM

Yes I'm getting an error on upload. It keeps just saying "The Server returned an error during upload".
I've tried a .png, a .jpg and a plain .txt.

This post has been edited by jcborland: 18 May 2017 - 08:30 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13385
  • View blog
  • Posts: 53,420
  • Joined: 12-June 08

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:31 AM

What's wrong with copy/pasting the code here and using the code tags around it?

.. or, if images is a must, using some external image hosting site like imgur.
Was This Post Helpful? 0
  • +
  • -

#5 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:37 AM

I've copied and pasted it in now. I just had few notes scribbled over it to try and help explain what I'm after but hopefully it's ok as is.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13385
  • View blog
  • Posts: 53,420
  • Joined: 12-June 08

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:39 AM

Okay.. so explain what's the issue? What I am understanding from your modified first post is that you have hard coded values and if you use a different input those hard coded values are no longer valid?
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13385
  • View blog
  • Posts: 53,420
  • Joined: 12-June 08

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:49 AM

FYI - it would be better if you were not perpetually editing your first post. I don't know what changed, if anything did, and it doesn't trigger a notification that you had an update. Just post your actual replies to the thread.
Was This Post Helpful? 0
  • +
  • -

#8 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:51 AM

Well the valid values will be fairly static. Currently valid values would include 12,52,13,26 & 1.

So I could just write 5 copies of the same thing with a UNION statement in between each. There are two things wrong with that:
1 - I have 5 copies of essentially the same logic. So 5 chances to make a mistake and 5 pieces to amend and add to if things change.

2 - If a new valid Frequency comes along I'll have to add to the SQL statement and I might not be aware that a new Frequency has been created.

I can get a list of Frequencies in use by using "SELECT Frequency FROM PayrollRuns GROUP BY Frequency" but I just don't know how I would incorporate it into the code.
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13385
  • View blog
  • Posts: 53,420
  • Joined: 12-June 08

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 08:53 AM

Where do these 'valid values' originate or how are they manufactured/updated?
Was This Post Helpful? 0
  • +
  • -

#10 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 09:02 AM

The data is all coming from a payroll package. The users of the payroll can and do create new employees as and when they need to. The Frequency is the frequency they are paid 12 = monthly, 52 = weekly etc. The software is being used by a Payroll Bureau and they have as of now about 400 payrolls each with their own database and each requiring different frequencies.
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13385
  • View blog
  • Posts: 53,420
  • Joined: 12-June 08

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 09:16 AM

The easiest route would be to have a table matching up constants to user IDs.. and inside the procedure set a few variables with that information.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6334
  • View blog
  • Posts: 25,541
  • Joined: 12-December 12

Re: A more elegant and dynamic use on a UNION in SQL

Posted 18 May 2017 - 12:11 PM

I don't fully understand the issue but it sounds like you could be looking at either

.. WHERE field_name IN(SELECT frequency FROM frequency_table)

or

.. WHERE field_name IN(1,12,52,13,26)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1