Period Based Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Period Based Query

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2011-03-02 : 11:23:55

Hi Team,

I have a MaintenanceContractTable as shown below.

There is a ContractEndingDate for each order. There is a concept called “Contract Period”. The Contract Period for an OrderID starts 12 months before the ContractEndingDate and ends on the ContractEndingDate.

For the record shown below with OrderID = 1
ContractEndingDate: ‘2011-03-20’
Contract Period: ‘2010-03-21’ - ‘2011-03-20’

I want to generate a report based on the “Contract Period”. The input parameters for the report are @FromDateInputParam and @ToDateInputParam.

The expected result is Order ID 1 and 3. Can you plese help me with a query for this?

Note 1: While loop is not expected to use
Note 2: I am not allowed to change the schema even though it is not best DB design.

DECLARE @MaintenanceContractTable TABLE (OrderID INT,ContractEndingDate SMALLDATETIME)

INSERT INTO @MaintenanceContractTable (OrderID ,ContractEndingDate ) VALUES (1,'2011-03-20')
INSERT INTO @MaintenanceContractTable (OrderID ,ContractEndingDate ) VALUES (2,'2011-05-05')
INSERT INTO @MaintenanceContractTable (OrderID ,ContractEndingDate ) VALUES (3,'2010-06-06')
INSERT INTO @MaintenanceContractTable (OrderID ,ContractEndingDate ) VALUES (4,'2010-01-01')

DECLARE @FromDateInputParam SMALLDATETIME
SET @FromDateInputParam = '2010-04-04'
DECLARE @ToDateInputParam SMALLDATETIME
SET @ToDateInputParam = '2010-04-05'

Thanks
Lijo Cheeran Joseph

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-02 : 11:35:20

[code]select
orderId
from
@MaintenanceContractTable
where
@FromDateInputParam between dateadd(yy,-1,ContractEndingDate)+1 and ContractEndingDate
or @ToDateInputParam between dateadd(yy,-1,ContractEndingDate)+1 and ContractEndingDate
[/code]

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2011-03-04 : 09:01:27

Thank you..

If you can direct/refer me a url that describes about such PERIOD based query logic, that will be great.

Thanks
Lijo Cheeran Joseph

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 11:32:46

The logic I used was that if the from_date or to_date is within the one year period, then that record qualifies. (That may not be all what you need: for example, what if the from_date is earlier than and to_date is after the one year period? In other words, can the timespan between from_date and to_date be more than one year? Also, is your requirement that the from_date and to_date be completely contained within the one year period?)

Once you know the logic you want to use, it is a matter of translating that to SQL. SQL has a lot of datetime functions that let you do this - see here: http://msdn.microsoft.com/en-us/library/ms186724.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources