Union or variable to return a single row

[simonsharratt]

Hi,



I'm using the following query

SELECT staff.staffid,sum(holiday.[DaysToBePaid]) AS [Holidays Taken]
FROM Staff INNER JOIN Holiday ON Staff.[StaffID] = Holiday.[StaffID]
WHERE Holiday.[datereturnedtowork] <= date()
group by staff.staffid
UNION ALL
SELECT staff.staffid,sum(Holiday.DaysToBePaid) AS [Days Booked But Not Taken]
FROM Staff INNER JOIN Holiday ON Staff.[StaffID] = Holiday.[StaffID]
WHERE holiday.[firstdayofholiday] >= date()
group by staff.staffid
order by staff.staffid;



to UNION 2 queries to return 2 values from the same table but based on 2 different where statements: But what i want is the result displayed a single line like so:

StaffID Holidays Taken Holidays To Be Taken
4 20 6
5 1
7 4
10 5 6

Is this possible ?

Thanks

[ranman256]

Q1 is :select ID,field as holidayTaken, 0 as Remain from table1
Q2 is :select ID, 0 as holidayTaken, fields2 as Remain from table2

q3:
select * from Q1
union
select * from Q2

[simonsharratt]

Sorry Ranman that doesn't make a lot of sense to me. How does your statement relate to the 2 queries i have and are you saying i need to introduce a third query ?

[CJ_London]

Ranman's point - Be aware that in a union query the resultant column headings will be taken from the first query - so you have no way of identifying if a value is holidays taken or days booked.

Add another column to your select query "Holiday Taken" and "Days Booked But Not Taken" both AS ColHeader, then use it as a source for a crosstab query staffed - row header, ColHeader - column Header - Holidays taken - value

alternatively use a single group by query - not quite sure what you are trying to achieve but something like

SELECT staff.staffid,sum(holiday.[DaysToBePaid]*-.[datereturnedtowork] <= date()) AS [Holidays Taken], sum(holiday.[DaysToBePaid]*-Holiday.[firstdayofholiday] >= date()) AS [Days Booked But Not Taken
FROM Staff INNER JOIN Holiday ON Staff.[StaffID] = Holiday.[StaffID]
WHERE Holiday.[datereturnedtowork] <= date()
group by staff.staffid

Not sure if you need the WHERE statement, but it limits on the assumption that datereturned to work is always later than firstdayofholiday

You also do not need the staff table - you can just reference the staffID in the holiday table - unless you want all staff, in which case it should be a left join rather than an inner join