dynamic datepart to dateadd()? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

dynamic datepart to dateadd()?

Author

Topic

TrondBjerkan
Starting Member

6 Posts

Posted - 2006-06-09 : 10:10:18

Hi,

windows 2000
mssql 2000

I am trying to use dateadd with a dynamic parameter, but it is not possible. I was hoping that I could do something like

declare @timeframe varchar(4)
set @timeframe = 'dd'

select dateadd(@timeframe, -1, getdate()).

Have you solved a similar problem?

What I what to achieve is to store a timeframe (eg. One month back, one week back) and a number of times (maxusage) a given user is allowed to play a game. The timeframe and the maxusage are checked when a new game is being started.

So if userid 2 has a maxusage of 3 per week, a fourth game should not be able to start if the user already has played 3 games looking a week back

I have attached some code to illustrate the situation below. Do you have some suggestions ? Thanks!

set dateformat dmy
create table #maxUsage
( [userid] int,
[maxusage] int,
[timeframe] varchar(4))

create table #gamer
( [userid] int)

create table #gamerlog
( [userid] int,
[timestamp] datetime)

insert into #gamer (userid) values (1)
insert into #gamer (userid) values (2)

insert into #gamerlog (userid, timestamp) values (1, cast('01-06-2006 02:45:17:000' as datetime))
insert into #gamerlog (userid, timestamp) values (1, cast('09-06-2006 10:14:00:000' as datetime))
insert into #gamerlog (userid, timestamp) values (1, cast('09-06-2006 12:34:56:000' as datetime))

insert into #gamerlog (userid, timestamp) values (2, cast('29-05-2006 13:54:23:000' as datetime))
insert into #gamerlog (userid, timestamp) values (2, cast('06-06-2006 01:14:00:000' as datetime))
insert into #gamerlog (userid, timestamp) values (2, cast('07-06-2006 03:25:07:000' as datetime))
insert into #gamerlog (userid, timestamp) values (2, cast('08-06-2006 23:51:01:000' as datetime))

insert into #maxusage (userid, maxusage, timeframe) values (1, 2, 'dd')
insert into #maxusage (userid, maxusage, timeframe) values (1, 8, 'ww')
insert into #maxusage (userid, maxusage, timeframe) values (2, 3, 'ww')

select #maxusage.userid, #maxusage.maxusage, #maxusage.timeframe, count(*) as gamesessions from #gamerlog
join #maxUsage
on #gamerlog.userid = #maxUsage.userid
where
-- timestamp >= dateadd(#maxusage.timeframe, -1, getdate()) <-- imaginary t-sql
timestamp >= dateadd(ww, -1, cast('10-06-2006 00:00:00:000' as datetime))
group by #maxusage.userid, #maxusage.maxusage, #maxusage.timeframe

drop table #maxusage
drop table #gamerlog
drop table #gamer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-09 : 10:17:43

use case statements to do this

declare @timeframe varchar(4)
set @timeframe = 'dd'


select case @timeframe
       when 'dd' then dateadd(day, -1, getdate())
       when 'ww' then dateadd(week, -1, getdate())
       . . . 
       end

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources