generate date rows between 2 dates

Database
Enthusiast

generate date rows between 2 dates

with the following code is it possible to generate any date rows between DT1 and DT2 so as to join all the rows to a DT_DIM table?  DT1 and DT2 are the starting and ending dates.  could be several days between them or just 1 or 2.

 

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 10166 StartFragment: 314 EndFragment: 10134 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 9208 StartFragment: 314 EndFragment: 9176 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet
SELECT 
 a.RDE_LOAN_KY ,CAST(b.CLR_TO_CLS_DTTM AS DATE) DT1 ,d1.cal_dt ,d1.BUS_DY_IND ,d1.DY_OF_WK_CNT 
 ,CAST(c.CLS_PREP_PKG_DLVR_DTTM AS DATE) DT2 ,d2.cal_dt ,d2.bus_dy_ind ,d2.dy_of_wk_cnt
FROM
    db1.LOAN_DIM A
    INNER JOIN db1.MLSTN_DIM B ON A.RDE_LOAN_KY = B.RDE_LOAN_KY
    INNER JOIN db1.dt_dim d1 ON d1.cal_DT = dt1
    INNER JOIN db1.DOC_PKG_DIM C     ON A.RDE_LOAN_KY = C.RDE_LOAN_KY
    INNER JOIN db1.dt_dim d2 ON d2.cal_dt = dt2
WHERE
B.CLR_TO_CLS_DTTM IS NOT NULL AND C.CLS_PREP_PKG_DLVR_DTTM IS NOT NULL;


 

2 REPLIES
Teradata Employee

Re: generate date rows between 2 dates

How about

 

SELECT
 a.RDE_LOAN_KY ,CAST(b.CLR_TO_CLS_DTTM AS DATE) DT1 ,d1.cal_dt ,d1.BUS_DY_IND ,d1.DY_OF_WK_CNT
 ,CAST(c.CLS_PREP_PKG_DLVR_DTTM AS DATE) DT2
FROM
    db1.LOAN_DIM A
    INNER JOIN db1.MLSTN_DIM B ON A.RDE_LOAN_KY = B.RDE_LOAN_KY
    INNER JOIN db1.DOC_PKG_DIM C     ON A.RDE_LOAN_KY = C.RDE_LOAN_KY
    JOIN db1.dt_dim d1 ON d1.cal_DT between dt1 and dt2
WHERE
B.CLR_TO_CLS_DTTM IS NOT NULL AND C.CLS_PREP_PKG_DLVR_DTTM IS NOT NULL;

 

It wouldn't look the same but I think it would give you the data you need.

Senior Apprentice

Re: generate date rows between 2 dates

What exactly are you trying to do, can you show some example rows?

You might be able to apply EXPAND ON...