Sql Server 2008: Combining Two Complex Queries

here is one query that returns only 1 column called datapath: SELECT --assumes number not at end of string LEFT(startOf, PATINDEX('%[^0-9]%', startof)-1) FROM ( SELECT

Solution 1:

SELECT reporttime,
                    datapath,
                    finalconc, 
                    instrument 
      FROM

(
SELECT--assumes number not at end of stringLEFT(startOf, PATINDEX('%[^0-9]%', startof)-1) AS datapath, --correct?
    rowid, instrument , reporttime 
FROM
    (
    SELECT--assumed 3 digits minimumSUBSTRING(datapath, PATINDEX('%[0-9][0-9][0-9]%', datapath), 8000) AS startOf,
        rowid, instrument , reporttime
    FROM
       batchinfo --don't need LEN check. PATINDEX will do that implicitly
    ) foo

) batchinfo  
                    JOIN qvalues ON batchinfo.rowid = qvalues.rowid
                    WHERE compound =3AND name = "hey"
                   AND batchinfo.instrument =44AND batchinfo.reporttime LIKE'10/%/2010%'";

Solution 2:

If I'm reading this correctly, I would think you should be able assign your 'datapath' column in the second select to the value you're selecting in the first, and replace 'startOf' with the value you're selecting for startOf.

I think it would be something like this:

SELECT 
  reporttime, 
  'datapath'=LEFT(SUBSTRING(datapath, PATINDEX('%[0-9][0-9][0-9]%', datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(datapath, PATINDEX('%[0-9][0-9][0-9]%', datapath), 8000))-1) , 
  finalconc,  
  instrument  
FROM batchinfo   
JOIN qvalues ON batchinfo.rowid = qvalues.rowid 
WHERE compound =3AND name = "hey" 
AND batchinfo.instrument =44AND batchinfo.reporttime LIKE'10/%/2010%'";