Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio

FileTime

Is there a SQL function to get FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
You can use DATEDIFF(ms,startdate, GETDATE())
where ms stands for milliseconds, startdate is a your own starting date, and
getdate is the system function that returns the current date, up to
milliseconds.
Francesco Anti
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...[vbcol=seagreen]
> Is there a SQL function to get FileTime?
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
filetime
>
|||One thing to remember is that since SQL Server DateTime has less reolution,
you can do a direct comparison.
"Francesco Anti" <fanti @. sicosbt.it> wrote in message
news:ed2YskSBFHA.608@.TK2MSFTNGP15.phx.gbl...
> You can use DATEDIFF(ms,startdate, GETDATE())
> where ms stands for milliseconds, startdate is a your own starting date,
and[vbcol=seagreen]
> getdate is the system function that returns the current date, up to
> milliseconds.
> Francesco Anti
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
time
> filetime
>

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
No, not built-in; I guess the only way would be to create your own UDF.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...[vbcol=seagreen]
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
filetime
>

FileTime

Is there a SQL function to get FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the fileti
me
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>You can use DATEDIFF(ms,startdate, GETDATE())
where ms stands for milliseconds, startdate is a your own starting date, and
getdate is the system function that returns the current date, up to
milliseconds.
Francesco Anti
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
> Is there a SQL function to get FileTime?
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
>
filetime[vbcol=seagreen]
>|||One thing to remember is that since SQL Server DateTime has less reolution,
you can do a direct comparison.
"Francesco Anti" <fanti @. sicosbt.it> wrote in message
news:ed2YskSBFHA.608@.TK2MSFTNGP15.phx.gbl...
> You can use DATEDIFF(ms,startdate, GETDATE())
> where ms stands for milliseconds, startdate is a your own starting date,
and
> getdate is the system function that returns the current date, up to
> milliseconds.
> Francesco Anti
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
time[vbcol=seagreen]
> filetime
>

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the fileti
me
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>No, not built-in; I guess the only way would be to create your own UDF.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
>
filetime[vbcol=seagreen]
>sql

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:
>
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > How can I convert filetime to datetime in sql server inside a sored
> > procedure.
> >
> > Thanks
> > BVR
>
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>No, not built-in; I guess the only way would be to create your own UDF.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
> >
> > "uhway" <uhway@.discussions.microsoft.com> wrote in message
> > news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > > How can I convert filetime to datetime in sql server inside a sored
> > > procedure.
> > >
> > > Thanks
> > > BVR
> >
> >
> > What do you mean by filetime? Can you give an example of what the
filetime
> > data looks like?
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
>