CODEWITHSUNDEEP
sqlsql-serverreporting-services
gavo12345
gavo12345

Reputation: 3

Insert duplicate values into column

I'm writing a complex report in SSRS against a SQL server database. The database design seems a little unusual, and I am having to do more work that expected to create the SSRS datasets that meet the requirements and accommodate the various input parameters.

One challenge is that I now need to duplicate the values in the final column of a select statement; this is to allow me to conditionally supress rows in SSRS based on the date value. Cn anyone advise how to do this?

I've tried grouping on various columns and inserting into one or more temp tables.

select Question, Estate, Inspection_Date_Act
from #TempPIMSSRS

Current results of the simplified select statement are:

Col 1                  Col 2                Col 3
Inspection Date        Estate Name 1        23 Jan 2019
Fire Panels            Estate Name 1        NULL
Stairs                 Estate Name 1        NULL
Entrance               Estate Name 2        20 Mar 2019
Fire Panels            Estate Name 2        NULL
Stairs                 Estate Name 2        NULL

Desired results are:

Col 1                 Col 2                Col 3
Inspection Date       Estate Name 1        23 Jan 2019
Fire Panels           Estate Name 1        23 Jan 2019
Stairs                Estate Name 1        23 Jan 2019
Inspection Date       Estate Name 2        20 Mar 2019
Fire Panels           Estate Name 2        20 Mar 2019
Stairs                Estate Name 2        20 Mar 2019

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You seem to want the same date for everything based on Estate. You can use window functions:

select Question, Estate,
       max(Inspection_Date_Act) over (partition by Estate) as 
Inspection_Date_Act
from #TempPIMSSRS;

Upvotes: 4

Related Questions