Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Aug 2009
    Posts
    13

    Unanswered: Creating a View that adds a Column with a value based on condition

    First off I'd like to thnk you in advance if you kindly help me and let you know that this is a real world problem that I'd like solved.

    What I am trying to do is to Create a View that adds a Column (TransactionType) with a value based on condition. IF joindate = "Today" then value should be "INSERT" . If changedate = "Today" and and is <> to joindate the value should be "UPDATE" else the value should be "NUll"

    Below is what I have so far. I'm using MS SQL 2000 service pack 4.

    Thanks.



    SELECT M.First_Name, M.Last_Name, M.Member_Number, M.Middle_Initial, M.Nickname, M.Office_Number, M.MLS_ID, M.License_Number,
    CASE WHEN isdate(M.License_Expire_Date) = 0 THEN '1900-01-01 00:00:00' ELSE CONVERT(DATETIME, M.License_Expire_Date, 101) END AS LicExpDate,
    CASE WHEN isdate(M.Transfer_Date) = 0 THEN '1900-01-01 00:00:00' ELSE CONVERT(DATETIME, M.Transfer_Date, 101) END AS TransferDate, M.NRDS_ID,
    CASE WHEN isdate(M.Birth_Date) = 0 THEN '1900-01-01 00:00:00' ELSE CONVERT(DATETIME, M.Birth_Date, 101) END AS BirthDate,
    CASE WHEN isdate(M.Last_Modify_Date) = 0 THEN '1900-01-01 00:00:00' ELSE CONVERT(DATETIME, M.Last_Modify_Date, 101) END AS ChangeDate, M.Password,
    BT.Bill_Type_Code, MA.Association_ID, II.E_Mail_Address, II.Web_Page_Address, II.Record_Type, CASE WHEN MA.Status = 'A' THEN '1' ELSE '0' END AS IsAuth,
    M.Who_Modified, CASE WHEN isdate(MA.Member_Join_Date)= 0 THEN '1900-01-01 00:00:00' ELSE CONVERT(DATETIME, MA.Member_Join_Date, 101) END as JoinDate
    FROM dbo.Office AS O INNER JOIN
    dbo.Member AS M ON O.Office_Number = M.Office_Number INNER JOIN
    dbo.Internet_Information AS II ON M.Member_Number = II.Office_Member INNER JOIN
    dbo.Member_Association AS MA ON M.Member_Number = MA.Member_Number INNER JOIN
    dbo.Bill_Type AS BT ON BT.Bill_Type_Code = MA.Bill_Type_Code
    WHERE (MA.Association_ID = 'MLS') AND (BT.Bill_Type_Code <> 'N') AND (II.Record_Type = 'MI')
    ORDER BY M.Last_Name

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jasongar
    What I am trying to do is to Create a View that adds a Column (TransactionType) with a value based on condition. IF joindate = "Today" then value should be "INSERT" . If changedate = "Today" and and is <> to joindate the value should be "UPDATE" else the value should be "NUll"
    Code:
    SELECT CASE WHEN joindate = CURRENT_DATE
                THEN 'INSERT'
                WHEN changedate = CURRENT_DATE
                 AND changedate <> joindate
                THEN 'UPDATE'
                ELSE NULL END    AS  TransactionType
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    13
    Thanks r937, but I get "Incorrect syntax near the keyword 'CURRENT_DATE'."
    Could you post the enire code including what I have already?

    TIA

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh for cryin' out pete's sake

    i keep forgetting that SQL Server doesn't support SQL standards!!!

    please explain how the values in the joindate column are created -- how do you populate those values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2009
    Posts
    13
    The column where the data is stored in char(8) format '20090811'. My above query then converts to datetime which is what I need. The values are entered via the front end program which is entered as '08/11/09'

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure it's CHAR(8)??

    because that means if you enter '08/11/09' then that's how it will be stored, and ~not~ as '20090811'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2009
    Posts
    13
    Possitive. The front end software enters as 08/11/09 and it is stored as char(8) NOT NULL '20080811'. I can send you screen shots if you want. The database taht I'm working with stores all dates as char(8) in yyyymmdd format. Not sure why but I have no control of that.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't buy that explanation

    i'll bet it isn't a CHAR(8) column at all -- it's probably a DATETIME column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2009
    Posts
    13
    What would help you believe? I'll gladly post .

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    run this with your table name supplied:
    Code:
    sp_help tablename
    post the line(s) for the date field(s).

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jasongar
    What would help you believe? I'll gladly post .
    the part that i don't believe is that you enter one string into a CHAR(8) column and it magically stores as a completely different string

    now, if you had said that the users enter dates in '08/11/09' format, but your application logic reformats it so that it is stored into the CHAR(8) column as '20090811', then that would of course be okay

    but in that case, from the database point of view (which is what we're talking about, not the user interface), it is both entered as '20090811' and stored as '20090811'

    see the difference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2009
    Posts
    13
    Yes I see. The data is entered in the database and stored as char(8) in yyyymmdd format.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jasongar
    Yes I see. The data is entered in the database and stored as char(8) in yyyymmdd format.
    okay, great

    could you now please explain why you need to use the isDate() function?

    what kinds of non-date values are in there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2009
    Posts
    13
    Some of the fileds are " " or 00000000 which returns an "out-of-range" error
    when trying to simply convert to datetime as is.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jasongar
    Some of the fileds are " " or 00000000 which returns an "out-of-range" error
    when trying to simply convert to datetime as is.
    in that case, you could use this --
    Code:
    CONVERT(DATETIME,COALESCE(NULLIF(NULLIF(foo,' '),'00000000'),'19010101'),101) AS foo
    in any case, the new column you were originally asking for is going to have to deal with this as well, so the best way to do this is to push the error-checking down into a subquery --
    Code:
    SELECT data.*
         , CASE WHEN data.Member_Join_Date = DATEADD(D,DATEDIFF(D,'1901-01-01',GETDATE()),'1901-01-01') 
                THEN 'INSERT'
                WHEN data.Last_Modify_Date= DATEADD(D,DATEDIFF(D,'1901-01-01',GETDATE()),'1901-01-01') 
                 AND data.Last_Modify_Date<> data.Member_Join_Date 
                THEN 'UPDATE'
                ELSE NULL END    AS  TransactionType
      FROM (
           SELECT M.First_Name
                , M.Last_Name
                , M.Member_Number
                , M.Middle_Initial
                , M.Nickname
                , M.Office_Number
                , M.MLS_ID
                , M.License_Number
                , CONVERT(DATETIME,COALESCE(NULLIF(NULLIF(M.License_Expire_Date,' '),'00000000'),'19010101'),101) AS License_Expire_Date
                , CONVERT(DATETIME,COALESCE(NULLIF(NULLIF(M.Transfer_Date,' '),'00000000'),'19010101'),101) AS Transfer_Date
                , CONVERT(DATETIME,COALESCE(NULLIF(NULLIF(M.Birth_Date,' '),'00000000'),'19010101'),101) AS Birth_Date
                , CONVERT(DATETIME,COALESCE(NULLIF(NULLIF(M.Last_Modify_Date,' '),'00000000'),'19010101'),101) AS Last_Modify_Date
                , BT.Bill_Type_Code
                , MA.Association_ID
                , II.E_Mail_Address
                , II.Web_Page_Address
                , II.Record_Type
                , CASE WHEN MA.Status = 'A' THEN '1' ELSE '0' END AS IsAuth
                , M.Who_Modified
                , CONVERT(DATETIME,COALESCE(NULLIF(NULLIF(MA.Member_Join_Date,' '),'00000000'),'19010101'),101) AS Member_Join_Date
             FROM dbo.Office AS O 
           INNER 
             JOIN dbo.Member AS M 
               ON O.Office_Number = M.Office_Number 
           INNER 
             JOIN dbo.Internet_Information AS II 
               ON M.Member_Number = II.Office_Member 
           INNER 
             JOIN dbo.Member_Association AS MA 
               ON M.Member_Number = MA.Member_Number 
           INNER 
             JOIN dbo.Bill_Type AS BT 
               ON BT.Bill_Type_Code = MA.Bill_Type_Code
            WHERE (MA.Association_ID = 'MLS') 
              AND (BT.Bill_Type_Code <> 'N') 
              AND (II.Record_Type = 'MI')
           ) AS data
    ORDER 
        BY Last_Name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •