Calculating DATEDIFF on "non valid" columns SQL Server 2008

Question

I have code that splits records on months based on their length, the code currently works without problem, but I need to calculate how many days passed between these new records so I could get something like this:

id  begdate     enddate     days newbeg      newend      
---------------------------------------------------------
123 03/04/2018  03/05/2018  31   03/04/2018  30/04/2018  
123 03/04/2018  03/05/2018  31   01/04/2018  03/04/2018

What I need to display

id  begdate     enddate     days newbeg      newend      days2
---------------------------------------------------------------
123 03/04/2018  03/05/2018  31   03/04/2018  30/04/2018  28
123 03/04/2018  03/05/2018  31   01/04/2018  03/04/2018   4

In my code I created a CTE taken from here to split rows and on the final select I'm using a DATEDIFF for newbeg and newend but I get an Error 207, Level 16, State 1, Procedure xx because newbeg and newend aren't valid on the table

;WITH n(n) AS
 --first select goes here
(
--SECOND SELECT
)
SELECT
    --COLUMNS FROM ORIGINAL TABLE
    --DISP NEW COL
    BEGDATE = f, ENDDATE = t,
    --SPLITTED COL
    NEWBEG = CASE n WHEN 0  THEN f ELSE bp END,
    NEWEND = CASE n WHEN md THEN t ELSE ep END,
    --CALC NEW PERDIODS WHERE I GET Msg 207
    DATEDIFF(dd, NEWBEG, NEWEND) as DAYS2
FROM 
    d 
WHERE 
    md >= n
ORDER BY 
    BEGDATE, NEWBEG;

Maybe this is not the appropriate approach or I'm doing something wrong, any help is thanked in advance.

score 1 · Accepted Answer · answered Feb 04 '19 at 17:45

1

Just use another CTE:

WITH n(n) AS (
 --first select goes here
     ),
     d as (
     --SECOND SELECT
     ),
     e as (
SELECT --COLUMNS FROM ORIGINAL TABLE
       --DISP NEW COL
       BEGDATE = f, ENDDATE = t,
    --SPLITTED COL
       NEWBEG = CASE n WHEN 0  THEN f ELSE bp END,
       NEWEND = CASE n WHEN md THEN t ELSE ep END
      FROM d 
      WHERE md >= n
     )
SELECT e.*,
    --CALC NEW PERDIODS WHERE I GET Msg 207
       datediff(day, NEWBEG, NEWEND) as DAYS2
FROM e
ORDER BY BEGDATE, NEWBEG;

answered Feb 04 '19 at 17:45

Gordon Linoff

1,242,037
58
646
786

Working perfectly, I have other functions (about 3 as stored procedures) that do similiar things (calculations within the columns), could be more heavier to the sql to process all at once in a big CTE rather than storing this table and the work through it? (considering the 100k+ rows). – Abdón Araya Feb 04 '19 at 18:20
@Kurox . . . No. The CTEs should not add overhead to the process for a query logic that is referred to only once. – Gordon Linoff Feb 04 '19 at 18:24
I did put another cte in the query but gets overlapped with `days2` (display one of the other), this other select generates an id based on YYYYDD and on begdate, do you know why? did I reach a limit for the cte? This is the query : `select e.*,convert(nvarchar(6),newbeg,112) as perid from e ORDER BY BEGDATE, NEWBEG` – Abdón Araya Feb 04 '19 at 18:39
@Kurox . . . `order by` is not appropriate in a CTE. – Gordon Linoff Feb 04 '19 at 19:13

Calculating DATEDIFF on "non valid" columns SQL Server 2008

1 Answers1