April 6, 2011 at 1:24 pm
Hi sql experts,
I am trying to create a query that returns data from the 3 tables below so that it looks like this:
Year Name Complete In Progress Incomplete
2013 Bill 0 1 0
2012 Bob 0 2 0
2012 Doug 0 1 0
2012 Bill 0 1 0
2011 Bob 1 0 0
2011 Doug 0 0 1
2010 Bill 1 0 0
2010 Bob 0 0 1
2009 Doug 2 0 0
2008 Doug 1 0 0
2008 Bill 0 0 1
For each year each employees goals status is counted.
I am new to programming and don't even know where to start with something like this.
Any help would be greatly appreciated!!
declare @Employee table (
ID int,
Name varchar(20)
)
insert into @Employee (
ID,
Name
)
values
(1, 'Bob'),
(2, 'Doug'),
(3, 'Bill')
declare @GoalStatus table (
ID int,
GoalStatusDesc varchar(50)
)
insert into @GoalStatus (
ID,
GoalStatusDesc
)
values
(1, 'Complete'),
(2, 'In Progress'),
(3, 'Incomplete')
declare @Goal table (
EmployeeID int,
DeadlineDate date,
GoalStatusID int
)
insert into @Goal (
EmployeeID,
DeadlineDate,
GoalStatusID
)
values
(1, '2011-12-31', 1),
(1, '2012-12-31', 2),
(1, '2012-12-31', 2),
(1, '2010-12-31', 3),
(2, '2008-12-31', 1),
(2, '2009-12-31', 1),
(2, '2011-12-31', 3),
(2, '2012-12-31', 2),
(3, '2012-12-31', 2),
(3, '2013-12-31', 2),
(3, '2010-12-31', 1),
(3, '2008-12-31', 3)
SELECT
G.DeadlineDate,
E.Name,
S.GoalStatusDesc
FROM
@Employee E,
@GoalStatus S,
@Goal G
WHERE
E.ID = G.EmployeeID AND
G.GoalStatusID = S.ID
ORDER BY
DeadlineDate DESC
April 6, 2011 at 3:54 pm
I think this is what you want. The case expression is very powerful addition to your sql arsenal. Thanks for posting the useful DDL.
SELECT
G.DeadlineDate,
E.Name,
Sum(Case when S.GoalStatusDesc = 'Complete' then 1 else 0 end) as 'Complete',
Sum(Case when S.GoalStatusDesc = 'In Progress' then 1 else 0 end) as 'In Progress',
Sum(Case when S.GoalStatusDesc = 'Incomplete' then 1 else 0 end) as 'Incomplete'
FROM
@Employee E,
@GoalStatus S,
@Goal G
WHERE
E.ID = G.EmployeeID AND
G.GoalStatusID = S.ID
group by
G.DeadlineDate,
E.Name
ORDER BY
DeadlineDate DESC
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 7, 2011 at 9:48 am
Thanks toddasd!
Works great, but I am having a small issue though. In the real goal table the deadline date isn't always yyyy-12-31, it's all kinds of different dates month/days, like so:
insert into @Goal (
EmployeeID,
DeadlineDate,
GoalStatusID
)
values
(1, '2011-05-12', 1),
(1, '2012-11-05', 2),
(1, '2012-08-09', 2),
(1, '2010-10-16', 3),
(2, '2008-02-22', 1),
(2, '2009-06-21', 1),
(2, '2011-12-08', 3),
(2, '2012-12-04', 2),
(3, '2012-12-05', 2),
(3, '2013-02-15', 2),
(3, '2013-01-12', 3),
(3, '2010-06-15', 1),
(3, '2008-11-16', 3),
(3, '2008-12-21', 4),
(3, '2008-03-27', 3)
The data doesn't seem to want to group properly when the month/days are different. I tried casting the dates so they would all be the same month/date, but still no luck.
SELECT
CAST(DateAdd(year, DateDiff(year,0, G.DeadlineDate), 0) as date) DeadlineDate,
E.Name,
Sum(Case when S.GoalStatusDesc = 'Complete' then 1 else 0 end) as 'Complete',
Sum(Case when S.GoalStatusDesc = 'In Progress' then 1 else 0 end) as 'In Progress',
Sum(Case when S.GoalStatusDesc = 'Incomplete' then 1 else 0 end) as 'Incomplete'
FROM
@Employee E,
@GoalStatus S,
@Goal G
WHERE
E.ID = G.EmployeeID AND
G.GoalStatusID = S.ID
group by
G.DeadlineDate,
E.Name
ORDER BY
DeadlineDate DESC
Thanks so much for your help!!
April 7, 2011 at 10:17 am
If you want to group it by year, as in your original post, then you can get the year from the date with the year() function. Also remember to group by the same expression.
SELECT
year(G.DeadlineDate) DeadlineDate,
E.Name,
Sum(Case when S.GoalStatusDesc = 'Complete' then 1 else 0 end) as 'Complete',
Sum(Case when S.GoalStatusDesc = 'In Progress' then 1 else 0 end) as 'In Progress',
Sum(Case when S.GoalStatusDesc = 'Incomplete' then 1 else 0 end) as 'Incomplete'
FROM
@Employee E,
@GoalStatus S,
@Goal G
WHERE
E.ID = G.EmployeeID AND
G.GoalStatusID = S.ID
group by
year(G.DeadlineDate),
E.Name
ORDER BY
G.DeadlineDate DESC
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 7, 2011 at 10:34 am
Perfect!!
Thanks so much for your help, it is greatly appreciated!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply