|
|
Posted by jazpar on 05/26/06 10:34
Hi Hugo, and thanks
Here is table ADMIN (actual name GURU_ADMIN)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GURU_ADMIN]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[GURU_ADMIN]
GO
CREATE TABLE [dbo].[GURU_ADMIN] (
[OLAPFROMDATE] [datetime] NOT NULL ,
[OLAPCUBENAME] [varchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL
,
[OLAPTODATE] [datetime] NOT NULL ,
[BUDGETMODELID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL
,
[GURUDESCRIPTION] [varchar] (250) COLLATE Danish_Norwegian_CI_AS NOT
NULL ,
[DATAAREAID] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[RECID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GURU_ADMIN] ADD
CONSTRAINT [DF__GURU_ADMI__OLAPF__5C482906] DEFAULT ('1900-01-01
00:00:00.000') FOR [OLAPFROMDATE],
CONSTRAINT [DF__GURU_ADMI__OLAPC__5D3C4D3F] DEFAULT ('') FOR
[OLAPCUBENAME],
CONSTRAINT [DF__GURU_ADMI__OLAPT__5E307178] DEFAULT ('1900-01-01
00:00:00.000') FOR [OLAPTODATE],
CONSTRAINT [DF__GURU_ADMI__BUDGE__5F2495B1] DEFAULT ('') FOR
[BUDGETMODELID],
CONSTRAINT [DF__GURU_ADMI__GURUD__6018B9EA] DEFAULT ('') FOR
[GURUDESCRIPTION],
CONSTRAINT [DF__GURU_ADMI__DATAA__610CDE23] DEFAULT ('dat') FOR
[DATAAREAID],
CHECK ([RECID] <> 0)
GO
CREATE UNIQUE INDEX [I_50001RECID] ON
[dbo].[GURU_ADMIN]([DATAAREAID], [RECID]) ON [PRIMARY]
GO
Table Ledgerbudget
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[LEDGERBUDGET]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[LEDGERBUDGET]
GO
CREATE TABLE [dbo].[LEDGERBUDGET] (
[ACCOUNTNUM] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[STARTDATE] [datetime] NOT NULL ,
[ENDDATE] [datetime] NOT NULL ,
[FREQCODE] [int] NOT NULL ,
[ACTIVE] [int] NOT NULL ,
[AMOUNT] [numeric](28, 12) NOT NULL ,
[COMMENT_] [varchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION2_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION3_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[AUTOTRANS] [int] NOT NULL ,
[CURRENCY] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[QTY] [numeric](28, 12) NOT NULL ,
[PRICE] [numeric](28, 12) NOT NULL ,
[STOP] [int] NOT NULL ,
[KEY_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[EXPANDID] [int] NOT NULL ,
[REPORT] [int] NOT NULL ,
[COV] [int] NOT NULL ,
[COVSTATUS] [int] NOT NULL ,
[CREDITING] [int] NOT NULL ,
[FREQ] [int] NOT NULL ,
[TAXGROUP] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[MODELNUM] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[INVENTRECID] [int] NOT NULL ,
[INVENTTABLEID] [int] NOT NULL ,
[ALLOCATEMETHOD] [int] NOT NULL ,
[FORECASTMODELID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT
NULL ,
[ASSETID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[ASSETTRANSTYPE] [int] NOT NULL ,
[ASSETBOOKID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[MODIFIEDBY] [varchar] (5) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DATAAREAID] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[RECID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [I_196MODELIDX] ON
[dbo].[LEDGERBUDGET]([DATAAREAID], [MODELNUM], [ACCOUNTNUM],
[STARTDATE]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LEDGERBUDGET] ADD
CONSTRAINT [DF__LEDGERBUD__ACCOU__2B9540A9] DEFAULT ('') FOR
[ACCOUNTNUM],
CONSTRAINT [DF__LEDGERBUD__START__2C8964E2] DEFAULT ('1900-01-01
00:00:00.000') FOR [STARTDATE],
CONSTRAINT [DF__LEDGERBUD__ENDDA__2D7D891B] DEFAULT ('1900-01-01
00:00:00.000') FOR [ENDDATE],
CONSTRAINT [DF__LEDGERBUD__FREQC__2E71AD54] DEFAULT (0) FOR
[FREQCODE],
CONSTRAINT [DF__LEDGERBUD__ACTIV__2F65D18D] DEFAULT (0) FOR [ACTIVE],
CONSTRAINT [DF__LEDGERBUD__AMOUN__3059F5C6] DEFAULT (0) FOR [AMOUNT],
CONSTRAINT [DF__LEDGERBUD__COMME__314E19FF] DEFAULT ('') FOR
[COMMENT_],
CONSTRAINT [DF__LEDGERBUD__DIMEN__32423E38] DEFAULT ('') FOR
[DIMENSION],
CONSTRAINT [DF__LEDGERBUD__DIMEN__33366271] DEFAULT ('') FOR
[DIMENSION2_],
CONSTRAINT [DF__LEDGERBUD__DIMEN__342A86AA] DEFAULT ('') FOR
[DIMENSION3_],
CONSTRAINT [DF__LEDGERBUD__AUTOT__351EAAE3] DEFAULT (0) FOR
[AUTOTRANS],
CONSTRAINT [DF__LEDGERBUD__CURRE__3612CF1C] DEFAULT ('') FOR
[CURRENCY],
CONSTRAINT [DF__LEDGERBUDGE__QTY__3706F355] DEFAULT (0) FOR [QTY],
CONSTRAINT [DF__LEDGERBUD__PRICE__37FB178E] DEFAULT (0) FOR [PRICE],
CONSTRAINT [DF__LEDGERBUDG__STOP__38EF3BC7] DEFAULT (0) FOR [STOP],
CONSTRAINT [DF__LEDGERBUDG__KEY___39E36000] DEFAULT ('') FOR [KEY_],
CONSTRAINT [DF__LEDGERBUD__EXPAN__3AD78439] DEFAULT (0) FOR
[EXPANDID],
CONSTRAINT [DF__LEDGERBUD__REPOR__3BCBA872] DEFAULT (0) FOR [REPORT],
CONSTRAINT [DF__LEDGERBUDGE__COV__3CBFCCAB] DEFAULT (0) FOR [COV],
CONSTRAINT [DF__LEDGERBUD__COVST__3DB3F0E4] DEFAULT (0) FOR
[COVSTATUS],
CONSTRAINT [DF__LEDGERBUD__CREDI__3EA8151D] DEFAULT (0) FOR
[CREDITING],
CONSTRAINT [DF__LEDGERBUDG__FREQ__3F9C3956] DEFAULT (0) FOR [FREQ],
CONSTRAINT [DF__LEDGERBUD__TAXGR__40905D8F] DEFAULT ('') FOR
[TAXGROUP],
CONSTRAINT [DF__LEDGERBUD__MODEL__418481C8] DEFAULT ('') FOR
[MODELNUM],
CONSTRAINT [DF__LEDGERBUD__INVEN__4278A601] DEFAULT (0) FOR
[INVENTRECID],
CONSTRAINT [DF__LEDGERBUD__INVEN__436CCA3A] DEFAULT (0) FOR
[INVENTTABLEID],
CONSTRAINT [DF__LEDGERBUD__ALLOC__4460EE73] DEFAULT (0) FOR
[ALLOCATEMETHOD],
CONSTRAINT [DF__LEDGERBUD__FOREC__455512AC] DEFAULT ('') FOR
[FORECASTMODELID],
CONSTRAINT [DF__LEDGERBUD__ASSET__464936E5] DEFAULT ('') FOR
[ASSETID],
CONSTRAINT [DF__LEDGERBUD__ASSET__473D5B1E] DEFAULT (0) FOR
[ASSETTRANSTYPE],
CONSTRAINT [DF__LEDGERBUD__ASSET__48317F57] DEFAULT ('') FOR
[ASSETBOOKID],
CONSTRAINT [DF__LEDGERBUD__MODIF__4925A390] DEFAULT ('?') FOR
[MODIFIEDBY],
CONSTRAINT [DF__LEDGERBUD__DATAA__4A19C7C9] DEFAULT ('dat') FOR
[DATAAREAID],
CHECK ([RECID] <> 0)
GO
CREATE INDEX [I_196ACCOUNTIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[ACCOUNTNUM], [MODELNUM], [STARTDATE]) ON [PRIMARY]
GO
CREATE INDEX [I_196EXPANDIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[EXPANDID]) ON [PRIMARY]
GO
CREATE INDEX [I_196REPIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[REPORT], [ACCOUNTNUM], [MODELNUM], [STARTDATE]) ON [PRIMARY]
GO
CREATE INDEX [I_196COVIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[COVSTATUS]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [I_196RECID] ON
[dbo].[LEDGERBUDGET]([DATAAREAID], [RECID]) ON [PRIMARY]
GO
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
Sample data Guru_Admin (record with no BudgetModelId):
OLAPFromDate,OLAPToDate,BudgetModelId
01012006,31012006,''
Sample data Guru_Admin (record with BudgetModelId):
OLAPFromDate,OLAPToDate,BudgetModelId
01012006,31012006,'Test1'
So the first case should return both records from table LedgerBudget,
and the latter case should only return the first record from
LedgerBudget.
I hope you can be able to help with this matter.
Thanks /BR
Jan
Navigation:
[Reply to this message]
|