| Author |
 Topic  |
|
|
njguy
Starting Member
USA
16 Posts |
 Posted - 06/18/2012 : 13:47:18
|
I am having a difficult time on where to start in creating the following view based on two tables.
Project Table:
ProjectId
May
Jun
Jul
Aug
Sep
Oct
Nov
ProjectTasks Table: (Many records for each Project)
ProjectTaskId
ProjectId
DueDate
The result set would be:
Total Sum of the months from the Project Table
Total Count of Projects > system Date
Total Count of Projects < system Date
Total Count of Projects = system Date
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/18/2012 : 14:49:28
|
select m.ProjectId,
SUM(val) as Total,
SUM(CASE WHEN MONTH(DueDate) < MonthNo THEN m.val END) AS FutureTotal,
SUM(CASE WHEN MONTH(DueDate) > MonthNo THEN m.val END) AS PrevTotal,
SUM(CASE WHEN MONTH(DueDate) = MonthNo THEN m.val END) AS CurrentTotal
from
(
select ProjectId,
CASE monthname
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
...
WHEN 'Dec' THEN 12
END AS MonthNo,
val
from Project p
unpivot(val for monthname in (May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov
))u
)m
inner join ProjectTasks pt
on pt.ProjectId = m.ProjectId
group by m.ProjectId
i assume you've only singe year worth of data in Project else you might have to include year field also in it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
njguy
Starting Member
USA
16 Posts |
Posted - 06/18/2012 : 15:02:52
|
My bad on explaing the result set ....it should be
The result set would be:
Total Sum of the months from the Project Table
Total Count of ProjectTasks > system Date (From Task Table)
Total Count of ProjectsTasks < system Date (From Task Table)
Total Count of ProjectsTaks = system Date (From Task Table)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
|
|
njguy
Starting Member
USA
16 Posts |
Posted - 06/19/2012 : 09:09:36
|
Project Table:
ProjectId
May
Jun
Jul
Aug
Sep
Oct
Nov
..
..
ProjectTasks Table: (Many records for each Project)
ProjectTaskId
ProjectId
DueDate
select p.projectid, sum(p.may + p.jun + p.jul + p.aug + p.sep + p.oct + p.nov + p.dec + p.jan + p.feb + p.mar + p.apr) as total
from projects p
left outer join projecttasks pt
on pt.projectid = p.projectid
group by p.projectid
The above gets me the totals I am looking for the second part I need is to count the number of tasks for each project and categorize them based on the system date
Total Count of Project tasks(duedate) > system Date
Total Count of Project tasks(duedate) < system Date
Total Count of Project tasks(duedate) = system Date
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/19/2012 : 14:17:43
|
thats what i gave suggestion. try it first and see what it gives. then join that query onto your current query to get counts merged with this
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
njguy
Starting Member
USA
16 Posts |
Posted - 06/19/2012 : 22:19:16
|
I ran the following:
select m.ProjectId,
SUM(val) as Total,
SUM(CASE WHEN MONTH(DueDate) < MonthNo THEN m.val END) AS FutureTotal,
SUM(CASE WHEN MONTH(DueDate) > MonthNo THEN m.val END) AS PrevTotal,
SUM(CASE WHEN MONTH(DueDate) = MonthNo THEN m.val END) AS CurrentTotal
from
(
select ProjectId,
CASE monthname
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'Apr' THEN 4
WHEN 'May' THEN 5
WHEN 'Jun' THEN 6
WHEN 'Jul' THEN 7
WHEN 'Aug' THEN 8
WHEN 'Sep' THEN 9
WHEN 'Oct' THEN 10
WHEN 'Nov' THEN 11
WHEN 'Dec' THEN 12
END AS MonthNo,
val
from Projects p
unpivot(val for monthname in (May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec,
Jan,
Feb,
Mar,
Apr
))u
)m
left outer join ProjectTasks pt
on pt.ProjectId = m.ProjectId
group by m.ProjectId
Sample result set:
ProjectId Total FutureTotal PrevTotal CurrentTotal
1 900.00 300.00 300.00 300.00
What I was looking for was a count based on the following
Total Count of Project tasks that have a duedate > current system Date
Total Count of Project tasks that have a duedate < current system Date
Total Count of Project tasks that have a duedate = current system Date
so based on this projecttask table
ProjectTaskId ProjectId ActionItem DueDate
37 1 action1 2012-06-19
38 1 action2 2012-06-01
39 1 action3 2012-06-26
39 1 action3 2012-06-19
the results should have been
ProjectId Total FutureDateCount PrevDateCount CurrentDateCount
1 900.00 1 1 2 |
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/20/2012 : 06:41:54
|
quote:
Originally posted by njguy
My bad on explaing the result set ....it should be
The result set would be:
Total Sum of the months from the Project Table
Total Count of ProjectTasks > system Date (From Task Table)
Total Count of ProjectsTasks < system Date (From Task Table)
Total Count of ProjectsTaks = system Date (From Task Table)
Its still your bad mate.
If you still haven't got it working then it means that you forgot to give enough info.
If you post the DDL of the tables and some sample Data to go with it then you can get faster and better solutions.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
njguy
Starting Member
USA
16 Posts |
Posted - 06/20/2012 : 10:04:22
|
| The last message I sent gave data examples with results |
|
|
|
njguy
Starting Member
USA
16 Posts |
Posted - 06/20/2012 : 13:24:53
|
The following accomplished what I needed....
select p.projectid, sum(p.may + p.jun + p.jul + p.aug + p.sep + p.oct + p.nov + p.dec + p.jan + p.feb + p.mar + p.apr) as total,
COUNT(CASE WHEN pt.DueDate > CONVERT(date,GETDATE()) THEN pt.ProjectId END) AS TotalLate,
COUNT(CASE WHEN pt.DueDate < CONVERT(date,GETDATE()) THEN pt.ProjectId END) AS TotalOnTime,
COUNT(CASE WHEN pt.DueDate = CONVERT(date,GETDATE()) THEN pt.ProjectId END) AS TotalCurrent,
CONVERT(date,GETDATE()) as currentdate
from projects p
left outer join projecttasks pt
on pt.projectid = p.projectid
group by p.projectid
|
|
|
| |
Topic |
|
Creating a View
