Display All Month Data - 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

Display All Month Data

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-20 : 10:06:45

I have the following script:

SELECT tsk.TASK_NAME, COUNT(*) AS total, SUM(COALESCE (ts.TS_HOURS, 0)) AS total_hours, MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear
FROM OREX_TIMESHEET_DATA ts
LEFT OUTER JOIN OREX_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO
WHERE (ts.PROJ_NO IN (9381, 9382, 9383))
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, workYear, workMonth

TASK_NAME total total_hours workMonth workYear
Design 1 3 4 2009
Management 5 14 5 2009
Other 3 19 5 2009
Planning 12 30 1 2009

I would like the output to be:
TASK_NAME total total_hours workMonth workYear
Design 0 0 1 2009
Design 1 3 4 2009
Design 0 0 5 2009
Management 0 0 1 2009
Management 0 0 4 2009
Management 5 14 5 2009
Other 0 0 1 2009
Other 0 0 4 2009
Other 3 19 5 2009
Planning 12 30 1 2009
Planning 0 0 4 2009
Planning 0 0 5 2009

How can I modify the script to display the zero value months? I previously was using a select within a select when I had a single table, but now I have a separate timesheet and task table.

Thanks for any advice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 12:46:28

does OREX_TIMESHEET_DATA table has record for 0 valued months?

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-20 : 13:31:38

quote:
Originally posted by visakh16

does OREX_TIMESHEET_DATA table has record for 0 valued months?

No. I would exclude zero value months. Generally it is a range (Jan - Jun, April-Oct, etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:43:01

then why are you using it as base table? which table has whole month data?

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-20 : 13:57:33

The timesheet data table contains the hours worked on for a particular project. So for example, I have three hours in the Design phase during April 2009. I have o hours in January or May. I want to be able to show 0 when there are no hours for a task in the respective month.

The Timesheet table contains all the time entries for various project. The fields are as follows:

EMP_NO
PROJ_NO
TASK_NO
WORK_DT
TS_HOURS

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-20 : 14:00:30

FYI. This is my current script that works with a single table PROJECT_HOURS that provides the desired results:

SELECT m.task_name, COALESCE (f.total, 0) AS total, COALESCE (f.total_hours, 0) AS total_hours, m.[Month] AS workMonth, m.[Year] AS workYear
FROM (SELECT task_name, Month, Year
FROM (SELECT task_name
FROM PROJECT_HOURS
WHERE (project_number IN (9381, 9382, 9383))
GROUP BY task_name
HAVING COUNT(hours) > 0) AS t CROSS JOIN
(SELECT DISTINCT MONTH(work_date) AS Month, YEAR(work_date) AS year
FROM PROJECT_HOURS
WHERE (project_number IN (9381, 9382, 9383))) mnth) m LEFT OUTER JOIN
(SELECT task_name, COUNT(*) AS total, SUM(COALESCE (hours, 0)) AS total_hours, MONTH(work_date) AS workMonth, YEAR(work_date)
AS workYear
FROM PROJECT_HOURS
WHERE (project_number IN (9381, 9382, 9383))
GROUP BY task_name, MONTH(work_date), YEAR(work_date)) f ON f.task_name = m.task_name AND m.[Month] = f.workMonth AND
m.[Year] = f.workYear
ORDER BY m.task_name, m.[Year], m.[Month]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:07:49

only thing you need to do is to use table will all month data as base table and left join all other tables to this if you want all months data.
if you still cant make out from my explanation, post some data from your table and also reqd output in below format
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-20 : 14:38:14

Here is the structure:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DCSC_TIMESHEET_DATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DCSC_TIMESHEET_DATA]
GO

CREATE TABLE [dbo].[DCSC_TIMESHEET_DATA] (
[TRANS_ID] [int] NOT NULL ,
[EMP_NO] [int] NOT NULL ,
[PROJ_NO] [int] NOT NULL ,
[TASK_NO] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TS_WEEKEND_DT] [datetime] NOT NULL ,
[WORK_DT] [datetime] NOT NULL ,
[TS_HOURS] [decimal](38, 2) NOT NULL ,
[HOURS_TYPE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATE_DT] [datetime] NULL
) ON [PRIMARY]
GO

Here's the sample data:

TRANS_ID EMP_NO PROJ_NO TASK_NO TS_WEEKEND_DT WORK_DT TS_HOURS HOURS_TYPE CREATE_DT
6064898 999 9381 1.16 1/9/2009 1/5/2009 2 Labor 1/14/2009
6064899 999 9383 1.16 1/9/2009 1/5/2009 3 Labor 1/14/2009
6064903 999 9383 1.16 1/9/2009 1/6/2009 4 Labor 1/14/2009
6064910 999 9381 1.16 1/9/2009 1/7/2009 1 Labor 1/14/2009
6092826 999 9381 1.16 1/16/2009 1/12/2009 2 Labor 1/19/2009
6092827 999 9383 1.16 1/16/2009 1/12/2009 3 Labor 1/19/2009
6092831 999 9383 1.16 1/16/2009 1/13/2009 4 Labor 1/19/2009
6092838 999 9381 1.16 1/16/2009 1/14/2009 1 Labor 1/19/2009
6121205 999 9381 1.16 1/23/2009 1/19/2009 2 Labor 1/26/2009
6121206 999 9383 1.16 1/23/2009 1/19/2009 3 Labor 1/26/2009
6121210 999 9383 1.16 1/23/2009 1/20/2009 4 Labor 1/26/2009
6121217 999 9381 1.16 1/23/2009 1/21/2009 1 Labor 1/26/2009
6620397 998 9383 1.03 5/1/2009 4/30/2009 3 Labor 5/1/2009
6654969 777 9382 1.19 5/8/2009 5/4/2009 8 Labor 5/8/2009
6654970 777 9382 1.19 5/8/2009 5/5/2009 8 Labor 5/8/2009
6654971 777 9382 1.19 5/8/2009 5/6/2009 3 Labor 5/8/2009
6702403 662 9381 1.17 5/15/2009 5/11/2009 2 Labor 5/18/2009
6702406 662 9381 1.17 5/15/2009 5/12/2009 4 Labor 5/18/2009
6702408 662 9381 1.17 5/15/2009 5/13/2009 4 Labor 5/18/2009
6702410 662 9381 1.17 5/15/2009 5/14/2009 2 Labor 5/18/2009
6702413 662 9381 1.17 5/15/2009 5/15/2009 2 Labor 5/18/2009

Finally, this is the expected output:

TASK_NAME total total_hours workMonth workYear
Design 0 0 1 2009
Design 1 3 4 2009
Design 0 0 5 2009
Management 0 0 1 2009
Management 0 0 4 2009
Management 5 14 5 2009
Other 0 0 1 2009
Other 0 0 4 2009
Other 3 19 5 2009
Planning 12 30 1 2009
Planning 0 0 4 2009
Planning 0 0 5 2009

I cannot simply use a 12 month table because the monthly range differs from project to project.

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:48:13

so you want to display totals based on distinct month values existing in above table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 15:01:19

[code]SELECT m.TASK_NAME, COUNT(*) AS total, SUM(COALESCE (ts1.TS_HOURS, 0)) AS total_hours, workMonth, workYear
FROM
(SELECT TASK_NO ,PROJ_NO,TASK_NAME,workMonth, workYear
FROM
(SELECT DISTINCT TASK_NO ,tsk.PROJ_NO,tsk.TASK_NAME
FROM OREX_TASK) tsk
CROSS JOIN (SELECT DISTINCT MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear
FROM OREX_TIMESHEET_DATA )ts
)m
LEFT JOIN OREX_TIMESHEET_DATA ts1
ON m.workMonth =MONTH(ts1.WORK_DT)
AND m.workYear =YEAR(ts1.WORK_DT)
AND ts1.TASK_NO = m.TASK_NO
AND ts1.PROJ_NO = m.PROJ_NO
LEFT OUTER JOIN OREX_TASK tsk1
ON m.TASK_NO = tsk1.TASK_NO
AND m.PROJ_NO = tsk1.PROJ_NO
AND m.PROJ_NO IN (9381, 9382, 9383))
GROUP BY m.TASK_NAME, m.workMonth, m.workYear
ORDER BY m.TASK_NAME, m.workMonth, m.workYear
[/code]

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-20 : 15:47:46

I think there's some issue with an extra paren after the "AND m.PROJ_NO IN (9381, 9382, 9383))" I removed the extra parenthesis, but then receive an error that the column prefix 'tsk' does not match a table. I do see it defined, but it is being ignored.

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-22 : 11:08:52

OK. I've worked with the script and have it working. My last question is that it is displaying all task. I only want to display a task if there are hours associated in at least one of the months. If all months are zero, I do not want the task included in the record set.

SELECT m.TASK_NAME, COUNT(*) AS total, SUM(COALESCE (ts1.TS_HOURS, 0)) AS total_hours, m.workMonth, m.workYear
FROM (SELECT TASK_NO, PROJ_NO, TASK_NAME, workMonth, workYear
FROM (SELECT DISTINCT TASK_NO, PROJ_NO, TASK_NAME
FROM OREX_TASK) tsk CROSS JOIN
(SELECT DISTINCT MONTH(WORK_DT) AS workMonth, YEAR(WORK_DT) AS workYear
FROM OREX_TIMESHEET_DATA) ts) m LEFT OUTER JOIN
OREX_TIMESHEET_DATA ts1 ON m.workMonth = MONTH(ts1.WORK_DT) AND m.workYear = YEAR(ts1.WORK_DT) AND ts1.TASK_NO = m.TASK_NO AND
ts1.PROJ_NO = m.PROJ_NO LEFT OUTER JOIN
OREX_TASK tsk1 ON m.TASK_NO = tsk1.TASK_NO AND m.PROJ_NO = tsk1.PROJ_NO AND m.PROJ_NO IN (9381, 9382, 9383)
GROUP BY m.TASK_NAME, m.workMonth, m.workYear
ORDER BY m.TASK_NAME, m.workMonth, m.workYear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 14:01:48

[code]
SELECT m.TASK_NAME, COUNT(*) AS total, SUM(COALESCE (ts1.TS_HOURS, 0)) AS total_hours, workMonth, workYear
FROM
(SELECT TASK_NO ,PROJ_NO,TASK_NAME,workMonth, workYear
FROM
(SELECT DISTINCT ot.TASK_NO , ot.PROJ_NO, ot.TASK_NAME
FROM OREX_TASK ot
JOIN OREX_TIMESHEET_DATA td
ON td.TASK_NO = ot.TASK_NO
AND td.PROJ_NO = ot.PROJ_NO
GROUP BY ot.TASK_NO , ot.PROJ_NO, ot.TASK_NAME
HAVING SUM(td.TS_HOURS)>0) tsk
CROSS JOIN (SELECT DISTINCT MONTH(WORK_DT) AS workMonth, YEAR(WORK_DT) AS workYear
FROM OREX_TIMESHEET_DATA )ts
)m
LEFT JOIN OREX_TIMESHEET_DATA ts1
ON m.workMonth =MONTH(ts1.WORK_DT)
AND m.workYear =YEAR(ts1.WORK_DT)
AND ts1.TASK_NO = m.TASK_NO
AND ts1.PROJ_NO = m.PROJ_NO
LEFT OUTER JOIN OREX_TASK tsk1
ON m.TASK_NO = tsk1.TASK_NO
AND m.PROJ_NO = tsk1.PROJ_NO
AND m.PROJ_NO IN (9381, 9382, 9383)
GROUP BY m.TASK_NAME, m.workMonth, m.workYear
ORDER BY m.TASK_NAME, m.workMonth, m.workYear
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources