March 2, 2016 at 12:48 am
Hello,
Having a situation as below, need query help to get the expected result.
if consecutive month have Y then need to get the first day & last day of the month as a range.
for example if JAN, FEB & MAR has Y then need [1/1/2015]-[3/31/2015], just needed for 2015 year only.
Thank you so much for your help & support, very very much appreciated of your time. Thanks.
------------------------------------------------------------------------------------------------------------------
create table _t1 (id int
, Jan varchar(1)
, feb varchar(1)
, mar varchar(1)
, apr varchar(1)
, may varchar(1)
, Jun varchar(1)
, Jul varchar(1)
, aug varchar(1)
, sep varchar(1)
, oct varchar(1)
, nov varchar(1)
, dec varchar(1)
)
insert into _t1 values
(1,'Y','Y','N','Y','Y','Y','N','N','Y','Y','Y','Y'),
(2,'N','Y','N','Y','Y','N','Y','Y','Y','N','Y','Y'),
(3,'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N'),
(4,'Y','N','N','N','N','N','N','N','N','N','N','N'),
(5,'N','N','N','N','N','N','N','N','N','N','N','Y'),
(6,'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
select * from _t1
----------------------------- expected result --------------------------------------
1, [1/1/2015]-[2/28/2015],[4/1/2015]-[6/30/2015],[9/1/2015]-[12/31/2015]
2, [2/1/2015]-[2/28/2015],[4/1/2015]-[5/31/2015],[7/1/2015]-[9/30/2015],[11/1/2015]-[12/31/2015]
3, [1/1/2015]-[11/30/2015]
4, [1/1/2015]-[1/31/2015]
5, [12/1/2015]-[12/31/2015]
6, [1/1/2015]-[12/31/2015]
------------------------------------------------------------------------------------------------------------------
drop table _t1
------------------------------------------------------------------------------------------------------------------
March 2, 2016 at 2:18 am
Oh dear. The design of the input table is quite awful - it violates First Normal Form by having separate columns for the months. This is called a repeating group, and it often results in terrible queries. The format is nice for presenting data, not storing it.
The result you are after is also very unrelational - in this case it appears you want to have multiple values all smashed together in a single column. This also violates First Normal Form, specifically the requirement that column values are atomic.
Solving your request is however relatively easy, you'll just have to combine three techniques.
1: Google "Unpivot sql server" to transform the input data to a more relational format. (The UNPIVOT operator will work, but if you have a large volume of data you might need to find some of the faster techniques)
2: Google "Itzik Ben-Gan gaps and islands" and understand his amazing technique on identifyinig islands in data, then use some standard date logic to find the date ranges you need.
3: Google "for xml path string concatenation" to find how to combine all date ranges for each id in a single line.
March 2, 2016 at 2:35 am
Thanks Hugo for your reply, The table design is just a situation in EXCEL document and need to be converted to the expected result. The design is NOT actual database table design. A situation in EXCEL document, and trying to import the data to SQL and trying to find a way to get the result using T-SQL.
Thanks for your time looking into it, appreciated.
March 2, 2016 at 3:15 am
Fair enough. It's not uncommon to have to import stuff looking like that comning from external sources. The first part of my reply (Google "Unpivot sql server") should help with transforming that input to a better format.
If you want to store the end result of your process (the ranges) as permanent data in another table, I suggest stopping after step 2, so you would store it in a table that has three columns (Id, StartDate, EndDate), a primary key constraint on (Id, StartDate), and a unique constraint on (Id, EndDate). If user entry in that table is permitted, you'll need to add some more constraints.
The third step where you use FOR XML to concatenate the ranges to a single line would be for presentation only (and if possible, preferably handled in the presentation layer instaed of in the database)
The various google phrases I posted should get you a long way towards a solution. If you get stuck anywhere in the process, feel free to post the work you have done so far so that we can look at where you are stuck and try to get you over that bump in the road.
March 2, 2016 at 3:47 am
Thanks Hugo for your suggestions, I will try, very much appreciated of your help & support, Many thanks.
March 3, 2016 at 3:05 am
Hello Hugo,
I wrote a Stored Procedures to read the previous and next values and managed to get the expected result using some programming method. Thanks for your time looking into it. Thanks everyone, i do not need any further help on this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply