Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If Statement in Views 1

Status
Not open for further replies.
berryk

berryk

MIS
Joined
Feb 14, 2002
Messages
10
Location
US
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.
 
Sort by date Sort by votes
have you tried using case instead of if ?

[bandito] [blue]DBomrrsm[/blue] [bandito]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]
 
Upvote 0 Downvote
if used more for transaction processing than select in a view - show what you need to do and we can help

[bandito] [blue]DBomrrsm[/blue] [bandito]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

Code: 
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
 
Upvote 0 Downvote
here is an example - basically your two selects go in the brackets where my select count (*) are:
Code: 
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

[bandito] [blue]DBomrrsm[/blue] [bandito]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
**********************************************
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
You just have a single SELECT statement:

Code: 
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
jamesLean,

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

--Ken
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question Question
Create Function Within IF statement.
Replies
4
Views
2K
mikrom
mikrom
SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
grnzbra
  • Locked
  • Question Question
Is &quot;IF EXISTS()...&quot; better than &quot;IF (SELECT COUNT...) &gt; 0&quot;
Replies
4
Views
3K
gmmastros
gmmastros
leifoet
  • Locked
  • Question Question
Error in Select query
Replies
14
Views
1K
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
887
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top