We help IT Professionals succeed at work.

Adding an if condition to an existing calculated field.

82 Views
Last Modified: 2019-02-10
Hi Experts,

I have a column in my view named Duration defined as following.
CASE WHEN Shift_From_Hour IS NULL OR Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour < 0 OR Shift_From_Hour > 24 OR Shift_From_Minute < 0 OR Shift_From_Minute > 59 THEN 0 WHEN Shift_To_Hour < 0 OR Shift_To_Hour > 24 OR Shift_To_Minute < 0 OR Shift_To_Minute > 59 THEN 0 WHEN Shift_From_Hour > 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

Open in new window


Now would like to add a new field to my view named Duration2 having the following logic.
Duration2: CInt(IIf([duration]=0,1440,[duration])/60)

Open in new window

(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
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 < 0 OR Shift_From_Hour > 24 OR 
              Shift_From_Minute < 0 OR Shift_From_Minute > 59 THEN 0 
         WHEN Shift_To_Hour < 0 OR Shift_To_Hour > 24 OR 
              Shift_To_Minute < 0 OR Shift_To_Minute > 59 THEN 0 
         WHEN Shift_From_Hour > 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

Open in new window

CERTIFIED EXPERT

Author

Commented:
Hi Scott,

I'm not looking to select that value, instead I need this added to the following view as another column (named Duration2).
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 < 0 OR
                      Shift_From_Hour > 24 OR
                      Shift_From_Minute < 0 OR
                      Shift_From_Minute > 59 THEN 0 WHEN Shift_To_Hour < 0 OR
                      Shift_To_Hour > 24 OR
                      Shift_To_Minute < 0 OR
                      Shift_To_Minute > 59 THEN 0 WHEN Shift_From_Hour > 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 < 0 OR
                      Shift_From_Hour > 24 OR
                      Shift_From_Minute < 0 OR
                      Shift_From_Minute > 59 THEN 0 WHEN Shift_To_Hour < 0 OR
                      Shift_To_Hour > 24 OR
                      Shift_To_Minute < 0 OR
                      Shift_To_Minute > 59 THEN 0 WHEN Shift_From_Hour > 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

Open in new window


What is the simplest (the most clear, and easy to read/maintain) way to accomplish it?

Thanks,
Ben
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I'd still use CROSS APPLYs to create Duration and Duration2.  You can use them however you want.

Wouldn't "adding them [the new columns] to the view" mean SELECTing them?!
CERTIFIED EXPERT

Author

Commented:
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
Untitled.png
CERTIFIED EXPERT

Author

Commented:
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
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Here is a somewhat brute force approach but should do what you requested.

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 < 0
                OR Shift_From_Hour > 24
                OR Shift_From_Minute < 0
                OR Shift_From_Minute > 59 THEN 0
           WHEN Shift_To_Hour < 0
                OR Shift_To_Hour > 24
                OR Shift_To_Minute < 0
                OR Shift_To_Minute > 59 THEN 0
           WHEN Shift_From_Hour > 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 < 0
                OR Shift_From_Hour > 24
                OR Shift_From_Minute < 0
                OR Shift_From_Minute > 59
                OR Shift_To_Hour < 0
                OR Shift_To_Hour > 24
                OR Shift_To_Minute < 0
                OR Shift_To_Minute > 59 THEN 1440
           WHEN Shift_From_Hour > 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 < 0
                OR Shift_From_Hour > 24
                OR Shift_From_Minute < 0
                OR Shift_From_Minute > 59 THEN 0
           WHEN Shift_To_Hour < 0
                OR Shift_To_Hour > 24
                OR Shift_To_Minute < 0
                OR Shift_To_Minute > 59 THEN 0
           WHEN Shift_From_Hour > 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

Open in new window


ยปbp
CERTIFIED EXPERT

Author

Commented:
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
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thank you!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.