Creating a View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a View

Author

Topic

njguy
Starting Member

16 Posts

Posted - 2012-06-18 : 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

52326 Posts

Posted - 2012-06-18 : 14:49:28

[code]
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
[/code]

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

16 Posts

Posted - 2012-06-18 : 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

52326 Posts

Posted - 2012-06-18 : 22:34:16

i'm not understanding your output. show us in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

njguy
Starting Member

16 Posts

Posted - 2012-06-19 : 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

52326 Posts

Posted - 2012-06-19 : 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

16 Posts

Posted - 2012-06-19 : 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

227 Posts

Posted - 2012-06-20 : 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

16 Posts

Posted - 2012-06-20 : 10:04:22

The last message I sent gave data examples with results

njguy
Starting Member

16 Posts

Posted - 2012-06-20 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources