CODEWITHSUNDEEP
sqlsql-serverssms
tpandas
tpandas

Reputation: 61

Syntax assistance for SQL inline view

I'm still learning SQL and struggling with inline views. I have been working on a project where I need to account for lags with certain things and so need to rely on having things nested so to speak. But I am not great with the syntax on inline views yet and it keeps throwing errors on parentheses, commas, etc. Could someone help me clean it up so it runs? 

SELECT * 
    ,   SiteName
    ,   VentCount                           AS 'Number Of Vents'
    ,   ROUND(PumpAHours,1)                 AS 'Pump A Hours'
    ,   ROUND(PumpBHours,1)                 AS 'Pump B Hours'
    ,   ROUND(PumpAStarts, 1)               AS 'Pump A Starts'
    ,   ROUND(PumpBStarts, 1)               AS 'Pump B Starts'

FROM ( 
SELECT * , SiteName 
, SUM(CASE WHEN ECV36_Open_Count_Lifetime - Lag_ECV36_Open_Count_Lifetime Between 1 AND 5 THEN ECV36_Open_Count_Lifetime - Lag_ECV36_Open_Count_Lifetime ELSE 0 END)    AS 'VentCount'
, CASE WHEN NULLIF(SUM(CASE WHEN PumpAStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT),0) < MAX(Pump_A_Hours) - MIN(Pump_A_Hours)
         THEN SUM(CASE WHEN PumpAStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT)
         ELSE MAX(Pump_A_Hours) - MIN(Pump_A_Hours) END                                                 AS 'PumpAHours'
, CASE  WHEN NULLIF(SUM(CASE WHEN PumpBStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT),0) < MAX(Pump_B_Hours) - MIN(Pump_B_Hours)
            THEN SUM(CASE WHEN PumpBStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT)
            ELSE MAX(Pump_B_Hours) - MIN(Pump_B_Hours) END                                              AS 'PumpBHours'
,COUNT(CASE WHEN Pump_A_StateId = 12 OR Pump_A_StateId = 14 OR Pump_A_StateId = 9 OR Pump_A_StateId = 1837 OR Pump_A_StateId = 1839 THEN 1 END) AS PumpAStarts
,COUNT(CASE WHEN Pump_B_StateId = 12 OR Pump_B_StateId = 14 OR Pump_B_StateId = 9 OR Pump_B_StateId = 1837 OR Pump_B_StateId = 1839 THEN 1 END) AS PumpBStarts

FROM ( 
SELECT * , SiteName
, LAG([ECV36_Open_Count_Lifetime],1,NULL)   OVER(PARTITION BY R.SiteId ORDER BY RecordDateTime ASC) AS 'Lag_ECV36_Open_Count_Lifetime'

FROM ( 
[Cryo].[dbo].[Record]       R
Cryo.dbo.Site S on S.SiteId = R.RecordId
Cryo.dbo.pad P on P.RecordId = R.RecordId
Cryo.dbo.Pump PP on PP.RecordId = R.RecordId
)a
)a
WHERE RecordDateTime BETWEEN DATEADD(DD,-1,CONVERT(DATE,GETDATE())) AND DATEADD(DD, 0,CONVERT(DATE,GETDATE()))
)a

EDIT: I reformatted the code and fixed some of the callouts. Things still aren't working but the only errors being given is with the I guess subquery aliases if that's what they are called?. For )a and )c I have syntax errors and )b says SiteName was specified multiple times, though I can just find one. I'm not sure how to fix that issue. Again, sorry for all the messy code, I'm really fresh. 

SELECT * , 
       sitename , 
       ventcount             AS 'Number Of Vents' , 
       Round(pumpahours,1)   AS 'Pump A Hours' , 
       Round(pumpbhours,1)   AS 'Pump B Hours' , 
       Round(pumpastarts, 1) AS 'Pump A Starts' , 
       Round(pumpbstarts, 1) AS 'Pump B Starts' 
FROM   ( 
              SELECT * , 
                     sitename , 
                     Sum( 
                     CASE 
                            WHEN ecv36_open_count_lifetime - lag_ecv36_open_count_lifetime BETWEEN 1 AND    5 THEN ecv36_open_count_lifetime - lag_ecv36_open_count_lifetime
                            ELSE 0 
                     END) AS 'VentCount' , 
                     CASE 
                            WHEN NULLIF(Sum( 
                                   CASE 
                                          WHEN pumpastatus LIKE '%Running%' THEN 1 
                                          ELSE 0 
                                   END) / Cast(20 AS FLOAT),0) < Max(pump_a_hours) - Min(pump_a_hours) THEN Sum(
                                   CASE 
                                          WHEN pumpastatus LIKE '%Running%' THEN 1 
                                          ELSE 0 
                                   END)            / Cast(20 AS FLOAT) 
                            ELSE Max(pump_a_hours) - Min(pump_a_hours) 
                     END AS 'PumpAHours' , 
                     CASE 
                            WHEN NULLIF(Sum( 
                                   CASE 
                                          WHEN pumpbstatus LIKE '%Running%' THEN 1 
                                          ELSE 0 
                                   END) / Cast(20 AS FLOAT),0) < Max(pump_b_hours) - Min(pump_b_hours) THEN Sum(
                                   CASE 
                                          WHEN pumpbstatus LIKE '%Running%' THEN 1 
                                          ELSE 0 
                                   END)            / Cast(20 AS FLOAT) 
                            ELSE Max(pump_b_hours) - Min(pump_b_hours) 
                     END AS 'PumpBHours' , 
                     Count( 
                     CASE 
                            WHEN pump_a_stateid = 12 
                            OR     pump_a_stateid = 14 
                            OR     pump_a_stateid = 9 
                            OR     pump_a_stateid = 1837 
                            OR     pump_a_stateid = 1839 THEN 1 
                     END) AS pumpastarts , 
                     Count( 
                     CASE 
                            WHEN pump_b_stateid = 12 
                            OR     pump_b_stateid = 14 
                            OR     pump_b_stateid = 9 
                            OR     pump_b_stateid = 1837 
                            OR     pump_b_stateid = 1839 THEN 1 
                     END) AS pumpbstarts 
              FROM   ( 
                              SELECT   * , 
                                       s.SiteName , 
                                       Lag([ECV36_Open_Count_Lifetime],1,NULL) OVER(partition BY r.siteid ORDER BY recorddatetime ASC) AS 'Lag_ECV36_Open_Count_Lifetime'
                              FROM     ( [Cryo].[dbo].[Record] R JOIN cryo.dbo.site s ON s.siteid = r.recordid JOIN cryo.dbo.pad p ON p.recordid = r.recordid JOIN cryo.dbo.pump pp ON pp.recordid = r.recordid )a
                              WHERE    recorddatetime BETWEEN dateadd(dd,-1,CONVERT(date,getdate())) AND      dateadd(dd, 0,CONVERT(date,getdate())) )b )c

Upvotes: 1

Views: 143

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415830

I had to start by improving indentation. This makes it so much easier to see how things line up:

SELECT * 
    ,   SiteName
    ,   VentCount                           AS 'Number Of Vents'
    ,   ROUND(PumpAHours,1)                 AS 'Pump A Hours'
    ,   ROUND(PumpBHours,1)                 AS 'Pump B Hours'
    ,   ROUND(PumpAStarts, 1)               AS 'Pump A Starts'
    ,   ROUND(PumpBStarts, 1)               AS 'Pump B Starts'

FROM ( 
    SELECT * , SiteName 
        , SUM(CASE WHEN ECV36_Open_Count_Lifetime - Lag_ECV36_Open_Count_Lifetime Between 1 AND 5 THEN ECV36_Open_Count_Lifetime - Lag_ECV36_Open_Count_Lifetime ELSE 0 END)    AS 'VentCount'
        , CASE WHEN NULLIF(SUM(CASE WHEN PumpAStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT),0) < MAX(Pump_A_Hours) - MIN(Pump_A_Hours)
         THEN SUM(CASE WHEN PumpAStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT)
         ELSE MAX(Pump_A_Hours) - MIN(Pump_A_Hours) END                                                 AS 'PumpAHours'
        , CASE  WHEN NULLIF(SUM(CASE WHEN PumpBStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT),0) < MAX(Pump_B_Hours) - MIN(Pump_B_Hours)
            THEN SUM(CASE WHEN PumpBStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT)
            ELSE MAX(Pump_B_Hours) - MIN(Pump_B_Hours) END                                              AS 'PumpBHours'
        ,COUNT(CASE WHEN Pump_A_StateId = 12 OR Pump_A_StateId = 14 OR Pump_A_StateId = 9 OR Pump_A_StateId = 1837 OR Pump_A_StateId = 1839 THEN 1 END) AS PumpAStarts
        ,COUNT(CASE WHEN Pump_B_StateId = 12 OR Pump_B_StateId = 14 OR Pump_B_StateId = 9 OR Pump_B_StateId = 1837 OR Pump_B_StateId = 1839 THEN 1 END) AS PumpBStarts

    FROM ( 
        SELECT * , SiteName
            , LAG([ECV36_Open_Count_Lifetime],1,NULL)   OVER(PARTITION BY R.SiteId ORDER BY RecordDateTime ASC) AS 'Lag_ECV36_Open_Count_Lifetime'

        FROM ( 
            [Cryo].[dbo].[Record]       R
              Cryo.dbo.Site S on S.SiteId = R.RecordId
               Cryo.dbo.Pad P on P.RecordId = R.RecordId
              Cryo.dbo.Pump PP on PP.RecordId = R.RecordId
        )a
    )a
    WHERE RecordDateTime BETWEEN DATEADD(DD,-1,CONVERT(DATE,GETDATE())) AND DATEADD(DD, 0,CONVERT(DATE,GETDATE()))
)a

Notice I didn't care as much about making items in the SELECT area look pretty as I did making sure the indentation of each level of nesting was correct. With that done, this section jumps out at me:

FROM ( 
    [Cryo].[dbo].[Record]       R
      Cryo.dbo.Site S on S.SiteId = R.RecordId
       Cryo.dbo.Pad P on P.RecordId = R.RecordId
      Cryo.dbo.Pump PP on PP.RecordId = R.RecordId

It looks like you're missing some JOINs here. Unfortunately, I can't just fix that for you, because we have no way to know whether you want INNER JOIN, LEFT JOIN, CROSS JOIN, etc. It's also suspect this query has a * in the SELECT clause.

Another thing I noticed is the aggregate functions in the next level up. You can't use aggregate functions like SUM() unless they are the only field in the SELECT clause, you have a GROUP BY clause, or they are inside a windowing function (OVER expression) with a PARTITION BY section.

Finally, to help make this easier to manage going forward, I suggest writing each level as a CTE (Common Table Expression). That will reduce indenting and (in my experience) makes the query easier to reason about mentally. Without re-writing everything, here's an abbreviated overview of how that would look:

WITH LaggedSiteLifetime AS (
    SELECT * , SiteName
            , LAG(....)
    FROM Record...
), PumpCounts AS (
    SELECT *, SiteName, COUNT(.....)
    FROM LaggedSiteLifetime
)
SELECT SiteName, 
      ROUND(...)
FROM PumpCounts

This is my preference. Some people prefer the nested view you already have, because it allows them to highlight just a section and hit F5 in Management Studio to run that section, where this would make it harder to run, say, just the PumpCounts section. However, I find this helps by reducing nesting and making indentation more consistent (all SELECT/FROM/WHERE/etc can be at the same level). Most of all, this lets us write the query in the order you need to read it to be able to understand it, where the other way you have to kind of read things from the inside out.

Upvotes: 2

Related Questions