J'ai le tableau suivant, j'essaie de compter le nombre de POC sous chaque évaluation pour chaque client.
SELECT ClientId, ProcDate, ProcDesc
FROM ProcJoins
WHERE ProcDesc] in ('Assessment','POC 20','POC 4b')
GROUP BY ClientId, ProcDate, ProcDesc
ORDER BY ProcedureDate
Je voudrais obtenir le résultat ci-dessous.
ClientId AssessDate Type CountPOC 7180 2018-06-13 Assessment 6 7180 2018-07-04 Assessment 5 7180 2018-09-05 Assessment 5 2584 2018-10-03 Assessment 2 2584 2018-11-04 Assessment 5 7585 2018-11-04 Assessment 5 6581 2018-11-04 CommAssessment 3
Je suis incapable de comprendre comment compter le nombre de POC sous chaque évaluation.
clientId ProcDate ProcDesc 7180 2018-06-13 Assessment 7180 2018-06-13 POC 20 7180 2018-06-13 POC 4b 7180 2018-06-20 POC 20 7180 2018-06-20 POC 4b 7180 2018-06-27 POC 20 7180 2018-06-27 POC 4b 7180 2018-07-04 Assessment 7180 2018-07-04 POC 20 7180 2018-07-04 POC 4b 7180 2018-07-11 POC 20 7180 2018-07-18 POC 20 7180 2018-07-18 POC 4b 7180 2018-09-05 Assessment 7180 2018-09-05 POC 20 7180 2018-09-12 POC 20 7180 2018-09-12 POC 4b 7180 2018-09-19 POC 20 7180 2018-09-19 POC 4b 2584 2018-10-03 Assessment 2584 2018-10-03 POC 20 2584 2018-10-03 POC 4b 2584 2018-11-04 Assessment 2584 2018-11-04 POC 20 2584 2018-11-04 POC 4b 2584 2018-11-11 POC 20 2584 2018-11-18 POC 20 2584 2018-11-18 POC 4b 7585 2018-11-04 Assessment 7585 2018-11-04 POC 20 7585 2018-11-04 POC 4b 7585 2018-11-11 POC 20 7585 2018-11-18 POC 20 7585 2018-11-18 POC 4b 6581 2018-11-04 CommAssessment 6581 2018-11-04 POC 20 6581 2018-11-04 POC 4b 6581 2018-11-11 POC 20
3 Réponses :
Voici 2 options pour obtenir ce dont vous avez besoin.
La première part du principe que chaque client commencera toujours par une évaluation.
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY clientId ORDER BY ProcDate) rn
FROM ProcJoins
WHERE ProcDesc NOT LIKE 'POC%'
)
SELECT c.clientId,
c.ProcDate,
c.ProcDesc,
COUNT(*)
FROM CTE c
LEFT JOIN CTE n ON c.ClientId = n.clientId
AND c.rn = n.rn-1
JOIN ProcJoins p ON c.ClientId = p.clientId
AND c.ProcDate <= p.ProcDate
AND ISNULL(n.ProcDate, '99991231') > p.ProcDate
WHERE p.ProcDesc LIKE 'POC%'
GROUP BY c.clientId,
c.ProcDate,
c.ProcDesc
ORDER BY c.ProcDate;
Le second ne fait qu'interroger en utilisant des plages.
WITH CTE AS(
SELECT *,
LEAD(ProcDate, 1, '99990101') OVER(PARTITION BY clientId ORDER BY ProcDate) EndDate
FROM ProcJoins
WHERE ProcDesc = 'Assessment'
)
SELECT c.clientId,
c.ProcDate,
c.ProcDesc,
COUNT(*)
FROM CTE c
JOIN ProcJoins p ON p.ProcDate >= c.ProcDate
AND p.ProcDate < c.EndDate
AND p.clientId = c.clientId
WHERE p.ProcDesc <> 'Assessment'
GROUP BY c.clientId,
c.ProcDate,
c.ProcDesc
ORDER BY clientId DESC, ProcDate;
Troisième option compatible avec les versions 2005, 2008 et 2008R2
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER( ORDER BY clientId, ProcDate) rn
FROM ProcJoins
)
SELECT clientId,
ProcDate,
ProcDesc,
LEAD(rn, 1, t.totalProc) OVER(ORDER BY clientId, rn) - rn - 1
FROM CTE
CROSS JOIN( SELECT COUNT(*) + 1 AS totalProc FROM CTE) t
WHERE ProcDesc = 'Assessment'
ORDER BY clientId DESC, ProcDate;
p >
Salut, j'obtiens cette erreur: les fonctionnalités de l'entrepôt de données parallèle (PDW) ne sont pas activées. Je pense que la fonction LEAD n'est pas compatible, j'utilise SQL SERVER 2008 R2.
Vous devez informer les gens que vous utilisez une version qui a presque atteint la fin du support. Ces solutions ne fonctionnent qu'à partir des versions 2012+.
La troisième option est très similaire à la seconde en termes de logique, mais en utilisant les outils disponibles dans les versions précédentes.
Luis Cazares, Merci pour l'aide, je voudrais calculer la durée en semaines entre le premier POC et le dernier POC dans chaque groupe d'évaluation et l'afficher dans une colonne dans chaque ligne. Est-ce possible? Merci
Vous les comptez déjà, il vous suffit donc d'obtenir le décalage horaire entre les dates max et min. Selon les besoins, vous souhaiterez peut-être utiliser DATEDIFF avec des semaines ou avec des jours divisés par 7.
Exemple de données:
select ClientId, AssessDate = ProcDate, [Type] = ProcDesc, CountPOC = (
select count(*)
from @Test
where ProcDesc like 'POC%' --- <> 'Assessment'
and ClientId = t.ClientId
and ProcDate >= t.ProcDate
and ProcDate < isNull((
select top 1 ProcDate
from @Test
where ClientId = t.ClientId
and ProcDate > t.ProcDate
and ProcDesc not like 'POC%' -- = 'Assessment'
order by ProcDate
), getdate())
)
from @Test t
where t.ProcDesc not like 'POC%' -- = 'Assessment'
Solution "old school" proposée:
MODIFIÉ : j'avais oublié le "ClientId"
Fiddle
declare @Test table
(ClientId int, ProcDate date,ProcDesc varchar(20))
insert @Test values
(7180,'2018-06-13','Assessment'),
(7180,'2018-06-13','POC 20'),
(7180,'2018-06-13','POC 4b'),
(7180,'2018-06-20','POC 20'),
(7180,'2018-06-20','POC 4b'),
(7180,'2018-06-27','POC 20'),
(7180,'2018-06-27','POC 4b'),
(7180,'2018-07-04','Assessment'),
(7180,'2018-07-04','POC 20'),
(7180,'2018-07-04','POC 4b'),
(7180,'2018-07-11','POC 20'),
(7180,'2018-07-18','POC 20'),
(7180,'2018-07-18','POC 4b'),
(7180,'2018-09-05','Assessment'),
(7180,'2018-09-05','POC 20'),
(7180,'2018-09-12','POC 20'),
(7180,'2018-09-12','POC 4b'),
(7180,'2018-09-19','POC 20'),
(7180,'2018-09-19','POC 4b'),
(2584,'2018-10-03','Assessment'),
(2584,'2018-10-03','POC 20'),
(2584,'2018-10-03','POC 4b'),
(2584,'2018-11-04','Assessment'),
(2584,'2018-11-04','POC 20'),
(2584,'2018-11-04','POC 4b'),
(2584,'2018-11-11','POC 20'),
(2584,'2018-11-18','POC 20'),
(2584,'2018-11-18','POC 4b'),
(7585,'2018-11-04','Assessment'),
(7585,'2018-11-04','POC 20'),
(7585,'2018-11-04','POC 4b'),
(7585,'2018-11-11','POC 20'),
(7585,'2018-11-18','POC 20'),
(7585,'2018-11-18','POC 4b'),
(1585,'2018-11-04','CommAssessment'),
(1585,'2018-11-04','POC 20'),
(1585,'2018-11-04','POC 4b'),
(1585,'2018-11-11','POC 20'),
(1585,'2018-11-18','POC 20'),
(1585,'2018-11-18','POC 4b')
p >
Salut, il y a d'autres clients avec les mêmes dates d'évaluation, et par conséquent, le décompte ne sort pas correctement. J'ai ajouté des lignes supplémentaires à votre instruction d'insertion pour afficher les décomptes. Pouvez-vous s'il vous plaît ajuster votre requête pour résoudre ce problème.
Salut, j'ai un autre ProcType CommAssessment, cela peut-il être pris en compte également? J'ai édité mon message original. Merci.
Vous pouvez maintenant ajouter un nouveau "ProcType", tandis que les descriptions dénombrables commencent par "POC"
Une autre approche possible consiste à définir des groupes et à obtenir le nombre:
Entrée:
ClientId ProcDate ProcDesc (No column name) 2584 2018-10-03 Assessment 2 2584 2018-11-04 Assessment 5 6581 2018-11-04 CommAssessment 3 7180 2018-06-13 Assessment 6 7180 2018-07-04 Assessment 5 7180 2018-09-05 Assessment 5 7585 2018-11-04 Assessment 5
Instruction (si SUM (...) OVER (ORDER BY ...) est pris en charge):
;WITH GroupsCTE AS (
SELECT
d.ClientId, d.ProcDate, d.ProcDesc,
c.[Group]
FROM #Data d
CROSS APPLY(
SELECT
SUM(CASE WHEN ProcDesc IN ('Assessment', 'CommAssessment') THEN 1 ELSE 0 END) AS [Group]
FROM #Data
WHERE (ClientId = d.ClientId) AND (ProcDate <= d.ProcDate)
) c
), CountCTE AS (
SELECT ClientId, [Group], COUNT(*) - 1 AS [Count]
FROM GroupsCTE
GROUP BY ClientId, [Group]
)
SELECT
g.ClientId, g.ProcDate, g.ProcDesc,
c.[Count]
FROM GroupsCTE g
CROSS APPLY (
SELECT ClientId, [Group], COUNT(*) - 1 AS [Count]
FROM GroupsCTE
WHERE (ClientId = g.ClientId) AND ([Group] = g.[Group])
GROUP BY ClientId, [Group]
) c
WHERE g.ProcDesc IN ('Assessment', 'CommAssessment')
ORDER BY g.ClientId, g.ProcDate, CASE WHEN g.ProcDesc IN ('Assessment', 'CommAssessment') THEN 0 ELSE 1 END, g.ProcDesc
Instruction (si SUM (...) OVER (ORDER BY ...) code> n'est pas pris en charge):
;WITH GroupsCTE AS (
SELECT
ClientId, ProcDate, ProcDesc,
SUM(CASE WHEN ProcDesc IN ('Assessment', 'CommAssessment') THEN 1 ELSE 0 END)
OVER (ORDER BY ClientId, ProcDate, CASE WHEN ProcDesc IN ('Assessment', 'CommAssessment') THEN 0 ELSE 1 END) AS Groups
FROM #Data
), CountCTE AS (
SELECT
ClientId, ProcDate, ProcDesc,
COUNT(*) OVER (PARTITION BY Groups) AS [Count]
FROM GroupsCTE
)
SELECT
ClientId, ProcDate, ProcDesc, [Count] - 1
FROM CountCTE
WHERE ProcDesc IN ('Assessment', 'CommAssessment')
ORDER BY ClientId, ProcDate
Sortie:
CREATE TABLE #Data (
ClientId int,
ProcDate date,
ProcDesc varchar(10)
)
INSERT INTO #Data
(ClientId, ProcDate, ProcDesc)
VALUES
(7180, '20180613', 'Assessment'),
(7180, '20180613', 'POC 20'),
(7180, '20180613', 'POC 4b'),
(7180, '20180620', 'POC 20'),
(7180, '20180620', 'POC 4b'),
(7180, '20180627', 'POC 20'),
(7180, '20180627', 'POC 4b'),
(7180, '20180704', 'Assessment'),
(7180, '20180704', 'POC 20'),
(7180, '20180704', 'POC 4b'),
(7180, '20180711', 'POC 20'),
(7180, '20180718', 'POC 20'),
(7180, '20180718', 'POC 4b'),
(7180, '20180905', 'Assessment'),
(7180, '20180905', 'POC 20'),
(7180, '20180912', 'POC 20'),
(7180, '20180912', 'POC 4b'),
(7180, '20180919', 'POC 20'),
(7180, '20180919', 'POC 4b'),
(2584, '20181003', 'Assessment'),
(2584, '20181003', 'POC 20'),
(2584, '20181003', 'POC 4b'),
(2584, '20181104', 'Assessment'),
(2584, '20181104', 'POC 20'),
(2584, '20181104', 'POC 4b'),
(2584, '20181111', 'POC 20'),
(2584, '20181118', 'POC 20'),
(2584, '20181118', 'POC 4b'),
(7585, '20181104', 'Assessment'),
(7585, '20181104', 'POC 20'),
(7585, '20181104', 'POC 4b'),
(7585, '20181111', 'POC 20'),
(7585, '20181118', 'POC 20'),
(7585, '20181118', 'POC 4b'),
(6581, '20181104', 'CommAssessment'),
(6581, '20181104', 'POC 20'),
(6581, '20181104', 'POC 4b'),
(6581, '20181111', 'POC 20')
Salut, j'obtiens cette erreur: les fonctionnalités de l'entrepôt de données parallèle (PDW) ne sont pas activées. J'utilise SQL SERVER 2008 R2.
Après avoir lu sur google, il semble que OVER ... ORDER BY n'est pas pris en charge pour les agrégats dans SQL Server 2008 R2. Par conséquent, je reçois le message d'erreur, une alternative? Merci
@ jk1844 J'ai mis à jour la réponse. Étrange, mais basé sur la documentation, SUM OVER et 'COUNT OVER' devraient être pris en charge à partir de SQL Server 2008.
cela fonctionne bien Merci beaucoup. Je dois également calculer la durée en semaines entre le premier POC et le dernier POC de chaque évaluation dans une colonne de durée.