change variable on the fly

Question

Post reply

change variable on the fly

MikeJ-437562

SSC Veteran

Points: 248
More actions
January 28, 2008 at 8:15 am

#202724

Posting again
is there a way to change varday on the fly to different hourly times? Need the output for 1 Month..
Declare @sampleday SMALLDATETIME
set @varday='01/01/2008 11:00:00'
select count(PRIM_KEY_TRANSACTIONID) from TRANSACTION_DB
where @varday between BeginTime and EndTime
-TIA

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

Do you think something like this would work?

Declare @varday SMALLDATETIME

set @varday='01/01/2008 11:00:00'

Select count(PRIM_KEY_TRANSACTIONID)

From

(

Select Dateadd(hh, Hourvalue, Varday) VarDay

From

(

Select @varday Varday

) tab1

cross join

(

/*create a temp table populated with all of the numbers you want*/

Select 0 HourValue Union all

Select 1 Union All

Select 2 Union All

Select 3 Union All

Select 4

) tab2

) tab3

INNER JOIN TRANSACTION_DB ON tab3.varday between TRANSACTION_DB.BeginTime and TRANSACTION_DB.EndTime

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

What do you mean change on the fly? Change from 11 to 12 to 1 (13)?

It's not clear from your question what the requirement is.

MikeJ-437562 SSC Veteran Points: 248 More actions · Answer 3

Yes, change the time stamps like you said.

So varday is '01/01/2008 11:00:00' to a specified end date. with time stamps changing for each hour/expanding this to a month worth of data.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

A bit of a spin on jeremy's thought, using a temp table

drop table #tally

declare @startvalue datetime

select @startvalue='01/01/2008'

select top 745 -- 31 days *24 hours +1

identity(int,0,1) as n, @startvalue as dateval into #tally from

syscolumns sc1, syscolumns sc2

update #tally

set dateval=dateadd(hour,n,dateval)

select * from #tally

you can now use the #tally for all of your hourly values.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MikeJ-437562 SSC Veteran Points: 248 More actions · Answer 5

Thank you for replies and your time but these are giving me Cumulative totals, i want to get the total per each hour as the time stamps change.

-Thank you

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 6

Try adding a GROUP BY clause to the query. It is missing from mine.

MikeJ-437562 SSC Veteran Points: 248 More actions · Answer 7

Thats it!!

Thank you Thank you Thank you

Jeremy and Matt Miller, you guys are fantastic, saved the day.

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 8

Matt,

This is quite a novel approach to getting a list of numbers:

select top 745 -- 31 days *24 hours +1

identity(int,0,1) as n, @startvalue as dateval into #tally from

syscolumns sc1, syscolumns sc2

The only place I see this failing is if the number of values you want to generate is larger than the count of the cross product of the tables. It seems you'll get quite a decent number from a database with a moderate number of columns in it. My question is have you ever run into that limit (maybe with a really tiny DB), and if so, is a simple while loop a sufficient way to generate the numbers you want, or is there another good set based approach to generating sequential values without cross joining existing tables?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

I can't take credit for it - I got clued into this by Jeff Moden (who was using 2000's syscolumns equivalent).

Considering it has 4100 rows in a brand new blank database, the cartesian product (cross join) yields something in excess of 16M rows. If you need more, just to a three-way cross join, which would require you to switch to BIGINT calculations (72Billion rows give or take a few hundred million :)).

edit: now that I'm looking at it - I didn't even need the cross join for this example...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 10

Cool. I'll have to stuff this one in my bag of tricks.

Thanks!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Matt Miller (1/28/2008)
I can't take credit for it - I got clued into this by Jeff Moden (who was using 2000's syscolumns equivalent).

Was doing some research on Tally tables and ran across this... thanks for the plug, Matt!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

It's always good to give credit where credit is due. Now - it's just a matter of passing said good ideas along...

Now we can really say:

"Inspected by #77"....;)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

You know why #77? You get "eight" more 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

someone call a medic....I'm choking over here.......:hehe::cool::w00t:;):P:D:):blush:

Stop it....I'm crying!!!!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?