Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThoseWhereTheDays
Starting Member
2 Posts |
 Posted - 2010-02-12 : 10:00:01
|
| Case: I need only the last weighing time(s) returned for a multivalued parameter instead of all weighing time records, according query-------------------------------------------------------SELECT MAX( CONVERT(datetime, dbo.GetRoundedDateTime(tblA.tTime, @TimePartID), 20) ) AS tLastWeighingTimeConverted ,tblP.cCode AS cPCFROM tblAJOIN tblB ON tblB.ID = tblA.iB JOIN tblT ON tblT.ID = tblB.iTJOIN tblL ON tblL.ID = tblT.iLJOIN tblP ON tblB.iP = tblP.IDWHERE ( @LC is NULL OR tblL.cCode = @LC )AND ( @PC is NULL OR tblP.cCode IN ( SELECT ExtratedPC FROM dbo.TablePC( @PC ) ) )GROUP BY dbo.GetRoundedDateTime(tblA.tTime, @TimePartID) ,tblP.cCode ORDER BY dbo.GetRoundedDateTime(tblA.tTime, @TimePartID) DESC-------------------------------------------------------I expected to get a 4 rows table returned: each row the MAX value of tLatestRecordedTime against each part of the multivalued parameter string===================================tLatestRecordedTime(cPC =1) -- @PC = 1tLatestRecordedTime(cPC =2) -- @PC = 2tLatestRecordedTime(cPC =3) -- @PC = 3tLatestRecordedTime(cPC =4) -- @PC = 4===================================Surprisingly, for each part of the multivalued parameter string I get ALL recorded times (as tlatestRecordedTime) returned.Conclusion: MAX() function does not do what I expected it should do. Note worthy is:@PC = '1,2,3,4' a string of de multivalued parameter to be chosen in SSRS Header@LC = '1' however can be NULL (to get all LC's returned)@TimePartID = 5 (this is an internal function by which I get a rounded datetime returned in the following format: 1:yyyy-01-01 00:00; 2:yyyy-MM-01 00:00; 3:yyyy-MM-dd 00:00;4:yyyy-MM-dd HH:00; 5:yyyy-MM-dd HH:mm;6 and so on ...According suggestions of "sweapBoogie - MS SQL Development forum" I kept fooling myself keeping the "dbo.GetRoundedDateTime(tblA.tTime, @TimePartID)" in the ORDER BY clause telling me: Column "tblA.tTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.I needed to delete "dbo.GetRoundedDateTime(tblA.tTime, @TimePartID)" from the GROUP BY clause and store its alias "tLastWeighingTime" into the ORDER BY clause. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
MAX() dt-column against part of multival parameter