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 2000 Forums
 Transact-SQL (2000)
 Using case instead of IIF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/30/2010 :  11:43:44  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
So I had the following query:


SELECT DISTINCT [Scratchpad1].Employee_Number, IIf([dif]<0,[Break]+[dif],[Break]) AS Paid, [ScratchPad1].name, 
[ScratchPad1].Date, [ScratchPad1].Day, IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30) AS Break,
 Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Break] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
 IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));


which was written in Access by someone else and I've changed it to this:


SELECT DISTINCT [Scratchpad1].EmployeeNumber,  [ScratchPad1].Day,
case when [OnTime1]>=225 then [OnTime1] <=450 when [OnTime1]+15 then [OnTime1]+30) AS Breaks,
 Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
 IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn


But I'm getting this error:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '<'.

Can someone please point out to me what's wrong with the syntax as I've written it?

Thank you

Doug

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 09/30/2010 :  12:06:57  Show Profile  Reply with Quote
Look at this line:
...
case when [OnTime1]>=225 then [OnTime1] <=450 when [OnTime1]+15 then [OnTime1]+30) AS Breaks,
...

you have "... then [OnTime1] <=450 ..."

i think you need "...And [OnTime1]<450 then [OnTime1]+15 ..."


SELECT DISTINCT [Scratchpad1].EmployeeNumber, [ScratchPad1].Day,
case
when [OnTime1]>=225 AND [OnTime1] <=450
then [OnTime1]+15
ELSE [OnTime1]+30
END AS Breaks,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

however i think your query is not right...
you have:
"...
case
when [OnTime1]>=225 AND [OnTime1] <=450
...
IIf([OnTime1]>=225 And [OnTime1]<450,
..."



Edited by - jleitao on 09/30/2010 12:16:38
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/30/2010 :  12:25:26  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Jleitao,

Thank you and sorry about the query. I'd added some things for testing but hadn't removed them before posting. Here is the new query:


SELECT DISTINCT [Scratchpad1].EmployeeNumber,  [ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <=450 or [OnTime1]+15 then [OnTime1]+30 AS Breaks,
 Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2
FROM Scratchpad1


now it's telling me that my error is:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'then'.



Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 09/30/2010 :  12:36:31  Show Profile  Reply with Quote
you welcome.

I think you need:

SELECT DISTINCT [Scratchpad1].EmployeeNumber, [ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <=450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS Breaks,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2
FROM Scratchpad1



And you need a "group by" because you have a agregate function (SUM) in your select

Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/30/2010 :  13:02:59  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Jl,

I do have a group by statement, I just didn't include it in this thread. I'm trying to correct what I can piece by piece.

Here's the last pieces of this query:

[code]
SELECT DISTINCT [Scratchpad1].EmployeeNumber, [ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS Breaks,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30 [Scratchpad1].Login

HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

I know that the syntax for the sum is incorrect and I have yet to find an equivalent for "having," but all else is good. Would the best choice to replace "Having" be grouped?

Thank you

Doug
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/30/2010 :  14:18:35  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Jl,

Sorry ... evidently I left a few things out of my query:

Here is the query as I got it :

SELECT DISTINCT [Scratchpad1].Employee_Number, IIf([dif]<0,[Break]+[dif],[Break]) AS Paid, [ScratchPad1].name,
[ScratchPad1].Date, [ScratchPad1].Day, IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30) AS Break,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Break] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

Here is how I've corrected it thus far:

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 and [Break]+[dif]
[Breaktime] AS Paid
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS [Breaktime],
Sum ([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaktime] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30
End

But here's where the issues are
Here:
[Scratchpad1].EmployeeNumber,
case when [dif]<0 and [Break]+[dif]
[Breaktime] AS Paid

and here:
HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

Any help is appreciated.

Thanks

Doug
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 09/30/2010 :  19:28:12  Show Profile  Reply with Quote
Hi again,

the CASE sintax is:

CASE
WHEN <Condition1> THEN <ACTION if condition1 is true>
WHEN <Condition2> THEN <ACTION if condition2 is true>
WHEN ....
ELSE <Action if all the previous condition are false>
END AS <COLUMN ALIAS>


so you need change your code to:
[Scratchpad1].EmployeeNumber,
CASE WHEN [dif]<0
THEN [Break]+[dif]
ELSE [Breaktime]
END AS Paid

hope this help.

jleitao
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  10:59:08  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
JL,

I have this now

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 then [Break]+[dif]
else [Breaktime]
END AS [Paid],
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 then [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
AS [Breaktime],
Sum ([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaktime] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30
End

and I'm getting the following error:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '<'.

and I'm not seeing what the issue is with the syntax of the query, because I didn't change this from when I was working on it yesterday with you advice. I'm sure it's something simple I'm not seeing. Your help is appreciated.

Thanks

Doug
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 10/01/2010 :  11:16:18  Show Profile  Reply with Quote
change your second case by:

case when [OnTime1]>=225 and [OnTime1] <450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS [Breaktime],

You have a "then" in the midle of the condition.

Edited by - jleitao on 10/01/2010 11:16:42
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  11:37:06  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Jl what about this having statement:


HAVING (((Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));

Is that better as a group by?

Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  11:49:22  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Oh and when I'm running this query I get the following error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Breaktime'.

but I thought this
case when [OnTime1]>=225 then [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
AS [Breaktime],

is creating a column named breaktime. Am I wrong?
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts

Posted - 10/01/2010 :  12:40:13  Show Profile  Reply with Quote
I don't understand what you need in the having statement. having works as the same way as WHERE statement.
Can you explain a little more your question?

About the [Breaktime] column, he doesn't exist in the Scratchpad1 table. You just can use table columns in your SQL command.
You are creating the column but you cant use it cause it doesn't exist yet.


Your query should be:

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 then [Break]+[dif]
else
(
case when [OnTime1]>=225 AND [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
END
)
END AS [Paid],
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 AND [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
END AS [Breaktime],
Sum ([ScratchPad1
...

Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  13:40:20  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
JL,

With that said, should the where go before or after the End Statement in the query?

Like this:

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 then [Breaktime]+[dif]
else
(
case when [OnTime1]>=225 AND [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
END
)
END AS [Paid],
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS [Breaktime],
Sum ([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaktime] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30
Where (((Sum([ScratchPad1].OnTime1))>=225));
End

or after the end? Also when trying to run the query with the where statement, it's telling me this:

Server: Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'Where'.
Server: Msg 170, Level 15, State 1, Line 49
Line 49: Incorrect syntax near 'Sum'.

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.17 seconds. Powered By: Snitz Forums 2000