Need help with Tricky SQL statement.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with Tricky SQL statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-02-17 : 15:11:10

Give the business rules below. How can I obtain the desire results.
Thank you so much in advance. SQL 2005

IF OBJECT_ID('Tempdb.dbo.#t', 'u') IS NOT NULL
DROP TABLE #t
GO

CREATE TABLE #t
(
LoanId INT NULL,
Reviewedby VARCHAR(10) NULL,
TaxesDueby1 VARCHAR(10) NULL,
TaxesDueby2 VARCHAR(10) NULL,
TaxesDueby3 VARCHAR(10) NULL,
TaxesDueby4 VARCHAR(10) NULL
)
GO

INSERT #t VALUES (100003, 'John', 'John', 'Lisa', '', '')
go

SELECT *
FROM #t;
go

LoanId Reviewedby TaxesDueby1 TaxesDueby2 TaxesDueby3 TaxesDueby4
----------- ---------- ----------- ----------- ----------- -----------
100003 John John Lisa

BEGIN TRAN

UPDATE Audits
SET Reviewedby = 'ykwan'
,TaxesDueby = 'ykwan'
,TaxesDueby2 = 'lkandaswam'
,TaxesDueby3 = ''
,TaxesDueby4 = ' '
WHERE LoanNum = '100003'

PRINT @@Trancount
ROLLBACK TRAN
COMMIT TRAN;
GO

-- Business rules:
1. if Reviewed by = '' return False
2. if Reviewedby <> TaxesDueBy1 return False
3. If Reviewedby = TaxesDueBy1 and TaxesDueby2 = 'Lisa', TaxesDueby3 = 'Christine', TaxesDueby4 = 'Fred' return False
4. if Reviewedby = TaxesDueBy1 and TaxesDueby2 = '', TaxesDueby3 = '', TaxesDueby4 = '' return True.

-- I can't figerout #3. I need to return False. Any helps would greatly appreciate.
SELECT TOP 1 LoanId,
CAST(Reviewedby AS VARCHAR(10)) AS 'Reviewedby',
CAST(TaxesDueby1 AS VARCHAR(10)) AS 'TaxesDueby',
CAST(TaxesDueby2 AS VARCHAR(10)) AS 'TaxesDueby2',
CAST(TaxesDueby3 AS VARCHAR(10)) AS 'TaxesDueby3',
CAST(TaxesDueby4 AS VARCHAR(10)) AS 'TaxesDueby4',
CASE
--WHEN TaxesDueby = Reviewedby AND TaxesDueby2 = Reviewedby AND TaxesDueby3 = Reviewedby AND TaxesDueby4 = Reviewedby THEN 'True'
WHEN TaxesDueby1 = Reviewedby THEN 'True'
--WHEN Reviewedby <> '' THEN 'True'
ELSE 'False'
END AS 'Tax Check'
FROM #t
WHERE LoanId = '100003';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:08:36

you've some hardcoded values for some fields like TaxesDueby2 = 'Lisa', TaxesDueby3 = 'Christine', TaxesDueby4 = 'Fred' in your business rules. Is it always like that? are they only people who need to be considered for each field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 04:11:59

And you don't need to CAST the columns as varchar(10) because they are already that type in your table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources