IT Anawer: If statement 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

From stackoverflow

```
IF condition
BEGIN
-- ...
END
ELSE
BEGIN
-- ...
END
```
John Saunders : If in doubt, simplify. Try SELECT IF condition BEGIN SELECT 1 AS OneHour, SELECT 2 AS TwoHour, etc. You'll find the solution quickly.

You forgot the BEGIN...

IF (@Origin = 'ALL')
BEGIN
--
END
ELSE
BEGIN
--
END

I've just tried the following as a test and it works fine. You should be able to use this as a starting point for your example.

DECLARE @Origin NVARCHAR(200)
SET @Origin = 'ALL'
IF @Origin = 'ALL'
    BEGIN
    (SELECT ('Test') AS HI)
    END
ELSE
    BEGIN
     (SELECT ('Test2') AS HI)
    END

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

Arvo : You did post while as I was writing same thing - instead voted you up :)

John Saunders : @Robin, that's _cheating_! I wanted him to find that out by himself. You've just given it away. ;-)

Robin Day : Sorry, have upvoted you to make up for it :)

Robin Day : The main point here is that IF is generally not used for what you were trying. Instead you would use the CASE statement. On a completely different note though, I think you will find there is much much more you can do to this statement to make it better. There is a lot of repetition of which you can probably find an alternative.

Robin Day : @elphj: I would suggest looing at a case statement to calculate the onehour, threehour items as a column and then performing a GROUP BY on that column to retrieve the data you are after. That way you will receive a record for each time frame with the count. If you then do need this for each column you can perform a PIVOT to get it exactly as you have it now. I suggest having a play with that and then maybe asking another question related to that if you struggle.

I think your query would be easier to read if you use this approach:

SELECT
    SUM(
        CASE WHEN DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) <= 60 THEN 1
        ELSE 0
    ) AS OneHour,
    SUM(
        CASE WHEN DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN 61 AND 120 THEN 1
        ELSE 0
    ) AS TwoHour,
    ...
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_PROPERTY.CONTRACT = @CONTRACT)
    AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) 
    AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103)))

Or better yet, pull the time window definitions into a table:

create table TimeWindow (MinuteLowerBound int , MinuteUpperBound int , TimeWindowName varchar(32))
insert TimeWindow (MinuteLowerBound , MinuteUpperBound , TimeWindowName)
select 0 as MinuteLowerBound , 60 as MinuteUpperBound , 'OneHour' as TimeWindowName
union all select 61, 120, 'TwoHour'
union all select 121, 180, 'ThreeHour'

And then just join against the time window definitions:

SELECT
    tw.TimeWindowName,
    count(*) as TimeWindowCount
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
    INNER JOIN TimeWindow as tw on DATEDIFF(mi, TBL_PARTORDER.RAISED, TBL_PARTORDER.RECEIVED) BETWEEN tw.MinuteLowerBound and tw.MinuteUpperBound
WHERE (TBL_PARTORDER.RAISED IS NOT NULL) 
    AND (TBL_PROPERTY.CONTRACT = @CONTRACT)
    AND (TBL_PARTORDER.RAISED BETWEEN CONVERT(DATETIME,@STARTDATE, 103) 
    AND DATEADD(hh,23,CONVERT(DATETIME,@ENDDATE, 103)))  
GROUP BY tw.TimeWindowName

IT Anawer

Thursday, May 5, 2011

If statement SQL

0 comments:

Post a Comment

Blog Archive