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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy