Solved

Group by problem

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 Select Distinct 8 35
T-SQL:  How to Display the Final Row of a Group of Rows from a Table 4 32
TSQL DateADD update Question 4 25
Add '#' to end of file 2 29
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

12 Experts available now in Live!

Get 1:1 Help Now