• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Group by problem

I have  the below sp that pulls out sale info on our staff based on a day,week,month or quarter.  is there a way to set the grouping option to a user selected varible for example if a users selects sale information for the month the results could be shown broken down into each week in that month?? something like the below
@p2 = 'weekday'
group by datename(@p2,t2.date)
(@part nvarchar(10),
@p1 nvarchar(max),
@p2 nvarchar(20))
as
declare @startdate datetime
declare @enddate datetime 
 
set nocount on
select @startdate = case @part 
   when 'day' then convert(datetime, convert(varchar(10), getdate(), 120), 120)
   when 'month' then convert(datetime, convert(varchar(8), getdate(), 120) + '01' , 120)
   when 'week' then dateadd(day,1-datepart(weekday, getdate()), convert(datetime, convert(varchar(10), getdate(),120),120))
   when 'Quarter' then dateadd(month,3-datepart(month, getdate()), convert(datetime, convert(varchar(10), getdate(),120),120))
 
    end
     , @enddate = case @part
   when 'day' then dateadd(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
   when 'month' then dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
   when 'week' then dateadd(day,8-datepart(weekday, getdate()), convert(datetime, convert(varchar(10), getdate(),120),120))
   When 'Quarter' then dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
end
 
 
 
 
select t1.col1 as 'con' datename(month,t2.date) count(*)as 'total'
from t1  
join t2 u on t1.id = t2.id
where t2.date >= @startdate 
	   and t2.date <  @enddate and t1.col1 in(SELECT SplitValues FROM dbo.ftn_DelimitedListToIntTableVariable(@p1,','))
group by datename(@p2,a.createdon),datepart(@p2,a.createdon

Open in new window

0
leap29
Asked:
leap29
1 Solution
 
vinurajrCommented:
u can make use of IF ElSE statements...
0
 
mfreudenCommented:
Do group by A week in a month you have to do something like the following:

select datepart(week,datefield),sum(amount
from table
where datefield>'4/1/09' and date<'5/1/09'
group by datepart(week,datefield)

Week is the week# within the year so if you want it to start at 1 instead of 19 in this case just add a variable to subtract it out

declare @weekstart int
select @weekstart=datepart(week,min(datefield) -1 from tablename where datefield>'4/1/09' and date<'5/1/09'

select datepart(week,datefield) - @weekstart , sum(amount)
from table
where datefield>'4/1/09' and date<'5/1/09'
group by datepart(week,datefield) - @weekstart


0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now