Intereting Posts
T-SQL N'EST PAS NULL vs Performances NOT NULL
Entity Framework Temporisations intermittentes sur SaveChanges
CASE dans une clause FROM
Doit déclarer la variable scalaire @Id?
Microsoft Codename "Data Transfer" erreur de colonne d'identité
Pourquoi la présence de la key primaire sur la table améliore-t-elle significativement les performances des index de magasin de colonnes?
Comment find des loggings répétitifs dans un tableau SQL
Comment mettre à jour plusieurs colonnes dans T-SQL à partir de tables multi-keys
Algorithme de sorting GUID SQL Server. Pourquoi?
Convertir la date JJ.MM.AAAA hh: mm: ss en AAAA-MM-JJ dans SSIS
Créer une vue qui supprime plusieurs tranches de données de la table en fonction de différents critères
Procédure d'erreur de procédure stockée SQL Server
Coloration de schéma dynamic basée sur les informations de la database
Existe-t-il un moyen de faire apparaître la spécification d'identité dans le concepteur de table en regard de la colonne Autoriser les valeurs nulles?
Quelle devrait être ma taille de database initiale?
Comment unpivot des colonnes et GROUP BY en fonction des valeurs dans SQL Server?
Je veux calculer des counts PID et VID distincts par année.
Condition:
1. Separate count for column A or B or C or D having value 1 ( A_to_D) 2. Separate count for column E having value 1 (E) 3. Separate count for column F having value 1 (F) 4. Separate count for ALL A to F column are NULL (ALL_NULL) Dans la sortie, je veux une nouvelle colonne Alpha séparée avec les valeurs suivantes:
A_to_D, E, F and ALL_Null
Sortie actuelle de la table:
- Classement par groupes SQL Server
- Nombre de counts SQL de groupes
- DISTINCT avec PARTITION BY vs. GROUPBY
- Comment puis-je afficher les valeurs des lignes dans le server SQL des colonnes?
- Groupement SQL / Valeur du contrat
PID VID Flag Date ABCDEF 1 A1 0 10/17/2013 NULL NULL NULL NULL NULL NULL 2 A2 1 5/27/2014 1 NULL NULL 1 NULL NULL 3 A3 NULL 2/23/2015 NULL NULL NULL NULL 1 NULL 4 A4 NULL 12/6/2013 NULL 0 NULL NULL NULL NULL 5 A5 NULL 7/14/2016 NULL NULL NULL NULL NULL 1 6 A6 NULL 4/29/2015 NULL 1 1 NULL NULL NULL 7 A7 1 9/30/2016 1 NULL NULL NULL NULL NULL 8 A8 NULL 6/28/2016 NULL NULL NULL NULL NULL NULL 9 A9 1 11/20/2013 NULL NULL NULL NULL NULL NULL 10 A10 2 10/8/2015 NULL 1 NULL NULL NULL NULL
Ici:
select datepart(Year,date) ,Count(distinct PID) ,Count( distinct VID) from table where A is not null or B is Not NUll or C is not null or D is not null or E is not Null or F is not null group by datepart(Year,date)
Production attendue:
Year Count_PID Count_VID Alpha 2013 A_to_D 2013 E 2013 F 2013 2 2 ALL_NULL 2014 1 1 A_to_D 2014 E 2014 F 2014 ALL_NULL 2015 2 2 A_to_D 2015 1 1 E 2015 F 2015 ALL_NULL 2016 2 2 A_to_D 2016 E 2016 1 F 2016 1 ALL_NULL
- Comment puis-je normaliser la capitalisation d'une colonne groupée par colonne?
- get "Aucune colonne n'a été spécifiée pour la colonne 2 de 'd'" dans SQL Server cte?
- Groupe SQL par jour, avec nombre
J'ai choisi d'utiliser une common table expression (cte) pour contenir les counts de base qui sont formés en utilisant des expressions de cas pour chacune des 8 conditions différentes (4 pour les PID et 4 pour les VID). Le cte est ensuite utilisé comme source de la dimension année nécessaire pour le résultat final qui est joint à la list des 4 labels alpha. Ensuite, le cte est à nouveau utilisé (deux fois) – sans pivot – pour permettre à gauche de joindre les counts dans la structure de rangée finale demandée. Les valeurs nulles dans les résultats sont délibérées, mais peuvent être remplacées par une string vide si nécessaire en utilisant coalesce() ou isnull() dans la clause select finale. Note Je préfère "unpivoter" en utilisant cross apply et des values car cela permet une disposition quasi-WYSIWYG des lignes qui sont produites au less aussi efficacement que la command unpivot (ref ci-dessous).
Démo à: SQL Fiddle
CREATE TABLE Table1 ([PID] int, [VID] varchar(3), [Flag] varchar(4), [Date] datetime, [A] varchar(4), [B] varchar(4), [C] varchar(4), [D] varchar(4), [E] varchar(4), [F] varchar(4)) ; INSERT INTO Table1 ([PID], [VID], [Flag], [Date], [A], [B], [C], [D], [E], [F]) VALUES (1, 'A1', '0', '2013-10-17 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (2, 'A2', '1', '2014-05-27 00:00:00', '1', NULL, NULL, '1', NULL, NULL), (3, 'A3', NULL, '2015-02-23 00:00:00', NULL, NULL, NULL, NULL, '1', NULL), (4, 'A4', NULL, '2013-12-06 00:00:00', NULL, '0', NULL, NULL, NULL, NULL), (5, 'A5', NULL, '2016-07-14 00:00:00', NULL, NULL, NULL, NULL, NULL, '1'), (6, 'A6', NULL, '2015-04-29 00:00:00', NULL, '1', '1', NULL, NULL, NULL), (7, 'A7', '1', '2016-09-30 00:00:00', '1', NULL, NULL, NULL, NULL, NULL), (8, 'A8', NULL, '2016-06-28 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (9, 'A9', '1', '2013-11-20 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (10, 'A10', '2', '2015-10-08 00:00:00', NULL, '1', NULL, NULL, NULL, NULL) ;
Requête proposée :
/* common table expression used so the results may be reused */ with cte as ( select year([date]) [Year] , count(distinct pA_to_D) pA_to_D , count(distinct pE) pE , count(distinct pF) pF , count(distinct pALL_NULL) pALL_NULL , count(distinct vA_to_D) vA_to_D , count(distinct vE) vE , count(distinct vF) vF , count(distinct vALL_NULL) vALL_NULL from ( select pid, vid, flag, [date] , case when a = 1 or b = 1 or c = 1 or d = 1 then pid end pA_to_D , case when E = 1 then pid end pE , case when F = 1 then pid end pF , case when coalesce(a,b,c,d,e,f) IS NULL then pid end pALL_NULL , case when flag is not null and a = 1 or b = 1 or c = 1 or d = 1 then vid end vA_to_D , case when flag is not null and E = 1 then vid end vE , case when flag is not null and F = 1 then vid end vF , case when flag is not null and coalesce(a,b,c,d,e,f) IS NULL then vid end vALL_NULL from Table1 ) t group by year([date]) ) select y.[Year], p.count_pid, v.count_vid, a.alpha from (select distinct [Year] from cte) y cross join ( select 'A_to_D' as Alpha union all select 'E' union all select 'F' union all select 'ALL_NULL' ) a left join ( select cte.Year, ca.alpha, ca.count_pid from cte cross apply ( values ('A_to_D' ,pA_to_D) , ('E' ,pE) , ('F' ,pF) , ('ALL_NULL',pALL_NULL) ) ca (alpha, count_pid) where ca.count_pid > 0 ) p on y.[Year] = p.[Year] and a.alpha = p.alpha left join ( select cte.Year, ca.alpha, ca.count_vid from cte cross apply ( values ('A_to_D' ,vA_to_D) , ('E' ,vE) , ('F' ,vF) , ('ALL_NULL',vALL_NULL) ) ca (alpha, count_vid) where ca.count_vid > 0 ) v on y.[Year] = v.[Year] and a.alpha = v.alpha ;
Résultats :
| Year | count_pid | count_vid | alpha | |------|-----------|-----------|----------| | 2013 | (null) | (null) | A_to_D | | 2013 | (null) | (null) | E | | 2013 | (null) | (null) | F | | 2013 | 2 | 2 | ALL_NULL | | 2014 | 1 | 1 | A_to_D | | 2014 | (null) | (null) | E | | 2014 | (null) | (null) | F | | 2014 | (null) | (null) | ALL_NULL | | 2015 | 2 | 2 | A_to_D | | 2015 | 1 | (null) | E | | 2015 | (null) | (null) | F | | 2015 | (null) | (null) | ALL_NULL | | 2016 | 1 | 1 | A_to_D | | 2016 | (null) | (null) | E | | 2016 | 1 | (null) | F | | 2016 | 1 | (null) | ALL_NULL |
Pour plus de détails sur l'utilisation de CROSS APPLY et VALUES sur UNPIVOT, voir Spotlight on UNPIVOT, Part 1 par Brad Schultz
Intime requête :
Il peut être utile de voir les résultats initiaux pour aider à suivre les actions suivantes. C'est la sous-requête la plus interne dans le cte comme une requête séparée avec les résultats ci-dessous:
/* initial results, prior to unpivot */ select pid, vid, flag, [date] , case when a = 1 or b = 1 or c = 1 or d = 1 then pid end pA_to_D , case when E = 1 then pid end pE , case when F = 1 then pid end pF , case when coalesce(a,b,c,d,e,f) IS NULL then pid end pALL_NULL , case when flag is not null and a = 1 or b = 1 or c = 1 or d = 1 then vid end vA_to_D , case when flag is not null and E = 1 then vid end vE , case when flag is not null and F = 1 then vid end vF , case when flag is not null and coalesce(a,b,c,d,e,f) IS NULL then vid end vALL_NULL from Table1 order by [date] ;
Résultats :
| pid | vid | flag | date | pA_to_D | pE | pF | pALL_NULL | vA_to_D | vE | vF | vALL_NULL | |-----|-----|--------|----------------------|---------|--------|--------|-----------|---------|--------|--------|-----------| | 1 | A1 | 0 | 2013-10-17T00:00:00Z | (null) | (null) | (null) | 1 | (null) | (null) | (null) | A1 | | 9 | A9 | 1 | 2013-11-20T00:00:00Z | (null) | (null) | (null) | 9 | (null) | (null) | (null) | A9 | | 4 | A4 | (null) | 2013-12-06T00:00:00Z | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | | 2 | A2 | 1 | 2014-05-27T00:00:00Z | 2 | (null) | (null) | (null) | A2 | (null) | (null) | (null) | | 3 | A3 | (null) | 2015-02-23T00:00:00Z | (null) | 3 | (null) | (null) | (null) | (null) | (null) | (null) | | 6 | A6 | (null) | 2015-04-29T00:00:00Z | 6 | (null) | (null) | (null) | A6 | (null) | (null) | (null) | | 10 | A10 | 2 | 2015-10-08T00:00:00Z | 10 | (null) | (null) | (null) | A10 | (null) | (null) | (null) | | 8 | A8 | (null) | 2016-06-28T00:00:00Z | (null) | (null) | (null) | 8 | (null) | (null) | (null) | (null) | | 5 | A5 | (null) | 2016-07-14T00:00:00Z | (null) | (null) | 5 | (null) | (null) | (null) | (null) | (null) | | 7 | A7 | 1 | 2016-09-30T00:00:00Z | 7 | (null) | (null) | (null) | A7 | (null) | (null) | (null) |
SELECT Part1Q.YearCol,Part1Q.Count_PID,Part2Q.Count_VID,Part1Q.Col FROM ( SELECT YearCol, SUM(CASE Col WHEN 'A_to_D' THEN CASE WHEN TQ.A>0 OR TQ.B>0 OR TQ.C>0 OR TQ.D>0 THEN 1 ELSE 0 END WHEN 'E' THEN CASE WHEN TQ.E>0 THEN 1 ELSE 0 END WHEN 'F' THEN CASE WHEN TQ.F>0 THEN 1 ELSE 0 END WHEN 'ALL_NULL' THEN CASE WHEN TQ.A IS NULL AND TQ.B IS NULL AND TQ.C IS NULL AND TQ.D IS NULL AND TQ.E IS NULL AND TQ.F IS NULL THEN 1 ELSE 0 END END ) AS Count_PID, AlphaQ.Col FROM ( SELECT DATEPART(YEAR,DataTable.Date) AS YearCol,PID,Flag,Date,A,B,C,D,E,F FROM DataTable GROUP BY DATEPART(YEAR,DataTable.Date),PID,Flag,Date,A,B,C,D,E,F ) AS TQ LEFT JOIN ( SELECT 'A_to_D' AS Col UNION SELECT 'E' AS Col UNION SELECT 'F' AS Col UNION SELECT 'ALL_NULL' AS Col )AlphaQ ON 1=1 GROUP BY YearCol,AlphaQ.Col ) AS Part1Q LEFT JOIN ( SELECT YearCol, SUM(CASE Col WHEN 'A_to_D' THEN CASE WHEN TQ.A>0 OR TQ.B>0 OR TQ.C>0 OR TQ.D>0 THEN 1 ELSE 0 END WHEN 'E' THEN CASE WHEN TQ.E>0 THEN 1 ELSE 0 END WHEN 'F' THEN CASE WHEN TQ.F>0 THEN 1 ELSE 0 END WHEN 'ALL_NULL' THEN CASE WHEN TQ.A IS NULL AND TQ.B IS NULL AND TQ.C IS NULL AND TQ.D IS NULL AND TQ.E IS NULL AND TQ.F IS NULL THEN 1 ELSE 0 END END ) AS Count_VID, AlphaQ.Col FROM ( SELECT DATEPART(YEAR,DataTable.Date) AS YearCol,VID,Flag,Date,A,B,C,D,E,F FROM DataTable GROUP BY DATEPART(YEAR,DataTable.Date),VID,Flag,Date,A,B,C,D,E,F ) AS TQ LEFT JOIN ( SELECT 'A_to_D' AS Col UNION SELECT 'E' AS Col UNION SELECT 'F' AS Col UNION SELECT 'ALL_NULL' AS Col )AlphaQ ON 1=1 GROUP BY YearCol,AlphaQ.Col )AS Part2Q ON Part2Q.YearCol = Part1Q.YearCol AND Part2Q.Col = Part1Q.Col ORDER BY Part1Q.YearCol,Part1Q.Col
J'ai suivi les étapes simples ci-dessous pour arriver au résultat
1) pid_prep – Récupère l'année, le PID et les counts 2) vid_prep – Récupère l'année, VID et les counts 3) pid_pivoted – Fait pivoter les counts pour PID 4) vid_pivoted – Fait pivoter les counts pour VID 5) SELECT – Rejoint pid_pivoted et vid_pivoted pour get le résultat.
WITH pid_prep AS (SELECT DISTINCT [PID], YEAR([Date]) AS Year, CASE WHEN [A] =1 OR [B] = 1 OR [C] = 1 OR [D] = 1 THEN 1 ELSE 0 END AS AToD, CASE WHEN [E] =1 THEN 1 ELSE 0 END AS E, CASE WHEN [F] =1 THEN 1 ELSE 0 END AS F, CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN 1 ELSE 0 END AS AllNull FROM @Table1), vid_prep AS (SELECT DISTINCT [VID], YEAR([Date]) AS Year, CASE WHEN [A] =1 OR [B] = 1 OR [C] = 1 OR [D] = 1 THEN 1 ELSE 0 END AS AToD, CASE WHEN [E] =1 THEN 1 ELSE 0 END AS E, CASE WHEN [F] =1 THEN 1 ELSE 0 END AS F, CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN 1 ELSE 0 END AS AllNull FROM @Table1), pid_pivoted AS (SELECT Year, cols, SUM(counts) AS counts FROM pid_prep UNPIVOT (counts for cols in (AToD, E, F, AllNull)) u GROUP BY Year, cols), vid_pivoted AS (SELECT Year, cols, SUM(counts) AS counts FROM vid_prep UNPIVOT (counts for cols in (AToD, E, F, AllNull)) v GROUP BY Year, cols) SELECT pp.Year, NULLIF(pp.counts, 0) AS PID_counts, NULLIF(vp.counts, 0) AS VID_Counts, pp.cols FROM pid_pivoted pp LEFT JOIN vid_pivoted vp ON (pp.Year = vp.Year AND pp.cols = vp.cols) ORDER BY pp.Year, pp.cols ;
La sortie est comme ci-dessous. Faites-moi savoir si vous voulez quelque chose expliqué.
Pour être honnête, je suis un peu perdu sur le drapeau parce qu'il ne semble pas que vous le traitiez de la même façon d'année en année lorsque vous identifiez votre count DISTINCT de VID. donc si vous pouvez élaborer, je peux facilement vous aider à identifier où l'utiliser comme critères pour filterr le VID de manière appropriée. Mais cela pourrait vous rapprocher. Les étapes de base trouvent toutes les années et alphas créent une jointure croisée et déterminent si la ligne de la table pour cette année répond aux critères si elle les count.
Données de test:
DECLARE @Table AS TABLE (PID INT, VID VARCHAR(3), Flag INT, [Date] DATE, A INT, B INT, C INT, D INT, E INT, F INT) INSERT INTO @Table VALUES (1 ,'A1',0 ,'10/17/2013',NULL,NULL,NULL,NULL,NULL,NULL) ,(2 ,'A2',1 ,'5/27/2014',1 ,NULL,NULL,1 ,NULL,NULL) ,(3 ,'A3',NULL,'2/23/2015',NULL,NULL,NULL,NULL,1 ,NULL) ,(4 ,'A4',NULL,'12/6/2013',NULL,0 ,NULL,NULL,NULL,NULL) ,(5 ,'A5',NULL,'7/14/2016',NULL,NULL,NULL,NULL,NULL,1 ) ,(6 ,'A6',NULL,'4/29/2015',NULL,1 ,1 ,NULL,NULL,NULL) ,(7 ,'A7',1 ,'9/30/2016',1 ,NULL,NULL,NULL,NULL,NULL) ,(8 ,'A8',NULL,'6/28/2016',NULL,NULL,NULL,NULL,NULL,NULL) ,(9 ,'A9',1 ,'11/20/2013',NULL,NULL,NULL,NULL,NULL,NULL) ,(10,'A10',2 ,'10/8/2015',NULL,1 ,NULL,NULL,NULL,NULL)
Question:
;WITH cteYears AS ( SELECT DISTINCT [Year] = YEAR([Date]) FROM @Table ) , cteAlphas AS ( SELECT Alpha FROM (VALUES ('A_to_D'),('E'),('F'),('ALL_NULL')) t(Alpha) ) , cteMeetsCriteria AS ( SELECT y.[Year] ,t.PID ,t.VID ,t.Flag ,a.Alpha ,MeetsCriteria = CASE WHEN a.Alpha = 'A_to_D' AND (tA = 1 OR tB = 1 OR tC = 1 OR tD = 1) THEN 1 WHEN a.Alpha = 'E' AND tE = 1 THEN 1 WHEN a.Alpha = 'F' AND tF = 1 THEN 1 WHEN a.Alpha = 'ALL_NULL' AND COALESCE(tA,tB,tC,tD,tE,tF) IS NULL THEN 1 ELSE 0 END FROM cteYears y CROSS JOIN cteAlphas a LEFT JOIN @Table t ON y.[Year] = YEAR(t.[Date]) ) SELECT [Year] ,Count_PID = NULLIF(COUNT(DISTINCT IIF(MeetsCriteria = 1, PID, NULL)),0) ,Count_VID = NULLIF(COUNT(DISTINCT IIF(MeetsCriteria = 1 AND Flag IS NOT NULL, VID, NULL)),0) ,Alpha FROM cteMeetsCriteria GROUP BY [Year] ,Alpha ORDER BY [Year] ,CASE Alpha WHEN 'A_to_D' THEN 1 WHEN 'E' THEN 2 WHEN 'F' THEN 3 WHEN 'ALL_NULL' THEN 4 ELSE 5 END
notez que si une rangée répond à plus d'un critère, elle sera comptée plus d'une fois dans les résultats!
Lien montrant comment cela fonctionne: http://rextester.com/ITVT9711
S'il est très facile de déplacer le drapeau vers différents endroits pour filterr ou changer le cteMeetsCriteria afin de pouvoir traiter le drapeau différemment en fonction de ce que l'alpha est.
J'utilise vos données d'échantillon. Votre sortie est fausse dans quelques cas. Essayez mon script en utilisant diverses données d'exemple.
create table #tbl(PID int,VID varchar(30),Flag int,Dates Date ,A int,B int,C int,D int,E int,F int,Years as year(dates)) insert into #tbl VALUES (1 ,'A1', 0 ,'10/17/2013', NULL ,NULL ,NULL, NULL,NULL,NULL) ,(2 ,'A2', 1 ,'5/27/2014', 1 ,NULL ,NULL, 1 ,NULL,NULL) ,(3 ,'A3', NULL ,'2/23/2015', NULL ,NULL ,NULL, NULL,1 ,NULL) ,(4 ,'A4', NULL ,'12/6/2013', NULL ,0 ,NULL, NULL,NULL,NULL) ,(5 ,'A5', NULL ,'7/14/2016', NULL ,NULL ,NULL, NULL,NULL,1 ) ,(6 ,'A6', NULL ,'4/29/2015', NULL ,1 ,1 , NULL,NULL,NULL) ,(7 ,'A7', 1 ,'9/30/2016', 1 ,NULL ,NULL, NULL,NULL,NULL) ,(8 ,'A8', NULL ,'6/28/2016', NULL ,NULL ,NULL, NULL,NULL,NULL) ,(9 ,'A9', 1 ,'11/20/2013', NULL ,NULL ,NULL, NULL,NULL,NULL) ,(10 ,'A10', 2 ,'10/8/2015', NULL ,1 ,NULL, NULL,NULL,NULL) -- This is hard coded create table #temp (Alpha varchar(50)) insert into #temp VALUES ('A_to_D'),('E'),('F'),('ALL_NULL') -- you can decide the value of @StartYear and @Maxyear according to some logic -- don't consider it hard coded .It is no big deal declare @StartYear int=2013 declare @Maxyear int =4 create table #year (Years int) insert into #year select TOP(@Maxyear) @StartYear+(ROW_NUMBER()OVER(ORDER BY number)-1) from master..spt_values --select * from #year ; WITH CTE AS ( SELECT * FROM #year ,#temp ) ,CTE1 AS ( SELECT t.Years ,sum(CASE WHEN A = 1 OR B = 1 OR c = 1 OR d = 1 THEN 1 END) PA_to_D ,sum(CASE WHEN (flag IS NOT NULL) AND ( A = 1 OR B = 1 OR c = 1 OR d = 1 ) THEN 1 END) VA_to_D ,sum(CASE WHEN E = 1 THEN 1 END) P_E ,sum(CASE WHEN (flag IS NOT NULL) AND (E = 1) THEN 1 END) V_E ,sum(CASE WHEN F = 1 THEN 1 END) P_F ,sum(CASE WHEN (flag IS NOT NULL) AND (F = 1) THEN 1 END) V_F ,sum(CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN 1 END) P_ALL_NULL ,sum(CASE WHEN (flag IS NOT NULL) AND COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN 1 END) V_ALL_NULL FROM #tbl t where exists(select years from #year Y where Y.years=t.years) GROUP BY t.Years ) SELECT c.years ,c.alpha ,ca.Count_PID ,ca.Count_VID FROM cte c CROSS APPLY ( SELECT CASE WHEN c.years = t.years AND alpha = 'A_to_D' THEN PA_to_D WHEN c.years = t.years AND alpha = 'E' THEN P_E WHEN c.years = t.years AND alpha = 'F' THEN P_F WHEN c.years = t.years AND alpha = 'ALL_NULL' THEN P_ALL_NULL ELSE NULL END Count_PID ,CASE WHEN c.years = t.years AND alpha = 'A_to_D' THEN VA_to_D WHEN c.years = t.years AND alpha = 'E' THEN V_E WHEN c.years = t.years AND alpha = 'F' THEN V_F WHEN c.years = t.years AND alpha = 'ALL_NULL' THEN V_ALL_NULL ELSE NULL END Count_VID FROM CTE1 t WHERE years = c.years ) ca ORDER BY c.Years drop table #tbl drop table #year drop table #temp
Explication
: J'ai utilisé des années comme année (dates) comme colonne calculée (ou peut être colonne permanente) pour profiter de l'index à l'avenir. C'est juste et idée.
Si vous utilisez Year (Dates) au lieu de Years, la sortie sera OK
Vous pouvez atteindre cette sortie en utilisant une simple requête UNION ,
SELECT YEAR([Date]) AS [Year] ,NULLIF(COUNT(CASE WHEN A = 1 OR B = 1 OR C = 1 OR D = 1 THEN [PID] END),0) AS [Count_PID] ,NULLIF(COUNT(CASE WHEN A = 1 OR B = 1 OR C = 1 OR D = 1 THEN [VID] END),0) AS [Count_VID] ,'A_to_D' AS [alpha] ,1 AS [sl_no] FROM @Table1 GROUP BY YEAR([Date]) UNION ALL SELECT YEAR([Date]) ,NULLIF(COUNT(CASE WHEN E = 1 THEN [PID] END),0) ,NULLIF(COUNT(CASE WHEN E = 1 THEN [VID] END),0) ,'E' ,2 FROM @Table1 GROUP BY YEAR([Date]) UNION ALL SELECT YEAR([Date]) ,NULLIF(COUNT(CASE WHEN F = 1 THEN [PID] END),0) ,NULLIF(COUNT(CASE WHEN F = 1 THEN [VID] END),0) ,'F' ,3 FROM @Table1 GROUP BY YEAR([Date]) UNION ALL SELECT YEAR([Date]) ,NULLIF(COUNT(CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN [PID] END),0) ,NULLIF(COUNT(CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN [VID] END),0) ,'ALL_NULL' ,4 FROM @Table1 GROUP BY YEAR([Date]) ORDER BY YEAR([Date]),[sl_no]
Sortie: –
Year Count_PID Count_VID alpha sl_no ------------------------------------------------- 2013 NULL NULL A_to_D 1 2013 NULL NULL E 2 2013 NULL NULL F 3 2013 2 2 ALL_NULL 4 2014 1 1 A_to_D 1 2014 NULL NULL E 2 2014 NULL NULL F 3 2014 NULL NULL ALL_NULL 4 2015 2 2 A_to_D 1 2015 1 1 E 2 2015 NULL NULL F 3 2015 NULL NULL ALL_NULL 4 2016 1 1 A_to_D 1 2016 NULL NULL E 2 2016 1 1 F 3 2016 1 1 ALL_NULL 4
Tester la création de la table (append quelques lignes en double pour prouver que le count DISTINCT fonctionne):
IF OBJECT_ID ('TEMPDB.DBO.#TEST','U') IS NOT NULL DROP TABLE #TEST; CREATE TABLE #Test ([PID] int, [VID] varchar(3), [Flag] varchar(4), [Date] datetime, [A] varchar(4), [B] varchar(4), [C] varchar(4), [D] varchar(4), [E] varchar(4), [F] varchar(4)); INSERT INTO #Test ([PID], [VID], [Flag], [Date], [A], [B], [C], [D], [E], [F]) VALUES (1, 'A1', '0', '2013-10-17 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (1, 'A1', '0', '2013-10-17 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (2, 'A2', '1', '2014-05-27 00:00:00', '1', NULL, NULL, '1', NULL, NULL), (3, 'A3', NULL, '2015-02-23 00:00:00', NULL, NULL, NULL, NULL, '1', NULL), (4, 'A4', NULL, '2013-12-06 00:00:00', NULL, '0', NULL, NULL, NULL, NULL), (5, 'A5', NULL, '2016-07-14 00:00:00', NULL, NULL, NULL, NULL, NULL, '1'), (5, 'A5', NULL, '2016-07-14 00:00:00', NULL, NULL, NULL, NULL, NULL, '1'), (6, 'A6', NULL, '2015-04-29 00:00:00', NULL, '1', '1', NULL, NULL, NULL), (7, 'A7', '1', '2016-09-30 00:00:00', '1', NULL, NULL, NULL, NULL, NULL), (8, 'A8', NULL, '2016-06-28 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (9, 'A9', '1', '2013-11-20 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL), (10, 'A10', '2', '2015-10-08 00:00:00', NULL, '1', NULL, NULL, NULL, NULL)
Code, en s'appuyant sur CROSS APPLY par Alpha et UNION ALL simple pour rassembler les résultats:
;WITH CTE AS ( --ALL NULL SELECT DTE, PID, VID, 'ALL_NULL' Alpha FROM ( SELECT DATEPART(YEAR, DATE) DTE, PID, VID, MAX(VALUE) VAL FROM #Test T1 CROSS APPLY (VALUES (A),(B),(C),(D),(E),(F) ) T2(VALUE) GROUP BY DATEPART(YEAR, DATE), PID, VID ) V WHERE VAL IS NULL UNION ALL --A THRU D SELECT DATEPART(YEAR, DATE) DTE, PID, VID, 'A_to_D' FROM #Test T1 CROSS APPLY (VALUES (A),(B),(C),(D) ) T2(VALUE) WHERE ISNULL(VALUE,0) = 1 --These ISNULL() make 0 equal to NULL, since you only want to count 1 GROUP BY DATEPART(YEAR, DATE), PID, VID UNION ALL --E SELECT DATEPART(YEAR, DATE) DTE, PID, VID, 'E' FROM #Test T1 CROSS APPLY (VALUES (E) ) T2(VALUE) WHERE ISNULL(VALUE,0) = 1 GROUP BY DATEPART(YEAR, DATE), PID, VID UNION ALL --F SELECT DATEPART(YEAR, DATE) DTE, PID, VID, 'F' FROM #Test T1 CROSS APPLY (VALUES (F) ) T2(VALUE) WHERE ISNULL(VALUE,0) = 1 GROUP BY DATEPART(YEAR, DATE), PID, VID ) --Reference table to fill in blanks for output ,REF as (SELECT DISTINCT DATEPART(YEAR, DATE) DTE, Alpha FROM #Test T1 CROSS APPLY (VALUES ('ALL_NULL'),('A_to_D'),('E'),('F') ) V(Alpha) ) --Final select SELECT R.DTE, COUNT(PID), COUNT(VID), R.Alpha FROM REF R LEFT JOIN CTE C ON R.DTE = C.DTE AND R.Alpha = C.Alpha GROUP BY R.DTE, R.Alpha ORDER BY DTE, CASE WHEN R.Alpha = 'ALL_NULL' THEN 1 ELSE 0 END, R.Alpha;
Sortie:
DTE Count_PID Count_VID Alpha 2013 0 0 A_to_D 2013 0 0 E 2013 0 0 F 2013 2 2 ALL_NULL 2014 1 1 A_to_D 2014 0 0 E 2014 0 0 F 2014 0 0 ALL_NULL 2015 2 2 A_to_D 2015 1 1 E 2015 0 0 F 2015 0 0 ALL_NULL 2016 1 1 A_to_D 2016 0 0 E 2016 1 1 F 2016 1 1 ALL_NULL
Je trouve pour des scénarios comme celui-ci qu'il est préférable d'écrire un peu plus de code (sans sacrifier la performance) afin de garder les changements futurs agréables et faciles. En séparant chaque exigence en un simple bloc de code et en les associant, il sera facile d'append ou de supprimer des exigences plus tard.