CASE statement output in WHERE condition

Hi,
If you take a look at the below script, I have a CASE statement on both ends of the union that returns a date value (as 'Invoice Date').  I'm trying to allow filtering on that date, so I tried putting the case statement in the where condition, but not sure it's working.

Open to any suggestions
 Select  DISTINCT * FROM 
		( SELECT DISTINCT *
		,RANK() OVER(partition by X.[Workorder] order by X.[Fulfilled Date] desc ) as rank1 FROM
		( SELECT
			a.UsrDfnId as 'Workorder',
			a.CustomerName as 'Customer Name',
			a.PartNumber as 'Part Number',
			c.UsrDfnId as 'Part Type',
			convert(char(10),(a.DueDate),101) as 'Due Date',
			convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
						CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
				else convert(char(10),(b.FUFILDAT),101) 
				end as 'Invoice Date',
			--convert(char(10),(cc.DOCDATE),101) 
			a.WoState,
			a.SaleOrdNum,
			DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
			CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'   
			,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
		from 
			FORG.dbo.COSS_Workorder as a
			left outer join FORG.dbo.SOP30300 as b
				on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null
				and a.SaleOrdLineIdent = b.LNITMSEQ 
			left outer join FORG.dbo.SOP10201 aa
				on a.UsrDfnId = aa.SERLTNUM
			left outer join forg.dbo.SOP30300 bb
				on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
			left outer join forg.dbo.SOP30200 cc
				on bb.SOPNUMBE = cc.SOPNUMBE
			join FORG.dbo.COSS_PartType as c
				on a.PartTypeIdent = c.ident
				left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
		where 
				CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
				else convert(char(10),(b.FUFILDAT),101) 
				end 
			between '2015-07-01' and '2015-08-13'
			and h.EndTime is not null
			and a.WoState <> 'Cancelled'
			
		UNION ALL
		select 
			a.UsrDfnId as 'Workorder',
			a.CustomerName as 'Customer Name',
			a.PartNumber as 'Part Number',
			c.UsrDfnId as 'Part Type',
			convert(char(10),(a.DueDate),101) as 'Due Date',
			convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
						CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
				else convert(char(10),(b.FUFILDAT),101) 
				end as 'Invoice Date',
			--convert(char(10),(cc.DOCDATE),101) 
			a.WoState,
			a.SaleOrdNum,
			DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
			CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'   
			,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
		from 
			FORG.dbo.COSS_Workorder as a
			left outer join FORG.dbo.SOP10200 as b
				on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null 
				and a.SaleOrdLineIdent = b.LNITMSEQ
			left outer join FORG.dbo.SOP10201 aa
				on a.UsrDfnId = aa.SERLTNUM
			left outer join forg.dbo.SOP30300 bb
				on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
			left outer join forg.dbo.SOP30200 cc
				on bb.SOPNUMBE = cc.SOPNUMBE
			join FORG.dbo.COSS_PartType as c
				on a.PartTypeIdent = c.ident
				left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
		where 
				CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
				else convert(char(10),(b.FUFILDAT),101) 
				end 
			between '2015-07-01' and '2015-08-13' 
			and h.EndTime is not null
			and a.WoState <> 'Cancelled'
			) X
			Where X.rank = 1 ) Y
			Where Y.rank1 = 1
		order by 
			Y.[Due Date] asc

Open in new window

Mystical_IceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You can nest the cases:
where 1=
case when
                  CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then                             (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)     end  

between '2015-07-01' and '2015-08-13'  then 1 else 0 end
Mystical_IceAuthor Commented:
Thanks - is the above query missing any syntax? commas, parentheses, etc?  It's not running.  I have:

where
                  1= case when
                        (CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end
                  between '2015-07-01' and '2015-08-13'  then 1 else 0 end
                  and h.EndTime is not null
                  and a.WoState <> 'Cancelled'
Vikas GargAssociate Principal EngineerCommented:
HI,

Hope this will fix it

where
                 
                          case when
                  (
                          CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                  else convert(char(10),(b.FUFILDAT),101)
                  end
                          )
                  between '2015-07-01' and '2015-08-13'  then 1 else 0 end = 1
                  and h.EndTime is not null
                  and a.WoState <> 'Cancelled'
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Anoo S PillaiCommented:
Related to previous where clause - There is a ')' missing immediately before the BETWEEN

where 1 = case
              when(case
                       when(convert(char(10), (cc.DOCDATE), 101)) is not null 
		       then(convert(char(10),(cc.DOCDATE),101))
                       else convert(char(10),(b.FUFILDAT),101)
                   end) between '2015-07-01' and '2015-08-13' then 1
              else 0
          end
      and h.EndTime is not null
      and a.WoState <> 'Cancelled'

Open in new window

Mystical_IceAuthor Commented:
Still not getting any results.  
Select  DISTINCT * FROM 
            ( SELECT DISTINCT *
            ,RANK() OVER(partition by X.[Workorder] order by X.[Fulfilled Date] desc ) as rank1 FROM
            ( SELECT
                  a.UsrDfnId as 'Workorder',
                  a.CustomerName as 'Customer Name',
                  a.PartNumber as 'Part Number',
                  c.UsrDfnId as 'Part Type',
                  convert(char(10),(a.DueDate),101) as 'Due Date',
                  convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
                                    CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101) 
                        end as 'Invoice Date',
                  --convert(char(10),(cc.DOCDATE),101) 
                  a.WoState,
                  a.SaleOrdNum,
                  DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
                  CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'   
                  ,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
            from 
                  FORG.dbo.COSS_Workorder as a
                  left outer join FORG.dbo.SOP30300 as b
                        on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null
                        and a.SaleOrdLineIdent = b.LNITMSEQ 
                  left outer join FORG.dbo.SOP10201 aa
                        on a.UsrDfnId = aa.SERLTNUM
                  left outer join forg.dbo.SOP30300 bb
                        on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
                  left outer join forg.dbo.SOP30200 cc
                        on bb.SOPNUMBE = cc.SOPNUMBE
                  join FORG.dbo.COSS_PartType as c
                        on a.PartTypeIdent = c.ident
                        left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
            where 
                  --cc.DOCDATE 
                        --(CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        --else convert(char(10),(b.FUFILDAT),101) 
                        --end)
			1 = case
              when(case
                       when(convert(char(10), (cc.DOCDATE), 101)) is not null 
		       then(convert(char(10),(cc.DOCDATE),101))
                       else convert(char(10),(b.FUFILDAT),101)
                   end) between '2015-07-01' and '2015-08-13' then 1
              else 0
          end
      and h.EndTime is not null
      and a.WoState <> 'Cancelled'
                  
            UNION ALL
            select 
                  a.UsrDfnId as 'Workorder',
                  a.CustomerName as 'Customer Name',
                  a.PartNumber as 'Part Number',
                  c.UsrDfnId as 'Part Type',
                  convert(char(10),(a.DueDate),101) as 'Due Date',
                  convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
                                    CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101) 
                        end as 'Invoice Date',
                  --convert(char(10),(cc.DOCDATE),101) 
                  a.WoState,
                  a.SaleOrdNum,
                  DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
                  CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'   
                  ,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
            from 
                  FORG.dbo.COSS_Workorder as a
                  left outer join FORG.dbo.SOP10200 as b
                        on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null 
                        and a.SaleOrdLineIdent = b.LNITMSEQ
                  left outer join FORG.dbo.SOP10201 aa
                        on a.UsrDfnId = aa.SERLTNUM
                  left outer join forg.dbo.SOP30300 bb
                        on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
                  left outer join forg.dbo.SOP30200 cc
                        on bb.SOPNUMBE = cc.SOPNUMBE
                  join FORG.dbo.COSS_PartType as c
                        on a.PartTypeIdent = c.ident
                        left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
            where 

				1 = case
              when(case
                       when(convert(char(10), (cc.DOCDATE), 101)) is not null 
		       then(convert(char(10),(cc.DOCDATE),101))
                       else convert(char(10),(b.FUFILDAT),101)
                   end) between '2015-07-01' and '2015-08-13' then 1
              else 0
          end
      and h.EndTime is not null
      and a.WoState <> 'Cancelled'

          
                  and h.EndTime is not null
                  and a.WoState <> 'Cancelled'
                  ) X
                  Where X.rank = 1 ) Y
                  Where Y.rank1 = 1
            order by 
                  Y.[Due Date] asc

Open in new window

Anoo S PillaiCommented:
Would like to clarify the issue that you are facing now.
Is it a syntax error OR is it that the data expected is not outputted ?

I did not look into the issue that you posted , Just posted a comment on the syntax error you posted later ( missing parenthesis on the where clause )
Anoo S PillaiCommented:
I think you are trying to do a date comparison.  If that is the case it will not work as expected, because as per the code you are doing a character comparison. The following code will illustrate the concept.

DECLARE @TestDate1 DATE = '20150721' -- July 21 2015
-- Is July 21 is between July 1 and August 13 

IF CONVERT(CHAR(10),@TestDate1,101) BETWEEN '2015-07-01' and '2015-08-13'
	SELECT  'I am right', @TestDate1 , CONVERT(CHAR(10),@TestDate1,101)
ELSE
	SELECT ' I am wrong' , @TestDate1 , CONVERT(CHAR(10),@TestDate1,101)

-- Actual operation that is happening is a character comparioson   
-- Character comparison '07/21/2015' BETWEEN '2015-07-01' AND '2015-08-13'
SELECT '2015-07-01 is greater than 07/21/2015'   WHERE '2015-07-01' >= '07/21/2015'
SELECT '2015-08-13 is greater than 07/21/2015'   WHERE '2015-08-13' >= '07/21/2015'

Open in new window

Do a date comparison instead. I am not clear about the data type of source columns. If the columns that you are trying to compare are character columns that save date value in the format 'mm/dd/yyyy, you could easily convert that into equivalent dates using convert function. See following example
SELECT CONVERT(DATE, '07/21/2015', 101 ) 

Open in new window

Scott PletcherSenior DBACommented:
With outer joins, you need to put conditions in the join clause, not the where clause.  And, as always, don't convert a table column to do a comparison unless it's absolutely required.  For dates/datetimes, see the code below for the preferred alternative coding style.

Select  DISTINCT * FROM
            ( SELECT DISTINCT *
            ,RANK() OVER(partition by X.[Workorder] order by X.[Fulfilled Date] desc ) as rank1 FROM
            ( SELECT
                  a.UsrDfnId as 'Workorder',
                  a.CustomerName as 'Customer Name',
                  a.PartNumber as 'Part Number',
                  c.UsrDfnId as 'Part Type',
                  convert(char(10),(a.DueDate),101) as 'Due Date',
                  convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
                                    CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end as 'Invoice Date',
                  --convert(char(10),(cc.DOCDATE),101)
                  a.WoState,
                  a.SaleOrdNum,
                  DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
                  CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'  
                  ,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
            from
                  FORG.dbo.COSS_Workorder as a
                  left outer join FORG.dbo.SOP30300 as b
                        on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null
                        and a.SaleOrdLineIdent = b.LNITMSEQ
                  left outer join FORG.dbo.SOP10201 aa
                        on a.UsrDfnId = aa.SERLTNUM
                  left outer join forg.dbo.SOP30300 bb
                        on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
                  left outer join forg.dbo.SOP30200 cc
                        on bb.SOPNUMBE = cc.SOPNUMBE and
                              cc.DOCDATE >= '20150701' and cc.DOCDATE < '20150814'
                  join FORG.dbo.COSS_PartType as c
                        on a.PartTypeIdent = c.ident
                        left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
            where
                  h.EndTime is not null
                  and a.WoState <> 'Cancelled'
                 
            UNION ALL
            select
                  a.UsrDfnId as 'Workorder',
                  a.CustomerName as 'Customer Name',
                  a.PartNumber as 'Part Number',
                  c.UsrDfnId as 'Part Type',
                  convert(char(10),(a.DueDate),101) as 'Due Date',
                  convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
                                    CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end as 'Invoice Date',
                  --convert(char(10),(cc.DOCDATE),101)
                  a.WoState,
                  a.SaleOrdNum,
                  DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
                  CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'  
                  ,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
            from
                  FORG.dbo.COSS_Workorder as a
                  left outer join FORG.dbo.SOP10200 as b
                        on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null
                        and a.SaleOrdLineIdent = b.LNITMSEQ
                  left outer join FORG.dbo.SOP10201 aa
                        on a.UsrDfnId = aa.SERLTNUM
                  left outer join forg.dbo.SOP30300 bb
                        on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
                  left outer join forg.dbo.SOP30200 cc
                        on bb.SOPNUMBE = cc.SOPNUMBE and
                              cc.DOCDATE >= '20150701' and cc.DOCDATE < '20150814'
                  join FORG.dbo.COSS_PartType as c
                        on a.PartTypeIdent = c.ident
                        left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
            where
                  h.EndTime is not null
                  and a.WoState <> 'Cancelled'
                  ) X
                  Where X.rank = 1 ) Y
                  Where Y.rank1 = 1
            order by
                  Y.[Due Date] asc
PortletPaulEE Topic AdvisorCommented:
WHERE
 CASE
    WHEN (CONVERT(char(10), (cc.DOCDATE), 101)) IS NOT NULL THEN (CONVERT(char(10), (cc.DOCDATE), 101))
    ELSE CONVERT(char(10), (b.FUFILDAT), 101)
 END
BETWEEN '2015-07-01' AND '2015-08-13'

Open in new window

Don't convert date/datetime data into varchar values when it isn't needed. Doing that will just slow down your query. So as a first step remove all the conversions.
WHERE
 CASE
    WHEN cc.DOCDATE IS NOT NULL THEN cc.DOCDATE
    ELSE b.FUFILDAT
 END
BETWEEN '2015-07-01' AND '2015-08-13'

Open in new window

and, once you remove those conversions it can be seen that all you wanted is a substitution of b.FUFILDAT if cc.DOCDATE is absent and that can be achieved using ISNULL() like this:

              ISNULL(cc.DOCDATE, b.FUFILDAT) BETWEEN '2015-07-01' AND '2015-08-13'

However, it is not good practice to use between* for date ranges, and this form is better:

             WHERE ( ISNULL(cc.DOCDATE, b.FUFILDAT) >= '20150701'
                       AND  ISNULL(cc.DOCDATE, b.FUFILDAT) < '20150814' )

Then, there is the issue that Scott Pletcher has identified. Both the tables FORG.dbo.SOP10200 (b) & forg.dbo.SOP30200 (cc) are LEFT JOINed. If you reference those tables through the where clause you run the risk of overriding the ouetr join.

If BOTH cc.DOCDATE & b.FUFILDAT are NULL what do you expect to happen?

--
* see: "Beware of Between"
Scott PletcherSenior DBACommented:
NEVER use ISNULL() (against a table column) in a WHERE or JOIN clause.  One should always try to avoid functions against table columns in WHERE and JOIN, and, since ISNULL() can ALWAYS be coded differently, NEVER use it.

WHERE
    ((cc.DOCDATE >= '20150701' AND cc.DOCDATE < '20150814') OR
     (cc.DOCDATE IS NULL AND b.FUFILDAT >= '20150701' AND b.FUFILDAT < '20150814'))
PortletPaulEE Topic AdvisorCommented:
good point, do always avoid functions on data in joins & where clause if they can be.

but I am  still wondering: What is required of the query if BOTH cc.DOCDATE & b.FUFILDAT are null?
---

btw: you don't need to convert a date column into a varchar column to test if it is null

e.g.
select ...
CASE
      WHEN (CONVERT(char(10), (cc.DOCDATE), 101)) IS NOT NULL
          THEN (CONVERT(char(10), (cc.DOCDATE), 101))
           ELSE CONVERT(char(10), (b.FUFILDAT), 101)
END AS 'Invoice Date'

can be:
select ...
CASE
      WHEN cc.DOCDATE IS NOT NULL
          THEN (CONVERT(char(10), (cc.DOCDATE), 101))
           ELSE CONVERT(char(10), (b.FUFILDAT), 101)
END AS 'Invoice Date'
or, even these:
select ...
CONVERT(char(10),
    CASE WHEN cc.DOCDATE IS NOT NULL THEN cc.DOCDATE ELSE b.FUFILDAT END
       , 101)

select ...
CONVERT(char(10), ISNULL(cc.DOCDATE,b.FUFILDAT), 101) AS 'Invoice Date'
Vitor MontalvãoMSSQL Senior EngineerCommented:
Mystical_Ice, do you still need help with this question?
Scott PletcherSenior DBACommented:
Note that ISNULL() is perfectly fine in the SELECT list.  It just should never be used in any WHERE or JOIN ON clauses.  So:
SELECT ISNULL(colA, ...) AS colA, ISNULL(colB, ...) AS New_Date, etc.
is good, no problem there at all.
Mystical_IceAuthor Commented:
Got this to work:

Select  DISTINCT * FROM
            ( SELECT DISTINCT *
            ,RANK() OVER(partition by X.[Workorder] order by X.[Fulfilled Date] desc ) as rank1 FROM
            ( SELECT
                  a.UsrDfnId as 'Workorder',
                  a.CustomerName as 'Customer Name',
                  a.PartNumber as 'Part Number',
                  c.UsrDfnId as 'Part Type',
                  convert(char(10),(a.DueDate),101) as 'Due Date',
                  convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
                                    CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end as 'Invoice Date',
                  --convert(char(10),(cc.DOCDATE),101)
                  a.WoState,
                  a.SaleOrdNum,
                  DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
                  CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'  
                  ,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
            from
                  FORG.dbo.COSS_Workorder as a
                  left outer join FORG.dbo.SOP30300 as b
                        on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null
                        and a.SaleOrdLineIdent = b.LNITMSEQ
                  left outer join FORG.dbo.SOP10201 aa
                        on a.UsrDfnId = aa.SERLTNUM
                  left outer join forg.dbo.SOP30300 bb
                        on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
                  left outer join forg.dbo.SOP30200 cc
                        on bb.SOPNUMBE = cc.SOPNUMBE
                  join FORG.dbo.COSS_PartType as c
                        on a.PartTypeIdent = c.ident
                        left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
            where
                  --cc.DOCDATE
                        CONVERT(SMALLDATETIME,(CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end)) between '20150701' and '20150813'
                 and h.EndTime is not null
                  and a.WoState <> 'Cancelled'
                 
            UNION ALL
            select
                  a.UsrDfnId as 'Workorder',
                  a.CustomerName as 'Customer Name',
                  a.PartNumber as 'Part Number',
                  c.UsrDfnId as 'Part Type',
                  convert(char(10),(a.DueDate),101) as 'Due Date',
                  convert(char(10),(b.FUFILDAT),101) as 'Fulfilled Date',
                                    CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end as 'Invoice Date',
                  --convert(char(10),(cc.DOCDATE),101)
                  a.WoState,
                  a.SaleOrdNum,
                  DATEDIFF(d,convert(char(10),(a.DueDate),101),convert(char(10),(h.EndTime),101)) AS OTD,
                  CONVERT(char(10),h.EndTime,101) as 'Final Inspect Completed'  
                  ,RANK() OVER(partition by a.UsrDfnId order by h.endtime desc) as rank
            from
                  FORG.dbo.COSS_Workorder as a
                  left outer join FORG.dbo.SOP10200 as b
                        on a.SaleOrdNum = b.SOPNUMBE and b.SOPNUMBE is not null
                        and a.SaleOrdLineIdent = b.LNITMSEQ
                  left outer join FORG.dbo.SOP10201 aa
                        on a.UsrDfnId = aa.SERLTNUM
                  left outer join forg.dbo.SOP30300 bb
                        on aa.SOPNUMBE = bb.SOPNUMBE and aa.LNITMSEQ = bb.LNITMSEQ
                  left outer join forg.dbo.SOP30200 cc
                        on bb.SOPNUMBE = cc.SOPNUMBE
                  join FORG.dbo.COSS_PartType as c
                        on a.PartTypeIdent = c.ident
                        left join FORG.dbo.COSS_workorderoprthistdetail h on a.ident = h.WorkOrderIdent and h.OprtIdent = '20CDB1FA-F7E0-484E-ABC1-40D0542E857E'
            where
                 CONVERT(SMALLDATETIME,(CASE when (convert(char(10),(cc.DOCDATE),101)) IS NOT NULL then (convert(char(10),(cc.DOCDATE),101))
                        else convert(char(10),(b.FUFILDAT),101)
                        end)) between '20150701' and '20150813'
                  and h.EndTime is not null
                  and a.WoState <> 'Cancelled'
                  ) X
                  Where X.rank = 1 ) Y
                  Where Y.rank1 = 1
            order by
                  Y.[Due Date] asc


Weird - any idea why doing the above (note bolded) did the trick?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.