September 7, 2011 at 3:57 am
this qry displaying the result of total two months in july column what is wrong in that.
I need individual july and aug lines any wrong in this qry
SELECT pr.practice_id AS 'Practice Id',pr.practice_name AS 'Practice Name',v.vendor_name,dr.doctor_id AS 'Doctor ID',
CONCAT(dr.first_name,' ',dr.middle_name,' ',dr.last_name) AS 'Doctor Name',
CASE WHEN vfs.final_uploaded_datetime BETWEEN '2011-07-01 00:00:00' AND '2011-08-01 00:00:00' THEN ROUND(SUM(DOC.DOCUMENT_LINECOUNT),2) END AS 'july',
CASE WHEN vfs.final_uploaded_datetime BETWEEN '2011-08-01 00:00:00' AND '2011-09-01 00:00:00' THEN ROUND(SUM(DOC.DOCUMENT_LINECOUNT),2) END AS 'Aug'
FROM
tf_vf_details vf
INNER JOIN tf_vf_status_details vfs ON vf.VF_ID=vfs.vf_id
INNER JOIN tf_document_details doc ON doc.VF_ID=vf.VF_ID
INNER JOIN tf_doctor_details dr ON dr.doctor_id=vf.UPLOADING_DR_ID
INNER JOIN tf_practice_details pr ON pr.practice_id=dr.practice_id
INNER JOIN tf_vendor_details v ON v.vendor_id=vfs.vendor_id
WHERE vfs.final_uploaded_datetime BETWEEN '2011-07-01 00:00:00' AND '2011-09-01 00:00:00'
AND pr.practice_id=505
GROUP BY pr.practice_id,dr.doctor_id
September 7, 2011 at 4:42 am
SELECT
pr.practice_id AS 'Practice Id',
pr.practice_name AS 'Practice Name',
v.vendor_name,
dr.doctor_id AS 'Doctor ID',
CONCAT(dr.first_name,' ',dr.middle_name,' ',dr.last_name) AS 'Doctor Name',
--CASE WHEN vfs.final_uploaded_datetime BETWEEN '2011-07-01 00:00:00' AND '2011-08-01 00:00:00' THEN ROUND(SUM(DOC.DOCUMENT_LINECOUNT),2) END AS 'july',
--CASE WHEN vfs.final_uploaded_datetime BETWEEN '2011-08-01 00:00:00' AND '2011-09-01 00:00:00' THEN ROUND(SUM(DOC.DOCUMENT_LINECOUNT),2) END AS 'Aug',
[july] = ROUND(SUM(
CASE WHEN vfs.final_uploaded_datetime BETWEEN '2011-07-01 00:00:00' AND '2011-08-01 00:00:00' THEN doc.DOCUMENT_LINECOUNT ELSE 0 END
),2),
[Aug] = ROUND(SUM(
CASE WHEN vfs.final_uploaded_datetime BETWEEN '2011-08-01 00:00:00' AND '2011-09-01 00:00:00' THEN doc.DOCUMENT_LINECOUNT ELSE 0 END
),2)
FROM
tf_vf_details vf
INNER JOIN tf_vf_status_details vfs ON vf.VF_ID=vfs.vf_id
INNER JOIN tf_document_details doc ON doc.VF_ID=vf.VF_ID
INNER JOIN tf_doctor_details dr ON dr.doctor_id=vf.UPLOADING_DR_ID
INNER JOIN tf_practice_details pr ON pr.practice_id=dr.practice_id
INNER JOIN tf_vendor_details v ON v.vendor_id=vfs.vendor_id
WHERE vfs.final_uploaded_datetime BETWEEN '2011-07-01 00:00:00' AND '2011-09-01 00:00:00'
AND pr.practice_id=505
GROUP BY pr.practice_id,dr.doctor_id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2011 at 5:14 am
Thanks Lot...............
working fine
September 8, 2011 at 2:44 am
Beware when using the BETWEEN operator, it is inclusive of the values of the two expressions.
From MSDN:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Note the highlighted "or equal to".
Therefore the following:
CASE WHEN somedate BETWEEN '2011-08-01 00:00:00' AND '2011-09-01 00:00:00'
will include rows where the date is equal to '2011-09-01 00:00:00'
And in Chris' code above any rows with a date of '2011-08-01 00:00:00' will be included in both month totals.
Instead use something like:
CASE WHEN somedate >= startdate AND somedate < enddate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply