December 9, 2008 at 12:04 pm
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
December 9, 2008 at 12:44 pm
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
December 9, 2008 at 12:48 pm
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