Need to show groups with no values.

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

Need to show groups with no values.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 11:37:11

Please look at the following code:

SELECT (rtrim (prempl.ffname) + ' ' + prempl.fname) AS [Full Name],
       (CASE jomast.fcusrchr1
           WHEN '' THEN 'Direct'
           WHEN 'I' THEN 'Indirect'
           WHEN 'E' THEN 'Engineering'
        END)
          AS [Type],
       sum(convert (NUMERIC (6, 2),
                    (DATEDIFF ("s", ladetail.fsdatetime, ladetail.fedatetime))
                    / 3600.0
           )),
       convert (VARCHAR, ladetail.fedatetime, 111) AS [HoursWorked]




  FROM       dbo.prempl prempl
          LEFT OUTER JOIN
             dbo.ladetail ladetail
          ON prempl.fempno = ladetail.fempno
       LEFT OUTER JOIN
          dbo.jomast jomast
       ON ladetail.fjobno = jomast.fjobno
 WHERE ladetail.fedatetime >= {TS '2009-02-07 10:40:48'}
GROUP BY (rtrim (prempl.ffname) + ' ' + prempl.fname),
         convert (VARCHAR, ladetail.fedatetime, 111),
         (CASE jomast.fcusrchr1
             WHEN '' THEN 'Direct'
             WHEN 'I' THEN 'Indirect'
             WHEN 'E' THEN 'Engineering'
          END)ORDER BY (rtrim (prempl.ffname) + ' ' + prempl.fname),
         convert (VARCHAR, ladetail.fedatetime, 111)

Here's the thing. Even if there aren't any Indirect or Engineering jobs, I still want the group to show with a 0.

Conversely, if ladetail.fedatetime isn't found in the range requested, I'd still like 0 values for that day.

Does that make sense? How can I achieve this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 12:16:52

quote:
Originally posted by DavidChel

Please look at the following code:

[code]SELECT (rtrim (prempl.ffname) + ' ' + prempl.fname) AS [Full Name],
(CASE jomast.fcusrchr1
WHEN '' THEN 'Direct'
WHEN 'I' THEN 'Indirect'
WHEN 'E' THEN 'Engineering'
ELSE '0' END)
AS [Type],
sum(convert (NUMERIC (6, 2),
(DATEDIFF ("s", ladetail.fsdatetime, ladetail.fedatetime))
/ 3600.0
)),
Coalesce(convert (VARCHAR, ladetail.fedatetime, 111),'0') AS [HoursWorked]

FROM dbo.prempl prempl
LEFT OUTER JOIN
dbo.ladetail ladetail
ON prempl.fempno = ladetail.fempno
LEFT OUTER JOIN
dbo.jomast jomast
ON ladetail.fjobno = jomast.fjobno
WHERE ladetail.fedatetime >= {TS '2009-02-07 10:40:48'}
GROUP BY (rtrim (prempl.ffname) + ' ' + prempl.fname),
Coalesce(convert (VARCHAR, ladetail.fedatetime, 111),'0'),
(CASE jomast.fcusrchr1
WHEN '' THEN 'Direct'
WHEN 'I' THEN 'Indirect'
WHEN 'E' THEN 'Engineering'
ELSE '0' END)ORDER BY
[FULL NAME],[HOURSWORKED]

Here's the thing. Even if there aren't any Indirect or Engineering jobs, I still want the group to show with a 0.

Conversely, if ladetail.fedatetime isn't found in the range requested, I'd still like 0 values for that day.

Does that make sense? How can I achieve this?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 12:31:34

I appreciate that help, but that doesn't seem to have done the trick.

I have the following code:

SELECT (rtrim (prempl.ffname) + ' ' + prempl.fname) AS [Full Name],
       (CASE jomast.fcusrchr1
           WHEN '' THEN 'Direct'
           WHEN 'I' THEN 'Indirect'
           WHEN 'E' THEN 'Engineering'
           ELSE '0'
        END)
          AS [Type],
       sum(CASE DATEPART (weekday, ladetail.fedatetime)
              WHEN 4
              THEN
                 convert (NUMERIC (6, 2),
                          (DATEDIFF ("s",
                                     ladetail.fsdatetime,
                                     ladetail.fedatetime
                           ))
                          / 3600.0
                 )
              ELSE
                 0
           END)
          AS [Wednesday]
 /*      sum(convert(NUMERIC (6, 2),
                    (DATEDIFF ("s", ladetail.fsdatetime, ladetail.fedatetime))
                    / 3600.0
           ))
       AS [HoursWorked]
  --coalesce(convert(varchar,ladetail.fedatetime,111),'0') */




  FROM       dbo.prempl prempl
          LEFT OUTER JOIN
             dbo.ladetail ladetail
          ON prempl.fempno = ladetail.fempno
       LEFT OUTER JOIN
          dbo.jomast jomast
       ON ladetail.fjobno = jomast.fjobno
 WHERE ladetail.fedatetime >= {TS '2009-02-07 10:40:48'}
GROUP BY (rtrim (prempl.ffname) + ' ' + prempl.fname),
         convert (VARCHAR, ladetail.fedatetime, 111),
(CASE jomast.fcusrchr1
           WHEN '' THEN 'Direct'
           WHEN 'I' THEN 'Indirect'
           WHEN 'E' THEN 'Engineering'
           ELSE '0'
        END)

However, I still don't get Engineering because nothing in the range qualifies for it.

Did I apply the fix above wrong? Any other suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 12:34:08

which is your master table? the table that contains Engineering info?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 12:38:13

prempl is an employee table.
ladetail is a labor detail table.
JoMast is the job order master table.

What I am building is an hours worksheet.

It should look similar to this:


	DATE	 	12/01/07	12/02/07	12/03/07	12/04/07	12/05/07	12/06/07	12/07/07
	NAME		   SAT	SUN	MON	TUE	WED	THUR	FRI
	 	 							
									
1379	Bugs Bunny								
	INDIRECT	 							
 	O/T								
	ENGINEERING		 	 		0.00 	0.00 	 	
	TOTAL		0.00 	0.00 	0.00 	0.00 	0.00 	0.00 	0.00 

	DATE	 	12/01/07	12/02/07	12/03/07	12/04/07	12/05/07	12/06/07	12/07/07
	NAME		   SAT	SUN	MON	TUE	WED	THUR	FRI
	 	 							
									
1380	Daffy Duck							
	INDIRECT	 							
 	O/T								
	ENGINEERING		 	 		0.00 	0.00 	 	
	TOTAL		0.00 	0.00 	0.00 	0.00 	0.00 	0.00 	0.00

Does that help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 12:49:52

which table contains the values INDIRECT
O/T
ENGINEERING,..?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 12:53:37

JOMast.

(CASE jomast.fcusrchr1
             WHEN '' THEN 'Direct'
             WHEN 'I' THEN 'Indirect'
             WHEN 'E' THEN 'Engineering'
          END)

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 12:55:41

Different jobs qualify for direct labor, indirect labor, and engineering labor.

However, not every employee performs all 3 kinds of jobs. With my query, employees who didn't do engineering don't have a value for that. I want a value of 0 to show for them.

Am I making sense? I really appreciate your help and am not trying to be difficult.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:06:39

quote:
Originally posted by DavidChel

Different jobs qualify for direct labor, indirect labor, and engineering labor.

However, not every employee performs all 3 kinds of jobs. With my query, employees who didn't do engineering don't have a value for that. I want a value of 0 to show for them.

Am I making sense? I really appreciate your help and am not trying to be difficult.

for that you need to declare a temporary table with all job details seperately and use it as base table in query. then left join with other tables on respective fields and then use isnull() or coalesce() in select to replace nulls by 0s for non available values

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 13:14:27

Thanks Visakh. That's what I thought I would have to do. I'll work towards that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:27:51

welcome
let us know if you face any more issues

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-02-13 : 15:02:09

I feel like an idiot, but I have to ask.

So, I create a temporary table with the following code:

select * 
into #Labor
from LADETAIL

where ladetail.fedatetime >= {TS '2009-02-07 10:40:48'}

This contains all the labor stats that I need.

What kind of query am I going to write to tie this temporary table to the employee and jobs tables to group by employee and jomast.fcusrchr1?

Can you provide a little more explanation?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 00:43:25

nope you dont need to take all columns . just this will do


select DISTINCT CASE jomast.fcusrchr1
             WHEN '' THEN 'Direct'
             WHEN 'I' THEN 'Indirect'
             WHEN 'E' THEN 'Engineering'
          END AS Type 
into #Labor
from LADETAIL

then use this as base

SELECT l.Type,ISNULL(q.field1,0),ISNULL(q.field2,0)...
FROM #Labor l
LEFT JOIN
(your current query) q
ON q.[Type]=l.[Type]
.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources