Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TSequel Between on Dates

Posted on 2012-04-05
9
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 1336 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:Brendt Hess
Brendt Hess earned 664 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 1336 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

609 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