CODEWITHSUNDEEP
sql-serversql-server-2005
Arsenal
Arsenal

Reputation: 362

MS SQL 2005: Problems with alias in subquery

Ok, so I'm a SQL noob and the way I'm trying to get this done is probably not the best - and as of now it doesn't even work, so here goes:

What I have is a table full of calibration data. The devices are identified by a serialnumber (column Serial), each device can have multiple calibration runs identified by the RunID. There are a lot of different things which get calibrated, and those values are all stored in the CalValue column. To identify which row contains what calibration, the column CalID exists. What I'm trying to get is the CalValue of three different CalIDs when they all differ from some standard values. As a device can have multiple runs, I'm only interested in the most recent one.

To illustrate that:

# Serial    #    RunID    #    CalValue   #    CalID    #
#      1    #        0    #    0.5        #        13   #
#      1    #        0    #    0.8        #        24   #
#      1    #        0    #    0.2        #        35   #
#      1    #        1    #    0.5        #        13   #
#      1    #        1    #    0.3        #        24   #
#      1    #        1    #    0.4        #        35   #
#      1    #        2    #    0.0        #        13   #
#      1    #        2    #   -2.3        #        24   #
#      1    #        2    #    0.6        #        35   #
#      2    #        0    #    0.0        #        13   #
#      2    #        0    #    0.0        #        24   #
#      2    #        0    #    0.0        #        35   #
#      2    #        1    #    0.6        #        13   #
#      2    #        1    #    0.7        #        24   #
#      2    #        1    #    0.8        #        35   #
#      2    #        2    #    0.0        #        13   #
#      2    #        2    #    0.0        #        24   #
#      2    #        2    #    0.0        #        35   #

What I ideally want to get is this:

# Serial    #    CalValue.ID=13    #    CalValue.ID=24   #    CalValue.ID=35    #
#      1    #        0.5           #          0.3        #        0.6           #
#      2    #        0.6           #          0.7        #        0.8           #

The values for Serial 1 were selected because the last all non-default values were stored in RunID = 1 rows. Serial 2 had also multiple runs, where the first and the third run only gave some standard values, so the values from the second run are selected.

So what I'm trying to do is to join tables, where I filtered out the standard values and then pick out only the values with the highest RunID. I've tried multiple things, ultimatively running into "The column CalValue was specified multiple times". I have not a real clue if my solution would actually work otherwise, but here is my approach:

WITH subq3 AS (
SELECT subq0.Serial AS Serial, subq0.RunID AS RunID, subq0.CalValue AS TRth0, subq1.CalValue AS TRth1, subq2.CalValue AS TRth2 
FROM CalibrationData AS subq0
INNER JOIN CalibrationData AS subq1 ON (subq0.Serial = subq1.Serial AND subq0.RunID = subq1.RunID AND ((subq1.CalID=24) AND (subq1.CalValue<>$0.0 And subq1.CalValue<>$0.03))) 
INNER JOIN CalibrationData AS subq2 ON (subq0.Serial = subq2.Serial AND subq0.RunID = subq2.RunID AND ((subq2.CalID=35) AND (subq2.CalValue<>$0.0)))
WHERE ((subq0.CalID=13) AND (subq0.CalValue<>$0.0 And subq0.CalValue<>$-400.0))
)
SELECT t1.Serial, t1.TRth0, t1.TRth1, t1.TRth2
FROM subq3 t1
  LEFT OUTER JOIN subq3 t2
    ON ((t1.Serial = t2.Serial) AND (t1.RunID < t2.RunID))
WHERE t2.Serial IS NULL AND t1.Serial < 90000000
ORDER BY t1.Serial ASC

Because of this question I've also built my example in SQLFiddle, and there it works just as I imagined it would. So my problem is actually with the specific features of the server. We have a MS SQL 2005 Server, which seems to have a problem with the alias in the subq3 statement.

Any suggestions how I can get around that "The column CalValue was specified multiple times"?

Upvotes: 0

Views: 61

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Update: Added business logic

select 
    serial, [13] as [TRth0], [24] as [TRth1],[35] as [TRth2]
from
    (
        select serial,Calvalue,CalID
            from
            (
                select 
                    Serial,
                    Rank() OVER (Partition by Serial order by RunId desc) as rank,
                    calvalue,
                    calid 
                from
                (
                    Select *,count(1) OVER (partition by Serial,runid ) as count from Calibrationdata 
                        where 
                            calValue<>0.00 and 
                                case 
                                    when (calid=13 and calvalue =-400)
                                    or (calid=24 and calvalue =0.03)
                                    then 0 ELSE 1 END =1
                ) d where d.count=3
            ) c
            where c.rank=1
    ) s
PIVOT
( max(calvalue) for calid in ([13],[24],[35])) p

updated fiddle link:http://sqlfiddle.com/#!3/c5f52/4

I used rank() in inner query to get only last run's reading for each serial Then I used PIVOT to transform the output.

please try the below query:

select serial, [13] as [TRth0], [24] as [TRth1],[35] as [TRth2]
from
(
  select 
     serial,Calvalue,CalID
  from
     (
      select 
         Serial, 
         Rank() OVER (Partition by Serial order by RunId desc) as rank, 
         calvalue,
         calid
      from Calibrationdata
      ) c
   where c.rank=1
  ) s
PIVOT
 ( 
   max(calvalue) for calid in ([13],[24],[35])
 ) p

sql fiddle link http://sqlfiddle.com/#!3/01c12/7

Upvotes: 1

Related Questions