Query Help

[SpeedStick]

All,

I need to write a query in SQL Server 2008 that will find all records that have an end date for next month and also all records for the previous months.

This query needs to be re-used in subsequent months.

Thank you,

Scott

 

[djj55]

Look at the DATEADD function. Also the MONTH and YEAR functions.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SpeedStick]

All:

Thanks for the suggested. I developed my own solution for myself.
Below is solution:

SELECT tblSubscriptionInformation.CompanyName, tblSubscriptionInformation.EndDate, tblSubscriptionInformation.Status
FROM tblSubscriptionInformation
WHERE ((Year([EndDate])*12+DatePart("m",[EndDate])<=Year(Date())*12+DatePart("m",Date())+1) AND ((tblSubscriptionInformation.Status)="Current"))
ORDER BY tblSubscriptionInformation.EndDate DESC;

Scott

 

[fredericofonseca]

Scott,

although your code will work, it has a potential issue with the fact that if field EndDate is a index of that table, it will not be used.

The following would give you the same results, and would allow the index to be used.

SELECT tblSubscriptionInformation.CompanyName
      ,tblSubscriptionInformation.EndDate
      ,tblSubscriptionInformation.Status
FROM tblSubscriptionInformation
WHERE EndDate < dateadd(m,2,convert(date,convert(varchar,(Year(getDate()))) +right('0'+convert(varchar,DatePart("m",getDate())),2) +'01',112))
  AND tblSubscriptionInformation.Status="Current"
ORDER BY tblSubscriptionInformation.EndDate DESC;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[drlex]

That seems to be quite a complicated expression to test the date against. Since DATEADD(mm,1+DATEDIFF(mm,0,getdate()),0) will give the first day of next month, how about just

SELECT tblSubscriptionInformation.CompanyName
      ,tblSubscriptionInformation.EndDate
      ,tblSubscriptionInformation.Status
FROM tblSubscriptionInformation
WHERE EndDate < [red]DATEADD(mm,1+DATEDIFF(mm,0,getdate()),0)[/red]
AND tblSubscriptionInformation.Status="Current"
ORDER BY tblSubscriptionInformation.EndDate DESC;

soi là, soi carré

 

[fredericofonseca]

thats neat - but it should be DATEADD(mm,2+DATEDIFF(mm,0,getdate()),0) (e.g. 2+, not 1+)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[drlex]

Frederico,
Re-reading Scott's post, I see that what you say is right; thanks.

soi là, soi carré