SQLTeam.com | Weblogs | Forums

Report Builder Min Value and Max Value from Same Dataset

#1

I am attempting to extract data from a database that has an error in it. I can't resolve the error (it's a "design feature"), so i have to try to query around it. Here's how it is stored.

Record ID | Create Date | Update Date | Record Status
123       | 05/01/2018  | 05/01/2018  | Active
123       | 05/08/2018  | 05/08/2018  | Active
123       | 05/15/2018  | 05/15/2018  | Closed
123       | 05/22/2018  | 05/22/2018  | Closed
456       | 06/02/2018  | 06/02/2018  | Pending
456       | 06/09/2018  | 06/09/2018  | Active
456       | 06/16/2018  | 06/16/2018  | Active
456       | 06/23/2018  | 06/23/2018  | Suspended

And so on. As you can see, the Create Date and Update Date values match on each row. The Create Date value is supposed to be the date the Record ID was initially created, but it's actually being captured as the date the Record ID update was created.

What I need is a report that brings me a single row per Record ID that shows me the minimum Create Date and the maximum Update Date, so that the result looks something like this:

Record ID | Create Date | Update Date | Record Status
123       | 05/01/2018  | 05/22/2018  | Closed
456       | 06/02/2018  | 06/23/2018  | Suspended

I've tried using the MIN and MAX aggregate functions in the Query Designer, and that works just fine until I add any other field that may change through the life of the record. I get this:

Record ID | Create Date | Update Date | Record Status
123       | 05/01/2018  | 05/08/2018  | Active
123       | 05/15/2018  | 05/22/2018  | Closed
456       | 06/02/2018  | 06/02/2018  | Pending
456       | 06/09/2018  | 06/16/2018  | Active
456       | 06/23/2018  | 06/23/2018  | Suspended

When I use the Query Designer, the query text looks like this:

SELECT
  DB.RECORD.RECORD_ID
  ,DB.RECORD.RECORD_STATUS_CODE
  ,MAX(DB.RECORD.RECORD_CREATED_DATE) AS Max_RECORD_CREATED_DATE
  ,MIN(DB.RECORD.RECORD_UPDATED_DATE) AS Min_RECORD_UPDATED_DATE
FROM
  DB.RECORD
GROUP BY
  DB.RECORD.RECORD_ID
  ,DB.RECORD.RECORD_STATUS_CODE

I'm relatively new to Report Builder, though I think I'm picking up its concepts quickly. What am I missing here?

#2

Next time, please post your question with sample DDL and DML meaning

create table #RECORD(RecordId int, CreateDate date, 
updateDate Date, RecordStatus varchar(10))

insert into #RECORD
select 123, '05/01/2018', '05/08/2018', Active union --etc

please try this.

;with src
as
(
		SELECT
		  mahalo.RECORD_ID
		  ,mahalo.RECORD_STATUS_CODE
		  ,MAX(mahalo.RECORD_CREATED_DATE) AS Max_RECORD_CREATED_DATE
		  ,MIN(mahalo.RECORD_UPDATED_DATE) AS Min_RECORD_UPDATED_DATE
		FROM  DB.RECORD mahalo
		GROUP BY
		  mahalo.RECORD_ID
		  ,mahalo.RECORD_STATUS_CODE
)
select * 
  from db.record aloha 
where exists (
				select * 
				from src mahalo
		where aloha.RECORD_ID = mahalo.RECORD_ID
		  and aloha.RECORD_STATUS_CODE = mahalo.RECORD_STATUS_CODE
		  and aloha.RECORD_CREATED_DATE = mahalo.Max_RECORD_CREATED_DATE
		  and aloha.RECORD_UPDATED_DATE = mahalo.Min_RECORD_UPDATED_DATE
  )
``
#3

To get the last status is trickier. You'd need something like this:

SELECT RECORD_ID, Min_RECORD_CREATED_DATE AS Create_Date, Max_RECORD_UPDATED_DATE AS Update_Date,
    SUBSTRING(Last_RECORD_STATUS_CODE, 9, 40) AS Record_Status
FROM (
    SELECT
      DB.RECORD.RECORD_ID
      ,MIN(DB.RECORD.RECORD_CREATED_DATE) AS Min_RECORD_CREATED_DATE
      ,MAX(DB.RECORD.RECORD_UPDATED_DATE) AS Max_RECORD_UPDATED_DATE
      ,MAX(CONVERT(varchar(8), DB.RECORD.RECORD_UPDATED_DATE, 112)) + 
           DB.RECORD.RECORD_STATUS_CODE AS Last_RECORD_STATUS_CODE
    FROM
      DB.RECORD
    GROUP BY
      DB.RECORD.RECORD_ID
) AS x
ORDER BY RECORD_ID
#4

Maybe I am missing something...but this should get you what you are asking for:

Declare @testTable Table (
        RecordID int
      , CreateDate date
      , UpdateDate date
      , RecordStatus varchar(10)
        );

 Insert Into @testTable (RecordID,CreateDate, UpdateDate, RecordStatus)
 Values (123, '2018-05-01', '2018-05-01', 'Active')
      , (123, '2018-05-08', '2018-05-08', 'Active')
      , (123, '2018-05-15', '2018-05-15', 'Closed')
      , (123, '2018-05-22', '2018-05-22', 'Closed')
      , (456, '2018-06-02', '2018-06-02', 'Pending')
      , (456, '2018-06-09', '2018-06-09', 'Active')
      , (456, '2018-06-16', '2018-06-16', 'Active')
      , (456, '2018-06-23', '2018-06-23', 'Suspended');
        
   With recordDates
     As (
 Select *
      , MinDate = min(tt.CreateDate) over(Partition By tt.RecordID)
      , MaxDate = max(tt.CreateDate) over(Partition By tt.RecordID)
      , CurrentRecord = row_number() over(Partition By tt.RecordID Order By tt.CreateDate desc)
   From @testTable tt
        )
 Select *
   From recordDates     rd
  Where rd.CurrentRecord = 1;

This could also be done in SSRS/Report Builder within a tablix - by grouping on the RecordID and only showing the group level. The detail level could be hidden or you could set the detail to be visible using a drilldown.

#5

I don't think gives the final Record Status.

#6

The final (current) status will be on the row with the most recent created/updated date. The row_number() identifies that row and will be the row returned.

The max() is not actually needed - since the latest row is being returned it can be pulled from the created/updated column.

   With recordDates
     As (
 Select *
      , MinDate = min(tt.CreateDate) over(Partition By tt.RecordID)
      , CurrentRecord = row_number() over(Partition By tt.RecordID Order By tt.CreateDate desc)
   From @testTable tt
        )
 Select rd.RecordID
      , CreateDate = rd.MinDate
      , rd.UpdateDate
      , rd.RecordStatus
   From recordDates     rd
  Where rd.CurrentRecord = 1;
#7

True enough. But the query requires another level of sort and lazy spools. Just depends on how many rows and how the performance is, vs:

SELECT RECORDID, Min_RECORD_CREATED_DATE AS Create_Date, Max_RECORD_UPDATED_DATE AS Update_Date,
    SUBSTRING(Last_RECORD_STATUS_CODE, 9, 40) AS Record_Status
FROM (
    SELECT
      tt.RECORDID
      ,MIN(tt.CREATEDATE) AS Min_RECORD_CREATED_DATE
      ,MAX(tt.UPDATEDATE) AS Max_RECORD_UPDATED_DATE
      ,MAX(CONVERT(varchar(8), tt.UPDATEDATE, 112) + tt.RECORDSTATUS) AS Last_RECORD_STATUS_CODE
    FROM
      #testTable tt
    GROUP BY
      tt.RECORDID
) AS x
ORDER BY RECORDID
#8

I tried your suggestion. I had to argue with it on syntax for a bit (I had an extra . in there somewhere). When I finally got that resolved, Report Builder gave me an error message: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

#9

Yeah, sorry about the syntax, I had some stuff out of place before.

Try the version in the last query I posted.

#10

What are the "#testTable tt" and "tt." references in that query? I'm not sure what to do with those.
(Sorry. I'm still new to SQL code. Until this current assignment, all the reports I've had to write could be done with the visual designer with maybe a few manual modifications to a Where clause.)

#11

They're just sample data to be able to run the query.

It looks like your original table name was "DB.RECORD", so you would replace #testTable with DB.RECORD.

The "tt" is a table alias -- a short name for the table that you can use in the query to indicate which table a column comes from. You can leave the alias "tt" or change it to something like "REC" (short for RECORD) or whatever else you want to use.

#12

Still no luck. I'm not sure what I'm doing wrong. Or maybe its a problem in the software I'm using or the database itself. I'll have to pass this one back to our development team (and wait several months for a reply due to how overworked and understaffed they are), or pull the data into Excel and fight with it that way.

Thanks for trying anyway. It was worth a shot.

#13

hi

i tried to do this hope this helps :slight_smile: :slight_smile:

please click arrow to the left for DROP Create SAMPLE Data 
drop table #testTable 
go 

create table  #testTable  (
        RecordID int
      , CreateDate date
      , UpdateDate date
      , RecordStatus varchar(10)
        );

 Insert Into #testTable (RecordID,CreateDate, UpdateDate, RecordStatus)
 Values (123, '2018-05-01', '2018-05-01', 'Active')
      , (123, '2018-05-08', '2018-05-08', 'Active')
      , (123, '2018-05-15', '2018-05-15', 'Closed')
      , (123, '2018-05-22', '2018-05-22', 'Closed')
      , (456, '2018-06-02', '2018-06-02', 'Pending')
      , (456, '2018-06-09', '2018-06-09', 'Active')
      , (456, '2018-06-16', '2018-06-16', 'Suspended')

select * from #testTable
go

image

please click arrow to the left for SQL ... using cte's 
; with cte as 
(
select   RecordID,max(UpdateDate) as maxupdate FROM #testTable GROUP BY   RecordID
), cte_status as 
(
select a.* from #testTable a join cte b on a.RecordID = b.RecordID and a.UpdateDate = b.maxupdate
) , cte_mincreate_date as 
(
select recordid,min(CreateDate) as mincreadate from #testTable group by recordid 
)
select 
      a.RecordID
    , a.mincreadate
	, b.UpdateDate
	, b.RecordStatus 
from 
    cte_mincreate_date a 
	   join 
	cte_status b 
	   on a.RecordID = b.RecordID

image

#14

This looks promising. I'll give it a try.
Thank you!

#15

Another one

SELECT DISTINCT recordId 
, FIRST_VALUE(createDate) OVER(PARTITION BY recordId ORDER BY createDate asc) [Create Date]
, LAST_VALUE(updateDate) OVER(PARTITION BY recordId ORDER BY updateDate ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) [Update Date]
, LAST_VALUE(recordStatus) OVER(PARTITION BY recordId ORDER BY updateDate ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) [Record Status]
FROM #tmp
1 Like
#16

THIS looks Nice

I like it femiolan !!!

1 Like
#17

Thanks harishgg1!