Sql-запрос для расчета ежемесячного бюджета

CREATE TABLE [dbo].[EmployeeMonthlyBudget]
(
    [Name] [NVARCHAR](50) NULL,
    [Budget_Day] [MONEY] NULL,
    [DateCreated] [DATETIME] NULL,
    [DateDeleted] [DATETIME] NULL
) 

INSERT INTO [dbo].[EmployeeMonthlyBudget] ([Name], [Budget_Day], [DateCreated], [DateDeleted]) 
VALUES (N'SAM', 20.0000, CAST(N'2018-01-01T00:00:00.000' AS DateTime), CAST(N'2018-10-01T00:00:00.000' AS DateTime)),
       (N'ROB', 10.0000, CAST(N'2018-01-01T00:00:00.000' AS DateTime), NULL),
       (N'TAM', 5.0000, CAST(N'2018-01-01T00:00:00.000' AS DateTime), CAST(N'2018-05-01T00:00:00.000' AS DateTime)),
       (N'TAN', 100.0000, CAST(N'2018-01-01T00:00:00.000' AS DateTime), NULL)   

Выше представлена ​​структура таблицы, которую нам потребовалось для расчета ежемесячного бюджета.

Мы отображаем столбчатую диаграмму для каждого имени, как их ежемесячный бюджет. Предположим, что если мы возьмем Сэма, его бюджет на январь - 620, февраль - 580 и т. д. Итак, нам нужно рассчитывать бюджет на каждый месяц, пока DateDeleted не будет иметь значение.

Мы можем рассчитать ежемесячный бюджет для каждого имени сотрудника, но не можем чтобы выяснить, как рассчитать их на месяцы подряд.

Пожалуйста, помогите мне в этом Как написать SQL для описанного выше подхода. Пожалуйста, образец данных таблицы

a busy cat

3
0
667
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Надеюсь это поможет

SELECT A.Name,A.budget_day,B.MonthName,B.totaldays*A.budget_day as MonthlySpent
FROM 
[dbo].[EmployeeMonthlyBudget] A
inner join(
SELECT [Name] , DATENAME(MONTH, DATEADD(MONTH, nos.monthnos, [DateCreated])-1) AS MonthName,DAY(EOMONTH(DATEADD(MONTH, nos.monthnos, [DateCreated])-1)) as totaldays,month(EOMONTH(DATEADD(MONTH, nos.monthnos, [DateCreated])-1)) as monthOrder
from [dbo].[EmployeeMonthlyBudget]
inner join    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos on  nos.monthnos <= DATEDIFF(MONTH, [DateCreated],isnull([DateDeleted],GETDATE()))+1
 ) B on
A.NAME=B.Name 
order by A.Name,monthOrder 

Попробуй это

       select name,budget_day,monthname,totalspent from
(Select j.* ,row_number () over (partition by j.name,j.monthname order by j.totalspent) as rn

from

(Select B.name,A.budget_day,MonthName,(datediff(day,[DateCreated],DefaultDate)+1)*A.budget_day as TotalSpent,monthOrder
from (SELECT 
  *, [Budget_Day]*DATEDIFF(day,[DateCreated],isnull([DateDeleted],GETDATE())) as TotalSpent,  DATENAME(MONTH, [DateCreated]) AS MonthNameStart, DATENAME(MONTH,isnull([DateDeleted],GETDATE())) AS MonthNameEND
from 
   [dbo].[EmployeeMonthlyBudget]
 where datecreated<=isnull(datedeleted,getdate())) A
LEFT join 


(
SELECT [Name] , DATENAME(MONTH, DATEADD(MONTH, nos.monthnos-1, [DateCreated])) AS MonthName,EOMONTH([DateCreated]) as DefaultDate ,
DAY(EOMONTH(DATEADD(MONTH, nos.monthnos, [DateCreated])-1)) as totaldays,
month(DATEADD(MONTH, nos.monthnos-1, [DateCreated])) as monthOrder
from [dbo].[EmployeeMonthlyBudget]
inner join    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos on  nos.monthnos <= DATEDIFF(MONTH, [DateCreated],isnull([DateDeleted],GETDATE()))

)  B
on A.MonthNameStart =B.MonthName and 
A.Name=B.name

UNION

Select B.name,A.budget_day,MonthName,((datediff(day,DefaultDate,isnull([Datedeleted],getdate())))+1)*A.budget_day as TotalSpent,monthOrder from
(SELECT 
  *, [Budget_Day]*DATEDIFF(day,[DateCreated],isnull([DateDeleted],GETDATE())) as TotalSpent,  DATENAME(MONTH, [DateCreated]) AS MonthNameStart, DATENAME(MONTH,isnull([DateDeleted],GETDATE())) AS MonthNameEND
from 
   [dbo].[EmployeeMonthlyBudget]
   where datecreated<=isnull(datedeleted,getdate())) A
   inner join 


(SELECT [Name] , DATENAME(MONTH, DATEADD(MONTH, nos.monthnos, [DateCreated])-1) AS MonthName,DATEADD(month, DATEDIFF(month, 0, isnull([DateDeleted],getdate())), 0) as DefaultDate,
DAY(EOMONTH(DATEADD(MONTH, nos.monthnos, [DateCreated])-1)) as totaldays,
month(EOMONTH(DATEADD(MONTH, nos.monthnos, [DateCreated])-1)) as monthOrder
from [dbo].[EmployeeMonthlyBudget]
inner join    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos on  nos.monthnos <= DATEDIFF(MONTH, [DateCreated],isnull([DateDeleted],GETDATE()))+1)  B
on A.MonthNameEND =B.MonthName and A.Name=B.name

UNION


SELECT B.name,A.budget_day,MonthName,totaldays*A.budget_day as TotalSpent ,monthOrder
FROM 
[dbo].[EmployeeMonthlyBudget] A
inner join(
SELECT [Name] , DATENAME(MONTH, DATEADD(MONTH, nos.monthnos-1, [DateCreated])) AS MonthName,EOMONTH([DateCreated]) as DefaultDate ,
DAY(EOMONTH(DATEADD(MONTH, nos.monthnos-1, [DateCreated]))) as totaldays,
month(DATEADD(MONTH, nos.monthnos-1, [DateCreated])) as monthOrder
from [dbo].[EmployeeMonthlyBudget]
inner join    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos on  nos.monthnos <= DATEDIFF(MONTH, [DateCreated],isnull([DateDeleted],GETDATE()))
 where datecreated<=isnull(datedeleted,getdate())
) B on
A.NAME=B.Name


 )j)k
where k.rn=1 and k.name is not null
order by k.name,k.monthOrder 

Можем ли мы вместо того, чтобы получать итоги сразу, разбить результат на ежемесячные

BMMV 26.10.2018 04:57

Спасибо, Аян, но нужно рассчитывать значения для каждого месяца, а не для всего месяца. Требуется, сколько бюджета расходуется на ежемесячной основе.

BMMV 26.10.2018 05:03

конечно, но нужно добавить дополнительный столбец для месяца в вашу таблицу

Ajan Balakumaran 26.10.2018 05:18

Не могли бы вы направить

BMMV 26.10.2018 05:26

Также я создал скрипку для тестирования dbfiddle.uk/…

BMMV 26.10.2018 05:56

Я внес изменения, просто попробуйте это

Ajan Balakumaran 26.10.2018 05:58

Удалось ли вам разобраться в этом

Ajan Balakumaran 26.10.2018 06:15

Аджан, Это сработало, но была одна проблема, если DateCreated - это «2018-01-15» в середине месяца или в другие дни, он вычисляет для всего месяца с даты начала, а не берет дату с 15, а также пропускает январь. Расчет за 16 дней Пожалуйста, загляните в скрипку ниже я обновил dbfiddle.uk/…

BMMV 26.10.2018 06:23

Я знаю, что это болезненный код, но это подойдет. Этот код можно оптимизировать.

Ajan Balakumaran 26.10.2018 07:26

Спасибо, Аян, dbfiddle.uk/… Эверт выглядит хорошо. Не считая начального месяца. Посмотрите на результат ниже ссылка

BMMV 26.10.2018 08:03

BMMV Вы имеете в виду, что конечный месяц рассчитывается правильно, а начальный - нет?

Ajan Balakumaran 26.10.2018 08:13

Ajan, Все выглядит, кроме месяца февраля, это занимает 31 день * бюджет, а оставшиеся данные за все месяцы выглядят хорошо

BMMV 26.10.2018 10:12

Когда я проверил сейчас, это не так, можете ли вы сгенерировать ответ и поделиться ссылкой, чтобы я мог проверить

Ajan Balakumaran 26.10.2018 10:22

Можешь проверить это dbfiddle.uk/…

Ajan Balakumaran 26.10.2018 10:37

Можете ли вы попробовать код сейчас, я обработал такой сценарий в коде

Ajan Balakumaran 26.10.2018 11:05

Аян, пожалуйста, проверьте ссылку ниже для этого сценария только для первого лица (запись Боба), проблема с февралем возникает, остальное все выглядит хорошо dbfiddle.uk/…

BMMV 26.10.2018 11:14

Только что проверено, записи SAM и TAM заполняются, но пользователи с DateDeleted null не заполняются в результатах.

BMMV 26.10.2018 11:22

да, я изменил эту часть некоторое время назад, теперь все хорошо, я думаю, вы можете скопировать код, который есть сейчас, ура

Ajan Balakumaran 26.10.2018 11:24

Ajan, Немного запутался в обновленном коде SQL, ты имеешь ввиду в скрипке? или выше в этом посте

BMMV 26.10.2018 11:47

Я имею в виду в посте

Ajan Balakumaran 26.10.2018 11:48

Другие вопросы по теме