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
  

How can I use Java to programmaticaly convert an Internal Stylesheet of a web-page to an inline one?

Is there a ready-made tool or utility that does that already

From stackoverflow

• You could just open the CSS file and just embed its content into a style tag.

  It should not be that hard:

  FileReader frCSS = new FileReader("your_css.css");
  FileReader frHTML = new FileReader("your_html.html");
  FileWriter output = new FileWriter("result.html");
  

  Then parse frHTML and copy its content to output until you find the head opening tag. When you do, open a style tag and copy all contents from frCSS. Then, close the style tag and copy the remaining portion of frHTML.

  It's not pretty, but it can be done.

  Oren Yosifon : That would convert an External Stylesheet to Internal. I am interested in converting internal to inline, if possible, without the heavylifting of doing all the parsing myself...

Deploying a WebService in glassfish with init parameters

I've created a WebService using the JAX-WS API. This service runs without any problem using an Endpoint class.

main(String args[])
{
(...)
MyService service=new MyService();
service.setParam1("limit=100");
service.setParam2("hello");
service.setParam3("max-value=10");
Endpoint endpoint = Endpoint.create(service);
endpoint.publish("http://localhost:8090/ws");
(...)
}

now, I'd like to deploy this service in glassfish. However, as I wrote in my example, I'd like to initialize my service with a some parameters. How can I achieve this ? should I use another API ?

Many thanks in advance

From stackoverflow

• OK, I finally found the answer : using javax.xml.ws.WebServiceContext was the solution .see this other answer http://stackoverflow.com/questions/261348

Serialise to XML and include the type of the serialised object

In a previous question about serialising an object to an XmlDocument in C#, I needed to serialise some fault information to an XmlDocument that was returned from a asmx-style webservice call. On the client I need to de-serialise the XmlDocument back to an object.

This is straightforward enough if you know the type, but I realised I wanted a flexible approach where the type to de-serialise to is also encoded in the XmlDocument. I'm currently doing it manually by adding an XmlNode to the XmlDocument that has the type name, calculated as follows:

    Type type = fault.GetType();
    string assemblyName = type.Assembly.FullName;

    // Strip off the version and culture info
    assemblyName = assemblyName.Substring(0, assemblyName.IndexOf(",")).Trim();

    string typeName = type.FullName + ", " + assemblyName;

Then on the client I first get this type name back from the XmlDocument, and create the type object that is passed into the XmlSerialiser thus:

        object fault;
        XmlNode faultNode = e.Detail.FirstChild;
        XmlNode faultTypeNode = faultNode.NextSibling;

        // The typename of the fault type is the inner xml of the first node
        string typeName = faultTypeNode.InnerXml;
        Type faultType = Type.GetType(typeName);

        // The serialised data for the fault is the second node
        using (var stream = new StringReader(faultNode.OuterXml))
        {
            var serialiser = new XmlSerializer(faultType);
            objectThatWasSerialised = serialiser.Deserialize(stream);
        }

        return (CastToType)fault;

So this is a brute-force approach, and I was wondering if there's a more elegant solution that somehow includes the typename of the serialised type automatically, rather than manually recording it elsewhere?

From stackoverflow

• Neil, why do you need it to be the same type on both the client and the server?

  Are you still using ASMX on the client? That would be a reason, as ASMX does not properly support faults.

  Also, do you have so many different fault types that a simple switch statement can't determine the correct type to use?

  Neil Barnwell : I'm using asmx on the server and client, yes. I'm sort've implementing a kind of half-baked WCF-like fault system because on the client I'm using an interface to the asmx service and I don't want users of that interface to get SoapExceptions, so they get something I've called a ServiceException instead, with a Fault property. That fault property is returned to the asmx client in the SoapException.Detail property, hence the XmlDocument/XmlNode serialisation. I don't have many fault types at the moment, but I want flexibility to add more to the server without breaking deployed clients.

  John Saunders : So, you're not reinventing the wheel; you're reinventing WCF.

• I had faced a similar problem and I came up with the same solution. As far as I am concerned, that is the only way to keep types together with values in XML serialization.

  I see you are cutting assembly version out as I did too. But I'd like to mention, that you will have troubles with generic types as theirs signature looks like that:

  System.Nullable`1[[System.Int, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  

  So I made a function to only cut out the assembly version(s), which seems to be enough to get rid of versioning problems:

      private static string CutOutVersionNumbers(string fullTypeName)
      {
          string shortTypeName = fullTypeName;
          var versionIndex = shortTypeName.IndexOf("Version");
          while (versionIndex != -1)
          {
              int commaIndex = shortTypeName.IndexOf(",", versionIndex);
              shortTypeName = shortTypeName.Remove(versionIndex, commaIndex - versionIndex + 1);
              versionIndex = shortTypeName.IndexOf("Version");
          }
          return shortTypeName;
      }
  

What is the difference between UML 2.1.2 and UML 2.2

I am evaluating a tool which says that it supports UML 2.2. However if I search on internet, I can't find what incremental change has occured between two version.

And yes, I tried to go through OMG documents, but those are too dense to read. Is there a simple explanation anywhere?

From stackoverflow

• Not sure what your reference level of UML is, but the UML 2.2 specifications with change bars shows you all the changes. It just takes time page turning. It's perhaps easier to read through the RTF Report for revision 2.2.

• I don't think there's a clear overview of the changes in 2.2. There's a UML 2.2 specification document with change bars: it marks the changed sections as opposed to version 2.1.2. It's really the best way to view those changes - again, there's no real overview, at least none that I could find. You can view the document (PDF) here.

  I've skimmed the document a bit. They're mostly minor changes. Some of the changes I came across you can quickly find in:

  • 8.3 (Class Descriptions)
  • 8.3.2 (ComponentRealization)
  • 10.2 (Deployments - Abstract Syntax)
  • 11.2 (Abstract Syntax)
  • 11.3.12 (ClearStructuralFeatureAction)
  • 11.3.19 (InputPin)
  • 11.3.33 (ReadLinkAction)
  • 11.3.47 (StartObjectBehaviorAction)
  • 11.3.55 (WriteStructuralFeatureAction)
  • 15.2 (State Machines - Abstract syntax)
  Varun Mahajan : Thank you, you saved me a lot of time.

  Razzie : You're welcome. Note though, that the list is not complete. However, with the change bars, it is not too hard to quickly view what sections have changed. Good luck!

Good books or online resources for learning Hibernate

What I found so far are some online resources like this:

http://www.torsten-horn.de/techdocs/java-hibernate.htm (GER)

https://www.hibernate.org/5.html (Hibernate Docs)

http://docs.jboss.org/hibernate/stable/core/reference/en/html/tutorial.html

http://www.manning.com/bauer2/chapter2.pdf (Sample chapter of Java Persistence with Hibernate)

http://www.wenzlaff.de/hibernate.html (GER)

So thats a good way to start but i wonder if you can recommand any good books for learning Hibernate, or maybe you know some other very good online resource for learning it?

From stackoverflow

• Visit www.flazx.com. I guess Professional Hibernate (Programmer to Programmer) would be a nice selection

• try search on scribd.com

• These are some links I have bookmarked:

  • http://www.laliluna.de
  • Hibernate Basics

  However, in my opinion Java Persistence with Hibernate definitely worths the money and time to read it.

• Hibernate in Action by Christian Bauer and Gavin King

  Its slightly out of date, but is an excellent introduction.

• Java Persistence with Hibernate is the second edition of "Hibernate in Action". They changed the name since they now provide JPA examples as well as Hibernate.

  toolkit : excellent book - covers both native Hibernate, and JPA using Hibernate.

identity to be incremented only if record is inserted

sql server 2005 : i have a column empid in employee table with identity on.if there is some error while inserting data into table .identity is incremented .i want identity to be incremented only if record is inserted .like if i have generated emp id from 1 to 5 and then on 6th record insertion error ocurrs.and on next record insertion identity value will be 7 .i want it to be 6.

From stackoverflow

• I don't think this can be done. If you want your identity numbers to be exactly sequential then you may have to generate them yourself, rather than using the SQL Identity feature.

  edit: Even rolling back the failed transactions will not make the Identity count go back down, this is by design, see this other question.

• Why do you want to do that ?

  The identity column should only be used as an 'internal administrative value' for the database, and it should have no 'business value', so why does it matter that there are gaps in that sequence ?
  If identity is used correctly, then users of your software will never be faced with the column that has an identity value; you just use it to uniquely identify a record.

• When you need to have this specific behaviour you should use stored procedures to generate the ID. This way you can really to a rollback. But keep in mind that the current behaviour is by purpose. Transaction isolation and different read levels (dirty reads) will most likely get you into trouble when you don't use locking on that id field in your masterdata table that holds the current or next ID value.

• What valid business reason do you have for caring if there are gaps? There is no reason for the database to care and every reason to want to make sure that identity values are never reused for something else as they can cause major problems with data integrity with looking up information based on old reports, etc. Suppose you have a report that shows the orders last month and then you delete one of the records becasue the customer was duplicated and thus dedupped. Then you reuse the identity field for the dupped customer that was removed. NOw someone looknig at last month's report goes to look up customer 12345 and the data associated with that cuisotmer belongs to John Smith rather than Sally Jones. BUt the person doesn;t know that because she is using an aggreagate, so now she has incorrect information that was totally avoidable. If she was looking up the delted customer, the process instead could have redirected her to the correct customer left after the dedupping.