Requête SQL de comparer les ventes de produits par mois
J'ai un Statut Mensuel vue de base de données j'ai besoin de créer un rapport basé sur. Les données de la vue ressemble à quelque chose comme ceci:
Category | Revenue | Yearh | Month
Bikes 10 000 2008 1
Bikes 12 000 2008 2
Bikes 12 000 2008 3
Bikes 15 000 2008 1
Bikes 11 000 2007 2
Bikes 11 500 2007 3
Bikes 15 400 2007 4
... Et ainsi de suite
Le point de vue a une catégorie de produits, des recettes, un an et un mois. Je veux créer un rapport de comparaison de 2007 et de 2008, montrant la valeur 0 pour le mois, avec aucune vente. Ainsi, le rapport devrait ressembler à quelque chose comme ceci:
Category | Month | Rev. This Year | Rev. Last Year
Bikes 1 10 000 0
Bikes 2 12 000 11 000
Bikes 3 12 000 11 500
Bikes 4 0 15 400
La clé de chose à remarquer, c'est le mois 1 seule un chiffre d'affaires en 2008, et est donc 0 pour 2007. Aussi, 4 mois seulement n'a pas de chiffre d'affaires en 2008, d'où le 0, alors qu'il a chiffre d'affaires en 2007 et encore.
Aussi, le rapport est en réalité financière de l'année donc j'aimerais avoir des colonnes vides avec des 0 dans les deux cas il n'y a pas de ventes à dire 5 mois, soit en 2007 ou en 2008.
La requête que j'ai obtenu ressemble à quelque chose comme ceci:
SELECT
SP1.Program,
SP1.Year,
SP1.Month,
SP1.TotalRevenue,
IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue
FROM PVMonthlyStatusReport AS SP1
LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON
SP1.Program = SP2.Program AND
SP2.Year = SP1.Year - 1 AND
SP1.Month = SP2.Month
WHERE
SP1.Program = 'Bikes' AND
SP1.Category = @Category AND
(SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND
((SP1.Year = @FinancialYear AND SP1.Month > 6) OR
(SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))
ORDER BY SP1.Year, SP1.Month
Le problème avec cette requête, c'est qu'il ne serait pas de retour de la quatrième rangée dans mon exemple, les données ci-dessus, puisque nous n'avons pas de chiffre d'affaires en 2008, mais nous avons fait en 2007.
C'est probablement une commune de la requête/problème, mais mon SQL est rouillé, après avoir fait front-end développement depuis si longtemps. Toute aide est grandement appréciée!
Oh, btw, je suis à l'aide de SQL 2005 pour cette question, donc si il y a tout utile de nouvelles fonctionnalités qui pourraient m'aider à me le faire savoir.
OriginalL'auteur Jonas Follesø | 2008-08-19
Vous devez vous connecter pour publier un commentaire.
Le Cas Énoncé est mon meilleur sql ami. Vous avez également besoin d'une table pour générer votre 0 rev dans les deux mois.
Hypothèses sont basées sur la disponibilité des tables suivantes:
et
Exemple 1 sans les lignes vides:
RETOURNE:
Exemple 2 avec des lignes vides:
Je vais utiliser une sous-requête (mais d'autres ne peuvent pas) et sera de retour une ligne vide pour chaque produit et année mois combo.
RETOURNE:
Noter que la plupart des outils de reporting fera ce tableau croisé ou de la matrice de la fonctionnalité, et maintenant que j'y pense SQL Server 2005, a pour pivot la syntaxe qui va le faire ainsi.
Voici quelques ressources supplémentaires.
CAS
http://www.4guysfromrolla.com/webtech/102704-1.shtml
SQL SERVER 2005 PIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx
OriginalL'auteur jason saldo
@Christian -- markdown éditeur-POUAH, surtout lors de la prévisualisation et la version finale de votre post en désaccord...
@Christian -- jointure externe complète -- la jointure externe complète est contredit par le fait qu'il y a des références à SP1 dans la clause where la clause where est appliqué après le REJOINDRE. Pour faire une jointure externe complète avec le filtrage sur l'une des tables, vous devez mettre votre clause where dans une sous-requête, de sorte que le filtrage se passe avant le rejoindre, ou essayer de construire l'ensemble de vos critères sur la JOINTURE SUR la clause, qui est incroyablement laid. Eh bien, il n'y a effectivement pas de jolie façon de faire de celui-ci.
@Jonas: compte tenu de ce:
et le fait que ce travail ne peut être fait avec une jolie requête, je voudrais certainement essayer d'obtenir les résultats que vous voulez vraiment. Aucun point en ayant une vilaine requête et pas même obtenir les données exactes que vous voulez vraiment. 😉
Donc, je vous suggère de le faire en 5 étapes:
1. créer une table temporaire dans le format que vous voulez que vos résultats correspondent
2. remplir avec douze lignes, avec 1 à 12 dans la colonne du mois
3. mise à jour de la "Cette Année" dans la colonne à l'aide de votre SP1 logique
4. mise à jour de la "Dernière Année" de la colonne à l'aide de votre SP2 logique
5. sélectionnez à partir de la table temp
Bien sûr, je suppose que je suis en train de travailler à partir de l'hypothèse que vous pouvez créer une procédure stockée pour accomplir cette tâche. Vous pourriez être techniquement en mesure d'exécuter le lot entier en ligne, mais ce genre de laideur est très rarement vu. Si vous ne pouvez pas faire un SP, je vous suggère de revenir à la jointure externe complète via la sous-requête, mais vous n'obtiendrez pas une ligne lorsqu'un mois avait pas de ventes, soit l'année.
OriginalL'auteur Kevin Crumley
Sur le markdown - Oui, c'est frustrant. L'éditeur a aperçu mon tableau HTML, mais après la publication, il avait disparu - avait Donc de supprimer tout le code HTML mise en forme du post...
@kcrumley je pense que nous sommes parvenus à des conclusions similaires. Cette requête facilement s'laid. J'ai effectivement résolu ce problème avant de lire votre réponse, à l'aide d'un semblable (mais encore différentes de l'approche). J'ai accès à créer des procédures stockées et des fonctions sur la base de données de rapports. J'ai créé une fonction à valeur de Table d'accepter un produit de la catégorie et un exercice en tant que paramètre. Basé sur que la fonction va remplir un tableau contenant les 12 rangs. Les lignes seront remplis avec les données de la vue si les ventes de la disposition, si ce n'est la ligne aura valeurs à 0.
Je puis joindre les deux tables retournées par les fonctions. Car je sais que toutes les tables ont douze vagabonde, il est plus facile d'attribuer, et je peux me joindre sur la Catégorie de Produit et par Mois:
Je pense que votre approche est probablement un peu plus propre que le GetFinancialYear fonction est tout à fait désordre! Mais au moins cela fonctionne - ce qui me rend heureux pour l'instant 😉
OriginalL'auteur Jonas Follesø
L'astuce est de faire une JOINTURE avec la ISNULL pour obtenir les colonnes jointes à partir d'un tableau. J'ai l'habitude de terminer dans une vue ou une table dérivée, sinon vous devez utiliser la fonction ISNULL dans la clause where.
Qui devrait vous obtenir vos exigences minimales - lignes avec un chiffre d'affaires soit en 2007 ou en 2008, ou les deux. Pour obtenir les lignes sans les ventes dans l'année, vous avez juste besoin de JOINTURE INTERNE à une 1-12 tableau de nombres (vous ne l'un de ceux, n'est-ce pas?).
OriginalL'auteur Mark Brackett
J'ai peut-être tort, mais ne devriez-vous pas être à l'aide d'une jointure externe complète au lieu d'une jointure gauche? De cette façon, vous obtiendrez le "vide", les colonnes des deux tables.
http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join
OriginalL'auteur Christian Hagelid
À l'aide du pivot et du Sql Dynamique, nous pouvons parvenir à ce résultat
Résultat
OriginalL'auteur