Query showing NULL when it should show a value

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

SQL Server 2000 Forums

Transact-SQL (2000)

Query showing NULL when it should show a value

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-23 : 15:24:26

I have the following query:

SELECT DISTINCT [ScratchPad5].EmployeeNumber, Sum([ScratchPad5].sumhours),
case when [sumhours]>40 THEN [sumhours]-40 ELSE 0 END AS ot, case when [sumhours]>40 THEN [sumhours]-[ot] ELSE [sumhours] END AS RegHours

FROM ScratchPad5

GROUP BY [ScratchPad5].EmployeeNumber, sumhours, ot

and the result set that it is giving me is this:

Employeenumber (No Column Name) ot RegHours
8247 23.683166 .000000 23.683166
8330 3.000000 .000000 3.000000
8378 29.226166 .000000 29.226166
8389 27.221166 .000000 27.221166
8428 32.371000 .000000 32.371000
8433 31.898833 .000000 31.898833
8442 29.853500 .000000 29.853500
8454 17.349500 .000000 17.349500
8455 25.910500 .000000 25.910500
8462 35.770833 .000000 35.770833
8464 38.410332 .000000 19.205166
8465 30.944500 .000000 30.944500
8466 29.450666 .000000 29.450666
8467 51.734000 11.734000 NULL

and it what I'm needing it to do is for entries like the last one, to show me both the total time worked (No Column Name) and the OT but then it should show 40 hours for Reg Hours. Can anyone please assist with this?

Thanks

Doug

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-23 : 16:54:23

You can do that?

In any case..you're grouping by a scalar function?

I didn't think that was possible..or had any meaning?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 10:02:41

are you sure query you posted is working one?

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

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-24 : 15:38:22

I am sure that it works. The result set is the results that I got from the query being ran.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 18:07:31

post the DDL of the Table and create the sample data as a DML Statement

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 10:30:38

See if this works

SELECT [ScratchPad5].EmployeeNumber, Sum([ScratchPad5].sumhours),
case when [sumhours]>40 THEN [sumhours]-40 ELSE 0 END AS ot,
case when [sumhours]>40 THEN [sumhours]-coalesce([ot],0) ELSE [sumhours] END AS RegHours
FROM ScratchPad5
GROUP BY [ScratchPad5].EmployeeNumber, sumhours, ot

Madhivanan

Failing to plan is Planning to fail

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 11:04:56

Madhivanan,

Here is the one line of my results that I'm using as my "benchmark" to see if this works or not:

Employeenumber (No column name) ot Regular Hours
8467 51.734000 11.734000 51.734000

and what this should have for Regular Hours is 40 hours. As you can see from the query that you provided, that it shows me the total number of hours, not just the total number of "regular hours." Does that make sense?

Thank you

Doug

dougancil
Posting Yak Master

217 Posts

Posted - 2010-12-03 : 12:37:56

Can anyone else offer any help with this?

Thank you

Doug

dougancil
Posting Yak Master

217 Posts

Posted - 2010-12-10 : 14:23:19

Just as an fyi, here is the correct way that this query had to be written:

SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
Sum([ScratchPad5].sumhours),
SUM( case when [sumhours]>40
THEN [sumhours]-40
ELSE 0
END ) AS TotalOT,
SUM( case when [sumhours]>40
THEN 40
ELSE [sumhours]
END ) AS TotalRegHours
FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours,
ot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources