Multiple Date rows into One single Date row(date range)
-
Dear ALL
I have a requirement of converting different date ranges into one single row, group by EmployeeNr.
You can exclude CompensationNr and Payment Date columns, while writing the query.
Following is an example of table and result i want it to be.
Thanks.
<!--more--> -
This was removed by the editor as SPAM
-
Based on that sample data very easy although I am going to guess not all your data is like that and you will have various different niches which need to be handled.
But back to the sample data
SELECT employeenr, min([from date]) as [from date], max([to date]) as [to date], sum([total days]) as [total days] FROM mytable GROUP BY employeenr
-
Thanks for quick reply @Ant-Green, I was not so quick in thanking you though :).
you are right there are other niches in the data which i totally mis-understood. I am sending again the case senario please refere to the following picture
CODE 301 : always starts when en employee becomes sick again after coming back to work this is i mentioned in colours , you can see continuous period are mentioned with same colour, then there is a gap in days when employee worked at office, after that he gets sick again and code 301 invoked again with his new sick period
Kindly ask me if anything is unclear
Thanks
Best wishes
-
The pictures are very helpful for understanding but it would also be extremely helpful if you included readily consumable data. Please see the first link in my signature line below for one of a few ways to do that but mostly to understand why such a thing is helpful.
What we really need is the CREATE TABLE statement for the simple test data that you're showing in your pictures and some INSERT code to populate that test table with the same data from your pictures.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Getting back to your second output, what exactly is it that determines if the entries are contiguous? Does a contiguous period always start with a comp code of 301? And, if so, why are there entries after the Green Section that have not been selected as single day groups?
And why does that one entry have a positive value? Is that an earned sick day or ???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi ,
Thank you for the correction , here is my table with code. I have included three employees sick leave dates.
you can change the date to date format for your ease, if you want.
CREATE TABLE [dbo].[Result](
[EmpNr] [nvarchar](10) NOT NULL,
[CompCode] [varchar](3)NOT Null,
[PaymentDate] [varchar](8)Not Null,
[FromDate] [varchar](8000) NULL,
[ToDate] [varchar](8000) NULL,
[TotalDays] [numeric](38, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Result]
VALUES
(800334,301,20210225,20210111,20210111, -1.00),
(800334,302,20210225,20210114,20210114, -1.00),
(800334,301,20210423,20210214,20210214, 1.00 ),
(800334,301,20210325,20210214,20210214, -1.00),
(800334,302,20210423,20210214,20210214, -1.00),
(800334,301,20210423,20210309,20210309, -1.00),
(800334,302,20210423,20210312,20210312, -1.00),
(800334,301,20210525,20210407,20210407, -1.00),
(800334,302,20210525,20210408,20210412, -5.00),
(800334,302,20210525,20210414,20210420, -7.00),
(800334,301,20210525,20210511,20210511, -1.00),
(800334,302,20210525,20210512,20210524, -13.00),
(800334,304,20210525,20210525,20210531, -7.00),
(800334,304,20210624,20210601,20210606, -6.00),
(800334,301,20210924,20210820,20210820, -1.00),
(800334,302,20210924,20210825,20210825, -1.00),
(800334,301,20211025,20210910,20210910, -1.00),
(800334,302,20211025,20210911,20210915, -5.00),
(800761,302,20201223,20201112,20201117, -6.00),
(800761,301,20210125,20201214,20201214, -1.00),
(800761,302,20210125,20201215,20201223, -9.00),
(800761,301,20201223,20201111,20201111, -1.00),
(800761,301,20210225,20210120,20210120, -1.00),
(800761,302,20210225,20210121,20210122, -2.00),
(800761,301,20210325,20210211,20210211, -1.00),
(800761,301,20211125,20211018,20211018, -1.00),
(800761,302,20211125,20211019,20211031, -13.00),
(800341,301,20201125,20201022,20201022, -1.00) ,
(800341,302,20201125,20201023,20201025, -3.00) ,
(800341,301,20201223,20201103,20201103, -1.00) ,
(800341,302,20201223,20201104,20201104, -1.00) ,
(800341,301,20201223,20201119,20201119, -1.00) ,
(800341,302,20201223,20201120,20201129, -10.00),
(800341,301,20210125,20201217,20201217, -1.00) ,
(800341,302,20210125,20201218,20201230, -13.00),
(800341,301,20210225,20210111,20210111, -1.00) ,
(800341,302,20210225,20210112,20210124, -13.00),
(800341,304,20210225,20210125,20210131, -7.00) ,
(800341,304,20210225,20210201,20210207, -7.00) ,
(800341,304,20210225,20210208,20210221, -14.00),
(800341,304,20210225,20210222,20210228, -7.00) ,
(800341,304,20210325,20210222,20210228, 7.00) ,
(800341,304,20210325,20210222,20210228, -7.00) ,
(800341,301,20210423,20210301,20210301, 1.00) ,
(800341,301,20210325,20210301,20210301, -1.00) ,
(800341,304,20210423,20210301,20210314, -14.00),
(800341,302,20210325,20210302,20210314, -13.00),
(800341,302,20210423,20210302,20210314, 13.00) ,
(800341,301,20210525,20210408,20210408, -1.00) ,
(800341,302,20210525,20210409,20210421, -13.00),
(800341,305,20210525,20210422,20210430, -9.00) ,
(800341,305,20210624,20210501,20210514, -14.00),
(800341,305,20210624,20210504,20210514, -11.00),
(800341,305,20210624,20210515,20210531, -17.00),
(800341,305,20210723,20210601,20210630, -30.00),
(800341,305,20210825,20210701,20210731, -31.00),
(800341,305,20210924,20210801,20210815, -15.00),
(800341,305,20210924,20210816,20210831, -16.00),
(800341,305,20211025,20210901,20210905, -5.00) ,
(800341,305,20211025,20210906,20210923, -18.00),
(800341,305,20211025,20210929,20210930, -2.00) ,
(800341,305,20211125,20211001,20211003, -3.00) ,
(800341,305,20211125,20211004,20211025, -22.00),
(800341,305,20211125,20211026,20211026, -1.00 ),
(800341,305,20211125,20211027,20211031, -5.00) ,
(800341,302,20211220,20211201,20211215, -15.00)
// Best regards
-
When any row is mistakenly added by HR personal, they nullify it with another row to make a balance.
you will see more of such rows in the data which i recently included with Create table and insert into codes in my one of the reply.
Explanation / Tips ;
Just consider the FROMDate and TODATE columns. If the period is contiguous then it should be shown in one row of that period range..and do not pay attention to CompCode because ,in my desired Result table , there are only four columns, those are . Empnr, FromDate,ToDate,TotalDAYS. please refer to my excel picture previously added.
Please ask more if i am unclear at any point,
Regards
-
Thanks. I definitely missed your 301 comments below the 2nd spreadsheet you posted.
You say to only worry about the contiguous dates but neither the Green or the Purple sections in that same graphic have any contiguous dates and the Gold section has a gap in it, as well. What's going on there?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oops, my bad, I am posting again new spread sheet to clarify
Please have a look at it now, if its better explained now , however if there are more questions please do not hesitate to ask
Regards
//Asim
-
I have tried this following code but it did not fully worked, somehow it manages to cap date range,
SELECT
f.empnr,
f.FromDate ,
min(s.ToDate) AS ToDate ,
sum(f.totaldays) as TDays
FROM [dbo].[Result] f
INNER JOIN
[dbo].[Result] s ON
f.FromDate <= s.ToDate
AND NOT EXISTS(SELECT * FROM [dbo].[Result] f1
WHERE s.ToDate >= f1.FromDate
AND s.ToDate < f1.ToDate)
WHERE NOT EXISTS(SELECT * FROM [dbo].[Result] s1
WHERE f.FromDate > s1.FromDate
AND f.FromDate <= s1.ToDate)
GROUP BY f.Empnr,f.FromDate
ORDER BY f.empnr
//Regards
- skiper wrote:
Oops, my bad, I am posting again new spread sheet to clarify
Please have a look at it now, if its better explained now , however if there are more questions please do not hesitate to ask
Regards
//Asim
Ahhhhh!!! That's much better and much more like I expected. The info on the corrections and the corrected grouping work a treat. If someone doesn't beat me to what I think is a performant and correct solution, I should be able to hammer out something along with comments in the code that explain what's going on (YOU are the one that ultimately needs to support it or look real bad when you can't).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
<!--more-->
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply