MS SQL Server
  1. MS SQL Server
  3. Création d'une requête pour agréger les totaux 3 mois et 6 mois
Intereting Posts
Comment SQL Server détermine le nombre de numbers significatifs lors de l'arrondi Requête SQL "transformer" Script pour tuer toutes les connections à une database (Plus de RESTRICTED_USER ROLLBACK) Création d'éléments et de valeurs XML à partir de données de table Comment concaténer plusieurs phrases de création de table afin que le script exécute tout avec une command d'exécution? Comment convertir MSSQL en MySQL date en PHP Valeur de colonne unique par key primaire Comparer et mettre à jour deux caractères dans une string Comment joindre la table principale à la table de plusieurs détails avec des rangées de cellules supplémentaires remplies avec NULL Après avoir copié des loggings d'une colonne Char vers une colonne Varchar, je ne parviens pas à find la ligne utilisant l'instruction like dans SQL Server 2014 mais fin 2003 Existe-t-il un événement dans la bibliothèque d'events permettant d'identifier l'index fragmenté à l'aide d'events étendus? Limitation de l'hémisphère SQL Server 2008 R2 Une méthode qui renvoie la différence entre deux dates Pourquoi en-dash (-) triggers-t-il une erreur de caractère XML illégale (C # / SSMS)? Erreur de return de plusieurs jointures SQL 'Syntaxe incorrecte près du mot key' JOIN '.'

Création d'une requête pour agréger les totaux 3 mois et 6 mois

J'utilise SQL Server 2008 R2. J'essaie de créer une requête qui agrège les totaux par totaux 3M, 6M, 9M, 12M. Voici quelques exemples de données: 

create table invoice (store int, invoice_date datetime, customer int, is_repeat varchar(1)); insert into invoice (store, invoice_date, customer, is_repeat) values (1, '2015-10-05', 1, 'N'), (1, '2016-03-04', 1, 'Y'), (1, '2016-02-07', 1, 'Y'), (1, '2015-08-03', 2, 'Y'), (2, '2015-12-01', 3, 'Y'), (2, '2016-02-16', 4, 'Y'), (2, '2015-06-11', 3, 'Y'), (2, '2015-09-18', 4, 'Y');

La requête doit donner un nombre de 3M, 6M, etc., et l'agréger par magasin, mois et année. Par exemple, il devrait y avoir un logging pour le magasin 1 pour mars 2016 qui totalise le nombre de valeurs 'Y' entre le 01/12/2015 et le 29/02/2016 et place cette valeur dans la colonne 3M. Ensuite, additionnez les valeurs entre le 01/09/2015 et le 29/02/2016 et placez ces valeurs dans la colonne 6M.

Voici un exemple de ce que les résultats de la requête doivent être pour datatables ci-dessus: 

 STORE MONTH YEAR 3M_REPEATS 6M_REPEATS ----- ----- ---- ---------- ---------- 1 2 2016 0 2 1 3 2016 1 2 2 2 2016 1 3 2 3 2016 2 3

J'essaie de find une solution élégante mais je ne suis pas sûr si je devrais utiliser un CTE, ou PIVOT, ou OVER (PARTITION BY), ou juste des clauses GROUP BY standard.

EDIT TO ADD: J'ai finalement besoin des 12 derniers mois dans ce tableau, donc il n'y aurait que 12 loggings dans ce tableau. Par exemple, si le mois / année actuel est 3/2016, il aurait 2/2015 – 3/2016 et les totaux pour chaque mois.

sumr les loggings qui tombent chaque sortingmestre en utilisant une déclaration de cas pour get le sortingmestre pour chaque logging, puis utiliser un groupe de 

 select store, datepart(m, invoice_date) as mth, datepart(yyyy, invoice_date) as yr, Sum(case when datename(qq,invoice_date)=1 then 1 else 0 end) as QTR1, Sum(case when datename(qq,invoice_date)=2 6 then 1 else 0 end) as QTR2 FROM test WHERE is_repeat='Y' GROUP BY store, datepart(m, t.invoice_date), datepart(yyyy, t.invoice_date) ORDER BY store, mth, yr

Je voudrais des expressions SUM CASE pour les agrégations et GROUP BY Store, Mois, Année.

Voici une version qui utilise des fonctions analytiques: 

 with agg as ( select store, min(invoice_date) as invoice_month, count(case when is_repeat = 'Y' then 1 end) as y_count from invoice where invoice_date >= dateadd(month, -14, current_timestamp) and datediff(month, invoice_date, current_timestamp) between 1 and 12 group by store, datediff(month, invoice_date, current_timestamp) ) select store, datepart(month, a1.invoice_month) as "month", datepart(year, a1.invoice_month) as "year", sum(y_count) over ( partition by store order by invoice_month rows between 2 preceding and current row ) as 3m_repeats, sum(y_count) over ( partition by store order by invoice_month rows between 5 preceding and current row ) as 6m_repeats from agg

Mais puisque vous ne pouvez pas utiliser les rows between 2008, je pense que cela fonctionne comme un substitut: 

 with agg as ( select store, min(invoice_date) as invoice_month, count(case when is_repeat = 'Y' then 1 end) as y_count from invoice where invoice_date >= dateadd(month, -14, current_timestamp) and datediff(month, invoice_date, current_timestamp) between 1 and 12 group by store, datediff(month, invoice_date, current_timestamp) ) select store, datepart(month, a1.invoice_month) as "month", datepart(year, a1.invoice_month) as "year", sum(case when datediff(month, a1.invoice_date, a2.invoice_date) between -2 and 0 then y_count end ) as 3m_repeats, sum(case when datediff(month, a1.invoice_date, a2.invoice_date) between -5 and 0 then y_count end ) as 6m_repeats, from agg a1 inner join agg a2 on a2.store = a1.store and datediff(month, a1.invoice_date, a2.invoice_date) between -5 and 0 group by a1.store, a1.invoice_month

Je suppose que vous avez des données pour tous les mois, donc je n'ai pas pris la peine de cette complication.