September 12, 2013 at 8:19 am
I was assigned a difficult project. It is beyond by skill. I need expert to help me.
From sample data,
1) Group by ID and COLOR
2) From this group, select ID, min(DATEFROM),max(DATETO) and Color and then insert into a new table,
3) For example, goup 111 and blue, select min(DATEFROM)=01/10/2012 and max(DATETO)=03/16/2012
4) Keep on selecting until end file.
Here is sample data:
IDDATEFROMDATETOCOLOR
11101/10/201201/11/2012BLUE
11102/02/201202/02/2012BLUE
11103/15/201203/16/2012BLUE
11104/05/201204/05/2012GREEN
11105/25/201205/26/2012GREEN
11106/06/201206/16/2012GREEN
11107/17/201207/17/2012BLUE
11109/08/201210/10/2012BLUE
22201/10/201301/11/2013RED
22202/02/201302/02/2013RED
22203/15/201303/16/2013RED
22204/05/201304/05/2013GREEN
22205/25/201305/26/2013GREEN
22206/06/201306/16/2013GREEN
22207/17/201307/17/2013BLUE
22209/08/201310/10/2013BLUE
Finally, I need select records and insert a new table like below:
11101/10/201203/16/2012BLUE
11104/05/201206/16/2012GREEN
11107/17/201210/10/2012BLUE
22201/10/201303/16/2013RED
22204/05/201306/16/2013GREEN
22207/17/201310/10/2013BLUE
September 12, 2013 at 8:36 am
You don't need a bunch of steps for this. A simple query (that you pretty much wrote) will do this in a single step.
select ID, min(DATEFROM),max(DATETO),Color
group by ID, Color
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 8:53 am
An 'insert into' and the new table name will create the table from the results.
insert into newtable
select ID, min(DATEFROM),max(DATETO),Color
group by ID, Color
September 12, 2013 at 9:01 am
No, this result is not right.
If I use this query, the result is not same as I expected.
September 12, 2013 at 9:44 am
Oops, that will insert records into an existing table. Try:
select ID, min(DATEFROM) as date1, max(DATETO)as date2,Color
into newtable from originaltable
group by ID, Color
September 12, 2013 at 9:48 am
adonetok (9/12/2013)
No, this result is not right.If I use this query, the result is not same as I expected.
Then maybe you should post ddl, sample data and desired output so we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 9:53 am
How to post ddl?
I list the sample data and result I need again as below.
Sample data:
ID--DATEFROM-DATETO-----COLOR
--------------------------------
111 01/10/2012 01/11/2012 BLUE
111 02/02/2012 02/02/2012 BLUE
111 03/15/2012 03/16/2012 BLUE
111 04/05/2012 04/05/2012 GREEN
111 05/25/2012 05/26/2012 GREEN
111 06/06/2012 06/16/2012 GREEN
111 07/17/2012 07/17/2012 BLUE
111 09/08/2012 10/10/2012 BLUE
222 01/10/2013 01/11/2013 RED
222 02/02/2013 02/02/2013 RED
222 03/15/2013 03/16/2013 RED
222 04/05/2013 04/05/2013 GREEN
222 05/25/2013 05/26/2013 GREEN
222 06/06/2013 06/16/2013 GREEN
222 07/17/2013 07/17/2013 BLUE
222 09/08/2013 10/10/2013 BLUE
Result:
ID--DATEFROM-DATETO-----COLOR
--------------------------------
111 01/10/2012 03/16/2012 BLUE
111 04/05/2012 06/16/2012 GREEN
111 07/17/2012 10/10/2012 BLUE
222 01/10/2013 03/16/2013 RED
222 04/05/2013 06/16/2013 GREEN
222 07/17/2013 10/10/2013 BLUE
September 12, 2013 at 9:59 am
adonetok (9/12/2013)
How to post ddl?I list the sample data and result I need again as below.
Come on...this isn't the first time we have asked you for ddl and sample data. You know that you need to post create table statements and insert statements for data. Just sticking a bunch of characters does not make it easy for the volunteers around here to help.
Please take a few minutes and read the first article in my signature. This is nowhere near the first time you have been told this. Your query is not all that difficult but I really don't feel like spending 20-30 minutes creating the tables and data so I can then spend time working on the actual problem. You have to do some of the work, after all you are the one who is getting ALL of the pay for the job.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 11:46 am
I might point out that your desired output is ambiguous. you have a 111/Blue group twice in your suggested output. What are your criteria for having 2 in that?
September 12, 2013 at 12:03 pm
That is what boss whant.
The sample data sort by DATEFROM but once COLOR change, it need to re-group and need min(DATEFROM) adn max(DATETO) again.
September 12, 2013 at 12:17 pm
venoym (9/12/2013)
I might point out that your desired output is ambiguous. you have a 111/Blue group twice in your suggested output. What are your criteria for having 2 in that?
I'm guessing, based on the desired output, that the second group should have been a 111/Red group. If this is the case, please see below for one possible solution:
--==== CREATE A TEMP TABLE TO HOLD THIS COLOR HISTORY DATA
if OBJECT_ID('tempdb..#colorHistory', 'u') is not null drop table #colorHistory
create table #colorHistory (id varchar(3), dateFrom date, dateTo date, color varchar(5))
insert #colorHistory
values
('111', '20120110', '20120111', 'blue'),
('111', '20120202', '20120202', 'blue'),
('111', '20120315', '20120316', 'blue'),
('111', '20120405', '20120405', 'green'),
('111', '20120526', '20120526', 'green'),
('111', '20120616', '20120616', 'green'),
('111', '20120717', '20120717', 'red'), --corrected this from blue
('111', '20121010', '20121010', 'red'), --corrected this from blue
('222', '20130110', '20130110', 'red'),
('222', '20130202', '20130202', 'red'),
('222', '20130316', '20130316', 'red'),
('222', '20130405', '20130405', 'green'),
('222', '20130526', '20130526', 'green'),
('222', '20130616', '20130616', 'green'),
('222', '20130717', '20130712', 'blue')
--==== NOW GET THE MIN DATEFROM AND MAX DATETO FOR EACH COLOR/ID COMBINATION
selectc1.id
,MIN(c1.dateFrom)
,MAX(c1.dateTo)
,c1.color
from #colorHistory c1
group by c1.id, c1.color
order by c1.id, c1.color
This is essentially the same solution that the original answers were suggesting. Can you help us understand why this is not the appropriate solution?
There are also overblown solutions involving CTEs and row_number()... but I'm sure you're not interested in that.
Hope this helps.
PS:
Sean Lange (9/12/2013)
You have to do some of the work, after all you are the one who is getting ALL of the pay for the job.
Sean is right, if I wasn't bored in a meeting, I wouldn't have bothered with creating the table and data here. If you expect people to help you, you need to at least do a little of the legwork.
September 12, 2013 at 12:29 pm
Here is my ddl. Please help.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
[AUTOID] INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,
[ID] [varchar](50) NULL,
[DATEFROM] [datetime] NULL,
[DATETO] [datetime] NULL,
[varchar](255) NULL
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DATEFROM, DATETO,COLOR)
SELECT '111','Jan 10 2012 12:00AM','Jan 11 2012 12:00AM','BLUE' UNION ALL
SELECT '111','Feb 2 2012 12:00AM','Feb 2 2012 12:00AM','BLUE' UNION ALL
SELECT '111','Mar 15 2012 12:00AM','Mar 16 2012 12:00AM','BLUE' UNION ALL
SELECT '111','Apr 5 2012 12:00AM','Apr 5 2012 12:00AM','GREEN' UNION ALL
SELECT '111','May 25 2012 12:00AM','May 26 2012 12:00AM','GREEN' UNION ALL
SELECT '111','Jun 6 2012 12:00AM','Jun 16 2012 12:00AM','GREEN' UNION ALL
SELECT '111','Jul 17 2012 12:00AM','Jul 17 2012 12:00AM','BLUE' UNION ALL
SELECT '111','Sep 8 2012 12:00AM','Oct 10 2012 12:00AM','BLUE' UNION ALL
SELECT '222','Jan 10 2013 12:00AM','Jan 11 2013 12:00AM','RED' UNION ALL
SELECT '222','Feb 2 2013 12:00AM','Feb 2 2013 12:00AM','RED' UNION ALL
SELECT '222','Mar 15 2013 12:00AM','Mar 16 2013 12:00AM','RED' UNION ALL
SELECT '222','Apr 5 2013 12:00AM','Apr 5 2013 12:00AM','GREEN' UNION ALL
SELECT '222','May 25 2013 12:00AM','May 26 2013 12:00AM','GREEN' UNION ALL
SELECT '222','Jun 6 2013 12:00AM','Jun 16 2013 12:00AM','GREEN' UNION ALL
SELECT '222','Jul 17 2013 12:00AM','Jul 17 2013 12:00AM','BLUE' UNION ALL
SELECT '222','Sep 8 2013 12:00AM','Oct 10 2013 12:00AM','BLUE' UNION ALL
SELECT '111','Jan 10 2012 12:00AM','Mar 16 2012 12:00AM','BLUE' UNION ALL
SELECT '111','Apr 5 2012 12:00AM','Jun 16 2012 12:00AM','GREEN' UNION ALL
SELECT '111','Jul 17 2012 12:00AM','Oct 10 2012 12:00AM','BLUE' UNION ALL
SELECT '222','Jan 10 2013 12:00AM','Mar 16 2013 12:00AM','RED' UNION ALL
SELECT '222','Apr 5 2013 12:00AM','Jun 16 2013 12:00AM','GREEN' UNION ALL
SELECT '222','Jul 17 2013 12:00AM','Oct 10 2013 12:00AM','BLUE'
September 12, 2013 at 1:15 pm
I see the hitch up here...you need to see the min date and max date from each of the consecutive color/ID group pairings.
Originally, we had been treating the grouping value (business key) as the pairing of color and ID. The key is actually the pairing of color and ID over sequential, uninterrupted AUTOID values.
In this case, the simple solution that the group has previously been advocating will not work. At first glance, this is probably going to require either really complicated logic or row by row processing. I'll keep considering the "complicated logic" approach (as to not anger Lord Moden), and report back if I come up with anything.
September 12, 2013 at 2:07 pm
paulcauchon (9/12/2013)
I see the hitch up here...you need to see the min date and max date from each of the consecutive color/ID group pairings.Originally, we had been treating the grouping value (business key) as the pairing of color and ID. The key is actually the pairing of color and ID over sequential, uninterrupted AUTOID values.
In this case, the simple solution that the group has previously been advocating will not work. At first glance, this is probably going to require either really complicated logic or row by row processing. I'll keep considering the "complicated logic" approach (as to not anger Lord Moden), and report back if I come up with anything.
Not that complicated;
SELECT ID, DATEFROM = MIN(DATEFROM), DATETO = MAX(DATETO), COLOR
FROM (
SELECT autoid, ID, DATEFROM, DATETO, COLOR,
NewGroup = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COLOR) - AUTOID
FROM #mytable
) d
GROUP BY ID, COLOR, NewGroup
ORDER BY ID, MIN(DATEFROM)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
September 12, 2013 at 2:27 pm
Thank you very much.
This is exatly what I need.
The last question is how to delete duplicate records before output?
ID-------DATEFROM-----------------DATETO--------------------COLOR
-----------------------------------------------------------------------
1112012-01-10 00:00:00.0002012-03-16 00:00:00.000BLUE
1112012-01-10 00:00:00.0002012-03-16 00:00:00.000BLUE
1112012-04-05 00:00:00.0002012-06-16 00:00:00.000GREEN
1112012-04-05 00:00:00.0002012-06-16 00:00:00.000GREEN
1112012-07-17 00:00:00.0002012-10-10 00:00:00.000BLUE
1112012-07-17 00:00:00.0002012-10-10 00:00:00.000BLUE
2222013-01-10 00:00:00.0002013-03-16 00:00:00.000RED
2222013-01-10 00:00:00.0002013-03-16 00:00:00.000RED
2222013-04-05 00:00:00.0002013-06-16 00:00:00.000GREEN
2222013-04-05 00:00:00.0002013-06-16 00:00:00.000GREEN
2222013-07-17 00:00:00.0002013-10-10 00:00:00.000BLUE
2222013-07-17 00:00:00.0002013-10-10 00:00:00.000BLUE
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply