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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.