Solved

Date range

Posted on 2014-04-07
3
201 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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