USP_DATALIST_APPUSERWORKSPACEBATCH

CREATE procedure dbo.USP_DATALIST_APPUSERWORKSPACEBATCH
(
    @CURRENTAPPUSERID uniqueidentifier,
    @INCLUDECOMMITED bit = 0
)
as
    set nocount on;

    declare @DATE datetime;
    set @DATE = getDate();

    select
        BATCH.ID,
        case
            when BATCHWORKFLOWSTATE.OVERDUEIN > 0 and datediff(day, @DATE, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc)) < 0 then 
            1
        else 
            0
        end [OVERDUE],
        case
            when BATCHWORKFLOWSTATE.OVERDUEIN > 0 and datediff(day, @DATE, (select top 1 BATCHWORKFLOWSTATUS.DATEADDED from dbo.BATCHWORKFLOWSTATUS where BATCHWORKFLOWSTATUS.BATCHID = BATCH.ID and BATCH.BATCHWORKFLOWSTATEID=BATCHWORKFLOWSTATUS.BATCHWORKFLOWSTATEID order by BATCHWORKFLOWSTATUS.DATEADDED desc)) < 0 then 
                'RES:warning'
            when BATCH.STATUSCODE = 1 then
                'RES:checkmark'
            else 
                'RES:lv_spacer'
        end [IMAGE],
        BATCH.BATCHNUMBER,
        BATCH.DESCRIPTION,
        BATCHWORKFLOWSTATE.NAME as STATUS,
        BATCHTYPECATALOG.CATEGORY,
        BATCH.DATECOMMITTED,
        dbo.UFN_BATCHWORKFLOWSTATE_TASKSAVAILABLE(BATCH.BATCHWORKFLOWSTATEID)
    from
        dbo.BATCH
    inner join
        dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
    inner join
        dbo.BATCHTYPECATALOG on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
    left join
        dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID
    where
        BATCH.APPUSERID = @CURRENTAPPUSERID
        and ( (BATCH.STATUSCODE <> 1) or (@INCLUDECOMMITED = 1) )
        and BATCH.STATUSCODE <> 2
    order by
        BATCH.BATCHNUMBER;

    return 0;