Création d'agrégat avec SQLServer

Version imprimable

[résolu] Création d'agrégat avec SQLServer

Bonjour à tous,

Je travaille sur SQL Server 2000 ....

Je suis confronté à un dilemne assez important, j'ai une table disont d'adhérent et une table regroupant l'historique des différentes adhésion de ces adhérents :
Code:

1 2 3 4 CREATE TABLE ADHERENT (no_adherent varchar(15)) CREATE TABLE HISTO_ADH (no_adherent varchar(15), date_debut datetime,date_fin datetime)
Le n° d'adhérent est clef primaire de la première, clef etrangère ds la seconde alors que la clef primaire de la seconde est n° adherent,date_debut. Voilà pour le cadre résumé de la base de données.

L'objectif est une restitution statistique, qui nécessite la pondération d'un résultat par un calcul extrémement savant à savoir qu'un adhérent compte de manière proportionelle à son nombre de mois de présence dans l'année sur laquelle s'effectue le calcul.
Code:

1 2 3 4 5 6 7 8 9 10 11 INSERT INTO ADHERENT VALUES('111111111111111') INSERT INTO ADHERENT VALUES('2222222222222222') INSERT INTO ADHERENT VALUES('3333333333333333') INSERT INTO ADHERENT VALUES('444444444444444') INSERT INTO HISTO_ADH VALUES('111111111111111','01/01/2000';'09/06/2000') INSERT INTO HISTO_ADH VALUES('111111111111111','01/11/2000';'31/12/2099') INSERT INTO HISTO_ADH VALUES('222222222222222','01/01/2000';'31/12/2099') INSERT INTO HISTO_ADH VALUES('333333333333333','01/01/2000';'01/01/2001') INSERT INTO HISTO_ADH VALUES('444444444444444','01/01/2000';'01/01/2001') INSERT INTO HISTO_ADH VALUES('444444444444444','01/09/2001';'31/12/2099')
Cette donnée sera ssez fréquemment requêtée et par conséquent il me semble opportun de la stockée dans un table que l'on pourrait appeler STATS_ADH, cette table n'existe pas mais sa structure pourrait être (no_adherent,année,valeur_du_calcul_en_question).

Le résultat que j'ai ds la tête ressemblerait à

no_adherent année nb mois presence
111111111111111|2000 |8 (6+2)
111111111111111|2001 |12
111111111111111|2002 |12
222222222222222|2000 |12
222222222222222|2001 |12
222222222222222|2002 |12
333333333333333|2000 |12
444444444444444|2000 |12
444444444444444|2001 |4
444444444444444|2002 |12

Cette solution est-elle la meilleure au niveau de la modélisation?
Comment coder ce calcul d'agrgéat pour qu'il s'éxécute tous les jours, je pensais à un lot DTS avec un script Active X et des curseurs mais cette fonctionnalmités n'a pas cet objectif .....
Je suppose qu'il faut en arriver à une procédure stockée? oui? non? comment faire?

Merci par avance pour toutes information,

Mat[/code][/quote]

19/09/2002, 15h43
SQLpro

A priori créer une table alors que l'info existe est une mauvaise solution.

Obtenir le nombre de mois de présence sur une année n'est pas très difficile si tu as implanté une table de planning telle que décrite dans mon article.
http://sqlpro.developpez.com/Planning/SQL_PLN.html

Dans ce cas la requête devient :

SELECT COUNT(MOIS) as NOMBRE_MOIS, ANNEE, no_adherent
FROM
(SELECT DISTINCT PAN_ID as ANNEE, PMS_ID as MOIS, date_debut, date_fin, no_adherent
FROM HISTO_ADH HAD
JOIN DB_PLN..T_PLN_JOUR_PJR PJR
ON PJR.PJR_DATE BETWEEN HAD.date_debut AND HAD.date_fin) T
GROUP BY ANNEE, no_adherent

Faire attention cependant aux effets de bords et ne compter que les mois entiers (c'est à dire dont le nombre de jours écoulées depuis debut ou avant fin = nombre de jour que contient le mois).

A +
19/09/2002, 15h46
SQLpro

Voici le jeu de données rectifié qu j'ai utilisé et le résultat :

CREATE TABLE ADHERENT (no_adherent varchar(15))

CREATE TABLE HISTO_ADH (no_adherent varchar(15),
date_debut datetime,date_fin datetime)

INSERT INTO ADHERENT VALUES('111111111111111')
INSERT INTO ADHERENT VALUES('222222222222222')
INSERT INTO ADHERENT VALUES('333333333333333')
INSERT INTO ADHERENT VALUES('444444444444444')

SET DATEFORMAT DMY

INSERT INTO HISTO_ADH VALUES('111111111111111','01/01/2000','09/06/2000')
INSERT INTO HISTO_ADH VALUES('111111111111111','01/11/2000','31/12/2010')
INSERT INTO HISTO_ADH VALUES('222222222222222','01/01/2000','31/12/2010')
INSERT INTO HISTO_ADH VALUES('333333333333333','01/01/2000','01/01/2001')
INSERT INTO HISTO_ADH VALUES('444444444444444','01/01/2000','01/01/2001')
INSERT INTO HISTO_ADH VALUES('444444444444444','01/09/2001','31/12/2010')

SELECT no_adherent, ANNEE, COUNT(MOIS) as NOMBRE_MOIS
FROM
(SELECT DISTINCT PAN_ID as ANNEE, PMS_ID as MOIS, date_debut, date_fin, no_adherent
FROM HISTO_ADH HAD
JOIN DB_PLN..T_PLN_JOUR_PJR PJR
ON PJR.PJR_DATE BETWEEN HAD.date_debut AND HAD.date_fin) T
GROUP BY ANNEE, no_adherent
ORDER BY no_adherent, ANNEE

no_adherent ANNEE NOMBRE_MOIS
--------------- ------ -----------
111111111111111 2000 8
111111111111111 2001 12
111111111111111 2002 12
111111111111111 2003 12
111111111111111 2004 12
111111111111111 2005 12
111111111111111 2006 12
111111111111111 2007 12
111111111111111 2008 12
111111111111111 2009 12
111111111111111 2010 12
222222222222222 2000 12
222222222222222 2001 12
222222222222222 2002 12
222222222222222 2003 12
222222222222222 2004 12
222222222222222 2005 12
222222222222222 2006 12
222222222222222 2007 12
222222222222222 2008 12
222222222222222 2009 12
222222222222222 2010 12
333333333333333 2000 12
333333333333333 2001 1
444444444444444 2000 12
444444444444444 2001 5
444444444444444 2002 12
444444444444444 2003 12
444444444444444 2004 12
444444444444444 2005 12
444444444444444 2006 12
444444444444444 2007 12
444444444444444 2008 12
444444444444444 2009 12
444444444444444 2010 12

(35 ligne(s) affectée(s))

A +
19/09/2002, 15h52
SQLpro

Autre problème, je supose que tu as mis 31/12/2099 pour signifier une date non encore valide. C'est une erreur. tu doit laissser cette colonne à NULL et utiliser l'opérateur COALESCE combiné à la date du jour.

CREATE TABLE ADHERENT (no_adherent varchar(15))

CREATE TABLE HISTO_ADH (no_adherent varchar(15),
date_debut datetime,date_fin datetime)

INSERT INTO ADHERENT VALUES('111111111111111')
INSERT INTO ADHERENT VALUES('222222222222222')
INSERT INTO ADHERENT VALUES('333333333333333')
INSERT INTO ADHERENT VALUES('444444444444444')

SET DATEFORMAT DMY

INSERT INTO HISTO_ADH VALUES('111111111111111','01/01/2000','09/06/2000')
INSERT INTO HISTO_ADH VALUES('111111111111111','01/11/2000',NULL)
INSERT INTO HISTO_ADH VALUES('222222222222222','01/01/2000',NULL)
INSERT INTO HISTO_ADH VALUES('333333333333333','01/01/2000','01/01/2001')
INSERT INTO HISTO_ADH VALUES('444444444444444','01/01/2000','01/01/2001')
INSERT INTO HISTO_ADH VALUES('444444444444444','01/09/2001',NULL)

SELECT no_adherent, ANNEE, COUNT(MOIS) as NOMBRE_MOIS
FROM
(SELECT DISTINCT PAN_ID as ANNEE, PMS_ID as MOIS, date_debut, date_fin, no_adherent
FROM HISTO_ADH HAD
JOIN DB_PLN..T_PLN_JOUR_PJR PJR
ON PJR.PJR_DATE BETWEEN HAD.date_debut AND COALESCE(HAD.date_fin, CURRENT_TIMESTAMP)) T
GROUP BY ANNEE, no_adherent
ORDER BY no_adherent, ANNEE

A +
19/09/2002, 16h54
Testav

Je teste et je vais voir ce que tout cela donne ... j'ai des erreurs lors des updates dans la table T_PLN_JOUR_PJR

Merci bcp ...