If Statement in Views 1

[berryk]

Can If statements be used in views?
I want one set of criteia if before a certain time and another set of criteria if after a certain time.

I dont have any problem with the time and date functions, Im just not if it is possible to use If stanements in views and if I do how to implement them.

Everything I have tried so far with the If statements has failed.

 

[dbomrrsm]

have you tried using case instead of if ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

if used more for transaction processing than select in a view - show what you need to do and we can help

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[berryk]

Below is the code. Commented out is what I have tried both as IF statements and CASE statements. It works fine without the conditions, but what I am attempting to do is show one query when the computer clock is before 3:00 PM and a different query when it is after 3:00 PM.

I got the conditional to work in a Stored Procedure, but I am not comfortable enough with Cursors to get the rows back out of a Stored Procedure.

-berryk
****************************************

Create View vw_2ndShift
AS


/*CASE WHEN cast(datepart (hh, GetDate()) as Varchar(2)) < 15

THEN*/

SELECT
dt.StartDateTime,
dt.SystemDowntimeEndDateTime,
dt.DTCategoryID AS CatID,
Round((DATEDIFF(ss, dt.StartDateTime, dt.SystemDowntimeEndDateTime)/60.),2) AS Minutes,
dtdef.Name AS DTDefName,
(CASE WHEN cat.name is null THEN 'Not Acknowledged' ELSE cat.name END) As CatName
FROM

tblSystem

INNER JOIN tblDTDef as dtdef
INNER JOIN tblDowntime as dt
LEFT OUTER JOIN tblDTCategory as cat
ON (dt.DTCategoryID = cat.[ID])
ON (DTDef.[ID] = dt.DTDefID)
ON (tblSystem.[ID] = DTDef.SystemID)


WHERE

(dt.DTDefID = dtdef.ID) AND
(dt.SystemDowntime = 1) AND
(dtdef.SystemID = 2) AND
(dt.StartDateTime BETWEEN cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()-1) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 3:00:00 PM'AND
cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()-1) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 10:59:59 PM')

/*ELSE

WHEN cast(datepart (hh, GetDate()) as Varchar(2)) >= 15

THEN

SELECT

dt.StartDateTime,
dt.SystemDowntimeEndDateTime,
dt.DTCategoryID AS CatID,
Round((DATEDIFF(ss, dt.StartDateTime, dt.SystemDowntimeEndDateTime)/60.),2) AS Minutes,
dtdef.Name AS DTDefName,
(CASE WHEN cat.name is null THEN 'Not Acknowledged' ELSE cat.name END) As CatName
FROM

tblSystem

INNER JOIN tblDTDef as dtdef
INNER JOIN tblDowntime as dt
LEFT OUTER JOIN tblDTCategory as cat
ON (dt.DTCategoryID = cat.[ID])
ON (DTDef.[ID] = dt.DTDefID)
ON (tblSystem.[ID] = DTDef.SystemID)

WHERE

(dt.DTDefID = dtdef.ID) AND
(dt.SystemDowntime = 1) AND
(dtdef.SystemID = 2) AND
(dt.StartDateTime BETWEEN cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 3:00:00 PM'AND
cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 10:59:59 PM')

END*/

GO

 

[JamesLean]

So your query in both cases is virtually identical. The only difference I can see is the date range you look at.

If the current time is before 3pm you return records between 3pm and 11pm yesterday. Other wise you return records between 3pm and 11pm today.

SELECT...
WHERE dt.startdatetime BETWEEN
	CONVERT(varchar, GETDATE() - CASE WHEN DATEPART(hh, GETDATE()) < 15 THEN 1 ELSE 0 END, 112) + ' 15:00'
	AND CONVERT(varchar, GETDATE() - CASE WHEN DATEPART(hh, GETDATE()) < 15 THEN 1 ELSE 0 END, 112) + ' 23:00'
...

--James

 

[dbomrrsm]

here is an example - basically your two selects go in the brackets where my select count (*) are:

declare @time datetime
set @time = '2005-02-07 15:08:09.763'
select 
CASE WHEN cast(datepart (hh, @time) as Varchar(2)) >= 15 THEN
(select count (*) from dbo.emp)
WHEN cast(datepart (hh, @time) as Varchar(2)) < 15 THEN
(select count (*) from dbo.emp where fname = 'John')
END

there are two records in dbo.emp and one where fname = john.

If I run query as is I get 2 rows i.e first select if I change the @time to '2005-02-07 14:08:09.763' I get one row i.e the second select.

Good luck

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

DBomrrsm, that syntax is invalid. Actually, it will work if you're just returning a single value as per your example (COUNT(*)) but as soon as this becomes a larger resultset then it will fail.

The only way to execute two different queries based on a condition is to use IF...ELSE but you cannot use this in a view.

However, because the two queries here are identical except for the WHERE criteria you can use the CASE expression in the WHERE clause of a single query as per my example.

--James

 

[berryk]

dbomrrsm:

I tried the logic below and got the following error messages in Query Analyzer:

Server: Msg 170, Level 15, State 1, Procedure vw_Today_OR_Yesterday_2ndShift, Line 7
Line 7: Incorrect syntax near ','.
Server: Msg 156, Level 15, State 1, Procedure vw_Today_OR_Yesterday_2ndShift, Line 77
Incorrect syntax near the keyword 'END'.

***********************************************
Create View vw_Today_OR_Yesterday_2ndShift
AS


SELECT
CASE WHEN cast(datepart (hh, GetDate()) as Varchar(2)) >= 15 THEN
dt.StartDateTime,
dt.SystemDowntimeEndDateTime,
dt.DTCategoryID AS CatID,
Round((DATEDIFF(ss, dt.StartDateTime, dt.SystemDowntimeEndDateTime)/60.),2) AS Minutes,
dtdef.Name AS DTDefName,
(CASE WHEN cat.name is null THEN 'Not Acknowledged' ELSE cat.name END) As CatName
FROM

tblSystem

INNER JOIN tblDTDef as dtdef
INNER JOIN tblDowntime as dt
LEFT OUTER JOIN tblDTCategory as cat
ON (dt.DTCategoryID = cat.[ID])
ON (DTDef.[ID] = dt.DTDefID)
ON (tblSystem.[ID] = DTDef.SystemID)


WHERE

(dt.DTDefID = dtdef.ID) AND
(dt.SystemDowntime = 1) AND
(dtdef.SystemID = 2) AND
(dt.StartDateTime BETWEEN cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()-1) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 3:00:00 PM'AND
cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()-1) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 10:59:59 PM')



WHEN cast(datepart (hh, GetDate()) as Varchar(2)) < 15 THEN


SELECT

dt.StartDateTime,
dt.SystemDowntimeEndDateTime,
dt.DTCategoryID AS CatID,
Round((DATEDIFF(ss, dt.StartDateTime, dt.SystemDowntimeEndDateTime)/60.),2) AS Minutes,
dtdef.Name AS DTDefName,
(CASE WHEN cat.name is null THEN 'Not Acknowledged' ELSE cat.name END) As CatName
FROM

tblSystem

INNER JOIN tblDTDef as dtdef
INNER JOIN tblDowntime as dt
LEFT OUTER JOIN tblDTCategory as cat
ON (dt.DTCategoryID = cat.[ID])
ON (DTDef.[ID] = dt.DTDefID)
ON (tblSystem.[ID] = DTDef.SystemID)

WHERE

(dt.DTDefID = dtdef.ID) AND
(dt.SystemDowntime = 1) AND
(dtdef.SystemID = 2) AND
(dt.StartDateTime BETWEEN cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 3:00:00 PM'AND
cast(datepart (mm, GetDate()) as Varchar(2))
+ '/' + cast(datepart (dd, GetDate()) as Varchar(2))
+ '/' + cast(datepart (yyyy, GetDate()) as Varchar(4))
+ ' 10:59:59 PM')

END

GO
**********************************************

 

[berryk]

JamesLean:

On yours, I am confused. Where do I put my criteria? Is the CASE.. THEN 1 ELSE 0 where I put it in?

berryk

 

[JamesLean]

You just have a single SELECT statement:

SELECT
    dt.StartDateTime,
    dt.SystemDowntimeEndDateTime,
    dt.DTCategoryID AS CatID,
    Round((DATEDIFF(ss, dt.StartDateTime, dt.SystemDowntimeEndDateTime)/60.),2) AS Minutes,
    dtdef.Name AS DTDefName,
    (CASE WHEN cat.name is null THEN 'Not Acknowledged' ELSE cat.name END) As CatName
FROM

    tblSystem

        INNER JOIN tblDTDef as dtdef
            INNER JOIN tblDowntime as dt
                LEFT OUTER JOIN tblDTCategory as cat
                ON (dt.DTCategoryID = cat.[ID])
            ON (DTDef.[ID] = dt.DTDefID)
        ON (tblSystem.[ID] = DTDef.SystemID)

 
WHERE

    (dt.DTDefID = dtdef.ID) AND
    (dt.SystemDowntime = 1) AND
    (dtdef.SystemID = 2) AND
    [COLOR=red]dt.startdatetime BETWEEN
    CONVERT(varchar, GETDATE() - CASE WHEN DATEPART(hh, GETDATE()) < 15 THEN 1 ELSE 0 END, 112) + ' 15:00'
    AND CONVERT(varchar, GETDATE() - CASE WHEN DATEPART(hh, GETDATE()) < 15 THEN 1 ELSE 0 END, 112) + ' 23:00'[/color]

--James

 

[berryk]

Jameslean,

Thanks that worked as far as I know. I will have to see after 3:00pm if I have any records for today.

It works great for looking at yesterday's records, however, I am unsure of the logic for today's records after 3:00PM

Thanks for the help!

--Ken

 

[JamesLean]

Basically, if the current hour is earlier than 3pm (DATEPART(hh, GETDATE()) < 15) then you take today - 1 day and look between 3pm and 11pm.

If it's currently after 3pm then you take today - 0 days, ie still today, between 3pm and 11pm.

Does that make sense?

--James

 

[berryk]

jamesLean,

Thanks. I will let you know how it works out.
So far it looks good!

--Ken