Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

If statement SQL

Tags:

sql

Hi i want to run an if statement but keep getting syntax errors near all my AS aliases, what am i doing wrong?

SELECT

IF @Origin = 'ALL'


(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) <= 60) AS OneHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 61 AND 120) AS TwoHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 121 AND 180) AS ThreeHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 181 AND 240) AS FourHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 241 AND 480) AS EightHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND  DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 481 AND 1440) AS TwentyFourHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 1441 AND 2880) AS FortyEightHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) >=2881) AS PlusFortyEightHour

END
ELSE

(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) <= 60) AS OneHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 61 AND 120) AS TwoHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 121 AND 180) AS ThreeHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 181 AND 240) AS FourHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 241 AND 480) AS EightHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND  DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 481 AND 1440) AS TwentyFourHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 1441 AND 2880) AS FortyEightHour,
(SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) >=2881) AS PlusFortyEightHour
END

2 Answers

IF condition
BEGIN
-- ...
END
ELSE
BEGIN
-- ...
END
like image 173
John Saunders Avatar answered Mar 08 '26 23:03

John Saunders

There are two problems. The lack of a BEGIN, and also, the SELECT needs to be within each of the IF statements. You cannot have an inline IF within a SELECT, if you need to do this you can use a CASE statement.

IF @Origin = 'ALL'
BEGIN
    SELECT
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) <= 60) AS OneHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 61 AND 120) AS TwoHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 121 AND 180) AS ThreeHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 181 AND 240) AS FourHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 241 AND 480) AS EightHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND  DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 481 AND 1440) AS TwentyFourHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 1441 AND 2880) AS FortyEightHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) >=2881) AS PlusFortyEightHour
END
ELSE
BEGIN
    SELECT
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) <= 60) AS OneHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 61 AND 120) AS TwoHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 121 AND 180) AS ThreeHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 181 AND 240) AS FourHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 241 AND 480) AS EightHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND  DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 481 AND 1440) AS TwentyFourHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 1441 AND 2880) AS FortyEightHour,
        (SELECT COUNT(*)  FROM TBL_PARTORDER INNER JOIN TBL_REPAIR_ORDER ON TBL_PARTORDER.ORDERID = TBL_REPAIR_ORDER.ORDERID INNER JOIN TBL_PROPERTY ON TBL_REPAIR_ORDER.PROPREF = TBL_PROPERTY.PROPREF WHERE (TBL_PARTORDER.RAISED IS NOT NULL) AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103))) AND (TBL_PROPERTY.CONTRACT = @CONTRACT) AND (TBL_REPAIR_ORDER.ORIGIN = @ORIGIN) AND DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) >=2881) AS PlusFortyEightHour
END
like image 44
Robin Day Avatar answered Mar 08 '26 22:03

Robin Day
Sign in to Comment

Related questions

What are the advantages/disadvantages of using a CTE?
Group by value range and drop duplicates rows in Hql/Sql
Names in Alphabetical order then location in alphabetical order "Order By" not working
SQL Server Dynamic Order By

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!