CODEWITHSUNDEEP
sqlsql-server-2008common-table-expression
Ali
Ali

Reputation: 85

Append data to split rows

I want to know how many people weren't available in months historically, for that I have an historicTable which contains data from 2012 to 2018 and each row contains how much time an employee wasn't available (vacations, sickness, etc.) this is one example:

idUser startDate    endDate     daysUn    reason    nameEmp
--------------------------------------------------------
123    25/01/2018   09/02/2018  12        Sickness  John Doe

This is what I need for every row

idUser startDate    endDate     daysUn    reason    nameEmp
--------------------------------------------------------
123    25/01/2018   31/01/2018   5        Sickness  John Doe
123    01/01/2018   09/02/2018   7        Sickness  John Doe

I know this been asked hundred of times here but I'm having trouble doing this for an entire table, for what I've tried in different answers all process work for specific given startdate and enddate columns, and what I need it's to append ALL data to this table and save it as-is so the analyst will be able to study specific cases and specific employees. This is what I get with my current code:

original_INI            original_FIN            new_INI                 new_FIN
----------------------- ----------------------- ----------------------- -----------------------
2017-10-15 00:00:00.000 2018-01-06 00:00:00.000 2017-10-15 00:00:00.000 2017-10-31 00:00:00.000
2017-10-15 00:00:00.000 2018-01-06 00:00:00.000 2017-11-01 00:00:00.000 2017-11-30 00:00:00.000
2017-10-15 00:00:00.000 2018-01-06 00:00:00.000 2017-12-01 00:00:00.000 2017-12-31 00:00:00.000
2017-10-15 00:00:00.000 2018-01-06 00:00:00.000 2018-01-01 00:00:00.000 2018-01-06 00:00:00.000

This is the code, original dates are ok as I can sort data more globally but it could print and save the rest of the data so it's more readable:

;WITH n(n) AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(n,f,t,md,bp,ep) AS 
(
  SELECT n.n, d.INI, d.FIN, 
    DATEDIFF(MONTH, d.INI, d.FIN),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(INI), INI)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
      DATEADD(DAY, 1-DAY(INI), INI))))
 FROM n INNER JOIN archivoFuente AS d 
 ON d.FIN >= DATEADD(MONTH, n.n-1, d.INI)
)
SELECT original_INI = f, original_FIN = t, 
  new_INI = CASE n WHEN 0  THEN f ELSE bp END,
  new_FIN   = CASE n WHEN md THEN t ELSE ep END 
FROM d WHERE md >= n
ORDER BY original_INI, new_INI;

Any help with the query it's appreciated.

Upvotes: 3

Views: 61

Answers (1)

Abdón Araya
Abdón Araya

Reputation: 186

It's pretty easy actually, I used the same code for my requirements, you need to call each column in each select statement so it exist when you split the rows, check this code:

;WITH n(n) AS 
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(n,f,t,md,bp,ep,
--CALL YOUR COLUMNS HERE EG: name, id, bla, ble
) AS  
(
SELECT n.n,d.INI, d.FIN,
    DATEDIFF(MONTH, d.INI, d.FIN),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(INI), INI)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
    DATEADD(DAY, 1-DAY(INI), INI)))),
    --CALL YOUR COLUMNS HERE AGAIN, PAY ATTENTION TO NAMES AND COMMAS
    d.id_hr,d.Tipo,d.ID_tip,d.Nom_inc,d.RUT,d.Nombre,d.ID_emp,d.Nom_pos,d.Dias_durac,d.Num_lic,d.ID_usu_ap,d.ult_act
FROM n INNER JOIN archivoFuente AS d 
ON d.FIN >= DATEADD(MONTH, n.n-1, d.INI)
)
SELECT --PUT ONCE AGAIN YOUR COLUMNS HERE, THIS WILL WORK FOR THE DISPLAYED RESULT
original_INI = f, original_FIN = t,
new_INI = CASE n WHEN 0  THEN f ELSE bp END,
new_FIN = CASE n WHEN md THEN t ELSE ep END
FROM d 
WHERE md >= n
ORDER BY original_INI, new_INI;

Now, to save the table, I'd recommend using an INSERT statement to a new table, how will you do it, I don't know, I'am in the same spot as you. Hope someone check this question.

Upvotes: 2

Related Questions