How do I use SQL create a date list from a table of this structure?

Question

0.00/5 (No votes)

See more:

See attached Image. This is the first time posting so I'm not sure if images are allowed...

images[^]

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

Basically, my data format structure is in Table A

i can do a

SQL

SELECT * from TableA where USERID = 3

But what I really need is to create the table on the right handside (Table B)

Is there any SQL I can use to do this?

What I have tried:

I know a bit of SQL but not good enough to create anything like this.

Posted 8-May-19 16:45pm

FullyLucky

Updated 9-May-19 2:51am

jaket-cp

v3

Add a Solution

Comments

[no name] 8-May-19 23:05pm

I think your sample is "out of balance". Don't you just hate that?

Wait. I see. Divide the weekly by 7 to get a daily rate. Then generate "daily" records for the period; with a running balance.

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

FullyLucky 8-May-19 23:14pm

how do you mean? it's not linking correctly?

FullyLucky 8-May-19 23:14pm

yeah :( how do i generate the dates is the first challenge.

Second challenge is how to get the price per day and the accumulative

[no name] 8-May-19 23:16pm

Price per day is price per week / 7.

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

FullyLucky 8-May-19 23:17pm

yeah

CHill60 9-May-19 3:39am

By the way many of us look at this site whilst at work. Most corporations block sites like DropBox. In future put all the relevant information into the body of your question to maximise the number of people who can offer to help

FullyLucky 9-May-19 7:09am

oh ok Thanks for the tip. I didn't know. But i can understand how some companies might want to block certain websites. This website platform doesn't give you an option to upload pictures.

CHill60 9-May-19 7:39am

Yeah - I know about not being able to upload pictures, but it's a very, very rare day that a picture is required to describe a coding problem. In this case you could have just copied the values into the body of your question

2 solutions

Solution 2

This method should work for multiple users with different date ranges.
If the user has same dates with different prices it should sum them up.
Not sure how efficient it is though - should work on SQL server 2012+

SQL

--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)
;

Accumulative can be worked out in multiple ways
I used this method https://stackoverflow.com/a/13331102

Posted 9-May-19 2:33am

jaket-cp

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2019-05-09T02:21:00

Here is the sample data I used for this

SQL

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)

I create a list of dates using the technique in Generating a Sequence in SQL[^]...

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

I then join to this to the original table to get the rest of the data and use a Window Function to get the rolling SUM (see How to Use Microsoft SQL Server 2012's Window Functions, Part 1 | IT Pro[^] ).
So the whole thing becomes

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, 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

Which produces results

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