Solved

TSequel Between on Dates

Posted on 2012-04-05
9
253 Views
Last Modified: 2012-04-06
Hi everyone,

I need some Tsequel Coding that will find some records between two dates so that I can do a Count.  I need the coding where Between is used.

SELECT     COUNT(dbo.LicenseFY10.LicType) AS TotalCount, dbo.luLicenseType.LicType, dbo.luLicenseType.LicDesc
FROM         dbo.LicenseFY10 INNER JOIN
                      dbo.luLicenseType ON dbo.LicenseFY10.LicType = dbo.luLicenseType.LicType
WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate < '09/30/2011')
GROUP BY dbo.luLicenseType.LicType, dbo.luLicenseType.LicDesc

This works but does not give me the real records between the two dates.

How can I re-write this to do the count correctly.

I need the count of Lictypes between, 10/01/2010 and 09/30.2011.

jjc9809
0
Comment
Question by:jjc9809
  • 5
  • 3
9 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 334 total points
ID: 37812736
WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate between 'oct 1, 2010' and 'sep 30, 2011')
0
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 166 total points
ID: 37812840
Two quick questions:

1)  Do you need the data through 9/30, or up to but not including 9/30

2)  Does tour ExpirationDate field contain a time part as well.

Your WHERE clauses vary in these different cases.  If you have no time portion in the fields, and you need it inclusive of 9/30 (as I assume), then Buttercup1 has your answer (although I would format the dates as yyyymmdd instead of as they are formatted in his example - yyyymmdd is a universally accepted date format, as opposed to American (mm/dd/yyyy), Europe and most everywhere else (dd/mm/yyyy), or dates including month names (which vary by language).  The BETWEEN clause in this case would be:

BETWEEN '20101010' and '20110930'

If the fields include a time portion that is used, then you need to use something other than BETWEEN.  Some people add the max time to the text string, but I find it cleaner and easier to use:

dbo.LicenseFY10.ExpirationDate >=  '20101001' and dbo.LicenseFY10.ExpirationDate < '20101001'

Any date/time from the start of October 1, 2010 to the end of Sep 30, 2011, would be included.
0
 

Author Comment

by:jjc9809
ID: 37812913
bHess1,

There is no time part on the issue date or expiration date.  The dates are in this format 10/01/2010 to 09/30/2011.

I need the data thorugh 9/30/2011.  I need all records where a License was issued from 10/01/2010 to 09/30/2011.  Then I can take a count of the License Number.

jjc9809
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 11

Expert Comment

by:Simone B
ID: 37812932
Change your Where clause to use the BETWEEN operator. You can use whatever format you like for the dates, and SQL will interpret it.

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate between '10/01/2010' and '09/30/2011')
0
 

Author Comment

by:jjc9809
ID: 37813101
Hello everyone,

I do have a time part on the issue date and expiration date after looking.  So Between will not work.

jjc9809
0
 
LVL 11

Expert Comment

by:Simone B
ID: 37813190
WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate between '10/01/2010 00:00:01' and '09/30/2011 11:59:59')
0
 
LVL 11

Accepted Solution

by:
Simone B earned 334 total points
ID: 37813208
Specifying the time means that you run the risk of losing 2 seconds of data. Not specifying the time will give you everything from both the beginning and ending dates and everything between, inclusive. It doesn't matter that your data type includes the time, you can still use "between" with dates alone.
0
 

Author Comment

by:jjc9809
ID: 37813598
Buttercup1,

That does makes sense what you said.   I am just confused not using the Issue Date in the query.  The Issue Date is always 10/01/2010 for the year though so I see where using just Expiration date would pull the number needed.

Am I right in assuming this?

jjc9809
0
 
LVL 11

Expert Comment

by:Simone B
ID: 37813894
I'm sorry, I missed that. So to clarify: do you want to return all lictype of 010 and 011, issued on Oct 1, 2010? If so then your where clause would look like this:

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate ='10/01/2010' )

If you also want to filter on the expiration date, you can add that as well, either using between or not, as you choose:

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate ='10/01/2010' ) and (dbo.LicenseFY10.ExpirationDate between '10/01/2010'  and '09/30/2011')

or this:

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate ='10/01/2010' ) and (dbo.LicenseFY10.ExpirationDate <= '09/30/2011')
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

820 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