MS SQL Server
  1. MS SQL Server
  3. Construire une requête qui éclate dépenser par mois. (tournant une rangée en plusieurs rangées)
Intereting Posts
Méthode la plus rapide pour calculer le nombre d'loggings dans la table enfant en fonction d'une condition J'ai une erreur de syntaxe incorrecte Les conditions sont-elles sans puanteur ISNULL? SQL Server: que peut faire une procédure stockée qu'une fonction définie par l'user ne peut pas faire? Insérer dans une requête de table temporaire Le nom de colonne ou le nombre de valeurs fournies ne correspond pas à la définition de la table – Impossible d'identifier la cause racine SQL Server: sous-requête sur une jointure Auto-jointure sur une colonne non-key Génère une requête sql par anorm, avec tous les zéros sauf un Erreur de dépassement arithmétique lors de la conversion d'un nombre en type de données lors de l'exécution de la procédure stockée Instruction DELETE se bloque sur SQL Server sans raison apparente Quel est le type de données de "select 123.866" dans SQL Server 2005? Requête SQL Séparer la date plusieurs colonnes GROUP BY dans une colonne Bonne pratique pour modifier les procédures stockées MSDB?

Construire une requête qui éclate dépenser par mois. (tournant une rangée en plusieurs rangées)

Donc, chaque logging a actuellement une date de début, une date de fin et une mesure de dépenses.

Disons un placement (logging) a commencé le 1/1/2016 et se termine le 31/03/2016 et a une dépense totale de 1000 $. Idéalement, je cherche la requête pour décomposer cet logging en 4 loggings:

  • Janvier: 344,44 $
  • Février: 311,11 $
  • Mars: 344,44 $

En Excel, j'utiliserais normalement une formule similaire à celle du lien ci-joint: http://www.excel-university.com/excel-formula-to-allocate-an-amount-into-monthly-columns/

Ma requête actuelle est la suivante: 

select placement_id, placement_start_date, placement_end_date, ordered_net from agency_views_mbox.digital_placement

NOTE: J'utilise Aginity Workbench pour Redshift et j'ai trouvé que quelques fonctions SQL randoms ne sont pas compatibles dans Aginity.

Toute aide serait grandement appréciée!

Je voudrais créer une table ou une vue qui représente les mois et joindre à la table de dépenses en fonction des dates de début et de fin. Cela devrait vous donner une rangée pour chaque mois que l'logging couvre.

Il y a plusieurs façons de faire cela, dans cet exemple, je vais vous requestr de créer une table de pointage (google it ou StackOverflow) qui contient des entiers 0,1,2,3,4,5,6, … une seule colonne n, vous pouvez ajuster le nombre de mois et la date de début en fonction de vos données.

Remarque J'ai utilisé nextMonthStart, car il est plus facile de soustraire le jour unique de chaque mois, car je peux simplement utiliser <plutôt que <= dans la jointure suivante, je devais seulement soustraire le jour unique à un endroit de cette façon. 

 CREATE VIEW MonthList as SELECT TOP (240) DATEADD(month, n, '2000-01-01') as MonthStart , DATEADD(month, n + 1, '2000-01-01') as NextMonthStart FROM tally

Ensuite, calculez le nombre de jours pendant lesquels l'logging chevauche le mois * les dépenses quotidiennes moyennes.

Pour la tidyness, je vais supposer que votre table est juste appelée placement. Remarque: Je dois lancer votre ordered_net et la durée du placement avant la division, vous devrez peut-être changer le type de données en fonction de votre database et de vos données. 

 select placement_id , placement_start_date , placement_end_date , DailySpend * DATEDIFF(day, PeriodStart, PeriodEnd) as PeriodSpend FROM ( SELECT placement_id , placement_start_date , placement_end_date , CAST(ordered_net as decimal(12,5)) / CAST( DATEDIFF(day, placement_start_date, placement_end_date) as decimal(12.5)) as DailySpend , CASE WHEN placement_start_date > monthStart THEN placement_start_date else monthStart end as PeriodStart , CASE WHEN placement_end_date < nextmonthStart THEN placement_end_date else DATEADD(day, -1, nextmonthStart) end as PeriodEnd from placement inner join monthList on placement_start_date < nextMonthStart and placement_end_date >= monthStart ) as monthlyCalcs

Cela devrait fonctionner dans sql-server, vous devrez peut-être apporter des modifications mineures aux calculs DATEDIFF, la forme de la JOIN et la CAST pour les bases de données alternatives.

EDIT Vous pouvez exposer plus de données dans la requête externe afin que vous vérifiiez que le nombre de jours pour chaque période est correct. Vous pouvez également append la description textuelle à la vue MonthList pour que vous puissiez voir le mois auquel la ligne s'applique également. N'oubliez pas de faire la sum des valeurs pour vérifier que le "pro rate" a correctement divisé les valeurs.