sql server union all
I am pulling historical data from a Position history table linking to payroll data to get gross wages for previous years. In some cases the employee will not be in the history table so I will want to use the current info. I wanted to use union all but I am not sure how to do it in this case. This is the query for my historical data. My problem is with the max date since the current date will always be the max date. PosHst.txt
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
it is indeed a bit unclear so far... but a UNION ALL or a LEFT JOIN technique can solve the problem...
ASKER
Since I am using the max function how would that work. The having clause really would only apply to the query of the historical data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I will try that tomorrow.
ASKER
I think I misinterpreted your instructions. I tried:
SELECT
Max (b.PHST_CHG_DATE) as CHG_DATE
, max(b.PHST_TITLE) as TITLE
, b.PHST_EMP_ID as EMP_ID
, b.PHST_APPOINT_ID as APPOINT_ID
, Max(b.PHST_POS_NO) as PHST_POS_NO
, max(b.PHST_OLD_TITLE) as PHST_OLD_TITLE
, Max(b.PHST_OLD_POS_NO) as PHST_OLD_POS_NO
, z.GrossWages
FROM ESYEMPLR a
INNER JOIN ESHPOSHD b ON a.EMP_ID = b.PHST_EMP_ID
LEFT JOIN (
SELECT APPOINT_ID,
SUM(Amount) GrossWages
FROM ESYBAMHD z
WHERE BENDED_CODE = 'gross'
AND PP_END_Date >= '01-01-2014'
AND PP_END_Date <= '12-31-2014'
GROUP BY APPOINT_ID)
z ON b.PHST_APPOINT_ID = z.APPOINT_ID
WHERE b.PHST_CHG_DATE <= '20140627'
AND COALESCE(b.PHST_POS_NO, b.PHST_OLD_POS_NO) IS NOT NULL
GROUP BY b.PHST_EMP_ID,
b.PHST_APPOINT_ID
I got an error on z.GrossWages
Column 'z.GrossWages' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT
Max (b.PHST_CHG_DATE) as CHG_DATE
, max(b.PHST_TITLE) as TITLE
, b.PHST_EMP_ID as EMP_ID
, b.PHST_APPOINT_ID as APPOINT_ID
, Max(b.PHST_POS_NO) as PHST_POS_NO
, max(b.PHST_OLD_TITLE) as PHST_OLD_TITLE
, Max(b.PHST_OLD_POS_NO) as PHST_OLD_POS_NO
, z.GrossWages
FROM ESYEMPLR a
INNER JOIN ESHPOSHD b ON a.EMP_ID = b.PHST_EMP_ID
LEFT JOIN (
SELECT APPOINT_ID,
SUM(Amount) GrossWages
FROM ESYBAMHD z
WHERE BENDED_CODE = 'gross'
AND PP_END_Date >= '01-01-2014'
AND PP_END_Date <= '12-31-2014'
GROUP BY APPOINT_ID)
z ON b.PHST_APPOINT_ID = z.APPOINT_ID
WHERE b.PHST_CHG_DATE <= '20140627'
AND COALESCE(b.PHST_POS_NO, b.PHST_OLD_POS_NO) IS NOT NULL
GROUP BY b.PHST_EMP_ID,
b.PHST_APPOINT_ID
I got an error on z.GrossWages
Column 'z.GrossWages' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
You are right.
Instead of this:
z.GrossWages
Do this:
SUM(z.GrossWages) GrossWages
Instead of this:
z.GrossWages
Do this:
SUM(z.GrossWages) GrossWages
ASKER