November 23, 2013 at 9:35 pm
Goodnight
Would like to separate the dates for months. For example I have a table that shows me the following:
Code Name fec_fin justification fec_ini
1 john 15/03/2013 Holiday 13/04/2013
What I want is that the range is separated for their respective months for example:
Code Name fec_fin justification fec_ini
1 john 15/03/2013 Holiday 31/03/2013
1 john 01/04/2013 Holiday 13/04/2013
As you can see that time has been separated into two sections, for months.
Keep in mind that the table exists and records that have been separated for months but many more to go missing are like 18000 records.
thanks
November 24, 2013 at 1:25 am
First, please read the first article I have referenced below in my signature block. It will walk you through the what and how of the things you should post to get the best possible answers to you questions. The extra work you put into posting as much as possible to help us will help you in the end, plus you will get tested code in return.
Second, be sure you understand everything you see in this code before you put it in production as you are the one you will have to support the code if something goes wrong.
One the things you will see in the code is a dynamic tally (or numbers) table. The second is the use of cross apply. There are excellent articles here on ssc that discuss these items. Search them out and read them.
with
-- From here
TestData as (
select
*
from
(values (1,'John',cast('2013-03-15' as date),'Holiday',cast('2013-04-13' as date)),
(2,'Jane',cast('2013-03-15' as date),'Holiday',cast('2013-05-13' as date))
)dt(Code,Name,fec_fin,justification,fec_ini)
),
-- to here simply defines the test data. Remove this and replace TestData
-- below with your actual table
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
etally(n) as (select 0 union all select row_number() over (order by (select null)) from e4 a cross join e4 b)
select
td.Code,
td.Name,
min(dt.Adate1) as fec_fin,
td.justification,
max(dt.Adate1) as fec_ini
from
TestData td
cross apply (
select top(datediff(dd,td.fec_fin,td.fec_ini) + 1)
dateadd(dd,t.n,td.fec_fin),
cast(dateadd(mm,datediff(mm,0,dateadd(dd,t.n,td.fec_fin)),0) as date)
from
etally t
)dt(Adate1,Mdate)
group by
td.Code,
td.justification,
td.Name,
dt.Mdate;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply