464,761 Members | 1,009 Online
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

Difference between rows

 P: n/a Hi, I have a table which is something like this : Date Hour Minute Name Cost 12/12/01 2 4 ABC 15 12/12/01 2 15 ABC 7 12/12/01 2 55 ABC 8 12/12/01 2 13 XYZ 88 12/12/01 4 17 ABC 20 12/12/01 4 49 ABC 66 12/12/01 4 8 DEF 99 12/12/02 1 9 XYZ 11 I would like to write a SQL query that would generate this result : Date Hour Name TotalCost 12/12/01 2 ABC (15-4)*15)+ (55-15)*7 + (60-55)*8 12/12/01 2 XYZ (60-13)*88 12/12/01 4 ABC (49-17)*20 + (60-49)*66 12/12/01 4 DEF (60-8)*99 12/12/02 1 XYZ (60-9)*11 For each unique [date hour name], compute the total cost that would be individual costs multiplied by the difference in minutes. Any suggestions would be genuinely appreciated. Thanks, Sam Nov 12 '05 #1
6 Replies

 P: n/a Sameer wrote: Hi, I have a table which is something like this : Date Hour Minute Name Cost 12/12/01 2 4 ABC 15 12/12/01 2 15 ABC 7 12/12/01 2 55 ABC 8 12/12/01 2 13 XYZ 88 12/12/01 4 17 ABC 20 12/12/01 4 49 ABC 66 12/12/01 4 8 DEF 99 12/12/02 1 9 XYZ 11 I would like to write a SQL query that would generate this result : Date Hour Name TotalCost 12/12/01 2 ABC (15-4)*15)+ (55-15)*7 + (60-55)*8 12/12/01 2 XYZ (60-13)*88 12/12/01 4 ABC (49-17)*20 + (60-49)*66 12/12/01 4 DEF (60-8)*99 12/12/02 1 XYZ (60-9)*11 For each unique [date hour name], compute the total cost that would be individual costs multiplied by the difference in minutes. Any suggestions would be genuinely appreciated. Yeah. Don't use queries for the process. Use recordsets. Otherwise, call a function within the query. You could create a function that computes the total cost based on the date, hour, and name that is passed to it. You could create a column called TotalCost : ComputeTotalCost({DateField],[HourField],[NameField]) and in a module create a function to compute the costs. Nov 12 '05 #2

 P: n/a "Sameer" wrote: Hi, I have a table which is something like this : Date Hour Minute Name Cost 12/12/01 2 4 ABC 15 12/12/01 2 15 ABC 7 12/12/01 2 55 ABC 8 12/12/01 2 13 XYZ 88 12/12/01 4 17 ABC 20 12/12/01 4 49 ABC 66 12/12/01 4 8 DEF 99 12/12/02 1 9 XYZ 11 I would like to write a SQL query that would generate this result : Date Hour Name TotalCost 12/12/01 2 ABC (15-4)*15)+ (55-15)*7 + (60-55)*8 12/12/01 2 XYZ (60-13)*88 12/12/01 4 ABC (49-17)*20 + (60-49)*66 12/12/01 4 DEF (60-8)*99 12/12/02 1 XYZ (60-9)*11 For each unique [date hour name], compute the total cost that would be individual costs multiplied by the difference in minutes. Any suggestions would be genuinely appreciated. Thanks, Sam A two steps solution, due some sql restrictions (btw, you shoud'nt use reserved words like Date, Hour, Minute as field names): Query1: SELECT Date, Hour, Name, Nz( ( SELECT Min(Minute) FROM Table as T WHERE T.Date = Table.Date And T.Hour = Table.Hour And t.Name = Table.Name and T.Minute > Table.Minute), 60 ) - Minute ) * Cost AS PartialCost FROM Table; Query2: SELECT Date, Hour, Name, Sum(PartialCost) AS TotalCost FROM Query1 GROUP BY Date, Hour, Name; hth Roberto Nov 12 '05 #3

 P: n/a Thanks Roberto, That was very helpful. "Roberto Spier" wrote in message news:... A two steps solution, due some sql restrictions (btw, you shoud'nt use reserved words like Date, Hour, Minute as field names): Query1: SELECT Date, Hour, Name, Nz( ( SELECT Min(Minute) FROM Table as T WHERE T.Date = Table.Date And T.Hour = Table.Hour And t.Name = Table.Name and T.Minute > Table.Minute), 60 ) - Minute ) * Cost AS PartialCost FROM Table; Query2: SELECT Date, Hour, Name, Sum(PartialCost) AS TotalCost FROM Query1 GROUP BY Date, Hour, Name; hth Roberto Nov 12 '05 #4

 P: n/a Hi Roberto, This query works very well in MsAccess. However, if i write this in VB, it gives me an error indicating Nz as an invalid function. I tried using Query Defs as well, where you execute the query in MsAccess. That too gives the same error. I then tried using ADODB commands and that gives me an error on the SELECT command. I am using the latest version of ADODB, just wondering does ADODB and DAO(3.6) support the Nz function call?. Any comments would be truly appreciated. Sameer "Roberto Spier" wrote in message news:... Query1: SELECT Date, Hour, Name, Nz( ( SELECT Min(Minute) FROM Table as T WHERE T.Date = Table.Date And T.Hour = Table.Hour And t.Name = Table.Name and T.Minute > Table.Minute), 60 ) - Minute ) * Cost AS PartialCost FROM Table; Query2: SELECT Date, Hour, Name, Sum(PartialCost) AS TotalCost FROM Query1 GROUP BY Date, Hour, Name; hth Roberto Nov 12 '05 #5

 P: n/a This isn't the best place to get answers for accessing Jet databases (yes, they call them "Access databases", but Access isn't involved) from VB. We tend to assume that questions here are about Access database applications, and NZ is a builtin function in Access so is available to Access queries. It is not a builtin function of Jet SQL, as far as I know. Larry Linson Microsoft Access MVP "Sameer" wrote in message news:7d**************************@posting.google.c om... Hi Roberto, This query works very well in MsAccess. However, if i write this in VB, it gives me an error indicating Nz as an invalid function. I tried using Query Defs as well, where you execute the query in MsAccess. That too gives the same error. I then tried using ADODB commands and that gives me an error on the SELECT command. I am using the latest version of ADODB, just wondering does ADODB and DAO(3.6) support the Nz function call?. Any comments would be truly appreciated. Sameer "Roberto Spier" wrote in message news:... Query1: SELECT Date, Hour, Name, Nz( ( SELECT Min(Minute) FROM Table as T WHERE T.Date = Table.Date And T.Hour = Table.Hour And t.Name = Table.Name and T.Minute > Table.Minute), 60 ) - Minute ) * Cost AS PartialCost FROM Table; Query2: SELECT Date, Hour, Name, Sum(PartialCost) AS TotalCost FROM Query1 GROUP BY Date, Hour, Name; hth Roberto Nov 12 '05 #6