Regd Small Query

  • hi,

    I have a table in which the columns are date,count,status

    this is what i have

    select date,sum(count),status from table1

    where date = getdate()-1

    group by date,status

    order by 1,2,3

    Now wat i need is if i dont have any records yesterday then i want to display the previous days data which is available

    i mean

    select date,sum(count),status from table1

    where date = getdate()-2

    group by date,status

    order by 1,2,3

    so how can i write a query for that please do let me know a bit urgent ...Thanks in advance..

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • Is this query part of a stored procedure? If so, the easiest way to work with this might be to determine a couple of things first, such as getting the last day with data, without time involved. Assuming that date column in table1 only has dates, no time:

    DECLARE

    @Today datetime,

    @LastDataDay datetime

    SET @Today = DateAdd(day, 0, DateDiff(day, 0, GetDate()))

    SELECT @LastDataDay = MAX(date) FROM table1

    WHERE date < @Today

    SELECT Date, SUM(count), Status,

    FROM table1

    WHERE date = @LastDataDay

    GROUP BY Date, Status

  • Chris (12/9/2008)


    hi,

    I have a table in which the columns are date,count,status

    this is what i have

    select date,sum(count),status from table1

    where date = getdate()-1

    group by date,status

    order by 1,2,3

    Now wat i need is if i dont have any records yesterday then i want to display the previous days data which is available

    i mean

    select date,sum(count),status from table1

    where date = getdate()-2

    group by date,status

    order by 1,2,3

    so how can i write a query for that please do let me know a bit urgent ...Thanks in advance..

    By the way are you going to use the same status as the previous date too ?


    * Noel

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply