SQL Server Forums - Null question

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Null question

New Topic

Reply to Topic

Printer Friendly

Author

Topic

hbadministrator
Yak Posting Veteran

63 Posts

Posted - 10/01/2012 : 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
Yak Posting Veteran

63 Posts

Posted - 10/01/2012 : 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
Yak Posting Veteran

63 Posts

Posted - 10/01/2012 : 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

India
47173 Posts

Posted - 10/01/2012 : 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
Yak Posting Veteran

63 Posts

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

worked perfect

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts

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

welcome

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

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.07 seconds.