Solved

Group by problem

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: "HAVING CASE" Clause 1 25
sql select record as one long string 21 24
Update a text value in another table 10 40
optimize stored procedure 6 27
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 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