Solved

Date range

Posted on 2014-04-07
3
203 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 500 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 48

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 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