select if statement

Question

question

tombiernacki asked Oct 26, '12

select if statement

Hi I have a select state that if the NAME column is 4,5, or 6 then do a select else do another select. Below is my select statement..... SELECT (SUM(BUD1_BUD_AMOUNT_01)+ SUM(BUD1_BUD_AMOUNT_02)+ SUM(BUD1_BUD_AMOUNT_03)+ SUM(BUD1_BUD_AMOUNT_04)+ SUM(BUD1_BUD_AMOUNT_05)+ SUM(BUD1_BUD_AMOUNT_06)+ SUM(BUD1_BUD_AMOUNT_07)+ SUM(BUD1_BUD_AMOUNT_08)+ SUM(BUD1_BUD_AMOUNT_09)+ SUM(BUD1_BUD_AMOUNT_10)+ SUM(BUD1_BUD_AMOUNT_11)+ SUM(BUD1_BUD_AMOUNT_12)) / (SUM(BUD1_BUD_UNITS_01)+ SUM(BUD1_BUD_UNITS_02)+ SUM(BUD1_BUD_UNITS_03)+ SUM(BUD1_BUD_UNITS_04)+ SUM(BUD1_BUD_UNITS_05)+ SUM(BUD1_BUD_UNITS_06)+ SUM(BUD1_BUD_UNITS_07)+ SUM(BUD1_BUD_UNITS_08)+ SUM(BUD1_BUD_UNITS_09)+ SUM(BUD1_BUD_UNITS_10)+ SUM(BUD1_BUD_UNITS_11)+ SUM(BUD1_BUD_UNITS_12)) *-1 AS 'PLAN',NAME FROM [test].[dbo].[report] where name is not null and acct_unit in ('4111','4611') and account in ('3100','3101','3102','3103','3104','3105','3261') GROUP BY NAME ------------------- else select statement is.... SELECT (SUM(BUD1_BUD_AMOUNT_01)+ SUM(BUD1_BUD_AMOUNT_02)+ SUM(BUD1_BUD_AMOUNT_03)+ SUM(BUD1_BUD_AMOUNT_04)+ SUM(BUD1_BUD_AMOUNT_05)+ SUM(BUD1_BUD_AMOUNT_06)+ SUM(BUD1_BUD_AMOUNT_07)+ SUM(BUD1_BUD_AMOUNT_08)+ SUM(BUD1_BUD_AMOUNT_09)+ SUM(BUD1_BUD_AMOUNT_10)+ SUM(BUD1_BUD_AMOUNT_11)+ SUM(BUD1_BUD_AMOUNT_12)) / (SUM(BUD1_BUD_UNITS_01)+ SUM(BUD1_BUD_UNITS_02)+ SUM(BUD1_BUD_UNITS_03)+ SUM(BUD1_BUD_UNITS_04)+ SUM(BUD1_BUD_UNITS_05)+ SUM(BUD1_BUD_UNITS_06)+ SUM(BUD1_BUD_UNITS_07)+ SUM(BUD1_BUD_UNITS_08)+ SUM(BUD1_BUD_UNITS_09)+ SUM(BUD1_BUD_UNITS_10)+ SUM(BUD1_BUD_UNITS_11)+ SUM(BUD1_BUD_UNITS_12)) *-1 AS 'PLAN',NAME FROM [CRCUtilities].[dbo].[GLREPORTING] where name is not null and acct_unit in ('4111','4611') and account in ('3100','3101') GROUP BY NAME --------- Thanks in advance

tsql case-statement

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-10-27T13:25:46Z

ThomasRushton answered Oct 27, '12

It sounds as though you need something like: SELECT (SUM(....)) AS 'Plan', Name FROM test.dbo.report WHERE name is not null AND account in (...) AND ( ( name IN (4,5,6) AND acct_unit in ('4111', '4611') ) OR ( name NOT IN (4,5,6) AND acct_unit = '4111' ) ) Something like that. Thinking about it, that could probably be compressed further...

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tombiernacki commented · Oct 30, 2012 at 04:24 PM

Thanks Thomas that worked

0 ·

Answer 2 · 2012-10-26T21:52:56Z

Tim answered Oct 26, '12

You need an IF Else statement. IF select something from table where (condition) value Begin END Else I can't tell from your query what is unique between the two since each has WHERE name is not null and acct_unit in ('4111','4611') and both included account in ('3100','3101').

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tombiernacki commented · Oct 26, 2012 at 10:18 PM

let me make the select statement shorter for simplicity..... so what I need is that if name is 4,5,or 6 then SELECT (SUM(BUD1_BUD_AMOUNT_01)) / ( SUM(BUD1_BUD_UNITS_12)) *-1 AS 'PLAN',NAME FROM [test].[dbo].[report] where name is not null and acct_unit in ('4111','4611') and account in ('3100','3101','3102','3103','3104','3105','3261') GROUP BY NAME else if name is different then do the select statement with other acct_unit SELECT (SUM(BUD1_BUD_AMOUNT_01)) / ( SUM(BUD1_BUD_UNITS_12)) *-1 AS 'PLAN',NAME FROM [test].[dbo].[report] where name is not null and acct_unit in ('4111') and account in ('3100','3101','3102','3103','3104','3105','3261') GROUP BY NAME

0 ·

question