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 для описанного выше подхода. Пожалуйста, образец данных таблицы
Надеюсь это поможет
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
Спасибо, Аян, но нужно рассчитывать значения для каждого месяца, а не для всего месяца. Требуется, сколько бюджета расходуется на ежемесячной основе.
конечно, но нужно добавить дополнительный столбец для месяца в вашу таблицу
Не могли бы вы направить
Также я создал скрипку для тестирования dbfiddle.uk/…
Я внес изменения, просто попробуйте это
Удалось ли вам разобраться в этом
Аджан, Это сработало, но была одна проблема, если DateCreated - это «2018-01-15» в середине месяца или в другие дни, он вычисляет для всего месяца с даты начала, а не берет дату с 15, а также пропускает январь. Расчет за 16 дней Пожалуйста, загляните в скрипку ниже я обновил dbfiddle.uk/…
Я знаю, что это болезненный код, но это подойдет. Этот код можно оптимизировать.
Спасибо, Аян, dbfiddle.uk/… Эверт выглядит хорошо. Не считая начального месяца. Посмотрите на результат ниже ссылка
BMMV Вы имеете в виду, что конечный месяц рассчитывается правильно, а начальный - нет?
Ajan, Все выглядит, кроме месяца февраля, это занимает 31 день * бюджет, а оставшиеся данные за все месяцы выглядят хорошо
Когда я проверил сейчас, это не так, можете ли вы сгенерировать ответ и поделиться ссылкой, чтобы я мог проверить
Можешь проверить это dbfiddle.uk/…
Можете ли вы попробовать код сейчас, я обработал такой сценарий в коде
Аян, пожалуйста, проверьте ссылку ниже для этого сценария только для первого лица (запись Боба), проблема с февралем возникает, остальное все выглядит хорошо dbfiddle.uk/…
Только что проверено, записи SAM и TAM заполняются, но пользователи с DateDeleted null не заполняются в результатах.
да, я изменил эту часть некоторое время назад, теперь все хорошо, я думаю, вы можете скопировать код, который есть сейчас, ура
Ajan, Немного запутался в обновленном коде SQL, ты имеешь ввиду в скрипке? или выше в этом посте
Я имею в виду в посте
Можем ли мы вместо того, чтобы получать итоги сразу, разбить результат на ежемесячные