|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sum Total Hours and Minutes in a reportusage: Campus Lab usage: how long has a student been in the lab I can get: daily hours and minutes on the report with: =HoursAndMinutes([CheckOut]-[CheckIn]) HoursAndMinutes function from: http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) It works great in an Access.mdb file. I get an aggregate error on the .adp report. At a loss as to what to do next? Create a UDF that will do the calc and then call that from the stored procedure that the report is based on? Take it all down to .mdb ? Tell the end users No Total Totals :) SQL Experience: 1 on a scale of 0-10 any direction would be greatly appreciated, Suzanne can you sum(HoursAndMinutes([checkOut]-[checkIn])?
Show quote "sack" <s***@discussions.microsoft.com> wrote in message news:03498991-433D-4C4D-9BC4-21D89463C4E6@microsoft.com... > using: Access.adp; Report > usage: Campus Lab usage: how long has a student been in the lab > > I can get: daily hours and minutes on the report with: > =HoursAndMinutes([CheckOut]-[CheckIn]) > > HoursAndMinutes function from: > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > > It works great in an Access.mdb file. I get an aggregate error on the > .adp > report. > > At a loss as to what to do next? Create a UDF that will do the calc > and > then call that from the stored procedure that the report is based on? > > Take it all down to .mdb ? > > Tell the end users No Total Totals :) > > SQL Experience: 1 on a scale of 0-10 > > any direction would be greatly appreciated, > Suzanne > > > still get the aggregate error.
I am thinking that I need to migrate the HoursAndSum function into an SQL UDF. Then use that function in a stored procedure to create a field I can then SUM ?? Where is a good place to learn how to create UDF's? thanks, Suzanne Show quote "JI" wrote: > can you sum(HoursAndMinutes([checkOut]-[checkIn])? > "sack" <s***@discussions.microsoft.com> wrote in message > news:03498991-433D-4C4D-9BC4-21D89463C4E6@microsoft.com... > > using: Access.adp; Report > > usage: Campus Lab usage: how long has a student been in the lab > > > > I can get: daily hours and minutes on the report with: > > =HoursAndMinutes([CheckOut]-[CheckIn]) > > > > HoursAndMinutes function from: > > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > > > > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > > > > It works great in an Access.mdb file. I get an aggregate error on the > > .adp > > report. > > > > At a loss as to what to do next? Create a UDF that will do the calc > > and > > then call that from the stored procedure that the report is based on? > > > > Take it all down to .mdb ? > > > > Tell the end users No Total Totals :) > > > > SQL Experience: 1 on a scale of 0-10 > > > > any direction would be greatly appreciated, > > Suzanne > > > > > > > > > What is really needed here is a TimeSpan UDT and then sum that. You may
find solution first, but I will add it to my business date lib anyway as that could be useful. See "add working days to a date" in this ng. -- Show quoteWilliam Stacey [MVP] "sack" <s***@discussions.microsoft.com> wrote in message news:A5C6FC73-3D8A-49F2-8A50-286FC80D6531@microsoft.com... > still get the aggregate error. > > I am thinking that I need to migrate the HoursAndSum function into an SQL > UDF. Then use that function in a stored procedure to create a field I can > then SUM ?? > > Where is a good place to learn how to create UDF's? > > thanks, > Suzanne > > "JI" wrote: > >> can you sum(HoursAndMinutes([checkOut]-[checkIn])? >> "sack" <s***@discussions.microsoft.com> wrote in message >> news:03498991-433D-4C4D-9BC4-21D89463C4E6@microsoft.com... >> > using: Access.adp; Report >> > usage: Campus Lab usage: how long has a student been in the lab >> > >> > I can get: daily hours and minutes on the report with: >> > =HoursAndMinutes([CheckOut]-[CheckIn]) >> > >> > HoursAndMinutes function from: >> > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx >> > >> > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) >> > >> > It works great in an Access.mdb file. I get an aggregate error on the >> > .adp >> > report. >> > >> > At a loss as to what to do next? Create a UDF that will do the calc >> > and >> > then call that from the stored procedure that the report is based on? >> > >> > Take it all down to .mdb ? >> > >> > Tell the end users No Total Totals :) >> > >> > SQL Experience: 1 on a scale of 0-10 >> > >> > any direction would be greatly appreciated, >> > Suzanne >> > >> > >> > >> >> >> Hello William,
I have not found a solution :( I am sooooo new to the SQL world that I am not sure what a UDT is (user defined T ?). Going to go look for : See "add working days to a date" in this ng. thanks, Suzanne Show quote "William Stacey [MVP]" wrote: > What is really needed here is a TimeSpan UDT and then sum that. You may > find solution first, but I will add it to my business date lib anyway as > that could be useful. See "add working days to a date" in this ng. > > -- > William Stacey [MVP] > > "sack" <s***@discussions.microsoft.com> wrote in message > news:A5C6FC73-3D8A-49F2-8A50-286FC80D6531@microsoft.com... > > still get the aggregate error. > > > > I am thinking that I need to migrate the HoursAndSum function into an SQL > > UDF. Then use that function in a stored procedure to create a field I can > > then SUM ?? > > > > Where is a good place to learn how to create UDF's? > > > > thanks, > > Suzanne > > > > "JI" wrote: > > > >> can you sum(HoursAndMinutes([checkOut]-[checkIn])? > >> "sack" <s***@discussions.microsoft.com> wrote in message > >> news:03498991-433D-4C4D-9BC4-21D89463C4E6@microsoft.com... > >> > using: Access.adp; Report > >> > usage: Campus Lab usage: how long has a student been in the lab > >> > > >> > I can get: daily hours and minutes on the report with: > >> > =HoursAndMinutes([CheckOut]-[CheckIn]) > >> > > >> > HoursAndMinutes function from: > >> > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > >> > > >> > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > >> > > >> > It works great in an Access.mdb file. I get an aggregate error on the > >> > .adp > >> > report. > >> > > >> > At a loss as to what to do next? Create a UDF that will do the calc > >> > and > >> > then call that from the stored procedure that the report is based on? > >> > > >> > Take it all down to .mdb ? > >> > > >> > Tell the end users No Total Totals :) > >> > > >> > SQL Experience: 1 on a scale of 0-10 > >> > > >> > any direction would be greatly appreciated, > >> > Suzanne > >> > > >> > > >> > > >> > >> > >> > > > OMG ... I just looked at the code for the Add Working Days and fainted. Not
really. I'm not sure that will work for me ... I'm basically just wanting to get the total number of hours and minutes a student has spent in the lab for a semester. I'm thinking if I can convert this VBA code into a SQL UDF I should be able to get it to work. This function could be used in my stored procedure and then sum the results of the function. arrgggg. :) Public Function HoursAndMinutes(interval As Variant) As String '*********************************************************************** ' Function HoursAndMinutes(interval As Variant) As String ' Returns time interval formatted as a hours:minutes string '*********************************************************************** Dim TotalMinutes As Long, totalseconds As Long Dim hours As Long, minutes As Long, seconds As Long If IsNull(interval) = True Then Exit Function hours = Int(CSng(interval * 24)) TotalMinutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins minutes = TotalMinutes Mod 60 totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs seconds = totalseconds Mod 60 If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours HoursAndMinutes = hours & ":" & Format(minutes, "00") End Function thanks Suzanne Show quote "William Stacey [MVP]" wrote: > What is really needed here is a TimeSpan UDT and then sum that. You may > find solution first, but I will add it to my business date lib anyway as > that could be useful. See "add working days to a date" in this ng. > > -- > William Stacey [MVP] > > "sack" <s***@discussions.microsoft.com> wrote in message > news:A5C6FC73-3D8A-49F2-8A50-286FC80D6531@microsoft.com... > > still get the aggregate error. > > > > I am thinking that I need to migrate the HoursAndSum function into an SQL > > UDF. Then use that function in a stored procedure to create a field I can > > then SUM ?? > > > > Where is a good place to learn how to create UDF's? > > > > thanks, > > Suzanne > > > > "JI" wrote: > > > >> can you sum(HoursAndMinutes([checkOut]-[checkIn])? > >> "sack" <s***@discussions.microsoft.com> wrote in message > >> news:03498991-433D-4C4D-9BC4-21D89463C4E6@microsoft.com... > >> > using: Access.adp; Report > >> > usage: Campus Lab usage: how long has a student been in the lab > >> > > >> > I can get: daily hours and minutes on the report with: > >> > =HoursAndMinutes([CheckOut]-[CheckIn]) > >> > > >> > HoursAndMinutes function from: > >> > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > >> > > >> > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > >> > > >> > It works great in an Access.mdb file. I get an aggregate error on the > >> > .adp > >> > report. > >> > > >> > At a loss as to what to do next? Create a UDF that will do the calc > >> > and > >> > then call that from the stored procedure that the report is based on? > >> > > >> > Take it all down to .mdb ? > >> > > >> > Tell the end users No Total Totals :) > >> > > >> > SQL Experience: 1 on a scale of 0-10 > >> > > >> > any direction would be greatly appreciated, > >> > Suzanne > >> > > >> > > >> > > >> > >> > >> > > > You might better off posting this to an Access newgroup.
sack wrote: Show quote > using: Access.adp; Report > usage: Campus Lab usage: how long has a student been in the lab > > I can get: daily hours and minutes on the report with: > =HoursAndMinutes([CheckOut]-[CheckIn]) > > HoursAndMinutes function from: > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > > It works great in an Access.mdb file. I get an aggregate error on the .adp > report. > > At a loss as to what to do next? Create a UDF that will do the calc and > then call that from the stored procedure that the report is based on? > > Take it all down to .mdb ? > > Tell the end users No Total Totals :) > > SQL Experience: 1 on a scale of 0-10 > > any direction would be greatly appreciated, > Suzanne > > > thanks Trey ... I did that as well. I am finding it hard to find support
for the .adp files. It encompasses both Access and t-SQL ... sort of a no-programmers land. Suzanne Show quote "Trey Walpole" wrote: > You might better off posting this to an Access newgroup. > > > sack wrote: > > using: Access.adp; Report > > usage: Campus Lab usage: how long has a student been in the lab > > > > I can get: daily hours and minutes on the report with: > > =HoursAndMinutes([CheckOut]-[CheckIn]) > > > > HoursAndMinutes function from: > > http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > > > > I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > > > > It works great in an Access.mdb file. I get an aggregate error on the .adp > > report. > > > > At a loss as to what to do next? Create a UDF that will do the calc and > > then call that from the stored procedure that the report is based on? > > > > Take it all down to .mdb ? > > > > Tell the end users No Total Totals :) > > > > SQL Experience: 1 on a scale of 0-10 > > > > any direction would be greatly appreciated, > > Suzanne > > > > > > > On Thu, 15 Dec 2005 09:39:03 -0800, sack wrote:
Show quote >using: Access.adp; Report Hi Suzanne,>usage: Campus Lab usage: how long has a student been in the lab > >I can get: daily hours and minutes on the report with: > =HoursAndMinutes([CheckOut]-[CheckIn]) > >HoursAndMinutes function from: >http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > >I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > >It works great in an Access.mdb file. I get an aggregate error on the .adp >report. > >At a loss as to what to do next? Create a UDF that will do the calc and >then call that from the stored procedure that the report is based on? > >Take it all down to .mdb ? > >Tell the end users No Total Totals :) > >SQL Experience: 1 on a scale of 0-10 > >any direction would be greatly appreciated, >Suzanne > > I'm not very experienced with .adp programming, but I do know one thing: if you use a function that is defined on the Access side in a query, the query has to be executed by Access (and hence, it has to use Jet-SQL). If you want to execute the query server-side, use Transact-SQL and make sure that the function is available as a user-defined function on the server as well. On checking the link you gave, I noticed some more things: 1. The function relies on internals of datetime storage in Access. SQL Server uses a different mechanism for storing datetime values. You can't use a straight port. 2. The function returns a character string in hh:mm format. Summing that information in SQL Server is extremely tricky. You'll get lots of implicit data conversions, and a huge chance of a conversion error. If the aim is to report on total elapsed time, why not use a DATEDIFF function to get the elapsed minutes, SUM the results, then massage the result a bit to get the nice hh:mm display: SELECT xx, yy, CAST((MinutesInLab / 60) AS varchar(10)) + ':' + RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) FROM (SELECT xx, yy, SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS MinutesInLab FROM SomeTable WHERE whatever GROUP BY xx, yy) AS derived (untested - see www.aspfaq.com/5006 if you prefer a tested reply) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) thanks Hugo,
I am going to spend the half day that I have today to digest this post. What you said makes sense to me ..... well, as much as t-SQL can make sense to me at this point. I've got to get this application up and running before the spring semester so my stress level has been a bit elevated. I will post the results of applying this approach. regards, Suzanne Show quote "Hugo Kornelis" wrote: > On Thu, 15 Dec 2005 09:39:03 -0800, sack wrote: > > >using: Access.adp; Report > >usage: Campus Lab usage: how long has a student been in the lab > > > >I can get: daily hours and minutes on the report with: > > =HoursAndMinutes([CheckOut]-[CheckIn]) > > > >HoursAndMinutes function from: > >http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > > > >I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > > > >It works great in an Access.mdb file. I get an aggregate error on the .adp > >report. > > > >At a loss as to what to do next? Create a UDF that will do the calc and > >then call that from the stored procedure that the report is based on? > > > >Take it all down to .mdb ? > > > >Tell the end users No Total Totals :) > > > >SQL Experience: 1 on a scale of 0-10 > > > >any direction would be greatly appreciated, > >Suzanne > > > > > > Hi Suzanne, > > I'm not very experienced with .adp programming, but I do know one thing: > if you use a function that is defined on the Access side in a query, the > query has to be executed by Access (and hence, it has to use Jet-SQL). > If you want to execute the query server-side, use Transact-SQL and make > sure that the function is available as a user-defined function on the > server as well. > > On checking the link you gave, I noticed some more things: > 1. The function relies on internals of datetime storage in Access. SQL > Server uses a different mechanism for storing datetime values. You can't > use a straight port. > 2. The function returns a character string in hh:mm format. Summing that > information in SQL Server is extremely tricky. You'll get lots of > implicit data conversions, and a huge chance of a conversion error. > > If the aim is to report on total elapsed time, why not use a DATEDIFF > function to get the elapsed minutes, SUM the results, then massage the > result a bit to get the nice hh:mm display: > > SELECT xx, yy, > CAST((MinutesInLab / 60) AS varchar(10)) + ':' + > RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) > FROM (SELECT xx, yy, > SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS MinutesInLab > FROM SomeTable > WHERE whatever > GROUP BY xx, yy) AS derived > > (untested - see www.aspfaq.com/5006 if you prefer a tested reply) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > If your using Sql2005, here is a project
http://channel9.msdn.com/ShowPost.aspx?PostID=147390 This will allow you to do things like: DECLARE @t table( Name nvarchar(80), TimeIn datetime, TimeOut datetime); insert into @t values('wjs', '12/16/2005 9am', '12/16/2005 5pm') insert into @t values('wjs', '12/15/2005 8am', '12/15/2005 5pm') insert into @t values('wjs', '12/14/2005 9:20am', '12/14/2005 4pm') select SqlTimeSpan::FromTicks(SUM(SqlTimeSpan::FromDates(TimeIn, TimeOut).Ticks)).ToLongDaysHoursMinutesSecondsString() from @t -- Sum using the SqlTimeSpan aggregate. select dbo.SumTimeSpan(SqlTimeSpan::FromDates(TimeIn, TimeOut)).ToShortHoursMinutesString() from @t -- Or select dbo.GetHoursAndMinutesString('12/15/2005 12:33pm', '12/16/2005 2:00pm') as HoursMinutes -- Show quoteWilliam Stacey [MVP] "sack" <s***@discussions.microsoft.com> wrote in message news:7A901781-0A40-4295-9250-368E3F63DEAF@microsoft.com... > thanks Hugo, > > I am going to spend the half day that I have today to digest this post. > What you said makes sense to me ..... well, as much as t-SQL can make > sense > to me at this point. I've got to get this application up and running > before the spring semester so my stress level has been a bit elevated. > > I will post the results of applying this approach. > > regards, Suzanne > > > > "Hugo Kornelis" wrote: > >> On Thu, 15 Dec 2005 09:39:03 -0800, sack wrote: >> >> >using: Access.adp; Report >> >usage: Campus Lab usage: how long has a student been in the lab >> > >> >I can get: daily hours and minutes on the report with: >> > =HoursAndMinutes([CheckOut]-[CheckIn]) >> > >> >HoursAndMinutes function from: >> >http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx >> > >> >I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) >> > >> >It works great in an Access.mdb file. I get an aggregate error on the >> >.adp >> >report. >> > >> >At a loss as to what to do next? Create a UDF that will do the calc >> >and >> >then call that from the stored procedure that the report is based on? >> > >> >Take it all down to .mdb ? >> > >> >Tell the end users No Total Totals :) >> > >> >SQL Experience: 1 on a scale of 0-10 >> > >> >any direction would be greatly appreciated, >> >Suzanne >> > >> > >> >> Hi Suzanne, >> >> I'm not very experienced with .adp programming, but I do know one thing: >> if you use a function that is defined on the Access side in a query, the >> query has to be executed by Access (and hence, it has to use Jet-SQL). >> If you want to execute the query server-side, use Transact-SQL and make >> sure that the function is available as a user-defined function on the >> server as well. >> >> On checking the link you gave, I noticed some more things: >> 1. The function relies on internals of datetime storage in Access. SQL >> Server uses a different mechanism for storing datetime values. You can't >> use a straight port. >> 2. The function returns a character string in hh:mm format. Summing that >> information in SQL Server is extremely tricky. You'll get lots of >> implicit data conversions, and a huge chance of a conversion error. >> >> If the aim is to report on total elapsed time, why not use a DATEDIFF >> function to get the elapsed minutes, SUM the results, then massage the >> result a bit to get the nice hh:mm display: >> >> SELECT xx, yy, >> CAST((MinutesInLab / 60) AS varchar(10)) + ':' + >> RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) >> FROM (SELECT xx, yy, >> SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS MinutesInLab >> FROM SomeTable >> WHERE whatever >> GROUP BY xx, yy) AS derived >> >> (untested - see www.aspfaq.com/5006 if you prefer a tested reply) >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) >> ok ..going to work all day today trying to pull this (your post and Hugo's)
all together. We close for the holidays tomorrow around noon so wanting to get this working before then. I really do appreciate you taking time to post all of this stuff. I would be completely lost otherwise. regards, Suzanne Show quote "William Stacey [MVP]" wrote: > If your using Sql2005, here is a project > http://channel9.msdn.com/ShowPost.aspx?PostID=147390 > > This will allow you to do things like: > DECLARE @t table( > Name nvarchar(80), > TimeIn datetime, > TimeOut datetime); > insert into @t values('wjs', '12/16/2005 9am', '12/16/2005 5pm') > insert into @t values('wjs', '12/15/2005 8am', '12/15/2005 5pm') > insert into @t values('wjs', '12/14/2005 9:20am', '12/14/2005 4pm') > > select SqlTimeSpan::FromTicks(SUM(SqlTimeSpan::FromDates(TimeIn, > TimeOut).Ticks)).ToLongDaysHoursMinutesSecondsString() > from @t > > -- Sum using the SqlTimeSpan aggregate. > select dbo.SumTimeSpan(SqlTimeSpan::FromDates(TimeIn, > TimeOut)).ToShortHoursMinutesString() > from @t > > -- Or > select dbo.GetHoursAndMinutesString('12/15/2005 12:33pm', '12/16/2005 > 2:00pm') as HoursMinutes > > -- > William Stacey [MVP] > > "sack" <s***@discussions.microsoft.com> wrote in message > news:7A901781-0A40-4295-9250-368E3F63DEAF@microsoft.com... > > thanks Hugo, > > > > I am going to spend the half day that I have today to digest this post. > > What you said makes sense to me ..... well, as much as t-SQL can make > > sense > > to me at this point. I've got to get this application up and running > > before the spring semester so my stress level has been a bit elevated. > > > > I will post the results of applying this approach. > > > > regards, Suzanne > > > > > > > > "Hugo Kornelis" wrote: > > > >> On Thu, 15 Dec 2005 09:39:03 -0800, sack wrote: > >> > >> >using: Access.adp; Report > >> >usage: Campus Lab usage: how long has a student been in the lab > >> > > >> >I can get: daily hours and minutes on the report with: > >> > =HoursAndMinutes([CheckOut]-[CheckIn]) > >> > > >> >HoursAndMinutes function from: > >> >http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > >> > > >> >I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > >> > > >> >It works great in an Access.mdb file. I get an aggregate error on the > >> >.adp > >> >report. > >> > > >> >At a loss as to what to do next? Create a UDF that will do the calc > >> >and > >> >then call that from the stored procedure that the report is based on? > >> > > >> >Take it all down to .mdb ? > >> > > >> >Tell the end users No Total Totals :) > >> > > >> >SQL Experience: 1 on a scale of 0-10 > >> > > >> >any direction would be greatly appreciated, > >> >Suzanne > >> > > >> > > >> > >> Hi Suzanne, > >> > >> I'm not very experienced with .adp programming, but I do know one thing: > >> if you use a function that is defined on the Access side in a query, the > >> query has to be executed by Access (and hence, it has to use Jet-SQL). > >> If you want to execute the query server-side, use Transact-SQL and make > >> sure that the function is available as a user-defined function on the > >> server as well. > >> > >> On checking the link you gave, I noticed some more things: > >> 1. The function relies on internals of datetime storage in Access. SQL > >> Server uses a different mechanism for storing datetime values. You can't > >> use a straight port. > >> 2. The function returns a character string in hh:mm format. Summing that > >> information in SQL Server is extremely tricky. You'll get lots of > >> implicit data conversions, and a huge chance of a conversion error. > >> > >> If the aim is to report on total elapsed time, why not use a DATEDIFF > >> function to get the elapsed minutes, SUM the results, then massage the > >> result a bit to get the nice hh:mm display: > >> > >> SELECT xx, yy, > >> CAST((MinutesInLab / 60) AS varchar(10)) + ':' + > >> RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) > >> FROM (SELECT xx, yy, > >> SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS MinutesInLab > >> FROM SomeTable > >> WHERE whatever > >> GROUP BY xx, yy) AS derived > >> > >> (untested - see www.aspfaq.com/5006 if you prefer a tested reply) > >> > >> Best, Hugo > >> -- > >> > >> (Remove _NO_ and _SPAM_ to get my e-mail address) > >> > > > No problem. This is actually a pretty cool project (IMO) to get started in
the CLR side of sql as it has clr UDFs, clr UDTs, and a clr aggregate. And the sql stuff is just thin wrappers around std c# classes and static methods, so you can also use the class(s) in other c# projects. All you should have to do (I think) is load the project, change the connection string to the respective DB and deploy. Then you can run the sql test scripts as shown. May have to add/change the "Use" statements on top as needed. -- Show quoteWilliam Stacey [MVP] "sack" <s***@discussions.microsoft.com> wrote in message news:683B6CFD-FD17-4EA9-8620-5CE1B353217B@microsoft.com... > ok ..going to work all day today trying to pull this (your post and > Hugo's) > all together. We close for the holidays tomorrow around noon so wanting > to > get this working before then. > > I really do appreciate you taking time to post all of this stuff. I > would be completely lost otherwise. > > regards, Suzanne > > "William Stacey [MVP]" wrote: > >> If your using Sql2005, here is a project >> http://channel9.msdn.com/ShowPost.aspx?PostID=147390 >> >> This will allow you to do things like: >> DECLARE @t table( >> Name nvarchar(80), >> TimeIn datetime, >> TimeOut datetime); >> insert into @t values('wjs', '12/16/2005 9am', '12/16/2005 5pm') >> insert into @t values('wjs', '12/15/2005 8am', '12/15/2005 5pm') >> insert into @t values('wjs', '12/14/2005 9:20am', '12/14/2005 4pm') >> >> select SqlTimeSpan::FromTicks(SUM(SqlTimeSpan::FromDates(TimeIn, >> TimeOut).Ticks)).ToLongDaysHoursMinutesSecondsString() >> from @t >> >> -- Sum using the SqlTimeSpan aggregate. >> select dbo.SumTimeSpan(SqlTimeSpan::FromDates(TimeIn, >> TimeOut)).ToShortHoursMinutesString() >> from @t >> >> -- Or >> select dbo.GetHoursAndMinutesString('12/15/2005 12:33pm', '12/16/2005 >> 2:00pm') as HoursMinutes >> >> -- >> William Stacey [MVP] >> >> "sack" <s***@discussions.microsoft.com> wrote in message >> news:7A901781-0A40-4295-9250-368E3F63DEAF@microsoft.com... >> > thanks Hugo, >> > >> > I am going to spend the half day that I have today to digest this post. >> > What you said makes sense to me ..... well, as much as t-SQL can make >> > sense >> > to me at this point. I've got to get this application up and >> > running >> > before the spring semester so my stress level has been a bit elevated. >> > >> > I will post the results of applying this approach. >> > >> > regards, Suzanne >> > >> > >> > >> > "Hugo Kornelis" wrote: >> > >> >> On Thu, 15 Dec 2005 09:39:03 -0800, sack wrote: >> >> >> >> >using: Access.adp; Report >> >> >usage: Campus Lab usage: how long has a student been in the lab >> >> > >> >> >I can get: daily hours and minutes on the report with: >> >> > =HoursAndMinutes([CheckOut]-[CheckIn]) >> >> > >> >> >HoursAndMinutes function from: >> >> >http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx >> >> > >> >> >I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) >> >> > >> >> >It works great in an Access.mdb file. I get an aggregate error on >> >> >the >> >> >.adp >> >> >report. >> >> > >> >> >At a loss as to what to do next? Create a UDF that will do the >> >> >calc >> >> >and >> >> >then call that from the stored procedure that the report is based on? >> >> > >> >> >Take it all down to .mdb ? >> >> > >> >> >Tell the end users No Total Totals :) >> >> > >> >> >SQL Experience: 1 on a scale of 0-10 >> >> > >> >> >any direction would be greatly appreciated, >> >> >Suzanne >> >> > >> >> > >> >> >> >> Hi Suzanne, >> >> >> >> I'm not very experienced with .adp programming, but I do know one >> >> thing: >> >> if you use a function that is defined on the Access side in a query, >> >> the >> >> query has to be executed by Access (and hence, it has to use Jet-SQL). >> >> If you want to execute the query server-side, use Transact-SQL and >> >> make >> >> sure that the function is available as a user-defined function on the >> >> server as well. >> >> >> >> On checking the link you gave, I noticed some more things: >> >> 1. The function relies on internals of datetime storage in Access. SQL >> >> Server uses a different mechanism for storing datetime values. You >> >> can't >> >> use a straight port. >> >> 2. The function returns a character string in hh:mm format. Summing >> >> that >> >> information in SQL Server is extremely tricky. You'll get lots of >> >> implicit data conversions, and a huge chance of a conversion error. >> >> >> >> If the aim is to report on total elapsed time, why not use a DATEDIFF >> >> function to get the elapsed minutes, SUM the results, then massage the >> >> result a bit to get the nice hh:mm display: >> >> >> >> SELECT xx, yy, >> >> CAST((MinutesInLab / 60) AS varchar(10)) + ':' + >> >> RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) >> >> FROM (SELECT xx, yy, >> >> SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS >> >> MinutesInLab >> >> FROM SomeTable >> >> WHERE whatever >> >> GROUP BY xx, yy) AS derived >> >> >> >> (untested - see www.aspfaq.com/5006 if you prefer a tested reply) >> >> >> >> Best, Hugo >> >> -- >> >> >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) >> >> >> >> >> Hi William,
It has been a fun project. I've had the application up and running for 8 years in MS Access (stand alone in about 20 labs). We finally got everything in place enough to have a single data repository. I'm an Access person so using SQL is a whole new ball game. Everything I have worked on to date has been doable ....... until I got to this Time Calc stuff. arrrgggghhhhh ...... I would imagine I will hit some snags once it is up and running in all the labs. I'm also close to having the Total Time In Lab report finished. I don't think it will be the optimal solution but it will get the job done. I'm getting the detail in one report and the total in another. We close for the holidays today through Jan 2nd so hope I don't forget all of this stuff. I must admit that I understand very little of your post :) CLR ? UDT's? clr aggregate? I'm thinking I need to look for a t-SQL for Dummies book. happy holidays, suzanne Show quote "William Stacey [MVP]" wrote: > No problem. This is actually a pretty cool project (IMO) to get started in > the CLR side of sql as it has clr UDFs, clr UDTs, and a clr aggregate. And > the sql stuff is just thin wrappers around std c# classes and static > methods, so you can also use the class(s) in other c# projects. All you > should have to do (I think) is load the project, change the connection > string to the respective DB and deploy. Then you can run the sql test > scripts as shown. May have to add/change the "Use" statements on top as > needed. > > -- > William Stacey [MVP] > > "sack" <s***@discussions.microsoft.com> wrote in message > news:683B6CFD-FD17-4EA9-8620-5CE1B353217B@microsoft.com... > > ok ..going to work all day today trying to pull this (your post and > > Hugo's) > > all together. We close for the holidays tomorrow around noon so wanting > > to > > get this working before then. > > > > I really do appreciate you taking time to post all of this stuff. I > > would be completely lost otherwise. > > > > regards, Suzanne > > > > "William Stacey [MVP]" wrote: > > > >> If your using Sql2005, here is a project > >> http://channel9.msdn.com/ShowPost.aspx?PostID=147390 > >> > >> This will allow you to do things like: > >> DECLARE @t table( > >> Name nvarchar(80), > >> TimeIn datetime, > >> TimeOut datetime); > >> insert into @t values('wjs', '12/16/2005 9am', '12/16/2005 5pm') > >> insert into @t values('wjs', '12/15/2005 8am', '12/15/2005 5pm') > >> insert into @t values('wjs', '12/14/2005 9:20am', '12/14/2005 4pm') > >> > >> select SqlTimeSpan::FromTicks(SUM(SqlTimeSpan::FromDates(TimeIn, > >> TimeOut).Ticks)).ToLongDaysHoursMinutesSecondsString() > >> from @t > >> > >> -- Sum using the SqlTimeSpan aggregate. > >> select dbo.SumTimeSpan(SqlTimeSpan::FromDates(TimeIn, > >> TimeOut)).ToShortHoursMinutesString() > >> from @t > >> > >> -- Or > >> select dbo.GetHoursAndMinutesString('12/15/2005 12:33pm', '12/16/2005 > >> 2:00pm') as HoursMinutes > >> > >> -- > >> William Stacey [MVP] > >> > >> "sack" <s***@discussions.microsoft.com> wrote in message > >> news:7A901781-0A40-4295-9250-368E3F63DEAF@microsoft.com... > >> > thanks Hugo, > >> > > >> > I am going to spend the half day that I have today to digest this post. > >> > What you said makes sense to me ..... well, as much as t-SQL can make > >> > sense > >> > to me at this point. I've got to get this application up and > >> > running > >> > before the spring semester so my stress level has been a bit elevated. > >> > > >> > I will post the results of applying this approach. > >> > > >> > regards, Suzanne > >> > > >> > > >> > > >> > "Hugo Kornelis" wrote: > >> > > >> >> On Thu, 15 Dec 2005 09:39:03 -0800, sack wrote: > >> >> > >> >> >using: Access.adp; Report > >> >> >usage: Campus Lab usage: how long has a student been in the lab > >> >> > > >> >> >I can get: daily hours and minutes on the report with: > >> >> > =HoursAndMinutes([CheckOut]-[CheckIn]) > >> >> > > >> >> >HoursAndMinutes function from: > >> >> >http://tc5.iponet.net/en-us/assistance/HA011102181033.aspx > >> >> > > >> >> >I cannot get: =HoursAndMinutes(Sum([CheckOut]-[CheckIn])) > >> >> > > >> >> >It works great in an Access.mdb file. I get an aggregate error on > >> >> >the > >> >> >.adp > >> >> >report. > >> >> > > >> >> >At a loss as to what to do next? Create a UDF that will do the > >> >> >calc > >> >> >and > >> >> >then call that from the stored procedure that the report is based on? > >> >> > > >> >> >Take it all down to .mdb ? > >> >> > > >> >> >Tell the end users No Total Totals :) > >> >> > > >> >> >SQL Experience: 1 on a scale of 0-10 > >> >> > > >> >> >any direction would be greatly appreciated, > >> >> >Suzanne > >> >> > > >> >> > > >> >> > >> >> Hi Suzanne, > >> >> > >> >> I'm not very experienced with .adp programming, but I do know one > >> >> thing: > >> >> if you use a function that is defined on the Access side in a query, > >> >> the > >> >> query has to be executed by Access (and hence, it has to use Jet-SQL). > >> >> If you want to execute the query server-side, use Transact-SQL and > >> >> make > >> >> sure that the function is available as a user-defined function on the > >> >> server as well. > >> >> > >> >> On checking the link you gave, I noticed some more things: > >> >> 1. The function relies on internals of datetime storage in Access. SQL > >> >> Server uses a different mechanism for storing datetime values. You > >> >> can't > >> >> use a straight port. > >> >> 2. The function returns a character string in hh:mm format. Summing > >> >> that > >> >> information in SQL Server is extremely tricky. You'll get lots of > >> >> implicit data conversions, and a huge chance of a conversion error. > >> >> > >> >> If the aim is to report on total elapsed time, why not use a DATEDIFF > >> >> function to get the elapsed minutes, SUM the results, then massage the > >> >> result a bit to get the nice hh:mm display: > >> >> > >> >> SELECT xx, yy, > >> >> CAST((MinutesInLab / 60) AS varchar(10)) + ':' + > >> >> RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) > >> >> FROM (SELECT xx, yy, > >> >> SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS > >> >> MinutesInLab > >> >> FROM SomeTable > >> >> WHERE whatever > >> >> GROUP BY xx, yy) AS derived > >> >> > >> >> (untested - see www.aspfaq.com/5006 if you prefer a tested reply) > >> >> > >> >> Best, Hugo > >> >> -- > >> >> > >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > >> >> > >> > >> > >> > > > On Wed, 21 Dec 2005 06:49:03 -0800, sack wrote:
(snip) >I must admit that I understand very little of your post :) CLR ? Hi Suzanne,>UDT's? clr aggregate? I'm thinking I need to look for a t-SQL for >Dummies book. You'll need a specific SQL Server 2005 book. And not a "for dummies", but an "advanced". CLR is "common language runtime" (or something like that); this refers to the integration of .NET components in SQL Server. Basically, you can code custom routines in .NET, compile them, then call use them as if they're part of SQL Server. UDT is User-defined type. In this context not refering to the old user-defined datatypes created with sp_addtype, but to a special type of CLR component that allows you to create your own datatype. CLR aggregate is another special type of CLR component, that allows you to create your own aggregates for use in GROUP BY queries. Note: All this works only on SQL Server 2005. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hugo,
I just wanted to let you know that the following code you sent me is working like a charm!!!! Classes start Tuesday and I am feeling confident that I can have the reports done within a week or so. Or at least enough of them to make the users happy. Thank you soooooo much. regards, Suzanne Show quote > SELECT xx, yy, > CAST((MinutesInLab / 60) AS varchar(10)) + ':' + > RIGHT('0' + CAST((MinutesInLab % 60) AS varchar(2)), 2) > FROM (SELECT xx, yy, > SUM(DATEDIFF(minute, CheckOut, CheckIn)) AS MinutesInLab > FROM SomeTable > WHERE whatever > GROUP BY xx, yy) AS derived > On Fri, 13 Jan 2006 09:19:02 -0800, sack wrote:
>Hugo, Hi Suzanne,> >I just wanted to let you know that the following code you sent me is working >like a charm!!!! Classes start Tuesday and I am feeling confident that I >can have the reports done within a week or so. Or at least enough of them to >make the users happy. > >Thank you soooooo much. Posts like this can really make my day. Thanks for letting me know that my suggestions were useful to you! -- Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics