CODEWITHSUNDEEP
sqlsql-servert-sqldaterecursion
user2390419
user2390419

Reputation: 61

for every row get all dates between columns and insert into column

I have table variable that I fill with all dates between a particular range (and the dw,wk,mm,yy,dd values)

I then join this to my inspection records by the start date.

SELECT     CalDate
           , DayN
           , WeekN
           , YearN
           , DayOfMonthN
           , [Start Date]
           , [End Date]
           , [Inspection Number] 
FROM       @Calendar             AS dateInfo
LEFT JOIN  [Inspection Records]  AS IR 
    ON     IR.[Start Date]       = dateInfo.CalendarDate

This works fine, what I am now needing to do (and i have to do it this way to pass to a 3rd party application...) is get all the dates between the [Start Date] and [End Date] and add them into a column as a comma separated list.

I've come across this answer t-sql get all dates between 2 dates that seems to get the dates perfectly for what I need.

I'm stuck on how to structure my script to use that script to get all the dates between my [Start Date] and [End Date] and add them into a column.

NOTE: I can not add anything to the database.

Example: (Trying to get the dates column)

  <table>
    <tr>
    <td>CalDate</td>
    <td>DayN</td>
    <td>WeekN</td>
    <td>YearN</td>
    <td>DayOfMonthN</td>
    <td>Start Date</td>
    <td>End Date</td>
    <td>Inspection Number</td>
    <td>Dates</td>
    </tr>
    <tr>
    <td>07-08-2014</td>
    <td>5</td>
    <td>32</td>
    <td>2014</td>
    <td>7</td>
    <td>07-08-2014</td>
    <td>11-08-2014</td>
    <td>A0001</td>
<td>07-08-2014,08-08-2014,09-08-2014,10-08-2014,11-08-2014</td>
    </tr>
    </table>

Upvotes: 0

Views: 365

Answers (2)

EricZ
EricZ

Reputation: 6205

SELECT     CalDate
           , DayN
           , WeekN
           , YearN
           , DayOfMonthN
           , [Start Date]
           , [End Date]
           , [Inspection Number]
           , [Dates] = STUFF((
                SELECT     ',' + CONVERT(VARCHAR(10), C.CalDate,105)
                FROM       @Calendar AS C
                WHERE      C.CalDate BETWEEN IR.[Start Date] AND IR.[End Date]
                FOR XML    PATH('')
            ), 1, 1, '')
FROM       @Calendar             AS dateInfo
LEFT JOIN  [Inspection Records]  AS IR 
    ON     IR.[Start Date]       = dateInfo.CalendarDate

Upvotes: 2

AntDC
AntDC

Reputation: 1907

This will put all your dates into a variable named @csv. I'm sure you'll be able to modify to suit your needs.......

DECLARE @csv nVarchar(max)
SELECT @csv = COALESCE(@csv + ', ', '') + CONVERT(Nvarchar(20), yourDateColumn) 
FROM yourDateTable

Upvotes: 0

Related Questions