Query involving dates - 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 2000 Forums

SQL Server Development (2000)

Query involving dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-30 : 09:32:27

sathish writes "I have a table called Loyalty. It stores the loyalty points obtained by users during a specfied period.

The following are the fields in the loyalty table:

1. LoyaltyPoints int
2. PeriodType    int       . It can take the value 1,2 or 3
3. SpecificDate  datetime
4. FromDate      datetime
5. ToDate        datetime

This is want is needed:
1. Select loyaltypoints from loyalty table.
2. Select the period from PeriodType.
3. If PeriodType is 1 then the loyaltypoints is applicable
for the entire period.
If PeriodType is 2 then the loyaltypoints is applicable
for the date value in the "SpecificDate" field.
If PeriodType is 3 then the loyaltypoints is applicable
from the date value in the "FromDate" to the date value in
the "ToDate".
4. Display all repeating Records. How do the records repeat?
If the PeriodType is 2 then if that record's specificdate
is already there in other records' specificdate (OR) if
that record's specificdate is present in other record's
fromdate todate (if the other record's periodtype is 3)
then display that record
5. If the PeriodType is 3 then if that record's fromdate and
todate lies between other record's fromdate todate (if the
other record's periodtype is 3) then display that record.

Since i'am using sqlserver stored procedure also would do.

with regards,
sathish kumar R.
sathish297@hotmail.com"

nr
SQLTeam MVY

12543 Posts

Posted - 2001-11-30 : 23:48:52

I think your difficulty with this is the way you are trying to define the problem.
What do you actually want as output - only 4 and 5 seem to define output the rest don't seem applicable?

4 to get the repeating recs for type 2
select *
from tbl
where periodtype = 2
and specificdate in
(select specificdate from tbl where periodtype = 2 group by specificdate having count(*) > 1)

type 2 matching a type 3 (Do you mean in the date period or matching a boundary?)
select t1.*
from tbl t1, tbl t2
where t1.periodtype = 2
and t2.periodtype = 3
and t1.specificdate between t2.FromDate and t2.ToDate

5. (see comment above)
select t1.*
from tbl t1, tbl t2
where t1.periodtype = 3
and t2.periodtype = 3
and
(t1.FromDate between t2.FromDate and t2.ToDate
or (t1.FromDate <= t2.FromDate and t1.ToDate >= t1.FromDate)
or (t1.FromDate <= t2.ToDate and t1.ToDate >= t1.ToDate)
)
and (t1.FromDate <> t2.FromDate or t1.ToDate <> t2.ToDate)

If you union all of these queries you should get somewhere near what you want.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources