find the min start date each time

hello community,

I was wondering if there was a way to find the earliest start date per product at a premise.

If you can see in the spreadsheet attached is a sample customer. This premise_id was supplied to 3 tenants, The last tenant left supply and then came back so I'd want another start date for that product at that premise. highlighted in yellow are the dates I want to find using SQL. bare in mind a customer could have multiple fuels at a premise however they are split by account_reference.

is there a way to do this?

Thank you all, hopefully that was a good enough description into what I am trying to do.
sample.xlsx
mehul bhaktaAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try full tested solution -

Data Generation

CREATE TABLE TheDates
(
	 FROM_DATE	DATETIME
	,TO_DATE	 DATETIME
	,PREMISE_ID	VARCHAR(15)
	,ACCOUNT_REFERENCE	VARCHAR(15)
	,PRODUCT_STATUS	VARCHAR(10)
	,[START_DATE]	 DATETIME
	,END_DATE	DATETIME
	,KEY_TYPE INT
)
GO

INSERT INTO TheDates VALUES
('2016/04/12',' 2016/05/03','123456789','987654321','ED','2015/11/19','2016/04/12',2),
('2016/05/04','2016/05/03','123456789','987654321','ED','2016/04/13','2016/04/12',2),
('2016/05/04','2017/08/08','123456789','987654321','ED','2016/04/23','2016/05/30',2 ),
('2017/08/09', NULL	  ,'123456789','987654321','LI' ,'2017/08/23', NULL		,2 )
GO

Open in new window


SOLUTION 1

SELECT *
	    , MIN([START_DATE]) OVER (PARTITION BY PREMISE_ID,ACCOUNT_REFERENCE,PRODUCT_STATUS) Minx 
		, MAX(END_DATE) OVER (PARTITION BY PREMISE_ID,ACCOUNT_REFERENCE,PRODUCT_STATUS) Maxy
FROM
TheDates

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
FROM_DATE               TO_DATE                 PREMISE_ID      ACCOUNT_REFERENCE PRODUCT_STATUS START_DATE              END_DATE                KEY_TYPE    Minx                    Maxy
----------------------- ----------------------- --------------- ----------------- -------------- ----------------------- ----------------------- ----------- ----------------------- -----------------------
2016-04-12 00:00:00.000 2016-05-03 00:00:00.000 123456789       987654321         ED             2015-11-19 00:00:00.000 2016-04-12 00:00:00.000 2           2015-11-19 00:00:00.000 2016-05-30 00:00:00.000
2016-05-04 00:00:00.000 2016-05-03 00:00:00.000 123456789       987654321         ED             2016-04-13 00:00:00.000 2016-04-12 00:00:00.000 2           2015-11-19 00:00:00.000 2016-05-30 00:00:00.000
2016-05-04 00:00:00.000 2017-08-08 00:00:00.000 123456789       987654321         ED             2016-04-23 00:00:00.000 2016-05-30 00:00:00.000 2           2015-11-19 00:00:00.000 2016-05-30 00:00:00.000
2017-08-09 00:00:00.000 NULL                    123456789       987654321         LI             2017-08-23 00:00:00.000 NULL                    2           2017-08-23 00:00:00.000 NULL

(4 row(s) affected)

Open in new window



or

SOLUTION 2

SELECT FROM_DATE,	TO_DATE	,PREMISE_ID,	ACCOUNT_REFERENCE	,PRODUCT_STATUS,	START_DATE,	END_DATE	,KEY_TYPE
	,CASE WHEN rnk = 1 THEN	Minx ELSE NULL END Minx
	,CASE WHEN rnk = 1 THEN	Maxy ELSE NULL END Maxy
 FROM
(
	SELECT * ,ROW_NUMBER() OVER (PARTITION BY PREMISE_ID,ACCOUNT_REFERENCE,PRODUCT_STATUS ORDER BY [START_DATE]) rnk
			, MIN([START_DATE]) OVER (PARTITION BY PREMISE_ID,ACCOUNT_REFERENCE,PRODUCT_STATUS) Minx 
			, MAX(END_DATE) OVER (PARTITION BY PREMISE_ID,ACCOUNT_REFERENCE,PRODUCT_STATUS) Maxy
	FROM
	TheDates
)r

Open in new window


OUTPUT

*------------------------
OUTPUT
------------------------*/
FROM_DATE               TO_DATE                 PREMISE_ID      ACCOUNT_REFERENCE PRODUCT_STATUS START_DATE              END_DATE                KEY_TYPE    Minx                    Maxy
----------------------- ----------------------- --------------- ----------------- -------------- ----------------------- ----------------------- ----------- ----------------------- -----------------------
2016-04-12 00:00:00.000 2016-05-03 00:00:00.000 123456789       987654321         ED             2015-11-19 00:00:00.000 2016-04-12 00:00:00.000 2           2015-11-19 00:00:00.000 2016-05-30 00:00:00.000
2016-05-04 00:00:00.000 2016-05-03 00:00:00.000 123456789       987654321         ED             2016-04-13 00:00:00.000 2016-04-12 00:00:00.000 2           NULL                    NULL
2016-05-04 00:00:00.000 2017-08-08 00:00:00.000 123456789       987654321         ED             2016-04-23 00:00:00.000 2016-05-30 00:00:00.000 2           NULL                    NULL
2017-08-09 00:00:00.000 NULL                    123456789       987654321         LI             2017-08-23 00:00:00.000 NULL                    2           2017-08-23 00:00:00.000 NULL

(4 row(s) affected)

Open in new window

0
 
mehul bhaktaAuthor Commented:
for some strange reason that wasn't giving me the results I was looking for, however I did find a good solution using

last_value(gender ignore nulls) over(partition by customer_id order by from_date rows between unbounded preceding and unbounded following)
0
 
Pawan KumarDatabase ExpertCommented:
cool. :)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mehul bhaktaAuthor Commented:
@Pawan Kumar thank you sooooo much for your time and help. you have no idea how much I appreciated your help and this website.
0
 
Pawan KumarDatabase ExpertCommented:
welcome. glad to help as always.
1
 
mehul bhaktaAuthor Commented:
a million thanks to Pawan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.