jjc9809
asked on
TSequel Between on Dates
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.LicT ype) 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.Expiratio nDate < '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
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.LicT
FROM dbo.LicenseFY10 INNER JOIN
dbo.luLicenseType ON dbo.LicenseFY10.LicType = dbo.luLicenseType.LicType
WHERE (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.Expiratio
GROUP BY dbo.luLicenseType.LicType,
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Expiratio nDate between '10/01/2010' and '09/30/2011')
WHERE (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.Expiratio
ASKER
Hello everyone,
I do have a time part on the issue date and expiration date after looking. So Between will not work.
jjc9809
I do have a time part on the issue date and expiration date after looking. So Between will not work.
jjc9809
WHERE (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.Expiratio nDate between '10/01/2010 00:00:01' and '09/30/2011 11:59:59')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.Expiratio nDate 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.Expiratio nDate <= '09/30/2011')
WHERE (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate
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
or this:
WHERE (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate
ASKER
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