if statement in where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if statement in where clause

Author

Topic

Mahavirjadhav
Starting Member

18 Posts

Posted - 2010-10-01 : 10:54:50

Hi all,
here is brief scenario. I want select sum of columns for the previous month.
if month = 1 then select select month =12 in previous year.
Here is query. But it showing error as
=========================================
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'MNTH'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'MNTH'.
======================================

Query is
========================================

SELECT (SUM(ISNULL(REF1_SHRE_AMT, 0)) + SUM(ISNULL(REF2_SHRE_AMT, 0)))
FROM BILL_REFER_INFO
WHERE BIZ_ID = 577 AND
(
IF MONTH('7/10/2010')= 1
BEGIN
MNTH = 12 AND
YER=YEAR('7/10/2010')-1
END
ELSE
BEGIN
MNTH = MONTH('7/10/2010')-1 AND
YER=YEAR('7/10/2010')
END
)

Mahavir

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 11:07:00

You're going to have to give us the table structure and sample data with expected output for this one.

What you've written doesn't make sense - even if it was translated to a CASE expression.

Provide the example data and required output. Someone will help you, probably quickly.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jleitao
Posting Yak Master

100 Posts

Posted - 2010-10-01 : 11:07:54

hi mahavir,

why you don't use the dateadd function to get the previous month?

SAMPLE:
SELECT DATEADD(MONTH, -1, '20/01/2010')

your code will be:
SELECT (SUM(ISNULL(REF1_SHRE_AMT, 0)) + SUM(ISNULL(REF2_SHRE_AMT, 0)))
FROM BILL_REFER_INFO
WHERE BIZ_ID = 577
AND MNTH = MONTH(DATEADD(MONTH, -1, '07/10/2010'))
AND YER = YEAR(DATEADD(MONTH, -1, '07/10/2010'))

JL

PS - NEXT TIME PROVIDE A EXAMPLE

Mahavirjadhav
Starting Member

18 Posts

Posted - 2010-10-01 : 11:43:04

here is data in table:

biz_id run_date mnth yer ref1_shre_amt ref2_shre_amt
577 2009-12-30 00:00:00.000 12 2009 25.000 NULL
577 2010-01-30 00:00:00.000 1 2010 19.000 NULL
577 2010-08-30 00:00:00.000 8 2010 10.000 9.500
577 2010-09-30 00:00:00.000 9 2010 19.000 NULL
570 2010-09-30 00:00:00.000 9 2010 NULL 9.500
570 2010-09-30 00:00:00.000 9 2010 26.000 NULL
577 2010-09-30 00:00:00.000 9 2010 19.000 NULL
570 2010-09-30 00:00:00.000 9 2010 NULL 9.500
570 2010-09-30 00:00:00.000 9 2010 26.000 NULL

and expected output is
when mnth =9 then sum(ref1_shre_amt + ref2_shre_amt ) for
output should be = 19.500 i.e for mnth = 8 for yer= 2010
and if mnth =1 & yer= 2010 then sum(ref1_shre_amt + ref2_shre_amt ) = 25.000
i.e for mnth= 12 and yer = 2009

quote:
Originally posted by Transact Charlie

You're going to have to give us the table structure and sample data with expected output for this one.

What you've written doesn't make sense - even if it was translated to a CASE expression.

Provide the example data and required output. Someone will help you, probably quickly.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Mahavir

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 11:58:03

Sorry - don't have the time to offer a suggestion.

Here's your data in a usable form for someone else though.

good luck -- I'm off for a beer.


DECLARE @tblData TABLE (
	[biz_Id] INT
	, [run_date] DATETIME
	, [mnth] INT
	, [yer] INT
	, [refl_shre_amt] MONEY
	, [ref2_shre_amt] MONEY
	)
INSERT @tblData
      SELECT 577, '2009-12-30T00:00:00.000', 12, 2009,  25.000,  NULL
UNION SELECT 577, '2010-01-30T00:00:00.000',  1, 2010,  19.000,  NULL
UNION SELECT 577, '2010-08-30T00:00:00.000',  8, 2010,  10.000,  9.500	
UNION SELECT 577, '2010-09-30T00:00:00.000',  9, 2010,  19.000,  NULL
UNION SELECT 570, '2010-09-30T00:00:00.000',  9, 2010,  NULL,    9.500	
UNION SELECT 570, '2010-09-30T00:00:00.000',  9, 2010,  26.000,  NULL
UNION SELECT 577, '2010-09-30T00:00:00.000',  9, 2010,  19.000,  NULL
UNION SELECT 570, '2010-09-30T00:00:00.000',  9, 2010,  NULL,    9.500	
UNION SELECT 570, '2010-09-30T00:00:00.000',  9, 2010,  26.000,  NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-01 : 12:10:14

[code]
-- *** Test Data (You should provide this) ***
CREATE TABLE #t
(
biz_id int NOT NULL
,run_date datetime NOT NULL
,ref1_shre_amt money NULL
,ref2_shre_amt money NULL
)
INSERT INTO #t
SELECT 577, '20091230', 25.000, NULL
UNION ALL SELECT 577, '20100130', 19.000, NULL
UNION ALL SELECT 577, '20100830', 10.000, 9.500
UNION ALL SELECT 577, '20100930', 19.000, NULL
UNION ALL SELECT 570, '20100930', NULL, 9.500
UNION ALL SELECT 570, '20100930', 26.000, NULL
UNION ALL SELECT 577, '20100930', 19.000, NULL
UNION ALL SELECT 570, '20100930', NULL, 9.500
UNION ALL SELECT 570, '20100930', 26.000, NULL
-- *** End Test Data (You should provide this) ***

DECLARE @now datetime
SET @now = '20100923' --GETDATE()

SELECT COALESCE(SUM(ref1_shre_amt), 0.0) + COALESCE(SUM(ref2_shre_amt), 0.0) AS Total
FROM #t
WHERE biz_id = 577
-- start of previous month
AND run_date >= DATEADD(month, DATEDIFF(month, 0, @now) -1, 0)
-- start of current month
AND run_date < DATEADD(month, DATEDIFF(month, 0, @now), 0)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources