Solved

TSequel Between on Dates

Posted on 2012-04-05
9
246 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

19 Experts available now in Live!

Get 1:1 Help Now