Null question - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Null question

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-01 : 09:08:46

I have this an issue with my code I am trying to get it to show me null dates. Example I have employees that are active and inactive during some days and not the other. What we are trying to view is the employees that forgot to place their time into the system. The code I wrote shows me active employees I do not care about inactive and dates of work between time. But I do not see ones that they did not fill any time. Below is a copy of the code.

SELECT [emp-s].[Emp-id], [emp-s].[Emp-status], timcard.[disp-no], timcard.[Job-no], timcard.[Reg-time], timcard.[Over-time], timcard.[Double-time], CONVERT(CHAR(10),timcard.[Date-of-work], 101) AS Date_of_Work

FROM timcard LEFT OUTER JOIN [emp-s] ON timcard.[Emp-id] = [emp-s].[Emp-id]
WHERE ([emp-s].[Emp-status] = 'A') AND (timcard.[Date-of-work] BETWEEN @StartDate AND @EndDate)

Header
Emp-id,Emp-status, disp-no, Job-no, Reg-time, Over-time, Double-time, Date_of_work

7145 A 457870AC 2.50 0.00 0.00 09/16/2012
7145 A 450615AC 8.00 0.00 0.00 09/16/2012
7145 A 455159KC 6.00 0.00 0.00 09/16/2012
7145 A 445018AC 1.00 0.00 0.00 09/16/2012
7145 A 600045 1.00 0.00 0.00 09/16/2012
7145 A 462451AC 14.00 0.00 0.00 09/16/2012
7145 A 462962AC 4.00 0.00 0.00 09/16/2012
7146 A 445612EC 4.00 0.00 0.00 09/16/2012
7146 A 445612EC 12.00 0.00 0.00 09/16/2012
7146 A 445612EC 24.00 0.00 0.00 09/16/2012
7149 A 2.50 0.00 0.00 09/16/2012
7149 A 426127KC 37.50 0.00 0.00 09/16/2012
7150 A 462414AC 14.00 0.00 0.00 09/16/2012
7150 A 457870AC 26.00 0.00 0.00 09/16/2012
7151 A 1.00 0.00 0.00 09/16/2012
7151 A 600027 4.00 0.00 0.00 09/16/2012
7151 A 600027 3.50 0.00 0.00 09/16/2012
7151 A 600027 0.50 0.00 0.00 09/16/2012
7151 A 600027 1.00 0.00 0.00 09/16/2012
7151 A 600023 1.00 0.00 0.00 09/16/2012
7151 A 600023 1.00 0.00 0.00 09/16/2012
7151 A 600023 0.50 0.00 0.00 09/16/2012
7151 A 600023 2.00 0.00 0.00 09/16/2012
7151 A 600023 2.00 0.00 0.00 09/16/2012
7151 A 600023 2.00 0.00 0.00 09/16/2012
7151 A 600023 0.50 0.00 0.00 09/16/2012
7151 A 600023 1.00 0.00 0.00 09/16/2012
7151 A 458247 2.00 0.00 0.00 09/16/2012
7151 A 458247 2.00 0.00 0.00 09/16/2012
7151 A 458247 1.00 0.00 0.00 09/16/2012
7151 A 458247 2.00 0.00 0.00 09/16/2012
7151 A 458247 3.00 0.00 0.00 09/16/2012
7151 A 458247 8.00 0.00 0.00 09/16/2012
7151 A 458247 1.00 0.00 0.00 09/16/2012
7151 A 458247 1.00 0.00 0.00 09/16/2012
7155 A 0.00 1.00 0.00 09/16/2012
7155 A -1.00 0.00 0.00 09/16/2012
7155 A 0.00 3.50 0.00 09/16/2012
7155 A -3.50 0.00 0.00 09/16/2012
7156 A 0.00 5.00 0.00 09/16/2012
7156 A 455036EC 5.00 0.00 0.00 09/16/2012
7156 A 455036EC 22.00 0.00 0.00 09/16/2012
7156 A 455036EC 3.00 0.00 0.00 09/16/2012
7156 A 455036EC 6.00 0.00 0.00 09/16/2012
7156 A 455036EC 0.00 1.50 0.00 09/16/2012
7156 A 455036EC 4.00 0.00 0.00 09/16/2012
7162 A 1.00 0.00 0.00 09/16/2012
7162 A 461471 5.00 0.00 0.00 09/16/2012
7162 A 461471 12.00 0.00 0.00 09/16/2012
7162 A 461471 4.00 0.00 0.00 09/16/2012
7162 A 461471 3.00 0.00 0.00 09/16/2012
7162 A 455726 1.00 0.00 0.00 09/16/2012
7162 A 455726 1.00 0.00 0.00 09/16/2012
7162 A 455726 1.00 0.00 0.00 09/16/2012
7162 A 455726 4.50 0.00 0.00 09/16/2012
7162 A 455726 2.00 0.00 0.00 09/16/2012
7162 A 457365 1.00 0.00 0.00 09/16/2012
7162 A 457365 1.50 0.00 0.00 09/16/2012
7162 A 457365 1.00 0.00 0.00 09/16/2012
7162 A 457365 1.00 0.00 0.00 09/16/2012
7162 A 457365 1.00 0.00 0.00 09/16/2012
7164 A 1.00 0.00 0.00 09/16/2012
7164 A 458171 0.00 0.50 0.00 09/16/2012
7164 A 458171 1.50 0.00 0.00 09/16/2012
7164 A 458171 0.00 7.50 0.00 09/16/2012
7164 A 458171 24.50 0.00 0.00 09/16/2012
7164 A 458171 0.00 1.00 0.00 09/16/2012
7164 A 458171 3.00 0.00 0.00 09/16/2012
7164 A 460567 0.50 0.00 0.00 09/16/2012
7164 A 460567 0.50 0.00 0.00 09/16/2012
7164 A 460567 8.50 0.00 0.00 09/16/2012
7164 A 460567 0.50 0.00 0.00 09/16/2012
7165 A 2.50 0.00 0.00 09/16/2012
7165 A 426127KC 37.50 0.00 0.00 09/16/2012
7166 A 460634 0.00 0.50 0.00 09/16/2012
7166 A 460634 0.00 2.00 0.00 09/16/2012
7166 A 460634 0.00 2.00 0.00 09/16/2012
7166 A 460634 0.00 4.00 0.00 09/16/2012
7166 A 460634 0.00 0.50 0.00 09/16/2012
7166 A 461469 0.50 0.00 0.00 09/16/2012
7166 A 461469 1.50 0.00 0.00 09/16/2012
7166 A 461469 1.50 0.00 0.00 09/16/2012
7166 A 461469 34.50 0.00 0.00 09/16/2012
7166 A 461469 2.00 0.00 0.00 09/16/2012
7167 A 454309KC 24.00 0.00 0.00 09/16/2012
7167 A 455036EC 0.00 1.50 0.00 09/16/2012
7167 A 455036EC 7.00 0.00 0.00 09/16/2012
7167 A 455036EC 9.00 0.00 0.00 09/16/2012
7168 A 1.00 0.00 0.00 09/16/2012
7168 A 457870AC 13.00 0.00 0.00 09/16/2012

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-01 : 09:10:08

Some of these customers will actually have no date but they are active employees that forgot to put time in so even if date is null I would want to see that employee number and null across the rest of the columns.

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-01 : 09:16:17

Really what I want is all active employees show up on the list no matter what and if their is a date of work during that date range great if not still show me the active employees with null in all the other fields.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:45:51

as i understand from your explanation, i think what you want is this


SELECT [emp-s].[Emp-id], [emp-s].[Emp-status], timcard.[disp-no], timcard.[Job-no], timcard.[Reg-time], timcard.[Over-time], timcard.[Double-time], CONVERT(CHAR(10),timcard.[Date-of-work], 101) AS Date_of_Work
FROM [emp-s]
LEFT OUTER JOIN timcard  ON timcard.[Emp-id] = [emp-s].[Emp-id]
AND (timcard.[Date-of-work] BETWEEN @StartDate AND @EndDate)
WHERE ([emp-s].[Emp-status] = 'A')

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

hbadministrator
Posting Yak Master

120 Posts

Posted - 2012-10-01 : 14:38:52

worked perfect

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 15:00:59

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources