Solved

Date range

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now