Solved

Group by problem

Posted on 2009-05-06
2
391 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 250 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now