Posted by Hugo Kornelis on 05/27/06 00:58

On 26 May 2006 00:34:23 -0700, jazpar wrote:

>Hi Hugo, and thanks
>
>Here is table ADMIN (actual name GURU_ADMIN)
(snip)

Hi Jan,

Thanks for the CREATE TABLE statements.

>Sample data could be (I dont know how to make these in a file)
>
>Sample data Ledgerbudget:
>Accountnum, Startdate, Modelnum,Amount,Active,AllocateMethod
>1, 01012006,Test1,100,1,0
>1, 01012006,Test2,100,1,0

Converting these rows to INSERT statements yields

INSERT INTO LEDGERBUDGET (ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT,
ACTIVE, ALLOCATEMETHOD)
SELECT 1, '20060101', 'Test1', 100, 1, 0
UNION ALL
SELECT 1, '20060101', 'Test2', 100, 1, 0

Howver, this gives me an error because several required columns are not
specified. Please either trim irrelevant columns from the CREATE TABLE
statement, or post INSERT statements that include all columns.

(snip)
>So the first case should return both records from table LedgerBudget,
>and the latter case should only return the first record from
>LedgerBudget.

Untested (for the reasons stated above), but maybe this works:

SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,
dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE,
- dbo.LEDGERBUDGET.AMOUNT AS BUDGET
FROM dbo.LEDGERBUDGET
INNER JOIN dbo.ADMIN
ON dbo.LEDGERBUDGET.STARTDATE >= dbo.ADMIN.FROMDATE
AND dbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATE
AND (dbo.LEDGERBUDGET.MODELNUM = dbo.ADMIN.BUDGETMODELID
OR dbo.ADMIN.BUDGETMODELID = '')
WHERE dbo.LEDGERBUDGET.ACTIVE = 1
AND dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0

--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: Trying To find a Match in computed columns
• Prev in forum: Re: Trying To find a Match in computed columns
• Thread view: Re: How to make a correct select

[Reply to this message]