J'ai besoin d'aide pour calculer le montant en fonction de la période. En outre, il faut faire un calcul pour tout le mois. Il s'agit d'un calcul de transaction de quart basé sur la date d'arrivée.
Par exemple: L'équipe du 01/05/2019 commence à 8:00:00 et se termine le 02/05/2019 07:59:59 Ceci est mon sql
------------------------- | Date | Amount | ------------------------- | 2019-05-01 | 579.00 | | 2019-05-02 | 891.00 | | 2019-05-03 | 721.00 | | .......... | ..... | | .......... | ..... | -------------------------
Ce qui suit est la sortie:
------------------------- | Date | Amount | ------------------------- | 2019-05-01 | 579.00 | -------------------------
Ce SQL est déjà correct .. Mais comment peut Je fais la sortie comme suit:
------------------------- | Date | Amount | ------------------------- | 2019-05-01 | 123.00 | | 2019-05-02 | 456.00 | -------------------------
Et à partir de ce qui précède .. je dois générer un récapitulatif mensuel des équipes par date et sortie comme suit:
Select CONVERT(char(10), IsNull(CheckInDate, DateCheckIn), 120) Date, SUM(P.Amount - B.TourismTaxAmount) Amount From Bookings B Inner Join BookingPayments BP On B.ID = BP.BookingID Inner Join Payments P On P.ID = BP.PaymentID Where BookingStatus IN (2,3,4,6) and IsNull(CheckInDate, DateCheckIn) >= '2019-05-01 08:00:00' and IsNull(CheckInDate, DateCheckIn) <= '2019-05-02 07:59:59' group by CONVERT(char(10), IsNull(CheckInDate, DateCheckIn), 120) order by CONVERT(char(10), IsNull(CheckInDate, DateCheckIn), 120)
Nous apprécions votre aide et vos solutions. Merci
3 Réponses :
utiliser la fonction de fenêtre sum () avec ordre par date
Select CONVERT(char(10), IsNull(CheckInDate, DateCheckIn), 120) Date, SUM(P.Amount - B.TourismTaxAmount) over(order by CONVERT(char(10), IsNull(CheckInDate, DateCheckIn), 120)) Amount From Bookings B Inner Join BookingPayments BP On B.ID = BP.BookingID Inner Join Payments P On P.ID = BP.PaymentID Where BookingStatus IN (2,3,4,6) and IsNull(CheckInDate, DateCheckIn) >= '2019-05-01 08:00:00' and IsNull(CheckInDate, DateCheckIn) <= '2019-05-02 07:59:59' order by CONVERT(char(10), IsNull(CheckInDate, DateCheckIn), 120)
Il y a 7 transactions en 1/5/2019 de 08:00:00 à 23: 5959 et 1 transaction le 2/5/2019 de 00:00:00 à 07:59:59 .. Elle se répète 7 fois pour 1 / Montant 5/2019 123,00 et la dernière ligne affiche le montant 5/5/2019 579,00. Presque là mais toujours pas correct.
@Julie pourriez-vous créer votre table avec des données ici dans dbfiddle.uk afin que je puisse vérifier avec les données appropriées
voici les exemples de données: dbfiddle.uk/…
FYI .. CheckInDate est la date d'arrivée prévue et DateCheckIn est la date d'arrivée réelle
@Julie dbfiddle.uk/… vérifier ce lien a-t-il résolu votre problème
ce n'est pas correct .. le résultat attendu est (05/01/2019, 72,00) et (05/02/2019, 38,00). La plage horaire requise est de 08:00:00 à 07:59:59 le jour suivant
Pour Sql-Server, je créerais d'abord un SQL, qui amène la colonne de date au bon format avec
Select Year(myDate)*10000+Month(myDate)*100+Day(myDate) as MyNewDate ...
Ensuite, il est plus facile de grouper dessus, si vous utilisez la requête à l'intérieur un CTE via WITH.
peut aider à expliquer? .. Je ne suis pas habitué à créer des sql complexes ..>. <
Voici un simple SELECT d'un rapport de table (mes données de démonstration) à la colonne créée (ma DateColumn): WITH MyDateSelect (MyDate) AS (SELECT 10000 * YEAR (r.Created) + 100 * MONTH (r.Created) ) + DAY (r.Created) AS MyDate FROM Report r) SELECT COUNT (*), MyDate FROM MyDateSelect GROUP BY MyDate
Au fait ... la fonction Fenêtre de l'affichage ci-dessous est plus puissante. Mais à mon avis, la solution basée sur CTE aide à obtenir une meilleure solution étape par étape.
Il suffit de soustraire 8 heures avant de procéder à l'agrégation. Vous pouvez le faire plus facilement en définissant une variable (enfin, une colonne) avec l'expression de date souhaitée, ce qui donne:
Select convert(char(10), v.dte, 120) as Date,
sum(P.Amount - B.TourismTaxAmount) as Amount
From Bookings B Inner Join
BookingPayments BP
On B.ID = BP.BookingID Inner Join
Payments P
On P.ID = BP.PaymentID cross apply
(values (convert(date, dateadd(hour, -8, coalesce(CheckInDate, DateCheckIn))))
) v(dte)
Where BookingStatus in (2, 3, 4, 6) and
v.dte >= '2019-05-01' and
v.dte < '2019-05-02'
group by v.dte
order by v.dte;
Cela s'étendra également facilement à plusieurs dates ou mois. p>
Merci !!!!! .. maintenant je peux sourire .. hahaha .. merci beaucoup .. cela fonctionne parfaitement pour moi ..
Pourriez-vous fournir des exemples de données?
@Julie. . . Sur la base de la syntaxe, j'ai ajouté une balise SQL Server.
Votre question, telle qu'elle est présentée, suggère (à mon avis) un problème plus fondamental qui rendrait toute requête très sensible aux erreurs. Il n'y a aucun élément dans le code / les données affichés qui pourrait être utilisé comme une seule identification d'entité, quelque chose comme
Shift_ID. Imaginez que vous ayez une erreur dans vos données (la cause n'est pas pertinente ici) de telle sorte que vous ayez un check-in, un autre check-in, puis un check-out. Comment votre requête se comporterait-elle? (bien sûr, ce n'est qu'un exemple très simple et vous pouvez penser à beaucoup d'autres, bien plus complexes que celui-ci).@FDavidov Merci pour votre commentaire. Il y a un identifiant de quart de travail ... mais je ne peux pas l'utiliser car l'identifiant de quart de travail n'est pas interrompu par plage horaire de 08h00 à 07h59 le jour suivant. Il y a 3 équipes et les équipes sont flexibles. Rapports pour les transactions par équipes assez faciles à générer car aucun délai n'est nécessaire. Mais cette requête vise à générer un rapport pour les finances car l'heure limite du lot est de 8h00 à 8h00 le lendemain ... je suis vraiment coincé pour le moment.
@Julie, je comprends. Et pourtant, mes propos sur la faiblesse de la solution restent solides de mon point de vue. En tout cas, je vois que le message de Gordon répond à votre question. Bonne chance quand même.
@FDavidov Merci .. vous avez aidé aussi .. vous me donnez quelques idées pour réfléchir .. merci beaucoup :)