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 , startdatefrom CD group by startdate order by startdate desc
select sum(Amount), duration from @.temptable group by duration order by max(date) descQuery 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
