?
Solved

Date range

Posted on 2014-04-07
3
Medium Priority
?
208 Views
Last Modified: 2014-04-07
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
Comment
Question by:lulu50
3 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 2000 total points
ID: 39983356
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
 

Author Closing Comment

by:lulu50
ID: 39983401
Thank you
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39984703
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month4 days, 22 hours left to enroll

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question