|
|
Posted by Erland Sommarskog on 05/27/06 01:18
jazpar (jannoergaard@hotmail.com) writes:
> Sample data could be (I dont know how to make these in a file)
You don't know how to type INSERT statements?:
INSERT LEDGERBUDGET(ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT, ACTIVE,
ALLOCATEMETHOD, RECID)
VALUES(1, '20060101','Test1',100,1,0, 1)
INSERT LEDGERBUDGET(ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT, ACTIVE,
ALLOCATEMETHOD, RECID)
VALUES(1, '20060101', 'Test1', 100,1,0, 2)
go
INSERT GURU_ADMIN(OLAPFROMDATE, OLAPTODATE, BUDGETMODELID, RECID)
VALUES('20060101', '31010206','', 1)
INSERT GURU_ADMIN(OLAPFROMDATE, OLAPTODATE, BUDGETMODELID, RECID)
VALUES('20060101','31010206','Test1', 2)
If you had made the effort to do this, and actually tested the
script, it would have saved me the time from changing all the
column names, adding quotes, and fixing the bad dates.
I also like to remind you that part of the recommendation is that you
post the desired output from the query. This makes it possible to
test and validate the query.
Anyway, after having read your requirements, I think what you need is
to change the query to:
SELECT LTRIM(l.ACCOUNTNUM) AS ACCOUNT_ID,
l.STARTDATE AS TRANSDATE, - l.AMOUNT AS BUDGET
FROM dbo.LEDGERBUDGET l
LEFT JOIN dbo.GURU_ADMIN g ON
l.STARTDATE >= g.OLAPFROMDATE
AND l.STARTDATE <= g.OLAPTODATE
AND l.MODELNUM = coalesce(nullif(g.BUDGETMODELID, ''), l.MODELNUM)
WHERE l.ACTIVE = 1
AND l.ALLOCATEMETHOD = 0
The important line is:
AND l.MODELNUM = coalesce(nullif(g.BUDGETMODELID, ''), l.MODELNUM)
nullif says that space should be interpreted as NULL. (Your default
values appear excessive to me.) coalesce returns the first non-NULL
value of its argument.
The problem with this solution is that it may not perform well. But
without knowing sizes of the tables, I don't feel like considering
alternate solutions.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|