lulu50
asked on
Date range
Hi,
I am using SQL 2008. I am not sure why it's only display 4 records.
the date range looks good to me.
Thanks,
Lulu
I am using SQL 2008. I am not sure why it's only display 4 records.
the date range looks good to me.
Thanks,
Lulu
SELECT distinct L.IssueID
, L.ParentIssueID
FROM LogEntry L
LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID
LEFT JOIN DepartmentImpacted DI on DI.DepartmentImpactedID = SD.DepartmentImpactedID
where 1 = 1
<CFIF (IsDefined("Cookie.CookieDateFrom") && #Cookie.CookieDateFrom# neq "" ) && (IsDefined("Cookie.CookieGetDateTo") && #Cookie.CookieGetDateTo# neq "" ) >
AND (L.DateIdentified >= '#Trim(DateFormat(Cookie.CookieDateFrom, "YYYY-mm-dd"))#' and L.DateIdentified <= '#Trim(DateFormat(Cookie.CookieGetDateTo, "YYYY-mm-dd"))#')
</CFIF>
SELECT distinct L.IssueID , L.ParentIssueID FROM LogEntry L LEFT JOIN IssueStatus I on I.StatusID = L.StatusID LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID LEFT JOIN DepartmentImpacted DI on DI.DepartmentImpactedID = SD.DepartmentImpactedID where 1 = 1 AND (L.DateIdentified >= '2014-03-08' and L.DateIdentified <= '2014-04-08')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this is your current - and wildly inefficient - query:
SELECT DISTINCT
L.IssueID
, L.ParentIssueID
FROM LogEntry L
LEFT JOIN IssueStatus I
ON I.StatusID = L.StatusID
LEFT JOIN CategoryType C
ON C.CategoryTypeID = L.CategoryTypeID
LEFT JOIN TicketNumber T
ON T.TicketNumberID = L.TicketNumberID
LEFT JOIN SelectedDepartment SD
ON SD.IssueID = L.IssueID
LEFT JOIN DepartmentImpacted DI
ON DI.DepartmentImpactedID = SD.DepartmentImpactedID
WHERE 1 = 1
AND (L.DateIdentified >= '2014-03-08'
AND L.DateIdentified <= '2014-04-08')
;
I can only presume that the very expensive DISTINCT is used to remove the effect of all those expensive left joins - which you do not need at all for that query. This would produce the same result:
SELECT
L.IssueID
, L.ParentIssueID
FROM LogEntry L
WHERE 1 = 1
AND (L.DateIdentified >= '2014-03-08'
AND L.DateIdentified <= '2014-04-08')
;
By the way, YYYYMMDD is the safest most reliable date string format in SQL Server.
ASKER