FullyLucky Ответов: 2

Как с помощью SQL создать список дат из таблицы этой структуры?

Смотрите прилагаемое изображение. Это первая публикация, поэтому я не уверен, что изображения разрешены...

изображения[^]

Table Structure				
ID(PK)	UserID	StartDate	EndDate	PricePW($)
157	3	20190102	20190105	56
189	3	20190106	20190109	77
200	3	20190116	20190119	35


Desired Outcome		
Date	PricePerDay($)	Accumulative($)
20190102	8	8
20190103	8	16
20190104	8	24
20190105	8	32
20190106	11	43
20190107	11	54
20190108	11	65
20190109	11	76
20190110	5	81
20190111	5	86
20190112	5	91
20190113	5	96
20190114	5	101
20190115	5	106
20190116	5	111
20190117	5	116
20190118	5	121
20190119	5	126

В принципе, моя структура формата данных находится в таблице а

я могу это сделать.
SELECT * from TableA where USERID = 3


Но что мне действительно нужно, так это создать таблицу с правой стороны (таблица B)

Есть ли какой-нибудь SQL, который я могу использовать для этого?

Что я уже пробовал:

Я немного разбираюсь в SQL, но недостаточно хорош, чтобы создать что-то подобное.

Gerry Schmitz

Я думаю, что ваш образец "вышел из равновесия". Разве тебе это не противно?

Подождите. Я вижу. Разделите недельную норму на 7, чтобы получить дневную норму. Затем создайте "ежедневные" записи за период; с текущим балансом.

https://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server

FullyLucky

что вы имеете в виду? это неправильно связывается?

FullyLucky

да :( как мне генерировать даты-это первая задача.

Вторая проблема заключается в том, как получить цену за день и накопительную

Gerry Schmitz

Цена за день-это цена за неделю / 7.

https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/

FullyLucky

да

CHill60

Кстати, многие из нас заглядывают на этот сайт во время работы. Большинство корпораций блокируют такие сайты, как DropBox. В будущем вложите всю необходимую информацию в текст вашего вопроса, чтобы максимально увеличить количество людей, которые могут предложить свою помощь

FullyLucky

ну ладно, спасибо за подсказку. Я не знал, что это такое. Но я могу понять, как некоторые компании могут захотеть заблокировать определенные веб-сайты. Эта веб-платформа не дает вам возможности загружать фотографии.

CHill60

Да, я знаю, что не могу загружать фотографии, но это очень, очень редкий день, когда изображение требуется для описания проблемы кодирования. В этом случае вы могли бы просто скопировать значения в тело вашего вопроса

2 Ответов

Рейтинг:
20

CHill60

Вот пример данных, которые я использовал для этого 

declare @A table (ID int, UserID int, StartDate date, EndDate date, PricePW numeric(15,2))
insert into @A values
(157,3,'02-JAN-2019','05-JAN-2019', 56),
(189,3,'06-JAN-2019','09-JAN-2019', 77),
(200, 3, '10-JAN-2019', '19-JAN-2019', 35)
Я создаю список дат, используя технику в Генерация последовательности в SQL[^]...
DECLARE @minDate date = (SELECT MIN(StartDate) FROM @A)
DECLARE @maxDate date = DATEADD(DAY, 1, (SELECT MAX(EndDate) FROM @A))

;WITH cteDays (datum) AS
(
    SELECT  @minDate AS datum
    UNION ALL
    SELECT  DATEADD(DAY, 1, datum)
    FROM    cteDays
    WHERE DATEADD(DAY, 1, datum) <  @maxDate
    )
SELECT datum FROM cteDays
Затем я соединяю это с исходной таблицей, чтобы получить остальные данные, и использую оконную функцию, чтобы получить прокатку SUM (видеть Как использовать оконные функции Microsoft SQL Server 2012, Часть 1 | IT Pro[^] ).
Так что все это становится 
DECLARE @minDate date = (SELECT MIN(StartDate) FROM @A)
DECLARE @maxDate date = DATEADD(DAY, 1, (SELECT MAX(EndDate) FROM @A))


;WITH cteDays (datum) AS
(
    SELECT  @minDate AS datum
    UNION ALL
    SELECT  DATEADD(DAY, 1, datum)
    FROM    cteDays
    WHERE DATEADD(DAY, 1, datum) <  @maxDate
    )
SELECT datum, A.*, A.PricePW / 7 AS PricePD, SUM (A.PricePW / 7) OVER (ORDER BY datum) AS Accummulative
FROM cteDays C 
LEFT JOIN @A A ON C.datum >= A.StartDate AND C.datum <= A.EndDate
Что дает результаты
datum	   ID	UserID  StartDate	EndDate		PricePW	PricePD	Acc.
2019-01-02 157	3	2019-01-02	2019-01-05	56.00	8.00	8.00
2019-01-03 157	3	2019-01-02	2019-01-05	56.00	8.00	16.00
2019-01-04 157	3	2019-01-02	2019-01-05	56.00	8.00	24.00
2019-01-05 157	3	2019-01-02	2019-01-05	56.00	8.00	32.00
2019-01-06 189	3	2019-01-06	2019-01-09	77.00	11.00	43.00
2019-01-07 189	3	2019-01-06	2019-01-09	77.00	11.00	54.00
2019-01-08 189	3	2019-01-06	2019-01-09	77.00	11.00	65.00
2019-01-09 189	3	2019-01-06	2019-01-09	77.00	11.00	76.00
2019-01-10 200	3	2019-01-10	2019-01-19	35.00	5.00	81.00
2019-01-11 200	3	2019-01-10	2019-01-19	35.00	5.00	86.00
2019-01-12 200	3	2019-01-10	2019-01-19	35.00	5.00	91.00
2019-01-13 200	3	2019-01-10	2019-01-19	35.00	5.00	96.00
2019-01-14 200	3	2019-01-10	2019-01-19	35.00	5.00	101.00
2019-01-15 200	3	2019-01-10	2019-01-19	35.00	5.00	106.00
2019-01-16 200	3	2019-01-10	2019-01-19	35.00	5.00	111.00
2019-01-17 200	3	2019-01-10	2019-01-19	35.00	5.00	116.00
2019-01-18 200	3	2019-01-10	2019-01-19	35.00	5.00	121.00
2019-01-19 200	3	2019-01-10	2019-01-19	35.00	5.00	126.00

jaket-cp

хорошее решение - мои 5
очень похоже на мое - но ты меня опередил :)

Рейтинг:
0

jaket-cp

Этот метод должен работать для нескольких пользователей с разными диапазонами дат.
Если у пользователя есть одинаковые даты с разными ценами, он должен суммировать их.
Хотя я не уверен, насколько это эффективно - должно работать на SQL server 2012+

--test data
declare @tableA table (ID int, UserID int, StartDate datetime, EndDate DateTime, PricePW money );
insert into @tableA
select 157, 3, '20190102', '20190105', 56
union select 189, 3, '20190106', '20190109', 77
--union select 201, 3, '20190106', '20190109', 35
union select 200, 3, '20190110', '20190119', 35
union select 500, 10, '20190102', '20190105', 56
--union select 501, 10, '20190106', '20190109', 77
union select 502, 10, '20190110', '20190119', 35
;

with UserStartEnd as(
	--get the date start and end for each user
	select
		UserID, 
		min(StartDate) StartDt, 
		max(EndDate) EndDt
	from 
		@tableA
	group by
		UserID
), UserDateRange as (
	--recursive to get all date ranges for each user
	select 
		UserID,
		StartDt TheDate,
		EndDt EndDate
	from UserStartEnd
	union all
	select 
		UserID, 
		dateadd (dd, 1, TheDate) TheDate,
		EndDate
	from UserDateRange
	where dateadd (dd, 1, TheDate) <= EndDate
), UserDateRangePricePerDay as (
	--get price per day for each user for the date range
	select
		udr.UserID,
		udr.TheDate,
		sum (a.PricePW/7) PricePerDay
	from UserDateRange udr
	inner join @tableA a
		on 
			udr.TheDate between a.StartDate and a.EndDate 
			and udr.UserID = a.UserID
	group by
		udr.UserID,
		udr.TheDate
)
select 
	UserID,
	TheDate,
	PricePerDay,
	sum(PricePerDay) 
		over(
			partition by UserID
			order by UserID, TheDate
			rows between unbounded preceding and current row
	) Accumulative
from UserDateRangePricePerDay
order by
	UserID,
	TheDate
option (maxrecursion 1000)
;

Накопительный можно отработать несколькими способами
Я использовал этот метод https://stackoverflow.com/a/13331102