SQL Conditional if statement

Question

Hello, I am attempting to figure out the correct code for sql if/else statements. Below is an example script that I am working on that will list out the ranking of equipment alarms, up to the 1st three major alarms. I am having issues getting the script to calculate the alarms correct at script execution time. For example someone triggers the script between 7 a.m. to 11 p.m., then the tally should be based on current day, anything after that time, would be based on another day.

Select * FROM( if executiontime between '7:00AM' and '11:59PM' SELECT EquipmentID - 100 as equipmentno ,AlarmID ,Count (AlarmID) as Total ,AlarmDefinitionID AS [AlarmDefinition ID] , CASE WHEN Name not like '%example%' THEN DENSE_RANK() OVER(PARTITION BY equipment, (case when Name not like '%example%' then 1 else 0 end) ORDER BY Count (AlarmID) DESC) END as Rank ,Name ,Description FROM Events LEFT OUTER JOIN AlarmDefinition ON AlarmID = AlarmDefinitionID AND StartDate>= DATEADD(hour, 15, DATEADD( Day, DATEDIFF(day, 0, GETDATE()),0)) AND EndDate <= DATEADD(hour, 23, DATEADD( Day, DATEDIFF(day, 0, GETDATE()),0)) Group by EquipmentID, AlarmDefinitionName, AlarmID, Description, AlarmDefinitionID) t) WHERE (Rank <=3);

else

SELECT EquipmentID - 100 as equipmentno ,AlarmID ,Count (AlarmID) as Total ,AlarmDefinitionID AS [AlarmDefinition ID] , CASE WHEN Name not like '%example%' THEN DENSE_RANK() OVER(PARTITION BY equipment, (case when Name not like '%example%' then 1 else 0 end) ORDER BY Count (AlarmID) DESC) END as Rank ,Name ,Description FROM Events LEFT OUTER JOIN AlarmDefinition ON AlarmID = AlarmDefinitionID AND StartDate>= DATEADD(hour, 15, DATEADD( Day, DATEDIFF(day, 0, GETDATE()),0)) AND EndDate <= DATEADD(hour, 23, DATEADD( Day, DATEDIFF(day, 0, GETDATE()),0)) Group by EquipmentID, AlarmDefinitionName, AlarmID, Description, AlarmDefinitionID) t) WHERE (Rank <=3);

Answer 1

Below is what the output of the script would be. It would calculate based on specific timeframes for a work shift. If it is for shift 1 (7 am. to 3 p.m.), then totals would be calculated based on the present date, if it is for shift 3 (11 p.m. to 7 a.m.), then shift 1 and shift 2 would calculate based on previous date. The user doesn't get a selection for shifts. The report would be scheduled to run at 3 p.m., 11 p.m. and 7 a.m. which is the executiontime.

Equipment AlarmID Name Total Rank

1 Alarm1 AlarmName1 5 1

1 Alarm2 AlarmName2 2 2

1 Alarm3 AlarmName3 1 3

2 Alarm7 AlarmName7 2 1

2 Alarm10 AlarmName10 1 2

2 Alarm1 AlarmName1 1 2

The raw data would be:

Alarm_date Equipment AlarmID AlarmName

01/02/2023 7:23 AM 1 Alarm1 AlarmName1

01/02/2023 10: 40AM 1 Alarm1 AlarmName1

01/02/2023 11:53AM 1 Alarm1 AlarmName1

01/02/2023 12:05 PM 1 Alarm1 AlarmName1

01/02/2023 2:12 PM 1 Alarm1 AlarmName1

01/02/2023 2:30 PM 1 Alarm2 AlarmName2

01/02/2023 2:56 PM 1 Alarm2 AlarmName2

And do forth.

Answer 2

Hi @Anonymous

I'm mostly responsible for cases in the TSQL section, and I've never seen if/else statements after from.

I think to achieve your goal, you can use stored procedures and use if/else statements in them. I tried creating a stored procedure where you can replace 'print 1' and 'print 2' with what you need to populate.

create or alter procedure dbo.Alarm
as
declare @executiontime datetime;
begin
  set @executiontime = CONVERT(datetime,CONVERT(varchar(100),getdate(), 24),100);;
  if @executiontime between CONVERT(datetime,'7:00AM',100) and CONVERT(datetime,'11:59PM',100)
   begin
    print 1
   end
  else
   begin
    print 2
   end;
end;

exec dbo.Alarm;

I tried to read the two complex strings of code above, but the column names in your code outside of the sample data you gave me appeared, which made me unable to read. I don't dare mess with the code.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Answer 3

Thanks, hopefully someone else will respond to assist with how this should be devised. I changed the actual names in the data. So I must have missed something. But essentially, the results of what occurs is hopefully apparent.