ansaurus

Question

If statement SQL

Answer 1

+6 A:

IF condition
BEGIN
-- ...
END
ELSE
BEGIN
-- ...
END

John Saunders 2009-05-05 10:19:33

tried that i still get syntax erros by all the AS at the end of each count, cant you use these in an If statement?

2009-05-05 10:21:47

If in doubt, simplify. Try SELECT IF condition BEGIN SELECT 1 AS OneHour, SELECT 2 AS TwoHour, etc. You'll find the solution quickly.

John Saunders 2009-05-05 10:27:42

Answer 2

A:

You forgot the BEGIN...

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

Fermin 2009-05-05 10:23:16

nope still not working, can i have the AS columns all with the same name. Im sure ive done this before but i cant find it!!!

2009-05-05 10:28:24

Answer 3

A:

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

Gavin Draper 2009-05-05 10:27:35

nope still wont work!!!!

2009-05-05 10:36:01

Answer 4

+1 A:

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

Robin Day 2009-05-05 10:35:15

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

Arvo 2009-05-05 10:36:49

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

John Saunders 2009-05-05 10:37:34

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

Robin Day 2009-05-05 10:39:40

yay it worked thanks :) i had a select to start with but deleted it and forgot to add back in, didnt know i needed two though so will def have try remember this next time.

2009-05-05 10:39:47

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 2009-05-05 10:42:28

i was wondering if i could do a case statement but have never done one for such a large query, normally use it one at a time so thought that might slow the procedure down which is why i went with the IF statement. Would i use the same format have one case statement with all the selects in or a statment for each one and encorporate teh second statements into it. Would that have been better as it seams just as long winded to me?!

2009-05-05 10:51:38

@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.

Robin Day 2009-05-05 11:02:10

thanks, never heard of a pivot so will have have look

2009-05-05 11:10:13

Answer 5

A:

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

2009-05-05 14:03:40

ansaurus

tags:

views:

answers:

If statement SQL

related questions