Click here to Skip to main content
14,920,935 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
SELECT     FileNo, ReqControlNo, Requester, ReqDate, IssueDate, DueDate, RequesterDeptt, Remarks, HandOverTo, RecordType, ReceivedDate, ProjectName,
                      ReceivedRemarks, IssueTime, ReceiveTime
FROM         Issue
WHERE     (format(IssueDate, 'dd-mm-yyyy') BETWEEN format(?, 'dd-mm-yyyy') AND format(?, 'dd-mm-yyyy')) OR
                      (format(ReceivedDate, 'dd-mm-yyyy') BETWEEN format(?, 'dd-mm-yyyy') AND format(?, 'dd-mm-yyyy'))
ORDER BY ReceivedDate

It returns 677 results.
SQL
SELECT     FileNo, ReqControlNo, Requester, ReqDate, IssueDate, DueDate, RequesterDeptt, Remarks, HandOverTo, RecordType, ProjectName,
                     IssueTime From         Issue
WHERE     (format(IssueDate, 'dd-mm-yyyy') BETWEEN format(?, 'dd-mm-yyyy')

it returns 362 results.
SQL
SELECT     FileNo, ReqControlNo, Requester, ReqDate, IssueDate, DueDate, RequesterDeptt, Remarks, HandOverTo, RecordType, ReceivedDate, ProjectName,
                      ReceivedRemarks, IssueTime, ReceiveTime
FROM         Issue
WHERE     ( (format(ReceivedDate, 'dd-mm-yyyy') BETWEEN format(?, 'dd-mm-yyyy') AND format(?, 'dd-mm-yyyy'))

it returns 338 results.
why Query1 result !=Query2+query3 results.
it's very urgent.
help me!
Problem is that the first query is not include those files that are issued and received same day.


Update from OP: I got the solution.It's my conceptual mistake.
Posted
Updated 20-Jul-11 0:37am
v3
Comments
Herman<T>.Instance 20-Jul-11 4:04am
   
does your second query miss a part?

1 solution

second query uses IssueDate in the where clause, while the other queries use ReceivedDate
   
Comments
uspatel 20-Jul-11 4:32am
   
yes, but sum of result of below both queries is different from query1.
it's real problem
Herman<T>.Instance 20-Jul-11 5:32am
   
use at least the same where clause in all 3 queries and see if the result changes

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900