Could someone help me with this que 1

[infinitizon]

Could someone help me with this query, it doesn't seem to be performing as expected.

SELECT (CASE SUM(DATEDIFF(SECOND,start_time,end_time))
	    WHEN NULL THEN 0 ELSE SUM(DATEDIFF(SECOND,start_time,end_time)) 
	END)[totalMthSecs]
FROM OvertimeApplication
WHERE DATEPART(MONTH,apply_date)=5 AND staff_id='CST000012'

It is supposed to check if the total number of seconds done on overtime by a staff (e.g staff_id='CST000012') for a particular month (e.g. DATEPART(MONTH,apply_date)=5) exists. That is, it is not null. If it is null put zero(0) else, put the number of seconds.

it keeps on giving me null if the staff_id does not exist instead of zero(0).
I'm using MSSQL Server 2008

Help.

 

[infinitizon]

This is a test data

|over_id      |  staff_id  |dept_id |desig  | apply_date | start_time    | end_time
|OVT0000092   |  CST000003 |  003   |  MIS  |2012-05-22  | 07:00:00.000  | 16:00:00.000
|OVT0000093   |  CST000004 |  003   |  MIS  |2012-05-22  | 07:00:00.000  | 15:00:00.000
|OVT0000094   |  CST000003 |  003   |  MIS  |2012-05-29  | 08:00:00.000  | 17:00:00.000
|OVT0000095   |  CST000007 |  003   |  MIS  |2012-05-29  | 11:00:00.000  | 18:05:00.000

 

[SQLScholar]

Ok - well you would need to have a join to somewhere where the ID does exist. I wont show you rows that arent there.

So do you have a table with all the staff ID`s in? If so i would start with that table then join to the one with the overtime numbers. Then with the where clause search the staff ID in the new table.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[bborissov]

DECLARE @Test TABLE (over_id char(10),
                     staff_id CHAR(10),
                     dept_id char(3),
                     desig char(3),
                     apply_date date,
                     start_time time,
                     end_time  time)
INSERT INTO @Test VALUES ('OVT0000092','CST000003', '003','MIS','20120522','07:00:00.000','16:00:00.000')


INSERT INTO @Test VALUES ('OVT0000093','CST000004','003','MIS','20120522','07:00:00.000','15:00:00.000')
INSERT INTO @Test VALUES ('OVT0000094','CST000003','003','MIS','20120529','08:00:00.000','17:00:00.000')
INSERT INTO @Test VALUES ('OVT0000095','CST000007','003','MIS','20120529','11:00:00.000','18:05:00.000')

SELECT ISNULL(totalMthSecs,0) AS totalMthSecs
FROM(
SELECT SUM(DATEDIFF(SECOND,start_time,end_time)) AS totalMthSecs
FROM @Test
WHERE MONTH(apply_date)=5 AND
      staff_id='CST000012' ) Tbl1

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[infinitizon]

@bborissov: Thanks, I forgot the ISNULL() function. Your answer pointed me in the right direction. So I changed my query to:

SELECT ISNULL(SUM(DATEDIFF(SECOND,start_time,end_time)),0)[totalMthSecs]
FROM [OvertimeApplication]
WHERE DATEPART(MONTH,apply_date)=5 AND staff_id='CST0000333'

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.