SQL loop to calculate fields???

[skijop]

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:

 

[SQLSister]

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

 

[AlexCuse]

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

 

[SQLSister]

you are right alex, I forgot to say that

"NOTHING is more important in a database than integrity." ESquared

 

[skijop]

I dont have the luxury of creating temp tables in this case...It would just have to be some sort of loop to calculate....?

 

[SQLSister]

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

 

[skijop]

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?

 

[Lamprey13]

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.

 

[skijop]

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.

 

[SQLSister]

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

 

[skijop]

Any examples to point me towards....?

 

[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]