Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Group by problem

Posted on 2009-05-06
2
Medium Priority
?
399 Views
Last Modified: 2012-05-06
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
Comment
Question by:leap29
2 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 24313474
u can make use of IF ElSE statements...
0
 
LVL 3

Accepted Solution

by:
mfreuden earned 750 total points
ID: 24313981
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question