MS SQL Server
  1. MS SQL Server
  3. Utilisation de SQL pour calculer au prorata en fonction des valeurs DateTime
Intereting Posts
Qu'est-ce. NET SsortingngComparer est équivalent à Latin1_General_CI_AS de SQL Rejoindre plusieurs tables (relation plusieurs-à-plusieurs) meilleur moyen de suivre l'heure de création et de mise à jour dans le server SQL Pourquoi la conversion de time renvoie-t-elle une erreur? Auto-jointure et SQL itératif Binaire est craché en tant que valeur hexadécimale Récupération et utilisation de la sortie d'une requête "FOR JSON" dans une variable Comment voir la requête SQL dans le profileur SQL? Sélectionnez des lignes non existantes Erreur SQL : la sous-requête a renvoyé plus de 1 valeur Quand utiliser SELECT … FOR UPDATE? Y at-il une raison de vérifier null à l'intérieur de plusieurs en utilisant clausule dans c #? Quelle est l'alternative pour le nombre (2,0) d'oracle dans sqlserver? Échec de l'abonnement SSRS rsProcessingAborted Projet de database Visual Studio: vérifier si la connection au server SQL existe avant de le créer

Utilisation de SQL pour calculer au prorata en fonction des valeurs DateTime

Le context de ce problème est un système de facturation pour un entrepôt qui stocke des widgets. Les frais de stockage sont basés sur la taille du widget (par exemple $ x * height * length * width). Cette partie est facile. J'utilise le code SQL suivant pour calculer le montant dû et agréger par identifiant d'user. 

Select UserID, Sum((Height * Length * Width * 5) as AmtDue From Widget Where StatusID=2 Group By UserID

5 est une valeur arbitraire pour le moment. Finalement, je vais le passer en paramètre ou l'get à partir d'une table dans la database. Supposons que c'est toujours 5 pour le moment.

Voici la partie avec laquelle je me bats. Les frais sont calculés à la fin du mois et sont calculés au prorata du nombre de jours pendant lesquels le widget a été stocké dans l'entrepôt (par exemple, si le widget se trouve dans l'entrepôt pendant 15 jours sur un mois de 30 jours 50% du montant). Dans ma table Widget, j'ai une colonne pour DateReceived et DateShipped. DateReceived a toujours une valeur datetime. DateShipped est parfois nul (par exemple si le widget est encore en stockage). J'ai des parameters disponibles pour CycleStart et CycleEnd qui représentent le début et la fin du mois de facturation concerné. J'essaie de modifier ma requête SQL pour inclure dans l'expression select un facteur proportionnel égal à (nombre de jours de stockage / nombre de jours dans le mois). En d'autres termes, AmtDue devient (height * length * width * 5 * prorate factor) Comment faire ceci?

Voici un exemple pour illustrer comment le facteur proportionnel est calculé.

Si @CycleStart = 7/1/11 et @CycleEnd = 7/31/11 mm / jj / aa 

 DateReceived | DateShipped | Prorate Factor 6/5/11 Null 100% 6/5/11 7/15/11 48.38% 7/30/11 8/5/11 6.45% 7/15/11 7/25/11 35.48%

Je me bats vraiment avec la nature conditionnelle de ceci et le fait que nous traitons des valeurs de datetime.

Voici. Ce 

 drop table #work go create table #work ( id int not null identity(1,1) primary key clustered , DateReceived datetime not null , DateShipped datetime null , ) go insert #work ( DateReceived , DateShipped ) values ( '06/05/2011' , null ) insert #work ( DateReceived , DateShipped ) values ( '06/05/2011' , '07/15/2011' ) insert #work ( DateReceived , DateShipped ) values ( '07/30/2011' , '08/05/2011' ) insert #work ( DateReceived , DateShipped ) values ( '07/15/2011' , '07/25/2011' ) go declare @CycleStart datetime , @CycleEnd datetime , @DaysInPeriod money set @CycleStart = '07/01/2011' set @CycleEnd = '07/31/2011' set @DaysInPeriod = datediff(day,@CycleStart,dateadd(day,1,@CycleEnd)) select * , DaysInStorage = datediff( day , case when DateReceived < @CycleStart then @CycleStart else DateReceived end , case when DateShipped > @CycleEnd then dateadd(day,1,@CycleEnd) else dateadd(day,1,coalesce(DateShipped,@CycleEnd)) end ) , DaysInPeriod = @DaysInPeriod , ProrateFactor = datediff( day , case when DateReceived < @CycleStart then @CycleStart else DateReceived end , case when DateShipped > @CycleEnd then dateadd(day,1,@CycleEnd) else dateadd(day,1,coalesce(DateShipped,@CycleEnd)) end ) / @DaysInPeriod from #work

Donne le suivant 

 id DateReceived DateShipped DaysInStorage DaysInPeriod ProrateFactor -- ----------------------- ----------------------- ------------- ------------ ------------- 1 2011-06-05 00:00:00.000 NULL 30 31.00 1.00 2 2011-06-05 00:00:00.000 2011-07-15 00:00:00.000 14 31.00 0.4838 3 2011-07-30 00:00:00.000 2011-08-05 00:00:00.000 1 31.00 0.0645 4 2011-07-15 00:00:00.000 2011-07-25 00:00:00.000 10 31.00 0.3548

Essaye ça: 

 SELECT CAST(DATEPART( dd,DateShipped) as decimal)/ CAST(DATEPART(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as decimal) as ProrateFactor

Vous pouvez multiplier par 100 si vous voulez get le pourcentage, mais je suppose que vous voulez la décimale pour des raisons de calcul.

Vous pouvez lancer celui-ci pour voir le facteur du prorata d'aujourd'hui: 

 SELECT CAST(DATEPART( dd,GETDATE()) as decimal)/ CAST(DATEPART(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as decimal)

Pour le rouler avec votre calcul actuel, vous pouvez le faire (avertissement – c'est hideux): 

 Select UserID, Sum((Height * Length * Width * 5 * SELECT CAST(DATEPART( dd,DateShipped) as decimal)/ CAST(DATEPART(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as decimal) ) as AmtDue From Widget Where StatusID=2 Group By UserID