January 24, 2008 at 12:04 pm
I have the following query:
DECLARE @start_date datetime,
@weeks int
SET @start_date = '1/1/2007'/*Set this to the date you want your analysis to begin on. Be mindful of the date you pick. For instance, in this case we want the
beginning of the week to always be a Monday so we are OK picking 1/1/2007 because it is a Monday.*/
SET @weeks = 12
SELECT DATEADD(dd, (n.Num-1) * 7, @start_date) [Beginning of Week], /*This is creating the Beginning of each week. Looks at each record in the Number table,
subtracts one off of the Num field and then multiplies by 7. Takes the resulting number and adds that many days to our start date variable.*/
DATEADD(dd,(n.Num * 7)-1,@start_date) [End of Week], /*This is creating the Ending of each week. Looks at each record in the Number table,
muiltplies it by 7 and then subtracts one off of the result. Takes the resulting number and adds that many days to our start date varaible.*/
( /*This is where we are counting the number of opportunities in each week. This returns a reasulting count for each combination that is
generated in the outer query.*/
SELECT COUNT(*)
FROM op_opportunity op_o
WHERE op_o.open_date BETWEEN DATEADD(dd, (n.Num-1) * 7 , @start_date) AND DATEADD(dd, (n. Num * 7)-1, @start_date) /*Count if the
open date is between
what we've identified as the Beginning of Week and End of Week for each iteration.*/
) AS [Opportunities Open]
FROM Numbers n
WHERE DATEADD(dd,n. Num * 7,@start_date) >= DATEADD(wk, -@weeks+1, GETDATE()) AND DATEADD(dd,n. Num * 7,@start_date) <= DATEADD(wk, 1, GETDATE()) /*We are
just limiting our evaluation to anything less than todays date and within the number of weeks in our @week variable.*/
It returns a result set that looks like this:
Beginning of Week End of Week Opportunities Open
11/5/2007 12:00:00 AM 11/11/2007 12:00:00 AM 369
11/12/2007 12:00:00 AM 11/18/2007 12:00:00 AM 326
11/19/2007 12:00:00 AM 11/25/2007 12:00:00 AM 203
11/26/2007 12:00:00 AM 12/2/2007 12:00:00 AM 333
12/3/2007 12:00:00 AM 12/9/2007 12:00:00 AM 421
12/10/2007 12:00:00 AM 12/16/2007 12:00:00 AM 286
12/17/2007 12:00:00 AM 12/23/2007 12:00:00 AM 411
12/24/2007 12:00:00 AM 12/30/2007 12:00:00 AM 48
12/31/2007 12:00:00 AM 1/6/2008 12:00:00 AM 234
1/7/2008 12:00:00 AM 1/13/2008 12:00:00 AM 314
1/14/2008 12:00:00 AM 1/20/2008 12:00:00 AM 309
1/21/2008 12:00:00 AM 1/27/2008 12:00:00 AM 207
What I want to do is change this so that I can get a count of new opportunties by week but also by rep. In the op_opportunity table there is a field called sales_owner that I would use. Ideally, I also want to have it Pivoted so that the weeks are columns and the sales_owner are the rows. I tried using the PIVOT command but you have to explicitly indicate your columns. Because the number of weeks that are chosen could always be different I can't do that.
Ultimately, this will be presented through reporting services so I can probably use the Cross Tab tool to show it the way I want, so if I could just get the same result set but instead the rep added and the opportunities they created that would probably work. So if we had 12 weeks and 10 reps that would produce 120 rows.
I also hardcoded the @startdate to 1/1/07 because that is a Monday and it is back far enough in the past that I don't have to worry. As we get further away from that date will that cause any performance issues?
Any input would be helpful. Thanks.
January 24, 2008 at 12:18 pm
If you add the reps as another column, you should be able to use Reporting Services to pivot it the way you want using cross-tab reports. If not, put it into an analysis services cube and you'll definitely be able to pivot it there.
What I'd do instead of hard-coding the beginning date is add something like:
select @start_date = dateadd(day, 2, dateadd(day, -1 * datepart(weekday, @start_date), @start_date)
If you add that after the point where the start date is assigned, it will set it to Monday regardless of what's entered, so long as the server is using Sunday as day 1 of the week (there are settings for that). If you change the first day of the week on the server, you'll need to rewrite the code.
With an update like that, if you put in a Wednesday date, it will change it to the prior Monday. Same for Tuesday, Thursday, Friday, or Saturday. A Sunday date will be moved ahead to Monday, and Monday dates will stay the same.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2008 at 1:53 pm
I'll try what you are suggesting on the beginning date. As far as adding the sales_owner do you have any advice on how to do that. I can't add it into the Select subquery because then it will return more than one value (Count and owner) which isn't allowed.
January 24, 2008 at 2:03 pm
Add a derived table to the From statement:
From Numbers
cross join
(select distinct sales_owner
from dbo.op_opportunity) SubOwner
Then add "and sales_owner = subowner.sales_owner", to the sub-query.
That should give you what you want.
Note that the query, as written, should work, but will be slow (depending on the number of rows in the table). Before I can suggest a best way to rewrite it, I need to know if you're using SQL 2000 or SQL 2005. Let me know if you'd like some help in that regard, or if the speed of the query doesn't matter much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2008 at 2:35 pm
Thanks GSquared. I've got the rep in there now and am just doing some validity checks to see if I agree with the numbers. I am then going to work on changing it per your suggestion on the start date.
As far as increasing the performance I am always open to getting help and learning how to do things more efficiently so since you are offering I'd be gratefull for any other input you can provide. I'm currently using 2005.
Thanks again.
January 24, 2008 at 2:43 pm
Another issue I just ran accross and am looking at resolving is that there is another table we use called op_owner. What we do in that table is track changes to the ownership of an opportunity. So, if I want to know who the opportunity was assigned to originially I'll have to incorporate that table as well. I think if I just add that table to the subquery and join the open_date with the change_date I should have it. I'll post when I have something.
January 24, 2008 at 3:11 pm
Here is where I'm at:
DECLARE @start_date datetime,
@weeks int
SET @start_date = '10/31/2007'
SELECT @start_date = dateadd(day, 2, dateadd(day, -1 * datepart(weekday, @start_date), @start_date))
SET @weeks = 12
SELECT DATEADD(dd, (n.Num-1) * 7, @start_date) [Beginning of Week],
DATEADD(dd,(n.Num * 7)-1,@start_date) [End of Week],
(
SELECT COUNT(*)
FROM op_opportunity op_o JOIN op_owner op_w ON op_o.opid = op_w.opid AND CONVERT(varchar(16), op_o.open_date, 120) = CONVERT(varchar(16), op_w.ownerchg_date, 120)
WHERE op_o.open_date BETWEEN DATEADD(dd, (n.Num-1) * 7 , @start_date) AND DATEADD(dd, (n. Num * 7)-1, @start_date)
AND op_w.role = 'Sales'
AND op_w.rep = subowner.sales_owner
) AS [Opportunities Open],
subowner.sales_owner
FROM Numbers n
CROSS JOIN (SELECT DISTINCT sales_owner FROM op_opportunity op_o JOIN employee e ON op_o.sales_owner = e.LoginName
WHERE e.TerminationDate IS NULL and e.category = 'Sales') SubOwner
WHERE DATEADD(dd,n. Num * 7,@start_date) >= DATEADD(wk, -@weeks+1, GETDATE()) AND DATEADD(dd,n. Num * 7,@start_date) <= DATEADD(wk, 1, GETDATE())
Had to convert the dates when evaluating them because I found that in some instances they were off by a millisecond or two. The only other concern I have is that if that is the case then they could be of as it rolls over to the next second as well. Any ideas on that would be welcome as well.
Probably will take out the @weeks variable since no matter what date they enter it evaluates back to the begining of the week. That works a lot better I think.
Thanks.
January 24, 2008 at 3:24 pm
DateTime fields in SQL Server have a precision of 3ms, so in SQL Server's world
2008/01/24 17:22:15.000=2008/01/24 17:22:15.001 =2008/01/24 17:22:15.002
2008/01/24 17:22:15.003=2008/01/24 17:22:15.004 =2008/01/24 17:22:15.005
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 24, 2008 at 4:12 pm
I misspoke. The seconds were off so I am converting to minutes. Same concern exists though.
January 25, 2008 at 8:59 am
Try this:
declare @Start_Date datetime, @Weeks int
declare @MaxDate datetime
select @maxdate = dateadd(week, 1, getdate()),
@start_date = dateadd(day, 2, dateadd(day, -1 * datepart(weekday, @start_date), @start_date))
;with Weeks (WStart datetime, WEnd datetime) as
(select dateadd(week, num-1, @start_date), dateadd(week, num, @start_date)
from dbo.numbers
where num between 1 and @weeks+1)
SELECT wstart [Beginning of Week],
wend [End of Week],
(SELECT COUNT(*)
FROM op_opportunity op_o
JOIN op_owner op_w
ON op_o.opid = op_w.opid
AND CONVERT(varchar(16), op_o.open_date, 120) = CONVERT(varchar(16), op_w.ownerchg_date, 120)
WHERE op_o.open_date >= weeks.wstart
AND op_o.open_date < weeks.wend
and op_o.open_date <= @maxdate
AND op_w.role = 'Sales'
AND op_w.rep = subowner.sales_owner ) AS [Opportunities Open],
subowner.sales_owner
FROM weeks
CROSS JOIN
(SELECT DISTINCT sales_owner
FROM op_opportunity op_o
JOIN employee e
ON op_o.sales_owner = e.LoginName
WHERE e.TerminationDate IS NULL
and e.category = 'Sales') SubOwner
I can't test it without having the structure of your tables and some sample data, but it should work.
If you can also add a calculated column to op_o.opid and op_w.opid for the "CONVERT(varchar(16), op_o.open_date, 120) ", and index those columns, you can get even better performance out of the whole thing.
alter table dbo.op_o
add Open_DateMinutes as cast(CONVERT(varchar(16), op_o.open_date, 120) as datetime) persisted
go
create index IDX_Op_O_DateMinutes on dbo.op_o(open_dateminutes)
(Same thing on the other table.)
You don't have to include the index, but you can try it and test the execution plan for the query with and without. Change the Join in the sub-query to use the calculated columns, instead of the functions.
You should get much better performance out of this method.
As mentioned, I can't test this, so let me know if there are any problems with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 3:30 pm
It works. I didn't add in the calculated column and try that yet but I'm not finding huge performance differences between both methods. Now, I've only timed the queries and haven't take a close look at the execution plans. I did add something in to take care of the concern I had with rolling over minute to minute. I just do a datediff and use the absolute value and require it to be less than 3 seconds. Here is how it looks in your latest iteration:
declare @Start_Date datetime, @Weeks int
declare @MaxDate datetime
SET @start_date = '8/31/2007'
select @maxdate = dateadd(week, 1, getdate()),
@start_date = dateadd(day, 2, dateadd(day, -1 * datepart(weekday, @start_date), @start_date))
SET @weeks = DATEDIFF(week, @Start_Date, GETDATE())
;with Weeks (WStart, WEnd) AS
(select dateadd(week, num-1, @start_date), dateadd(week, num, @start_date)
from dbo.Numbers
where num between 1 and @weeks+1)
SELECT wstart [Beginning of Week],
wend [End of Week],
(SELECT COUNT(*)
FROM op_opportunity op_o
JOIN op_owner_history op_h
ON op_o.opid = op_h.opid
WHERE ABS(DATEDIFF(ss, op_o.open_date, op_h.ownerchg_date)) <=3
AND op_o.open_date >= weeks.wstart
AND op_o.open_date < weeks.wend
and op_o.open_date <= @maxdate
AND op_h.role = 'Sales'
AND op_h.rep = subowner.sales_owner ) AS [Opportunities Open],
subowner.sales_owner
FROM weeks
CROSS JOIN
(SELECT DISTINCT sales_owner
FROM op_opportunity op_o
JOIN employee e
ON op_o.sales_owner = e.LoginName
WHERE e.TerminationDate IS NULL
and e.category = 'Sales') SubOwner
Thanks again for all your help and have a good weekend.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply