Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-SQL 2012: Embedding a Calculated field within a Select Statement with a Variable

Answered T-SQL 2012: Embedding a Calculated field within a Select Statement with a Variable

  • Monday, January 07, 2013 1:14 AM
     
     
    Sign In to Vote
    0

    Problem:  I'm trying to check DB1.Date1 to see if the current record is the most current date of all the records.  If the current record is the most current date I want to store the value "Current" in a new column called "TransStatus".  If the DB1.Date1 is not the most current date store "Prior" in DB1.TransStatus.

    I don't know how to write this and realize the following code is bugus, but hopefully it explains what I'm trying to do.

    I'm new to SQL Variables and Calculated fields.

    Thank you.

    Bob Sutor

    CODE LOGIC:

    Declare @Status CHAR(7) = ' '

    Select

     DB1.Date1,

         IF DB1.Date = max(DB1.Date1) then @Status = "Current" Else @Status = "Prior"

    DB1.TransStatus = @Status

    DB1.EmployeeName

    From: DB1

     

All Replies

  • Monday, January 07, 2013 1:41 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    select Db1.Date1, case when row_number() over (order by Db1.Date1 DESC) = 1 then 'Current' else 'Prior' end as [Status], Db1.EmployeeName

    from Db1

    or

    ;with cte as (select *, row_number() over (order by Date1 DESC) as Rn from Db1)

select *, case when Rn=1 then 'Current' else 'Prior' end as [Status] from cte


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Monday, January 07, 2013 2:40 AM
     
     Answered
    Sign In to Vote
    0
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn to follow ISO-11179 data element naming conventions and formatting rules (you did not, but were close). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. 

    Date is a reserved word, as well as too vague to be a data element name. Rows are not anything like records, columns are not fields. 

    >> I'm trying to check DB1. to see if the current record [sic] is the most current date of all the records [sic].  If the current record [sic] is the most current date I want to store the value "Current" in a new column called "trans_status".  If the DB1.foobar_date is not the most current date store "Prior" in DB1.trans_status. <<

    We do not materialize computed values like we did in the fields of records in files. Create a VIEW that is always right: 

    SELECT X.employee_name, X.foobar_date, ..,
           CASE WHEN X.foobar_date = X.current_foobar_date
                THEN 'Current' ELSE 'Prior' END
           AS foobar_status
      FROM (SELECT employee_name, foobar_date, ..,
                   MAX(foobar_date) OVER () AS current_foobar_date 
              FROM Floobs)
            AS X;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Monday, January 07, 2013 3:02 AM
     
     
    Sign In to Vote
    0

    Hi Naomi!

    I"m not sure I understand the "When Row_Number() Over" is doing.  I know what the (Order by Db1.Date1 DESC) = 1 is??  Also I'm actually trying the determine the latest date for each given Employee.  Each employee has mulitiple records in the table (Employee-Sequence).  I assume a Max(Db1.Date1) could not be used??  Could a Group By Clause be used? Also how would I test for a Null condition in a Case statement?  Sorry I only gave you a part of the problem.

    Select Db1.Date1 (I want to show this value separately)

    Case Db1.Date1

       WHEN rownumber() over (orderby Db1.Date1 DESC) =1 THEN 'Current'

       END AS Status 

    Db1.EmployeeName,

    Db1.Sequence

    FROM Db1

     
  • Monday, January 07, 2013 4:10 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    If you want the latest date per employee, we just need to add partition by, e.g.

    ;with cte as (select *, row_number() over (partition by EmployeeId order by Date1 DESC) as Rn from Db1)

select *, case when Rn=1 then 'Current' else 'Prior' end as [Status] from cte

    To understand my suggestion you may find the following blogs useful

    Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
    Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
    Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog