June 28, 2009 at 11:35 am
Hi
I have the followin query which I am trying to rewrite in MS SQL. It is in MS Access and uses the First Clause.
SELECT First(BATCHES.CampaignNo) AS FirstOfCampaignNo, First(tblStoppagesBR.Category) AS FirstOfCategory, tblStoppagesBR.ReasonForDownTime, Sum(tblStoppagesBR.LostHrs) AS SumOfLostHrs
FROM BATCHES LEFT JOIN tblStoppagesBR ON BATCHES.autoNum = tblStoppagesBR.BatchRefNo
GROUP BY tblStoppagesBR.ReasonForDownTime
HAVING (((First(BATCHES.CampaignNo))="C22009-1141"));
Result:
FirstOfCampaignNoFirstOfCategoryReason For Down Time:SumOfLostHrs
C22009-1141M/T CorrectiveEquipment leaking517
C22009-1141OtherProblem with blockage6
C22009-1141M/T CorrectiveSeals2.5
C22009-1141Equipment Not AvailableWaiting for parts9.5
June 28, 2009 at 11:38 am
sorry pressed send by mistake before completing. If I did not First Clause in the Category field my result would be different as you can see. Does anyone know how I can get the same result without using First in MS SQL server 2008.
FirstOfCampaignNoCategory:Reason For Down Time:SumOfLostHrs
C22009-1141Equipment Not AvailableWaiting for parts9.5
C22009-1141M/T CorrectiveEquipment leaking469
C22009-1141M/T CorrectiveSeals2.5
C22009-1141OtherProblem with blockage6
C22009-1141Process ProblemOther Batch's/Cleaning Prioritised5
Thank you very much.
June 28, 2009 at 11:54 am
Well, first you need to tell us what you consider to be the 'first' value. Tables have no order, so any row is equally valid as the 'first'
Why would row C22009-1141 Process Problem Other Batch's/Cleaning Prioritised 5 not appear? The Reason for Downtime differs from all others in the example. I also can't see why the sum of lost hours would be different for C22009-1141 M/T Corrective Equipment leaking between the two.
Could you please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2009 at 12:12 pm
hmmm your making me rethink why I want to include certain columns at all when only showing first of looses the relavance of the data. Maybe I dont need this. I need to rethink what I want and why.
Thank
You 🙂
June 28, 2009 at 12:18 pm
keil.asta (6/28/2009)
hmmm your making me rethink why I want to include certain columns at all when only showing first of looses the relavance of the data. Maybe I dont need this. I need to rethink what I want and why.Thank
You 🙂
Any time. :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply