March 21, 2012 at 1:22 pm
So, it looks like you helped me out once again but I am unable to create this as a view. I get an error "Incorrect Syntax near the keyword "DECLARE'. It looks like I cannot declare variables in view definitions. So if I take out create view statement, it seems like it works beautifully. So now I'm not sure what to do...stored procedure? Wouldn't know where to begin really. But I so appreciate your help. At least I am much closer to achieving my goal than I was that's for sure.
March 21, 2012 at 2:16 pm
jdamian (3/21/2012)
So, it looks like you helped me out once again but I am unable to create this as a view. I get an error "Incorrect Syntax near the keyword "DECLARE'. It looks like I cannot declare variables in view definitions. So if I take out create view statement, it seems like it works beautifully. So now I'm not sure what to do...stored procedure? Wouldn't know where to begin really. But I so appreciate your help. At least I am much closer to achieving my goal than I was that's for sure.
Sorry, I didn't test it 🙂
I would use a proc instead:
CREATE PROCEDURE pGetDataByShift
AS ...
It will return a result set just like a view would. Run like:
EXEC pGetDataByShift
_________________________________
seth delconte
http://sqlkeys.com
March 21, 2012 at 4:37 pm
First, if you look at the code below again, you will find a logic error:
IF @now BETWEEN '06:40:00' AND '14:39:00'
SELECT ProductionDate, Shift, LineNumber, Cart, CavityPosition, ProgramNumber, PartNumber,Cavity
FROM tblTransactiondetail WHERE Shift = 1 and PartNumber <>''
ELSE IF @now BETWEEN '14:40:00' AND '22:39:00'
SELECT ProductionDate, Shift, LineNumber, Cart, CavityPosition, ProgramNumber, PartNumber,Cavity
FROM tblTransactionDetail WHERE Shift = 2 and PartNumber <>''
ELSE IF @now BETWEEN '22:40:00' AND '24:59:59' OR @now BETWEEN '00:00:00' AND '06:39:00'
SELECT ProductionDate, Shift, LineNumber, Cart, CavityPosition, ProgramNumber, PartNumber,Cavity
FROM tblTransactionDetail WHERE Shift = 3 and PartNumber <>''
what if the time is 14:39:30, 22:39:30, or 06:39:30? You have three one minute gaps where your code won't work.
March 21, 2012 at 5:07 pm
How about giving this a try:
SELECT
t.ProductionDate,
t.Shift,
t.LineNumber,
t.Cart,
t.CavityPosition,
t.ProgramNumber,
t.PartNumber,
t.Cavity
FROM
dbo.tblTransactionDetail t
INNER JOIN dbo.tblLPCurrentOnline c
ON t.Cart = c.Fixture
AND t.ProgramNumber = c.Program
WHERE
t.Shift = case when convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 06:40:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-01 14:40:00'
then 1
when convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 14:40:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-01 22:40:00'
then 2
when (convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 22:40:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-02 00:00:00') or
(convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 00:00:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-01 06:40:00')
then 3
end
and t.PartNumber <>'';
March 21, 2012 at 5:35 pm
Lynn Pettis (3/21/2012)
How about giving this a try:
SELECT
t.ProductionDate,
t.Shift,
t.LineNumber,
t.Cart,
t.CavityPosition,
t.ProgramNumber,
t.PartNumber,
t.Cavity
FROM
dbo.tblTransactionDetail t
INNER JOIN dbo.tblLPCurrentOnline c
ON t.Cart = c.Fixture
AND t.ProgramNumber = c.Program
WHERE
t.Shift = case when convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 06:40:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-01 14:40:00'
then 1
when convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 14:40:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-01 22:40:00'
then 2
when (convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 22:40:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-02 00:00:00') or
(convert(datetime, convert(varchar(8), getdate(), 108)) >= '1900-01-01 00:00:00' and
convert(datetime, convert(varchar(8), getdate(), 108)) < '1900-01-01 06:40:00')
then 3
end
and t.PartNumber <>'';
Hi, Lynn.
I was under the impression that using a CASE statement in the WHERE clause section would be bad for the optimizer to use the correct index optimally. Or is it just urban legend?
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 21, 2012 at 5:39 pm
I'd say it depends on how the CASE function is being used. In this case it is being used on the right side of the equality condition and will return a single value to be evaluated against the Shift column. To me this looks perfectly SARGable.
Anyone else have any thoughts on this subject?
March 22, 2012 at 9:09 am
Lynn Pettis (3/21/2012)
I'd say it depends on how the CASE function is being used. In this case it is being used on the right side of the equality condition and will return a single value to be evaluated against the Shift column. To me this looks perfectly SARGable.Anyone else have any thoughts on this subject?
Interesting. I was advised before (by someone from my former company) not to use CASE in WHERE clauses even if it was on the right side of the expression. I haven't tested it thoroughly to confirm the issue. That's why I asked. 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply