SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
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 Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

115 Posts

Posted - 10/01/2012 :  09:08:46  Show Profile  Reply with Quote
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

115 Posts

Posted - 10/01/2012 :  09:10:08  Show Profile  Reply with Quote
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.
Go to Top of Page

hbadministrator
Posting Yak Master

115 Posts

Posted - 10/01/2012 :  09:16:17  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/01/2012 :  10:45:51  Show Profile  Reply with Quote
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/

Go to Top of Page

hbadministrator
Posting Yak Master

115 Posts

Posted - 10/01/2012 :  14:38:52  Show Profile  Reply with Quote
worked perfect
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/01/2012 :  15:00:59  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000