?
Solved

Group by problem

Posted on 2009-05-06
2
Medium Priority
?
398 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

719 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