March 29, 2012 at 10:02 am
Here is the scenario.
I want to write a query to find out which employees have preformed a service on the weekend over a quarter period. They get one point REGARDLESS of if they performed one service or multiple services over the 48 hour period. If the employee has 12 points or more I want to know who they are.
Thanks
David
March 29, 2012 at 10:15 am
That sounds like a neat problem. If you post your table definitions (CREATE TABLE statements), some sample data (INSERT statements) and the structure of your desired results we can better help you. Oh yeah, please provide the queries you have tried yourself so far as well, so we can see what you have attempted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:18 am
dweil (3/29/2012)
Here is the scenario.I want to write a query to find out which employees have preformed a service on the weekend over a quarter period. They get one point REGARDLESS of if they performed one service or multiple services over the 48 hour period. If the employee has 12 points or more I want to know who they are.
Thanks
David
as opc.three stated the DDL and inserts are a must in this question.
the other thing is this a rolling quarter (last 13 weeks) or a fixed quarter (the standard 4 quarters for your business). that will also determine the complexity of the problem.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 29, 2012 at 11:54 am
The good news is that it is a standard quarter.
I'm working on putting together the ddl that I can post.
March 29, 2012 at 12:19 pm
dweil (3/29/2012)
Here is the scenario.I want to write a query to find out which employees have preformed a service on the weekend over a quarter period. They get one point REGARDLESS of if they performed one service or multiple services over the 48 hour period. If the employee has 12 points or more I want to know who they are.
Thanks
David
Obviously, change the column and table names, but is it as easy as something like this:
Select Username
From
(
Select Username, Count(1) As [Count]
From IARTS..CorrespondenceLog cl
Where DatePart(dw, cl.CreatedDate) In (6, 7)
And cl.CreatedDate >= @QuarterStart And cl.CreatedDate <= @QuarterEnd
Group By Username
) a
Where a.Count > 12
March 29, 2012 at 1:20 pm
Thanks for the query but it gets sticky when the employee has multiple services over the same weekend. For example a service both on Saturday and Sunday would mean a score of two for the weekend but the maximum you can get for any weekend is a score of one. The other minor issue is that day 6 and 7 are actually Friday and Saturday rather than Saturday and Sunday. I changed it to 7 and 1.
March 29, 2012 at 1:33 pm
dweil (3/29/2012)
Thanks for the query but it gets sticky when the employee has multiple services over the same weekend. For example a service both on Saturday and Sunday would mean a score of two for the weekend but the maximum you can get for any weekend is a score of one. The other minor issue is that day 6 and 7 are actually Friday and Saturday rather than Saturday and Sunday. I changed it to 7 and 1.
How is your DDL/DML coming? Really could use your tables, and sample data.
March 29, 2012 at 1:55 pm
dweil (3/29/2012)
Thanks for the query but it gets sticky when the employee has multiple services over the same weekend. For example a service both on Saturday and Sunday would mean a score of two for the weekend but the maximum you can get for any weekend is a score of one. The other minor issue is that day 6 and 7 are actually Friday and Saturday rather than Saturday and Sunday. I changed it to 7 and 1.
Here you go, the approach I took is this:
Since you only count one for a Saturday or a Sunday, why not just add one day if the day equals Saturday, that way the end result will always be a Sunday when it counts...
Here's the code that I wrote which I believe will work for you....again, change table names, field names, and the day of week values to match what you need. I believe you'll want to check if it equals day of week of 7, then add 1 day...
Selectb.CorrespondenceTemplateID, Count(1) As [Count]
From
(
Selecta.CorrespondenceTemplateID As [CorrespondenceTemplateID], Count(1) As [OccurrenceCount]
From
(
SelectCorrespondenceTemplateID,
Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)) As [Date],
Case
When DatePart(dw, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))) = 3
Then DateAdd(dd, 1, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)))
Else Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))
End As [NewDate]
From IARTS..CorrespondenceLog cl
Where CorrespondenceTemplateID IS NOT NULL
And DatePart(dw, cl.CreatedDate) In (3, 4)
And cl.CreatedDate Between '2012-01-01' And '2012-04-01'
) a
Group By CorrespondenceTemplateID, NewDate
) b
Where b.OccurrenceCount > 12
Group By b.CorrespondenceTemplateID
March 29, 2012 at 2:02 pm
Be careful using DATEPART, output is dependent on server options.
From DATEPART (Transact-SQL) (2008 R2)
The return value depends on the language environment set by using SET LANGUAGE and by the default language of the login.
A calendar table would be useful in this scenario.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 2:02 pm
That one is a little off, here's the fixed version:
Selectb.CorrespondenceTemplateID, b.OccurrenceCount
From
(
-- Gets the Count for each template (username)
Selecta.CorrespondenceTemplateID As [CorrespondenceTemplateID], Count(1) As [OccurrenceCount]
From
(
-- Gets everything that is a Saturday or Sunday for the corresponding templateid (username)
-- Also sets everything to the Sunday if they do occur
SelectCorrespondenceTemplateID,
Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)) As [Date],
Case
When DatePart(dw, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))) = 3
Then DateAdd(dd, 1, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)))
Else Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))
End As [NewDate]
From IARTS..CorrespondenceLog cl
Where CorrespondenceTemplateID IS NOT NULL
And DatePart(dw, cl.CreatedDate) In (3, 4)
And cl.CreatedDate Between '2012-01-01' And '2012-04-01'
) a
Group By CorrespondenceTemplateID, NewDate
) b
Where b.OccurrenceCount > 12
Hold on, this still isn't correct, there should be a max count possible of ~12 and I've got results in the thousands for some templates....
March 29, 2012 at 2:11 pm
gregory.anderson (3/29/2012)
That one is a little off, here's the fixed version:
Selectb.CorrespondenceTemplateID, b.OccurrenceCount
From
(
-- Gets the Count for each template (username)
Selecta.CorrespondenceTemplateID As [CorrespondenceTemplateID], Count(1) As [OccurrenceCount]
From
(
-- Gets everything that is a Saturday or Sunday for the corresponding templateid (username)
-- Also sets everything to the Sunday if they do occur
SelectCorrespondenceTemplateID,
Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)) As [Date],
Case
When DatePart(dw, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))) = 3
Then DateAdd(dd, 1, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)))
Else Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))
End As [NewDate]
From IARTS..CorrespondenceLog cl
Where CorrespondenceTemplateID IS NOT NULL
And DatePart(dw, cl.CreatedDate) In (3, 4)
And cl.CreatedDate Between '2012-01-01' And '2012-04-01'
) a
Group By CorrespondenceTemplateID, NewDate
) b
Where b.OccurrenceCount > 12
Hold on, this still isn't correct, there should be a max count possible of ~12 and I've got results in the thousands for some templates....
Ok, this one definitely works...make sure to take into consideration what the poster above said about using DatePart...
Selectb.CorrespondenceTemplateID, Count(1) As [Count]
From
(
-- Gets the Count for each template (username)
Selecta.CorrespondenceTemplateID As [CorrespondenceTemplateID], a.NewDate
From
(
-- Gets everything that is a Saturday or Sunday for the corresponding templateid (username)
-- Also sets everything to the Sunday if they do occur
SelectCorrespondenceTemplateID,
Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)) As [Date],
Case
When DatePart(dw, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))) = 3
Then DateAdd(dd, 1, Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101)))
Else Convert(DateTime, Convert(varchar(10), cl.CreatedDate, 101))
End As [NewDate]
From IARTS..CorrespondenceLog cl
Where CorrespondenceTemplateID IS NOT NULL
And DatePart(dw, cl.CreatedDate) In (3, 4)
And cl.CreatedDate Between '2012-01-01' And '2012-04-01'
) a
Group By CorrespondenceTemplateID, NewDate
) b
Group By b.CorrespondenceTemplateID
Having Count(1) >= 12
March 29, 2012 at 3:12 pm
I think that you are overcomplicating things by using the wrong aggregate. Instead of using COUNT(1) you should be using COUNT(DISTINCT <some expression>)
I think this does the exact same thing as your original query. Of course, it's hard to test without data.
SELECT b.CorrespondenceTemplateID, COUNT(DISTINCT w.WeekKey)
FROM CorrespondenceLog AS b
CROSS APPLY (
SELECT DATEDIFF(DAY, '2000-01-01', b.CreatedDate
) AS d(DayKey)
CROSS APPLY
SELECT DayKey/7 AS WeekKey
WHERE DayKey%7 < 2
) AS w
WHERE b.CreatedDate >= DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
AND b.CreatedDate < DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0)
GROUP BY b.CorrespondenceTemplateID
HAVING COUNT(DISTINCT w.WeekKey) >= 12
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 29, 2012 at 3:57 pm
This is very simple subset of the data but I think it shows what I am looking for.
In the fourth quarter of 2011, employee 1000 would score a 2 and employee 2000 would also score a 2 even though employee 1000 had three weekend services and employee 2000 has two weekend services.
create table service_sample
(Service_id int,
Service_Start datetime,
Empl_id int);
insert into service_sample (service_id, Service_Start, Empl_id)
values (500, '2011-10-01 11:15', 1000) ;
insert into service_sample (service_id, Service_Start, Empl_id)
values (501, '2011-10-02 12:15', 1000) ;
insert into service_sample (service_id, Service_Start, Empl_id)
values (502, '2011-10-08 12:15', 1000) ;
insert into service_sample (service_id, Service_Start, Empl_id)
values (800, '2011-10-01 9:15', 2000) ;
insert into service_sample (service_id, Service_Start, Empl_id)
values (802, '2011-10-08 10:15', 2000) ;
March 30, 2012 at 7:25 am
A simple modification of my previous response will give you the results you want. Of course, including the HAVING clause excludes both of your records.
DECLARE @QuarterEnd DATE = '2011-12-31'
SELECT s.Empl_ID, COUNT(DISTINCT w.WeekKey)
FROM #Service_Sample AS s
CROSS APPLY(
SELECT DATEDIFF(DAY, '2000-01-01', s.Service_Start)
) AS d(DayKey)
CROSS APPLY (
SELECT DayKey/7 AS WeekKey
WHERE DayKey%7 < 2
) AS w
WHERE s.Service_Start >= DATEADD(qq, DATEDIFF(qq, 0, @QuarterEnd), 0)
AND s.Service_Start < DATEADD(qq, DATEDIFF(qq, 0, @QuarterEnd) + 1, 0)
GROUP BY Empl_ID
HAVING COUNT(DISTINCT w.WeekKey) >= 12
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply