SQL Query to Create a Calculated Field

Sql query to create a calculated field

Select all rows with 'm001-1-In' with DateTime as I and add the fitting 'm001-1-Exit' rows to this with a Subquery as O, this will look like this:

SELECT t1.[Card No], t1.[User Name],dateTime as I
,(Select TOP 1 dateTime from Tab t2 where  t2.[Card No]= t1.[Card No] 
and  t2.[User Name]= t1.[User Name] and  t2.Addr='m001-1-Exit' 
and t2.DateTime>t1.datetime ORDER by DateTime) as O
FROM Tab t1
where t1.Addr='m001-1-In' 

Now it's easy to encapsulate this, show as Prepared below and add our SUM and Grouping to this:

SELECT [Prepared].[Card No], [Prepared].[User Name], SUM(DateDiff('n',I,O))/60 AS Hours
FROM (
      SELECT t1.[Card No], t1.[User Name],dateTime as I
    ,(Select TOP 1 dateTime from Tab t2 where  t2.[Card No]= t1.[Card No] 
      and  t2.[User Name]= t1.[User Name] and  t2.Addr='m001-1-Exit' 
      and t2.DateTime>t1.datetime ORDER by DateTime) as O
FROM Tab t1
where t1.Addr='m001-1-In' 
)  AS [Prepared]
GROUP BY [Prepared].[Card No], [Prepared].[User Name]

If you need to restrict the DateRange you add the needed conditions to the row where t1.Addr='m001-1-In'

Creating calculated fields in sql

You are very close, it's called Computed Column

https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

ALTER TABLE dbo.tablename ADD Employment AS ((m1+m2+m3)/3)

Update:

If you would like to force data type for a computed column, you could do the following

ALTER TABLE dbo.tablename ADD Employment AS CAST((m1+m2+m3)/3 AS Numeric (9,0))

How to create a conditional calculated column in SQL?

You may be able to use a case statement in the calculation.

     SELECT AIR.Code, AIR.City, AIR.Country, SUM(CASE WHEN flight.f = 'C' then £
                                                        when flight.f = 'A' then (-1) * £
                                                        else null
                                                        end) as "£ calculated"
     from AirportTable as "air" 
     join FlightTable as "flight" on "air".CODE = "flight".code -- could be AKEY = AKEY

How to create a calculated column on the fly in SQL Server?

You can use the case expression in calculation.

Select Product, Number, 
CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END as DaysRem,
SUM(DailySales_YTD*CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END)+SUM(SUMYTDRev) as ProjRev
From report  
Group by Product,Number,CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END

If you don't want to specify the calculation multiple times, use a derived table with the calculation.

Select Product, Number, DaysRem,
SUM(DailySales_YTD*DaysRem)+SUM(SUMYTDRev) as ProjRev
From (select r.*,CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END as DaysRem
      from report r) r
Group by Product,Number,DaysRem

Calculated column in SELECT SQL Query (MySQL)

Gordon's answer is probably what I would want to see as a DBA inheriting someone else's code. But we could actually avoid using a subquery at all and just write the following:

SELECT
    source,
    COUNT(*) AS TotalCount,
    SUM(CASE WHEN int_lastdate > CURDATE() AND status LIKE 'Published'
             THEN 1 ELSE 0 END) AS ActiveCount,
    COUNT(*) - SUM(CASE WHEN int_lastdate > CURDATE() AND status LIKE 'Published'
                        THEN 1 ELSE 0 END) AS ExpiredCount
FROM internship
GROUP BY source

MS SQL Server - Use Calculated Field of SELECT statement

In sql server at least, you can do it with a subquery:

SELECT  Machine 
,       TestFormula
,       TestFormula*12 AS TestFormulaYear
FROM    (
           SELECT Machine 
           ,      Amount*Value AS TestFormula
           FROM   Test
        ) T

How can I use a calculated field in another calculation further down in the query?

Snowflake supports inline column lateral reference, thus referencing alias at the same SELECT level will work as long as there is no nesting of windowed functions or shadowing table columns:

SELECT 
   expr AS col1,
   expr2 AS col2,
   col1 + col2 AS col3
FROM table;

Sample (col1, col2, col3 are expressions):

How to use a calculated column to calculate another column in the same query using a subquery

You can use Temp_tables, Derived tables or Common table expressions (CTE) to obtain the result. Simple approach would be Derived table as you dont need much more coding.

  SELECT A.*
  ,  A.NLength/(A.NSpeedLimit * 88)  as [TravelTime]
  FROM
  (

  SELECT          
    sc.OBJECTID, 
    sn.Name, 
    case    when hn.side = 'Right Side' then ''
        else sc.LCity
    end as LCity, 
    case    when hn.side = 'Left Side' then ''
        else sc.RCity
    end as RCity, 
    case    when hn.side = 'Right Side' then ''
        else sc.LZip
    end as LZip, 
    case    when hn.side = 'Left Side' then ''
        else sc.RZip
    end as RZip, 
    sc.SHAPE.STLength() AS NLength,
    ISNULL(sc.SpeedLimit,1) AS NSpeedLimit

  FROM    STREETNAME AS sn 
   INNER JOIN
            STREETHASSTREETNAME AS hn ON 
                     sn.GlobalID = hn.GlobalID AND 
                     hn.Role = 'Primary'
          INNER JOIN      STREETCENTERLINE AS sc ON 
              hn.GlobalID = sc.GlobalID

            ) AS A

How to use a calculated column to calculate another column in the same view

You could use a nested query:

Select
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC as calccolumn2
From (
  Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from t42
);

With a row with values 3, 4, 5 that gives:

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4

You can also just repeat the first calculation, unless it's really doing something expensive (via a function call, say):

Select
  ColumnA,
  ColumnB,
  ColumnA + ColumnB As calccolumn1,
  (ColumnA + ColumnB) / ColumnC As calccolumn2
from t42; 

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4 

---

Related Topics