Discussion :

[figarojuju]

Bonjour à tous,

j'ai un fichier avec des clients qui ont un abonnement avec une date de début (startdate) et une date de fin (enddate)clients.xls.

Sauriez-vous comment faire pour avoir pour chaque année et chaque mois le nombre d'abonnements courant ?

En fait je cherche à calculer pour le mois m de l'année A (Am) combien d'abonnements sont tels que stardate<=Am et Am<endate.
Avec Am pouvant varier du minimum des startdate au maximum des stardate.
J'arrive à le faire dans un cas particulier d'un Am mais je n'arrive pas à généraliser...
Bonne journée

[elsuket]

Bonjour,

De façon brutale et pas performante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT	YEAR(StartDate)
	, MONTH(StartDate)
	, COUNT(*)
FROM	dbo.maTable
WHERE	StartDate >= @date_debut
AND	EndDate < @date_fin
GROUP	BY YEAR(StartDate), MONTH(StartDate)
ORDER	BY YEAR(StartDate), MONTH(StartDate)

Mais si vous souhaitez que la requête s'exécute rapidement, vous avez trois solutions :

- Soit vous créez une table de dates, comme je l'ai montré ici, et vous faites les jointures adéquates sur cette table.
Cela a l'avantage d'être réutilisable.

- Si la table est volumineuse et peu modifiée, faites de la requête une vue indexée : c'est très efficace pour les agrégats.

- Soit vous créez deux colonnes calculées indexées (mais pas persistées), l'une pour le mois, et l'autre pour l'année :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
ALTER TABLE dbo.maTable
ADD annee AS (YEAR(StartDate))
GO
 
ALTER TABLE dbo.maTable
ADD mois AS (MONTH(StartDate))
GO
 
CREATE INDEX IX_maTable__annee__mois
ON dbo.maTable (annee, mois)

De là soit vous simplifiez votre requête que je vous ai donné pour qu'elle spécifie les colonnes calculées, soit vous la conservez telle quelle : dans les deux cas SQL Server utilisera l'index.

@++

[figarojuju]

Merci beaucoup,
j'ai créé ma table de date de la manière suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
CREATE TABLE DateLookup
 (
     DateKey INT PRIMARY KEY,
     Date_Begin DATETIME,
     Date_End Datetime,
 )
 go
 
DECLARE @Date DATETIME
DECLARE @Date2 DATETIME
 SET @Date = (select MIN(startdate) from TblPolicy)
 SET @Date = DATEADD(month, -1, @Date)
 SET @Date2 = (select max(Enddate) from TblPolicy)
 SET @Date2 = DATEADD(month, 1, @Date2)
 
 WHILE @Date < @Date2
    BEGIN
     INSERT INTO DateLookup
     (
         DateKey, Date_Begin, Date_End
     )
     SELECT
         CONVERT(VARCHAR(8), @Date, 112),
         @Date,
         DATEADD(month, 1, @Date)
 
     SET @Date = DATEADD(month, 1, @Date)
 END

ensuite,
ça se complique pour mon unique neurone !
En effet, ma table contrat possède une colonne avec le numéro du contrat, date de début, date de fin, et numéro du contrat précédent.
Ce que j'avais fait dans un premier temps est que je prenais tous les contrats qui avaient une case vide (NULL) dans le numéro du contrat parent. Car je souhaite connaitre uniquement le nombre de nouveaux contrats par mois et exclure les renouvellements.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
select t1.PolicyID,
    t1.PolicyNr,
    year(d1.Date_Begin) As Année,
    month(d1.Date_Begin) as Mois
 from TblPolicy as t1
    inner join datelookup as d1 on
        t1.StartDate<=d1.Date_Begin and t1.Enddate>d1.Date_end
            where t1.ParentPolicyID is null

Or, je me suis aperçu que cette démarche est fausse : lorsque le contrat est renouvelé alors la case contrat parent n'est alors plus vide et le contrat qui doit être considéré comme nouveau lors de sa première occurence n'est plus pris en compte du tout. D'où ma seconde requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
select t1.PolicyID,
    t1.PolicyNr,
    year(d1.Date_Begin) As Année,
    month(d1.Date_Begin) as Mois
 from TblPolicy as t1
    inner join datelookup as d1 on
        t1.StartDate<=d1.Date_Begin and t1.Enddate>d1.Date_end
            left outer join TblPolicy as t2 on t2.PolicyID=t1.ParentPolicyID
                where t1.ParentPolicyID is null or (t2.StartDate)>=d1.date_end
                    order by D1.Date_Begin

qu'en pensez-vous ?