Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL loop to calculate fields???

Status
Not open for further replies.

skijop

Programmer
May 4, 2007
18
US
Is there an easy way to calculate a datediff of date/time field(effective_date based on the change of another field (client_id) changes by bed_id ?

AKA when status_code_id is 17 for a row, then the next row is a different client_id, calculate the datediff of effective date from these 2 rows, but only when the bed_id is the same.

This sql gives me the complete dataset.
(
select client_id,effective_date,bed_id,status_code_id from census_item where bed_id is not null and deleted = 'n'
)
union
(
select client_id,effective_date,bed_id,status_code_id from census_item
where status_code_id = '17' and deleted = 'n'
)

And here is an example of a small dataset:

ex.jpg
 
Consider inserting that union statement into a table varaible or temp table that has an identiy field.
then join the temp table to itself on t1.ifield = t2.idfield+1

Then you should have both records you want in the same row and you can use datediff to calc the difference.

"NOTHING is more important in a database than integrity." ESquared
 
Make sure to use an order by when you insert, or the identity column will have no meaning.

Hope this helps,

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
you are right alex, I forgot to say that

"NOTHING is more important in a database than integrity." ESquared
 
I dont have the luxury of creating temp tables in this case...It would just have to be some sort of loop to calculate....?
 
Temp tables will be much much faster than a loop. YOu should alawys try to avoid loops. Loops are bad things. Why can't you use table variables or temp tables?

"NOTHING is more important in a database than integrity." ESquared
 
Going to try to push this out to a table in a data warehouse....The database that I am using won't allow writing of temp or reporting tables bc of security. Amazing,right?
 
Are you using SQL 2005?

Also, can you give a sample of what your expected results should be from your sample data set? I'm confused if on which date difference you want.
 
bed id - discharge_date(stat17) - date_amount

26294 - 1/6/2007 - (diff between 1/6/07 and 1/17/07)

26294 - 2/17/07 - (diff between 2/17/07 and 2/28/07)



Basically at every status 17(per each bed_id)to a different client_id, the difference of the effective_dates. I basically want to return just one lime per each change.
 
why not use a table variable then? It rests in memory, it isn;t written to the database.

"NOTHING is more important in a database than integrity." ESquared
 
Table variables may exist mostly in memory. There is no guarantee: they can take up just as much space in tempdb as a temp table. Sometimes, they actually perform worse than a temp table.

Temp tables can have their indexes and constraints altered after creation time: sometimes in ways that kick the pants off table variables.

I have seen other situations where table variables performed worse than an identical temp table. And vice versa.

Table variables are not always best.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top