Conditional Statement help for Newbie

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)

Conditional Statement help for Newbie

Author

Topic

Shaft
Starting Member

5 Posts

Posted - 2006-12-08 : 09:24:23

Hi All,

I'm new to writing stored procedures, I'm used to using MS Access but I'm trying to migrate to SQL Server. I'm trying to use the Select statement below but can't get the syntax right. Basically what I'm trying to do is add up an engineers audit score from a questionaire database (which I can do), but the score can be reset, so if the employeeID exists in the reset table I want it to add the score up from that date. the if statment i've written would work for this if I could get the syntax right, but somebody with some more experience of stored procedure might be able to tell me a completely different way of doing this. Thank you in advance for your help.

SELECT Employee.EmployeeID, SUM(QuestionScore.Score) AS Total, Instance.AuditDate
FROM Instance INNER JOIN
Response ON Instance.InstanceID = Response.InstanceID INNER JOIN
Employee ON Instance.EngineerID = Employee.EmployeeID INNER JOIN
QuestionScore ON Instance.QuestionaireID = QuestionScore.QuestionaireID INNER JOIN
Reset ON Employee.EmployeeID = Reset.EmployeeID AND Instance.AuditDate >= if('reset.employeeid=null', '01/01/1900', Reset.ResetDate)
WHERE (Response.Response = 0)
GROUP BY Employee.EmployeeID, Instance.AuditDate

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 09:32:25

[code]Instance.AuditDate >= (case when reset.employeeid is null then '01/01/1900' else Reset.ResetDate end)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-08 : 09:35:52

quote:
INNER JOIN
Reset ON Employee.EmployeeID = Reset.EmployeeID AND Instance.AuditDate >= if('reset.employeeid=null', '01/01/1900', Reset.ResetDate)

With the inner join you are only selecting records that exist in both tables. what you need to do is to use an OUTER join so that you select ALL records from your Instance table and any matching records from the reset table (I Can't recall if its a left or right outer join you need. I generally end up trying it all three ways, left , right, left! to get it right

) If a record doen't exsist in teh reset table teh row will have nulls in it. If you then use ISNULL([reset].[employeeID],'01 Jan 1900', [Reset].[ResetDate]) instead of your if (Can't use if to test for the null condition it always returns false!) this would give you a SQL String OF


  SELECT Employee.EmployeeID
       , SUM(QuestionScore.Score) AS Total
       , Instance.AuditDate
    FROM Instance INNER JOIN Response ON Instance.InstanceID = Response.InstanceID
                  INNER JOIN Employee ON Instance.EngineerID = Employee.EmployeeID
                  INNER JOIN QuestionScore ON Instance.QuestionaireID = QuestionScore.QuestionaireID
                  {RIGHT|LEFT} OUTER JOIN Reset ON Employee.EmployeeID = Reset.EmployeeID 
                         AND Instance.AuditDate >= ISNULL([reset].[employeeid], '01/01/1900', Reset.ResetDate)
   WHERE (Response.Response = 0)
  GROUP BY Employee.EmployeeID, Instance.AuditDate

Just remember to change the outer join to the right one

--
Regards
Tony The DBA

Shaft
Starting Member

5 Posts

Posted - 2006-12-08 : 09:47:19

Cheers mate just realised that myself it's a Left Outer Join I need. I've got another issue now in that if a score gets reset more than once, I need to take the latest date for that employee. Am I best using a derived table to get the MAX dates from the reset table, or another method.

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-08 : 09:53:31

Re Reding my post has shown a flaw

quote:
(Can't use if to test for the null condition it always returns false!)

is actually not correct You Cannot use the = coditional operator, or the != <> operators on Null Values you must use the [field] IS Null construct eg try

Declare @Mydate DateTime

Set @MyDate = NULL

PRINT 'Using = Conditional'

IF @MyDate = NULL
PRINT 'MyDate Is Null'
ELSE
PRINT 'Mydate Is Not Null'

PRINT 'Using IS NULL Conditional'

IF @MyDate IS NULL
PRINT 'MyDate Is Null'
ELSE
PRINT 'Mydate Is Not Null'

Will Illustrate this.

--
Regards
Tony The DBA

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-08 : 09:58:49

Try

                  LEFT OUTER JOIN Reset ON Employee.EmployeeID = Reset.EmployeeID 
                         AND Instance.AuditDate >= ( SELECT MAX ( ISNULL([reset].[employeeid], '01/01/1900', Reset.ResetDate ) )
                                                       FROM Reset WHERE Employee.EmployeeID = Reset.EmployeeID  )

its a little inelegant (and may not actually work, but its a good starting point

--
Regards
Tony The DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 15:29:21

quote:

Originally posted by harsh_athalye

Instance.AuditDate >= (case when reset.employeeid is null then '01/01/1900' else Reset.ResetDate end)

What if AuditDate actually is earlier than 19000101?

Instance.AuditDate >= (case when reset.employeeid is null then Instance.AuditDate else Reset.ResetDate end)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources