SQL Server Performance

Data Types variance

Discussion in 'SQL Server 2005 General Developer Questions' started by alisag, Feb 9, 2010.

  1. alisag New Member

    Hi,
    I have the following query in which i have one integer type field called Status. But what i want to display is some meaningful data on this field.
    for Ex. If the value of Status = 0 then it should show "Pending", If it is 1 then it show "Finished" etc. I tried with using Report parameter but to no sucess.
    Any Idea Guys?
    SELECT [Service Header].[Document Type], [Service Header].[Responsibility Center], [Service Header].No_, [Service Header].Status,
    [Service Header].[Order Date], [Service Header].Name, [Service Header].Description, [Service Header].[Resource Code],
    [Service Header].[Customer No_], [Service Header].[Contract No_], [Service Header].[Response Date], Resource.Name AS [Resource Name]
    FROM dbo.[Company Name$Service Header] AS [Service Header] LEFT OUTER JOIN
    dbo.[Company Name$Resource] AS Resource ON [Service Header].[Resource Code] = Resource.No_
    WHERE ([Service Header].[Job Type] = 1)
    ORDER BY [Service Header].[Resource Code], [Service Header].Status
    alisag, Feb 9, 2010
    #2

  2. FrankKalis Moderator

    So you don't have a Status lookup table where you could get the status description from? This would probably be most flexible solution for you as you just need to join onto that table and be done with it.
    If you don't have such a lookup table, a CASE expression could do as well. Something like
    ...
    CASE Status
    WHEN 0 THEN 'Something'
    WHEN 1 THEN 'Something Else'
    ....
    END
    This however means that whenever you need a news status you have to change the CASE expression.
    FrankKalis, Feb 9, 2010
    #3

  3. alisag New Member

    Thank you. This is done. So similarly i can do for NULL dates. I mean if the response Date field have no value it is printing 1/1/1754 now so i can avoid this also by using case statement.
    alisag, Feb 9, 2010
    #4

  4. FrankKalis Moderator

    You could use a CASE for that as well. but it may be simpler when you use ISNULL(date column, < your replacement value>)
    FrankKalis, Feb 9, 2010
    #5

  5. Adriaan New Member

    Hm - the lowest non-null date you can store for DATETIME is 1/1/1753 (integer value -53,326). That's 1753, not 1754.
    Maybe the column is not nullable, and this is supposed to be a default value?
    Adriaan, Feb 9, 2010
    #6

  6. Madhivanan Moderator

    [quote user="alisag"]
    Thank you. This is done. So similarly i can do for NULL dates. I mean if the response Date field have no value it is printing 1/1/1754 now so i can avoid this also by using case statement.
    [/quote]
    How is it possible it has date 1/1/1754?
    Did you post the full code that produced that result?
    Madhivanan, Feb 10, 2010
    #7

  7. FrankKalis Moderator

    [quote user="Madhivanan"]
    [quote user="alisag"]
    Thank you. This is done. So similarly i can do for NULL dates. I mean if the response Date field have no value it is printing 1/1/1754 now so i can avoid this also by using case statement.
    [/quote]
    How is it possible it has date 1/1/1754?
    [/quote]
    Simple! The column is of type DATETIME. The range for that type is from 1753-01-01 00:00:00.000to 9999-12-31 23:59:59.997.
    FrankKalis, Feb 10, 2010
    #8

  8. Madhivanan Moderator

    [quote user="FrankKalis"]
    [quote user="Madhivanan"]
    [quote user="alisag"]
    Thank you. This is done. So similarly i can do for NULL dates. I mean if the response Date field have no value it is printing 1/1/1754 now so i can avoid this also by using case statement.
    [/quote]
    How is it possible it has date 1/1/1754?
    [/quote]
    Simple! The column is of type DATETIME. The range for that type is from 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997.
    [/quote]
    Well. My question was due to the post content from OP
    I mean if the response Date field have no value it is printing 1/1/1754
    I wonder how it assigns that value
    Madhivanan, Feb 10, 2010
    #9

  9. alisag New Member

    [quote user="Madhivanan"]
    I mean if the response Date field have no value it is printing 1/1/1754
    I wonder how it assigns that value
    [/quote]
    Hi Madhivanan,
    Yes if there is no value for response dae it was assigning it 1/1/1753. Anyway with one the member of this forum helped me to solve this using Case statement.
    alisag, Feb 10, 2010
    #10

Share This Page