September 14, 2009 at 4:26 pm
Hi Guys
I have got this query where I am using union all to combine different dates.
These dates have a pattern (6 month interval).
Is there a way to combine all this in one single query?
Using a look up or something?
Here is the output of this query
Date FTE
2009-09-01 00:00:00.000249
2009-03-01 00:00:00.000245
2008-09-01 00:00:00.000234
2008-03-01 00:00:00.000233
SELECT CONVERT(datetime, '1 sep 2009') AS Date,
SUM(l2.code) / 10 AS FTE
FROM DOCTOR AS a INNER JOIN
DOCTOR_SURGERY AS b ON a.DOCTOR_SURGERY_ID = b.DOCTOR_SURGERY_id WHERE ('1 sep 2009' BETWEEN b.valid_from_date AND COALESCE (b.valid_to_date, '1 sep 2009')) AND (vp.pho_id = 1)
UNION ALL
SELECT CONVERT(datetime, '1 mar 2009') AS Date,
SUM(l2.code) / 10 AS FTE
FROM DOCTOR AS a INNER JOIN
DOCTOR_SURGERY AS b ON a.DOCTOR_SURGERY_ID = b.DOCTOR_SURGERY_id WHERE ('1 mar 2009' BETWEEN b.valid_from_date AND COALESCE (b.valid_to_date, '1 mar 2009')) AND (vp.pho_id = 1)
UNION ALL
SELECT CONVERT(datetime, '1 sep 2008') AS Date,
SUM(l2.code) / 10 AS FTE
FROM DOCTOR AS a INNER JOIN
DOCTOR_SURGERY AS b ON a.DOCTOR_SURGERY_ID = b.DOCTOR_SURGERY_id WHERE ('1 sep 2008' BETWEEN b.valid_from_date AND COALESCE (b.valid_to_date, '1 sep 2008')) AND (vp.pho_id = 1)
UNION ALL
SELECT CONVERT(datetime, '1 mar 2008') AS Date,
SUM(l2.code) / 10 AS FTE
FROM DOCTOR AS a INNER JOIN
DOCTOR_SURGERY AS b ON a.DOCTOR_SURGERY_ID = b.DOCTOR_SURGERY_id WHERE ('1 mar 2008' BETWEEN b.valid_from_date AND COALESCE (b.valid_to_date, '1 mar 2008')) AND (vp.pho_id = 1)
Cheers
September 14, 2009 at 7:04 pm
You should be able to do something like this:
SELECT C.DateGroup,
SUM(I2.Code) FTE
FROM DOCTOR A
INNER JOIN DOCTOR_SURGERY B ON A.DOCTOR_SURGERY_ID = B.DOCTOR_SURGERY_ID
INNER JOIN (
SELECT B.Doctor_Surgery_ID,
CASE
WHEN valid_from_date BETWEEN '03/01/2008' AND '09/01/2008' THEN '03/01/2008'
WHEN valid_from_date BETWEEN '09/01/2008' AND '03/01/2009' THEN '09/01/2008'
WHEN valid_from_date BETWEEN '03/01/2009' AND '09/01/2009' THEN '03/01/2009'
WHEN valid_from_date BETWEEN '09/01/2009' AND '03/01/2010' THEN '09/01/2009'
END DateGroup
FROM DOCTOR_SURGERY
--INNER JOIN SOME VP TABLE YOU DIDN'T SUPPLY
--WHERE VP.pho_ID = 1
) C
ON B.DOCTOR_SURGERY_ID = C.DOCTOR_SURGERY_ID
-- INNER JOIN SOME OTHER TABLES THAT YOU DIDN'T SUPPLY
GROUP BY C.DateGroup
That's probably not exact due to the fact that you didn't supply table structure or sample data (see the link in my signature for how to do that), but it may put you on the right track.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply