Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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


 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>

Open in new window


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') 

Open in new window

0
lulu50
Asked:
lulu50
1 Solution
 
Lee SavidgeCommented:
Perhaps there are only 4 records to return? I can't say without having access to the data. My guess is that SQL is interpreting the date range as the 3rd and 4th August rather than 8th March to the 8th April.
0
 
lulu50Author Commented:
Thank you
0
 
PortletPaulCommented:
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')
;

Open in new window

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')
;

Open in new window

By the way, YYYYMMDD is the safest most reliable date string format in SQL Server.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now