TSQL Script: Min, Max function
-
Hello All,
I hope you are doing well.
I have difficulties to write a sql script.
May be you could help me.
I have a table like this.
And I want to have a result like this:
I used the min and max function but it doesn't work.
Do you have any idea ?
Thank you for your help
-
Show us your attempt. =)
SELECT BusinessKey
.name
,attribute
,MIN(ValidFrom)
,MAX(ValidTo)
FROM SomeTable
GROUP BY BusinessKey
.name
,attribute -
This looks to me like a Gaps and Islands query. Itzik Ben-Gan has posted numerous articles and written in books how best to solve those types of queries.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
>> I have a table like this. <<
Where is the DDL? For over 30 years, netiquette has been to post DDL on SQL forms. Now we have to take all the trouble to get the data types, the keys the constraints, and the meaning of this data. Basically, you posted garbage and been very rude to us. Here's a wild guess, taken from your useless spreadsheet.
1) you didn't even give us a table name in your picture.
2) "business_key" is not a valid table name. Have you ever read ISO 1179, or any of the metadata committee standards? The attribute property on this column of "_key" violates the rules about mixing data and metadata. If I wanted to identify a business. I would use the DUNS, since it's required by law in many industries and countries and is a universal standard.
3) there is no such thing as a generic unattached "name" in RDBMS; it has to be "<something in particular>_name"; this rule of data modeling comes directly from the law of identity in formal logic.
4) there is no such thing as a column named "attribute"; you name something for what it is by its nature, not how is it is used as metadata.
5) the next two columns are also wrong because you don't know that the only valid display format allowed in ANSI/ISO standard SQL is based on ISO 8601. You also have no constraint to assure that these columns are in the correct order.
6) having a column named "primary_key" is another minute metadata violation. It also doesn't make any sense. If you posted DDL, it would simply have a primary key (<column list>) declaration.
Why do you think this is the way to create a table? I am not being sarcastic; I've been teaching SQL for over 30 years. I helped write standards for the language. I have found that people do not make mistakes like this without some kind of mental model. In order to teach people I need to know what they are thinking and how they could be this confused
I have arbitrarily decided that you have a table that holds coupons for given business. And it gives their validation dates. Here is a not normalized attempt at fixing your garbage. DUNS should be in a separate table and referenced in this Coupons table.
CREATE TABLE Coupons --- you gave no table name!
(duns CHAR(9) NOT NULL,
business_name CHAR(25) NOT NULL,
coupon_start_date DATE NOT NULL,
coupon_end_date DATE NOT NULL,
CHECK (coupon_start_date <= coupon_end_date),
PRIMARY KEY (duns, coupon_start_date));
I'm going to make a guess that you have allowed contiguous temporal rows in your non-table. Since SQL is a declarative language, we want to prevent bad data from getting in the first place. Do you really want to spend the overhead of constantly correcting the bad data you put in? Google around for contiguous time periods; instead of repairing gaps, you can prevent them in the DDL.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
https://www.red-gate.com/simple-talk/sql/t-sql-programming/contiguous-time-periods/
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply