Select where one date must match several

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 2000 Forums

Transact-SQL (2000)

Select where one date must match several

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2005-05-30 : 14:56:15

I have got one table tblOperations, containing data on funds (IDTitre) for subscriber (IDSouscripteur) with parts they purchase (NbPartOperation) per date (DateLiberation) and one table tblRetros with amount per funds (Ret_Titre) and period (Ret_Periode).

I created a view for summing all parts by share "SELECT IDTitre, SUM(NbPartOperation) AS iTotalNbrParts FROM dbo.tblOperations GROUP BY IDTitre"

I create another view summing all part by share and subscriber "SELECT IDTitre, IDSouscripteur, SUM(NbPartOperation) AS iNbrParts FROM dbo.tblOperations GROUP BY IDTitre, IDSouscripteur"

I have a problem with those 2 views as I need to join them with the table tblRetros which doesn't contain the dates

Example:

I can have this:

(tblOperations) Date, Funds, Subscriber, Parts
10/1/2005, 1, 100, 10
15/1/2005, 1, 200, 30
20/1/2005, 1, 100, 40
26/1/2005, 2, 100, 10
16/2/2005, 2, 100, 10
15/2/2005, 1, 200, 30

(tblRetros) Date, Funds, Amount
31/1/2005, 1, 15000
31/1/2005, 2, 5000
20/2/2005, 1, 20000
20/2/2005, 2, 7000

This would give a vwPartsByFunds where date <= 20/2/2005
Funds, Parts
1, 31/1/2005, 80
2, 31/1/2005, 10
1, 20/2/2005, 110
2, 20/2/2005, 20

So how can I tweak that view so I can display the tblRetros data where as any date below 31/1 should say display the 31/1 value; any date > 31/1 and <= 20/2 should display the 20/2 value.

Thanks !

Here is the table's definition:

CREATE TABLE [dbo].[tblOperations] (
[IDOperation] [int] IDENTITY (1, 1) NOT NULL ,
[DateOperation] [datetime] NULL ,
[DateLiberation] [datetime] NULL ,
[DateApplicationNAV] [datetime] NULL ,
[IDEmploye] [int] NULL ,
[IDTitre] [int] NULL ,
[IDTypeOperation] [smallint] NULL ,
[IDSouscripteur] [smallint] NULL ,
[IDReference] [smallint] NULL ,
[IDDepositaire] [smallint] NULL ,
[IDTypeMouvement] [smallint] NULL ,
[MontantOperation] [float] NULL ,
[CoursOperation] [float] NULL ,
[NbPartOperation] [float] NULL ,
[IDStatus] [smallint] NULL ,
[Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Periode] [int] NULL ,
[Mail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRetros] (
[Ret_Titre] [int] NOT NULL ,
[Ret_Periode] [datetime] NOT NULL ,
[Ret_Montant] [float] NOT NULL ,
[Ret_ts] [timestamp] NOT NULL
) ON [PRIMARY]
GO

jean-luc
www.corobori.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources