Difference between rows

[Sameer]

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

[Salad]

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.

[Roberto Spier]

"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

[Sameer]

Thanks Roberto,
That was very helpful.

"Roberto Spier" <fo*****@invalid.com> wrote in message news:<bs************@ID-66191.news.uni-berlin.de>...

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

[Sameer]

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" <fo*****@invalid.com> wrote in message news:<bs************@ID-66191.news.uni-berlin.de>...

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

[Larry Linson]

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" <sw***@yahoo.com> 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" <fo*****@invalid.com> wrote in message

news:<bs************@ID-66191.news.uni-berlin.de>...

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

[Douglas J. Steele]

You're right, Larry: Nz isn't a builtin function of Jet SQL. However, I
think IIf is, so that you can use IIf(IsNull(A), A, 0) instead of Nz(A, 0)

However, I don't believe Jet's going to allow the SQL statement that Sameer
has inside the Nz function, even running inside of Access.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Larry Linson" <bo*****@localhost.not> wrote in message
news:Hi****************@nwrddc01.gnilink.net...

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" <sw***@yahoo.com> 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" <fo*****@invalid.com> wrote in message

news:<bs************@ID-66191.news.uni-berlin.de>...

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