<div>
<pre><code class="code-10 nolinks" id="code-20-42792721-1">SELECT ..., Duration, Duration2, ...
FROM dbo.table_in_view
CROSS APPLY (
 SELECT 
 CASE WHEN Shift_From_Hour IS NULL OR Shift_To_Hour IS NULL THEN 0 
 WHEN Shift_From_Hour &lt; 0 OR Shift_From_Hour &gt; 24 OR 
 Shift_From_Minute &lt; 0 OR Shift_From_Minute &gt; 59 THEN 0 
 WHEN Shift_To_Hour &lt; 0 OR Shift_To_Hour &gt; 24 OR 
 Shift_To_Minute &lt; 0 OR Shift_To_Minute &gt; 59 THEN 0 
 WHEN Shift_From_Hour &gt; Shift_To_Hour 
 THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) 
 ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) 
 END AS Duration
) AS alias1
CROSS APPLY (
 SELECT CASE WHEN Duration = 0 THEN 1440 ELSE Duration END / 60 AS Duration2
) AS alias2
</code></pre>
</div>

bfuchs

<div>
Hi Scott, 
 
I'm not looking to select that value, instead I need this added to the following view as another column (named Duration2). 

<pre><code class="code-10 nolinks" id="code-20-42792730-1">SELECT dbo.Skilled_Nursing_Visit_Note.ID, dbo.SNV_Printed_History.VendorsID, dbo.SNV_Printed_History.SNV_ID, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name, 
 dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, dbo.Skilled_Nursing_Visit_Note.Visit_Date, dbo.Skilled_Nursing_Visit_Note.Shift_From_Hour, 
 dbo.Skilled_Nursing_Visit_Note.Shift_To_Hour, dbo.Skilled_Nursing_Visit_Note.Date_Signed, dbo.Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, 
 dbo.Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, dbo.SNV_Printed_History.ReviewedBy, dbo.SNV_Printed_History.ReviewedDate, dbo.SNV_Printed_History.PrintedDate, 
 dbo.SNV_Printed_History.PrintedBy, dbo.Skilled_Nursing_Visit_Note.Client_First_Name, dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, 
 dbo.Skilled_Nursing_Visit_Note.Date_Of_Birth, dbo.Skilled_Nursing_Visit_Note.Shift_From_Minute, dbo.Skilled_Nursing_Visit_Note.Shift_To_Minute, 
 dbo.Skilled_Nursing_Visit_Note.Treatments_Administered, dbo.SNV_Printed_History.NoPrint, dbo.Skilled_Nursing_Visit_Note.Status, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name_Init, 
 dbo.Skilled_Nursing_Visit_Note.Visit_Date_Init, dbo.Skilled_Nursing_Visit_Note.Client_First_Name_Init, dbo.Skilled_Nursing_Visit_Note.Shift_From_Init, 
 dbo.Skilled_Nursing_Visit_Note.Shift_To_Init, dbo.Skilled_Nursing_Visit_Note.SNVNUM, Q.CountOfNotes, Q.CountOfPN, CASE WHEN Shift_From_Hour IS NULL OR
 Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour &lt; 0 OR
 Shift_From_Hour &gt; 24 OR
 Shift_From_Minute &lt; 0 OR
 Shift_From_Minute &gt; 59 THEN 0 WHEN Shift_To_Hour &lt; 0 OR
 Shift_To_Hour &gt; 24 OR
 Shift_To_Minute &lt; 0 OR
 Shift_To_Minute &gt; 59 THEN 0 WHEN Shift_From_Hour &gt; Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) 
 ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) END AS Duration, CASE WHEN Shift_From_Hour IS NULL OR
 Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour &lt; 0 OR
 Shift_From_Hour &gt; 24 OR
 Shift_From_Minute &lt; 0 OR
 Shift_From_Minute &gt; 59 THEN 0 WHEN Shift_To_Hour &lt; 0 OR
 Shift_To_Hour &gt; 24 OR
 Shift_To_Minute &lt; 0 OR
 Shift_To_Minute &gt; 59 THEN 0 WHEN Shift_From_Hour &gt; Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) 
 ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) END / 120 - Q.CountOfNotes AS MissingNotes, dbo.SNV_Printed_History.ts
FROM dbo.SNV_Printed_History INNER JOIN
 dbo.Skilled_Nursing_Visit_Note ON dbo.SNV_Printed_History.SNV_ID = dbo.Skilled_Nursing_Visit_Note.SNV_ID LEFT OUTER JOIN
 dbo.vw_SNVNotesQry AS Q ON Q.SNVID = dbo.Skilled_Nursing_Visit_Note.SNV_ID
</code></pre>
 
What is the simplest (the most clear, and easy to read/maintain) way to accomplish it? 
 
Thanks, 
Ben
</div>

<div>
I'd still use CROSS APPLYs to create Duration and Duration2. &nbsp;You can use them however you want. 
 
Wouldn't &quot;adding them [the new columns] to the view&quot; mean SELECTing them?!
</div>

<div>
Hi, 
Excuse me for asking such silly questions...however my experience level in t-sql is not that advanced...using SSMS query designer to accomplish my views. 
when tried to copy/paste your code in the column grid, it gives me the attached error. 
would it be possible to either add it to the above SQL or guide me what exactly to copy/paste and where? 
Thanks, 
Ben 
<a href="https://filedb.experts-exchange.com/incoming/2019/02_w06/1411629/Untitled.png" target="_blank" class="file-inline" title="1 (152 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Untitled.png</a>
</div>

Untitled.png

<div>
Perhaps you meant I should not add them to existing view, instead create a new view on top of that and add this code there? 
Thanks, 
Ben
</div>

<div>
Here is a somewhat brute force approach but should do what you requested. 
 

<pre><code class="code-10 nolinks" id="code-20-42794220-1">SELECT dbo.Skilled_Nursing_Visit_Note.ID,
 dbo.SNV_Printed_History.VendorsID,
 dbo.SNV_Printed_History.SNV_ID,
 dbo.Skilled_Nursing_Visit_Note.Client_Last_Name,
 dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name,
 dbo.Skilled_Nursing_Visit_Note.Visit_Date,
 dbo.Skilled_Nursing_Visit_Note.Shift_From_Hour,
 dbo.Skilled_Nursing_Visit_Note.Shift_To_Hour,
 dbo.Skilled_Nursing_Visit_Note.Date_Signed,
 dbo.Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV,
 dbo.Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV,
 dbo.SNV_Printed_History.ReviewedBy,
 dbo.SNV_Printed_History.ReviewedDate,
 dbo.SNV_Printed_History.PrintedDate,
 dbo.SNV_Printed_History.PrintedBy,
 dbo.Skilled_Nursing_Visit_Note.Client_First_Name,
 dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name,
 dbo.Skilled_Nursing_Visit_Note.Date_Of_Birth,
 dbo.Skilled_Nursing_Visit_Note.Shift_From_Minute,
 dbo.Skilled_Nursing_Visit_Note.Shift_To_Minute,
 dbo.Skilled_Nursing_Visit_Note.Treatments_Administered,
 dbo.SNV_Printed_History.NoPrint,
 dbo.Skilled_Nursing_Visit_Note.Status,
 dbo.Skilled_Nursing_Visit_Note.Client_Last_Name_Init,
 dbo.Skilled_Nursing_Visit_Note.Visit_Date_Init,
 dbo.Skilled_Nursing_Visit_Note.Client_First_Name_Init,
 dbo.Skilled_Nursing_Visit_Note.Shift_From_Init,
 dbo.Skilled_Nursing_Visit_Note.Shift_To_Init,
 dbo.Skilled_Nursing_Visit_Note.SNVNUM,
 Q.CountOfNotes,
 Q.CountOfPN,
 CASE
 WHEN Shift_From_Hour IS NULL
 OR Shift_To_Hour IS NULL THEN 0
 WHEN Shift_From_Hour &lt; 0
 OR Shift_From_Hour &gt; 24
 OR Shift_From_Minute &lt; 0
 OR Shift_From_Minute &gt; 59 THEN 0
 WHEN Shift_To_Hour &lt; 0
 OR Shift_To_Hour &gt; 24
 OR Shift_To_Minute &lt; 0
 OR Shift_To_Minute &gt; 59 THEN 0
 WHEN Shift_From_Hour &gt; Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)
 ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)
 END AS Duration,
 CASE
 WHEN Shift_From_Hour IS NULL
 OR Shift_To_Hour IS NULL
 OR Shift_From_Hour &lt; 0
 OR Shift_From_Hour &gt; 24
 OR Shift_From_Minute &lt; 0
 OR Shift_From_Minute &gt; 59
 OR Shift_To_Hour &lt; 0
 OR Shift_To_Hour &gt; 24
 OR Shift_To_Minute &lt; 0
 OR Shift_To_Minute &gt; 59 THEN 1440
 WHEN Shift_From_Hour &gt; Shift_To_Hour THEN (((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)) / 60
 ELSE ((Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)) / 60
 END AS Duration2,
 CASE
 WHEN Shift_From_Hour IS NULL
 OR Shift_To_Hour IS NULL THEN 0
 WHEN Shift_From_Hour &lt; 0
 OR Shift_From_Hour &gt; 24
 OR Shift_From_Minute &lt; 0
 OR Shift_From_Minute &gt; 59 THEN 0
 WHEN Shift_To_Hour &lt; 0
 OR Shift_To_Hour &gt; 24
 OR Shift_To_Minute &lt; 0
 OR Shift_To_Minute &gt; 59 THEN 0
 WHEN Shift_From_Hour &gt; Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)
 ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute)
 END / 120 - Q.CountOfNotes AS MissingNotes,
 dbo.SNV_Printed_History.ts
FROM dbo.SNV_Printed_History
INNER JOIN dbo.Skilled_Nursing_Visit_Note ON dbo.SNV_Printed_History.SNV_ID = dbo.Skilled_Nursing_Visit_Note.SNV_ID
LEFT OUTER JOIN dbo.vw_SNVNotesQry AS Q ON Q.SNVID = dbo.Skilled_Nursing_Visit_Note.SNV_ID
</code></pre>
<hr />
»bp
</div>

<div>
Hi Bill, 
It working except for case when difference = 0 (Shift_From_Hour + Shift_From_Min are same as Shift_To_Hour + Shift_To_Min), then it shows 0, while I need it to show 24. 
Thanks, 
Ben
</div>

<div>
<div class="content wysiwyg-content">
Hi Experts, 
 
I have a column in my view named Duration defined as following. 

<pre><code class="code-1 nolinks" id="code-10-29135014-1">CASE WHEN Shift_From_Hour IS NULL OR Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour &lt; 0 OR Shift_From_Hour &gt; 24 OR Shift_From_Minute &lt; 0 OR Shift_From_Minute &gt; 59 THEN 0 WHEN Shift_To_Hour &lt; 0 OR Shift_To_Hour &gt; 24 OR Shift_To_Minute &lt; 0 OR Shift_To_Minute &gt; 59 THEN 0 WHEN Shift_From_Hour &gt; Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) END
</code></pre>
 
Now would like to add a new field to my view named Duration2 having the following logic. 

<pre><code class="code-1 nolinks" id="code-10-29135014-2">Duration2: CInt(IIf([duration]=0,1440,[duration])/60)
</code></pre>
 (Code taken from Access query). 
Meaning if the duration column is 0 it should return 1440/60, otherwise return the results of Duration column divided by 60. 
 
Thanks
</div>
</div>

Hi Experts,

I have a column in my view named Duration defined as following.
(CODE)

Now would like to add a new field to my view named Duration2 having the following logic.
(CODE) (Code taken from Access query).
Meaning if the duration column is 0 it should return 1440/60, otherwise return the results of Duration column divided by 60.

Thanks

Adding an if condition to an existing calculated field.

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.