Select where one date must include others
-
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, 20So 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]
GOCREATE 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 -
Jean-Luc: Would you submit the join code you're trying?
Nicolas
-
Sounds like you're summing on the unknown date ranges in tblRetros if I read that correctly.
You might try building a self join of tblRetros with itself to get the date ranges first. Something like:
create table MyTable
( rDate datetime
, rVal int
)
go
insert MyTable select '12/31/04', 1
insert MyTable select '1/20/05', 2
insert MyTable select '2/3/05', 3
insert MyTable select '3/31/05', 4
go
select * from mytable
go
select a.rdate 'start'
, b.rdate 'end'
from MyTable a, MyTable b
where a.rDate = ( select max( rdate)
from MyTable c
where c.rdate < b.rdate)
go
drop table MyTable
Then join this back to the other tables to get the sums or rows you need.
-
SET DATEFORMAT 'dmy'
SET NOCOUNT ON
create table #tblOperations(Date Datetime,Funds int,SubScriber int, Parts int)
insert into #tblOperations VALUES('10/1/2005', 1, 100, 10)
insert into #tblOperations VALUES('15/1/2005', 1, 200, 30)
insert into #tblOperations VALUES('20/1/2005', 1, 100, 40)
insert into #tblOperations VALUES('26/1/2005', 2, 100, 10)
insert into #tblOperations VALUES('16/2/2005', 2, 100, 10)
insert into #tblOperations VALUES('15/2/2005', 1, 200, 30)
create table #tblRetros(Date datetime,Funds int, Amount int)
insert into #tblRetros VALUES ('31/1/2005', 1, 15000)
insert into #tblRetros VALUES ('31/1/2005', 2, 5000)
insert into #tblRetros VALUES ('20/2/2005', 1, 20000)
insert into #tblRetros VALUES ('20/2/2005', 2, 7000)
select * from #tblOperations
select * from #tblRetros
SELECT Funds,Date,
( SELECT SUM(Parts)
FROM #tblOperations
WHERE Funds=A.Funds AND Date<=A.Date
GROUP BY Funds
 
FROM #tblRetros A
drop table #tblRetros
drop table #tblOperations
Kindest Regards,Vasc
-
That is working fine. I need to go another step further (I tried !).
When running the select the result should be like this:
Funds, Subscriber, Date, Total Funds, Total Funds for Subscriber
1, 100,2005-01-31 00:00:00.00080, 50
1, 200,2005-01-31 00:00:00.00080, 30
2, 100,2005-01-31 00:00:00.00010, 10
1, 100,2005-02-20 00:00:00.000110, 50
1, 200,2005-02-20 00:00:00.000110, 60
2, 100,2005-02-20 00:00:00.00020, 20
Jean-Luc
www.corobori.com -
SET DATEFORMAT 'dmy'
SET NOCOUNT ON
create table #tblOperations(Date Datetime,Funds int,SubScriber int, Parts int)
insert into #tblOperations VALUES('10/1/2005', 1, 100, 10)
insert into #tblOperations VALUES('15/1/2005', 1, 200, 30)
insert into #tblOperations VALUES('20/1/2005', 1, 100, 40)
insert into #tblOperations VALUES('26/1/2005', 2, 100, 10)
insert into #tblOperations VALUES('16/2/2005', 2, 100, 10)
insert into #tblOperations VALUES('15/2/2005', 1, 200, 30)
create table #tblRetros(Date datetime,Funds int, Amount int)
insert into #tblRetros VALUES ('31/1/2005', 1, 15000)
insert into #tblRetros VALUES ('31/1/2005', 2, 5000)
insert into #tblRetros VALUES ('20/2/2005', 1, 20000)
insert into #tblRetros VALUES ('20/2/2005', 2, 7000)
select * from #tblOperations
select * from #tblRetros
SELECT Funds,Date,
( SELECT SUM(Parts)
FROM #tblOperations
WHERE Funds=A.Funds AND Date<=A.Date
GROUP BY Funds
 
AS TotalFundsFROM #tblRetros A
SELECT Funds,Subscriber, Date,TotalFunds,SUM(Parts) TotalSubscriber
FROM
(SELECT A.Funds, a.Date , CASE WHEN A.Date> B.Date THEN 1 ELSE 0 END GoodRow,SubScriber,Parts, ( SELECT SUM(Parts)
FROM #tblOperations
WHERE Funds=A.Funds AND Date<=A.Date
GROUP BY Funds
 
AS TotalFundsFROM #tblRetros A INNER JOIN #tblOperations B ON A.Funds=B.Funds) AAA
WHERE GoodRow=1
GROUP BY Funds,Date,Subscriber,TotalFunds
ORDER BY Date,Funds
drop table #tblRetros
drop table #tblOperations
Kindest Regards,Vasc
-
I guess this is what I would call the difference between one who knows and one who doesn't !
Jean-Luc
www.corobori.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply