SQL Query help

[DnetMHZ]

Lets say I have a table called Table1 with the following columns/data.

ItemNumber, Value, ValueDate

0001, $10.00, 01/01/2005
0001, $20.00, 02/01/2005
1234, $15.00, 01/01/2005
1234, $30.00, 02/01/2005

As you can see each item may appear multiple times. What I need to do is get the total
value of all the items using the most current valuedate. In other words sum the most recent single value of each item.

Given the data above the result should be $50.00
Item 0001 @ $20 + Item 1234 @ $30


Thanks in advance
DnetMHZ

 

[wkinney]

Originally posted by: DnetMHZ
Lets say I have a table called Table1 with the following columns/data.

ItemNumber, Value, ValueDate

0001, $10.00, 01/01/2005
0001, $20.00, 02/01/2005
1234, $15.00, 01/01/2005
1234, $30.00, 02/01/2005

As you can see each item may appear multiple times. What I need to do is get the total
value of all the items using the most current valuedate. In other words sum the most recent single value of each item.

Given the data above the result should be $50.00
Item 0001 @ $20 + Item 1234 @ $30


Thanks in advance
DnetMHZ

http://www.w3schools.com/sql/sql_groupby.asp

 

[DnetMHZ]

Originally posted by: wkinney

Originally posted by: DnetMHZ
Lets say I have a table called Table1 with the following columns/data.

ItemNumber, Value, ValueDate

0001, $10.00, 01/01/2005
0001, $20.00, 02/01/2005
1234, $15.00, 01/01/2005
1234, $30.00, 02/01/2005

As you can see each item may appear multiple times. What I need to do is get the total
value of all the items using the most current valuedate. In other words sum the most recent single value of each item.

Given the data above the result should be $50.00
Item 0001 @ $20 + Item 1234 @ $30


Thanks in advance
DnetMHZ

http://www.w3schools.com/sql/sql_groupby.asp

It's a little more complicated than just a grouping.

 

[MrChad]

Originally posted by: DnetMHZ
Lets say I have a table called Table1 with the following columns/data.

ItemNumber, Value, ValueDate

0001, $10.00, 01/01/2005
0001, $20.00, 02/01/2005
1234, $15.00, 01/01/2005
1234, $30.00, 02/01/2005

As you can see each item may appear multiple times. What I need to do is get the total
value of all the items using the most current valuedate. In other words sum the most recent single value of each item.

Given the data above the result should be $50.00
Item 0001 @ $20 + Item 1234 @ $30


Thanks in advance
DnetMHZ

Assuming that ValueDate is unique per Item:

SELECT SUM(Value)
FROM (SELECT ItemNumber, Value FROM Table1 WHERE ValueDate = (SELECT MAX(ValueDate) FROM Table1 SubTable WHERE SubTable.ItemNumber = Table1.ItemNumber))

 

[DnetMHZ]

Originally posted by: MrChad

Originally posted by: DnetMHZ
Lets say I have a table called Table1 with the following columns/data.

ItemNumber, Value, ValueDate

0001, $10.00, 01/01/2005
0001, $20.00, 02/01/2005
1234, $15.00, 01/01/2005
1234, $30.00, 02/01/2005

As you can see each item may appear multiple times. What I need to do is get the total
value of all the items using the most current valuedate. In other words sum the most recent single value of each item.

Given the data above the result should be $50.00
Item 0001 @ $20 + Item 1234 @ $30


Thanks in advance
DnetMHZ

Assuming that ValueDate is unique per Item:

SELECT SUM(Value)
FROM (SELECT ItemNumber, Value FROM Table1 WHERE ValueDate = (SELECT MAX(ValueDate) FROM Table1 SubTable WHERE SubTable.ItemNumber = Table1.ItemNumber))

Thanks so much MrChad, you come through once again.

DnetMHZ

 

[Velk]

Originally posted by: DnetMHZ

Originally posted by: MrChad

Originally posted by: DnetMHZ
Lets say I have a table called Table1 with the following columns/data.

ItemNumber, Value, ValueDate

0001, $10.00, 01/01/2005
0001, $20.00, 02/01/2005
1234, $15.00, 01/01/2005
1234, $30.00, 02/01/2005

As you can see each item may appear multiple times. What I need to do is get the total
value of all the items using the most current valuedate. In other words sum the most recent single value of each item.

Given the data above the result should be $50.00
Item 0001 @ $20 + Item 1234 @ $30


Thanks in advance
DnetMHZ

Assuming that ValueDate is unique per Item:

SELECT SUM(Value)
FROM (SELECT ItemNumber, Value FROM Table1 WHERE ValueDate = (SELECT MAX(ValueDate) FROM Table1 SubTable WHERE SubTable.ItemNumber = Table1.ItemNumber))

Thanks so much MrChad, you come through once again.

That will work given the assumptions, but are you 100% confident that there will never be a circumstance where you will have two entries for the same item number on the same day ?

I find this one is very rarely true in practice, as inconvenient as that is.

 

[mugs]

Originally posted by: Velk

That will work given the assumptions, but are you 100% confident that there will never be a circumstance where you will have two entries for the same item number on the same day ?

I find this one is very rarely true in practice, as inconvenient as that is.

If that were the case, it would likely cause problems in other areas of the application as well. And if it were the case, they would probably store the time in the ValueDate field as well. Which they may actually be doing, and he just didn't provide that information.

 

[Velk]

Originally posted by: mugs

Originally posted by: Velk

That will work given the assumptions, but are you 100% confident that there will never be a circumstance where you will have two entries for the same item number on the same day ?

I find this one is very rarely true in practice, as inconvenient as that is.

If that were the case, it would likely cause problems in other areas of the application as well. And if it were the case, they would probably store the time in the ValueDate field as well. Which they may actually be doing, and he just didn't provide that information.

Oh you might be surprised, personally I would be astonished if there was a unique index on the date/part number columns. Hence, I suggest the OP check that it's true before going with the solution suggested upthread - if it is, all good - if it's not he will need to re-think it.

 

[DnetMHZ]

Just to clarify, each item is given a value once a month. 1 item will never have 2 entries with the same date.