Retrieving Mutiple rows

I have a table like this.

Depositors Table

Value(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Please Can anyone write a simple query for this?

Thanks

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select top 100 sum(grandtotal),

case when saledate = dateadd("d",-1,dateadd("month",0,'07/20/2007')) then 'yesterday' --cast (saledate as nvarchar(30))

when saledate < dateadd("d",-1,dateadd("month",0,'07/20/2007')) and saledate >= dateadd("d",-7,dateadd("month",-1,'07/20/2007')) then 'Last 7 days'

when saledate < dateadd("day",-1,dateadd("month",-1,'07/20/2007')) and saledate >= dateadd("day",-2,dateadd("month",-3,'07/20/2007')) then 'Last month'

when saledate < dateadd("day",-2,dateadd("month",-3,'07/20/2007')) and saledate >= dateadd("d",-1,dateadd("year",-2,'07/20/2007')) then 'Last 1 year'

else '...'

end , saledate

from sale group by saledate order by saledate desc

select sum(amount), duration from @.temptable group by duration order by max(date) desc

Bad formatting but query works..

I checked it..

in my database i have old date so i need to use old date.. but you can use today's date..

|||

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

|||

shamen wrote:

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate = dateadd("d",-1,GETDATE()) then 'yesterday'

when Startdate between dateadd("d",-1,GETDATE()) and dateadd("d",-7,GETDATE()) then 'Last 7 days'

when startdate between dateadd("d",-7,GETDATE()) and dateadd("d",-30,GETDATE()) then 'Last month'

Else 'ABC'
end as duration, startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

May it works now. For testing purpose always keep default value so atlease you can know that condition is going where