Query problems - Group By and Latest date

Hi all,

hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.

There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:

SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber

What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:

SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03

What i really need is to only list the last part that is installed, hence the one with the latest timestamp:

SerialNumber Param1 TimeStamp

0 Part1 15/03/07

0 Part2 16/03/07

0 Part3 15/03/03

Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.

Many thanks in advance,

Grant

This should do the trick:

SELECT [SerialNumber]
,Param1
,MAX([TimeStamp])
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
ORDER BY SerialNumber

You only need to take the max of your timestamp field (and remove it from the group by). The group by all fields is a bit extreme in the previous query (you could use the distinct keyword instead if you had apparent duplicate rows (i.e. replaced the part 3 times in a day).

|||Thats sorted it.
I wasn't as far of the mark in the first place as i'd thought. Thank's very much for the assistance, its much appreciated.

Cheers,

Grant|||Hi, apologies but i need one more piece of advice on this subject.

If i want to include a column with a serial number of the part that has been replaced, how would i do that. As soon as i add it, it needs to be part of an aggregate function or the group by clause. When it becomes part of the group by clause it then duplicates the part again.

Any ideas?

Thanks,

Grant|||

Is the serial number of the part in the same table - if so presumably it is different for each time that part is replaced. You can use a nested query to get that - however it will run into a problem if there are multiple records with the same date. As it stands at the moment you could not distinuish between them.

If you had records:

SerialNumber Param1 TimeStamp Part_SN
0 Part1 15/03/07 1234
0 Part2 15/03/07 1235
0 Part2 16/03/07 1236
0 Part2 16/03/07 1237
0 Part3 15/03/03 1238

How would you know which of the two Part2 items fitted on 16 Mar to give the serial number of? If there are additional fields to determine this then we need to use them

If this does not arise then the query below should serve (substitute correct fieldname for Part_SN):

SELECT B.[SerialNumber]
,B.Param1
,B.[TimeStamp]
,B.Part_SN
FROM (
SELECT [SerialNumber]
,Param1
,MAX([TimeStamp]) AS TimeStamp
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
) A
INNER JOIN [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL] B
ON (A.[SerialNumber] = B.[SerialNumber]) AND
(A.Param1 = B.Param1) AND
(A.[TimeStamp] = B.[TimeStamp]) AND
(B.Test = 'T120')
ORDER BY B.[SerialNumber]

If this is a problem then you will get multiple records in that case - one for each serial number. If the TimeStamp is a datetime which includes the time of the replacement then this will not be an issue (as long as the required serial number is the last record.

|||Thanks,

That is exactly what i wanted to do. Works like a charm.

Grantsql

Query problem - top 5 validation

Hi,
I have a problem to run a query like this scenario:
i have players in a table who have scored in many matches and i want to take
the 5 latest.
The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
finally 0.2.
playerid round score
1, 10, 122
1, 9, 123
1, 8, 222
1, 7, 333
1, 6, 222
And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
Even handle if there are only 3 score.
Help, who can i do that in sql?
Regards
TWHi
It is always better to post DDL and example data see
http://www.aspfaq.com/etiquett?e.asp?id=5006
If the relationship between rounds is constant then you can use something
like:
CREATE TABLE #scores ( playerid int, [round] int, score decimal(8,3))
INSERT INTO #scores ( playerid, [round], score )
SELECT 1, 10, 122
UNION ALL SELECT 1, 9, 123
UNION ALL SELECT 1, 8, 222
UNION ALL SELECT 1, 7, 333
UNION ALL SELECT 1, 6, 222
INSERT INTO #scores ( playerid, [round], score )
SELECT 2, 9, 111
UNION ALL SELECT 2, 8, 222
UNION ALL SELECT 2, 7, 333
SELECT p.playerid,SUM(p.score*(1-(0.2*p.rank)))
FROM ( SELECT playerid,score,
(SELECT COUNT(*) FROM #scores s where s.[round] > r.[round] AND s.playerid =
r.playerid) as Rank
from #scores r ) p
where p.rank < 6
GROUP BY p.playerid
Alternatively use a table to store the multiplying factor.
John
"tw" <tw@.tactics.se> wrote in message
news:uyI$86lTFHA.616@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a problem to run a query like this scenario:
> i have players in a table who have scored in many matches and i want to
> take the 5 latest.
> The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4
> and finally 0.2.
> playerid round score
> 1, 10, 122
> 1, 9, 123
> 1, 8, 222
> 1, 7, 333
> 1, 6, 222
> And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
> Even handle if there are only 3 score.
> Help, who can i do that in sql?
> Regards
> TW
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess
CREATE TABLE Scorecards
(player_id INTEGER NOT NULL,
round_nbr INTEGER NOT NULL,
PRIMARY KEY (player_id, round_nbr),
score INTEGER NOT NULL);
INSERT INTO Scorecards VALUES (1, 10, 122);
INSERT INTO Scorecards VALUES (1, 9, 123);
INSERT INTO Scorecards VALUES (1, 8, 222);
INSERT INTO Scorecards VALUES (1, 7, 333);
INSERT INTO Scorecards VALUES (1, 6, 222);
INSERT INTO Scorecards VALUES (1, 5, 222);
SELECT W.player_id, SUM(W.weighted_score)
FROM (
SELECT S1.player_id,
(S1.score
* CASE WHEN round_nbr
= (SELECT MAX(round_nbr)
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 1.0
WHEN round_nbr
= (SELECT MAX(round_nbr)-1
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.8
WHEN round_nbr
= (SELECT MAX(round_nbr)-2
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.6
WHEN round_nbr
= (SELECT MAX(round_nbr)-3
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.4
WHEN round_nbr
= (SELECT MAX(round_nbr)-4
FROM Scorecards AS S2
WHERE S1.player_id = S2.player_id)
THEN 0.2 ELSE 0.0 END)
FROM Scorecards AS S1)
AS W(player_id, weighted_score)
GROUP BY W.player_id;
Put this in a VIEW so the system will update it for you. You can also
use the proprietary, non-portable TOP option if you do not care about
standards. It might be faster in SQL Server, but a good optimizer
would pull out the common expressions in the WHEN clauses and compute
each MAX() only once, probably getting it from an index on the
primarykey. TOP has to do a partition or sort under the covers.|||Hi John,
Im very thankful of your sql code, it works perfect.
Sorry about not post dll, it was new for me.
How should the sql string look if i have a table with multiplying
factor?
tw
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
You would need to add table (this one if temporary for demonstration)
CREATE TABLE #Multipliers ( Rank int, factor decimal(8,3) )
INSERT INTO #Multipliers ( Rank, factor )
SELECT 1, 1
UNION ALL SELECT 2, 0.8
UNION ALL SELECT 3, 0.6
And change the query to something like:
SELECT p.playerid,SUM(p.score*m.factor)
FROM ( SELECT playerid,score,
(SELECT COUNT(*) FROM #scores s where s.[round] > r.[round] AND s.playerid =
r.playerid) as Rank
from #scores r ) p
JOIN #Multipliers m ON p.rank = m.rank -- restricted by number of entries
in #Multipliers
GROUP BY p.playerid
You could also fit this into Joes solution.
John
"t w" <tw@.tactics.se> wrote in message
news:uVASgDnTFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> Im very thankful of your sql code, it works perfect.
> Sorry about not post dll, it was new for me.
> How should the sql string look if i have a table with multiplying
> factor?
> tw
> *** Sent via Developersdex http://www.examnotes.net ***

query problem

Hi,
I have a problem to run a query like this scenario:
i have players in a table who have scored in many matches and i want to take
the 5 latest.
The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
finally 0.2.
playerid round score
1, 10, 122
1, 9, 123
1, 8, 222
1, 7, 333
1, 6, 222
And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
Even handle if there are only 3 score.
Help, who can i do that in sql?
Regards
TW
On Sun, 1 May 2005 17:05:50 +0200, tw wrote:

>Hi,
>I have a problem to run a query like this scenario:
>i have players in a table who have scored in many matches and i want to take
>the 5 latest.
>The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
>finally 0.2.
>playerid round score
>1, 10, 122
>1, 9, 123
>1, 8, 222
>1, 7, 333
>1, 6, 222
>And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
>Even handle if there are only 3 score.
>Help, who can i do that in sql?
>Regards
>TW
>
Hi TW,
The following is untested. Please see www.aspfaq.com/5006 to find out
what you should post to enable me to test my suggestions:
SELECT s.playerid, SUM (f.factor * s.score)
FROM ScoreTable AS s
INNER JOIN (SELECT 1, 1.0 UNION ALL
SELECT 2, 0.8 UNION ALL
SELECT 3, 0.6 UNION ALL
SELECT 4, 0.4 UNION ALL
SELECT 5, 0.2) AS f(round, factor)
ON round = (SELECT COUNT(*)
FROM ScoreTable AS s2
WHERE s2.playerid = s.playerid
AND s2.round >= s.round)
GROUP BY s.playerid
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)