| Author |
 Topic  |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
 Posted - 09/30/2010 : 11:43:44
|
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
|
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 |
 |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/30/2010 : 12:25:26
|
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'.
|
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 09/30/2010 : 12:36:31
|
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
|
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/30/2010 : 13:02:59
|
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
|
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 09/30/2010 : 14:18:35
|
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
|
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 09/30/2010 : 19:28:12
|
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
|
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 10/01/2010 : 10:59:08
|
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
|
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 10/01/2010 : 11:16:18
|
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 |
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 10/01/2010 : 11:37:06
|
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?
|
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 10/01/2010 : 11:49:22
|
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? |
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 10/01/2010 : 12:40:13
|
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
...
|
|
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 10/01/2010 : 13:40:20
|
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'.
|
|
|
| |
Topic |
|
|
|
Using case instead of IIF
