我需要与此 CTE 查询兼容的 SQL Server 2000 替代方案

Question

以下查询连接多个表，然后创建一个名为MODIFIEDDATETIME的列，该列设置为最新的MODIFIEDTIME/MODIFIEDDATE。

MODIFIEDTIME从午夜开始在几秒钟内存储。我需要使此查询在 SQL Server 2000 上运行，并希望它尽可能简单。有什么想法吗？

编辑 - 我正在尝试在不使用临时表的情况下执行此操作。

;with CTE as 
(
select 
MODIFIEDDATETIME = (select max(modified)
from (values (dateadd(second, cij.MODIFIEDTIME, cij.MODIFIEDDATE)),
(dateadd(second, cn1.MODIFIEDTIME, cn1.MODIFIEDDATE)),
(dateadd(second, st1.MODIFIEDTIME, st1.MODIFIEDDATE)),
(dateadd(second, csg.MODIFIEDTIME, csg.MODIFIEDDATE)),
(dateadd(second, slo.MODIFIEDTIME, slo.MODIFIEDDATE)),
(dateadd(second, dsc.MODIFIEDTIME, dsc.MODIFIEDDATE)),
(dateadd(second, cms.MODIFIEDTIME, cms.MODIFIEDDATE))) as dateList (modified))
from 
dbo.HeaderTable cij
left join 
dbo.COUNTY cn1 on cn1.COUNTYID = cij.COUNTYID
and cn1.COMPANYID = cij.COMPANYID
and cn1.STATEID = cij.STATEID
left join 
dbo.STATETABLE st1 on st1.STATEID = cij.STATEID
and st1.COMPANYID = cij.COMPANYID
and st1.COUNTRYID = cij.DLVCOUNTRY
left join 
dbo.CUSTGROUPTABLE csg on csg.CUSTGROUP = cij.CUSTGROUP
and csg.COMPANYID = cij.COMPANYID
left join 
dbo.SALESORIGINTABLE slo on slo.ORIGINID = cij.SALESORIGINID
and slo.COMPANYID = cij.COMPANYID
left join 
dbo.DESTINATIONCODETABLE dsc on dsc.DESTINATIONCODEID = cij.DESTINATIONCODEID
and dsc.COMPANYID = cij.COMPANYID
left join 
dbo.COMMISSIONSALESGROUPTABLE cms on cms.GROUPID = cij.SALESGROUP
and cms.COMPANYID = cij.COMPANYID)
select *
from CTE;

Answer 1

只需将 cte 移动到 from，如下所示：

select * from 
(select MODIFIEDDATETIME = (   select max(modified)
from (   values (dateadd(second, cij.MODIFIEDTIME, cij.MODIFIEDDATE))
,(dateadd(second, cn1.MODIFIEDTIME, cn1.MODIFIEDDATE))
,(dateadd(second, st1.MODIFIEDTIME, st1.MODIFIEDDATE))
,(dateadd(second, csg.MODIFIEDTIME, csg.MODIFIEDDATE))
,(dateadd(second, slo.MODIFIEDTIME, slo.MODIFIEDDATE))
,(dateadd(second, dsc.MODIFIEDTIME, dsc.MODIFIEDDATE))
,(dateadd(second, cms.MODIFIEDTIME, cms.MODIFIEDDATE))) as dateList (modified) )
from dbo.HeaderTable                         cij
left join dbo.COUNTY                    cn1 on cn1.COUNTYID          = cij.COUNTYID
and cn1.COMPANYID         = cij.COMPANYID
and cn1.STATEID           = cij.STATEID
left join dbo.STATETABLE                st1 on st1.STATEID           = cij.STATEID
and st1.COMPANYID         = cij.COMPANYID
and st1.COUNTRYID         = cij.DLVCOUNTRY
left join dbo.CUSTGROUPTABLE            csg on csg.CUSTGROUP         = cij.CUSTGROUP
and csg.COMPANYID         = cij.COMPANYID
left join dbo.SALESORIGINTABLE          slo on slo.ORIGINID          = cij.SALESORIGINID
and slo.COMPANYID         = cij.COMPANYID
left join dbo.DESTINATIONCODETABLE      dsc on dsc.DESTINATIONCODEID = cij.DESTINATIONCODEID
and dsc.COMPANYID         = cij.COMPANYID
left join dbo.COMMISSIONSALESGROUPTABLE cms on cms.GROUPID           = cij.SALESGROUP
and cms.COMPANYID         = cij.COMPANYID) as a

Answer 2

select (dateadd(second, cij.MODIFIEDTIME, cij.MODIFIEDDATE)) cijDate   -- add column name
,(dateadd(second, cn1.MODIFIEDTIME, cn1.MODIFIEDDATE)) cn1Date
,(dateadd(second, st1.MODIFIEDTIME, st1.MODIFIEDDATE))st1Date
,(dateadd(second, csg.MODIFIEDTIME, csg.MODIFIEDDATE))csgDate
,(dateadd(second, slo.MODIFIEDTIME, slo.MODIFIEDDATE))sloDate
,(dateadd(second, dsc.MODIFIEDTIME, dsc.MODIFIEDDATE))dscDate
,(dateadd(second, cms.MODIFIEDTIME, cms.MODIFIEDDATE))cmsDate
into #temp1
from dbo.HeaderTable                         cij
left join dbo.COUNTY                    cn1 on cn1.COUNTYID          = cij.COUNTYID
and cn1.COMPANYID         = cij.COMPANYID
and cn1.STATEID           = cij.STATEID
left join dbo.STATETABLE                st1 on st1.STATEID           = cij.STATEID
and st1.COMPANYID         = cij.COMPANYID
and st1.COUNTRYID         = cij.DLVCOUNTRY
left join dbo.CUSTGROUPTABLE            csg on csg.CUSTGROUP         = cij.CUSTGROUP
and csg.COMPANYID         = cij.COMPANYID
left join dbo.SALESORIGINTABLE          slo on slo.ORIGINID          = cij.SALESORIGINID
and slo.COMPANYID         = cij.COMPANYID
left join dbo.DESTINATIONCODETABLE      dsc on dsc.DESTINATIONCODEID = cij.DESTINATIONCODEID
and dsc.COMPANYID         = cij.COMPANYID
left join dbo.COMMISSIONSALESGROUPTABLE cms on cms.GROUPID           = cij.SALESGROUP
and cms.COMPANYID         = cij.COMPANYID
create table #temp2 (modified datetime)
insert into #temp2
select cijDate from  #temp1
union all 
select cn1Date  from  #temp1
union all 
select st1Date from  #temp1
union all 
select csgDate from  #temp1
union all 
select sloDate from  #temp1
union all 
select dscDate from  #temp1
union all 
select cmsDate from  #temp1
select MAX( modified ) from #temp2

Answer 3

@rahul-richhariya是正确的。 我需要创建一堆嵌套案例...而且很丑。 谢谢你们的帮助。 在这里。

select MODIFIEDDATETIME = case
when dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(dim.MODIFIEDTIME, ''), coalesce(dim.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlm.MODIFIEDTIME, ''), coalesce(dlm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlt.MODIFIEDTIME, ''), coalesce(dlt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(dnc.MODIFIEDTIME, ''), coalesce(dnc.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(itl.MODIFIEDTIME, ''), coalesce(itl.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(pdg.MODIFIEDTIME, ''), coalesce(pdg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(pyt.MODIFIEDTIME, ''), coalesce(pyt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(ssp.MODIFIEDTIME, ''), coalesce(ssp.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(tgh.MODIFIEDTIME, ''), coalesce(tgh.MODIFIEDDATE, ''))
and dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, '')) > dateadd(second, coalesce(vig.MODIFIEDTIME, ''), coalesce(vig.MODIFIEDDATE, '')) then dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, ''))
else case
when dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(dim.MODIFIEDTIME, ''), coalesce(dim.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlm.MODIFIEDTIME, ''), coalesce(dlm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlt.MODIFIEDTIME, ''), coalesce(dlt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(dnc.MODIFIEDTIME, ''), coalesce(dnc.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(itl.MODIFIEDTIME, ''), coalesce(itl.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(pdg.MODIFIEDTIME, ''), coalesce(pdg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(pyt.MODIFIEDTIME, ''), coalesce(pyt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(ssp.MODIFIEDTIME, ''), coalesce(ssp.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(tgh.MODIFIEDTIME, ''), coalesce(tgh.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, '')) > dateadd(second, coalesce(vig.MODIFIEDTIME, ''), coalesce(vig.MODIFIEDDATE, '')) then dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, ''))
else case
when dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(dim.MODIFIEDTIME, ''), coalesce(dim.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlm.MODIFIEDTIME, ''), coalesce(dlm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlt.MODIFIEDTIME, ''), coalesce(dlt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(dnc.MODIFIEDTIME, ''), coalesce(dnc.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(itl.MODIFIEDTIME, ''), coalesce(itl.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(pdg.MODIFIEDTIME, ''), coalesce(pdg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(pyt.MODIFIEDTIME, ''), coalesce(pyt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(ssp.MODIFIEDTIME, ''), coalesce(ssp.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(tgh.MODIFIEDTIME, ''), coalesce(tgh.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, '')) > dateadd(second, coalesce(vig.MODIFIEDTIME, ''), coalesce(vig.MODIFIEDDATE, '')) then dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, ''))
else case
when dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dim.MODIFIEDTIME, ''), coalesce(dim.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlm.MODIFIEDTIME, ''), coalesce(dlm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlt.MODIFIEDTIME, ''), coalesce(dlt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dnc.MODIFIEDTIME, ''), coalesce(dnc.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(itl.MODIFIEDTIME, ''), coalesce(itl.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(pdg.MODIFIEDTIME, ''), coalesce(pdg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(pyt.MODIFIEDTIME, ''), coalesce(pyt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(ssp.MODIFIEDTIME, ''), coalesce(ssp.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(tgh.MODIFIEDTIME, ''), coalesce(tgh.MODIFIEDDATE, ''))
and dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, '')) > dateadd(second, coalesce(vig.MODIFIEDTIME, ''), coalesce(vig.MODIFIEDDATE, '')) then dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, ''))
else case
when dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(dim.MODIFIEDTIME, ''), coalesce(dim.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlm.MODIFIEDTIME, ''), coalesce(dlm.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlt.MODIFIEDTIME, ''), coalesce(dlt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(dnc.MODIFIEDTIME, ''), coalesce(dnc.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(itl.MODIFIEDTIME, ''), coalesce(itl.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(pdg.MODIFIEDTIME, ''), coalesce(pdg.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(pyt.MODIFIEDTIME, ''), coalesce(pyt.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(ssp.MODIFIEDTIME, ''), coalesce(ssp.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(tgh.MODIFIEDTIME, ''), coalesce(tgh.MODIFIEDDATE, ''))
and dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, '')) > dateadd(second, coalesce(vig.MODIFIEDTIME, ''), coalesce(vig.MODIFIEDDATE, '')) then dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, ''))
else case
     when dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(chd.MODIFIEDTIME, ''), coalesce(chd.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(cms.MODIFIEDTIME, ''), coalesce(cms.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(cpm.MODIFIEDTIME, ''), coalesce(cpm.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(csg.MODIFIEDTIME, ''), coalesce(csg.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(cst.MODIFIEDTIME, ''), coalesce(cst.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dim.MODIFIEDTIME, ''), coalesce(dim.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlm.MODIFIEDTIME, ''), coalesce(dlm.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dlt.MODIFIEDTIME, ''), coalesce(dlt.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(dnc.MODIFIEDTIME, ''), coalesce(dnc.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(itl.MODIFIEDTIME, ''), coalesce(itl.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(pdg.MODIFIEDTIME, ''), coalesce(pdg.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(pyt.MODIFIEDTIME, ''), coalesce(pyt.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(ssp.MODIFIEDTIME, ''), coalesce(ssp.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(tgh.MODIFIEDTIME, ''), coalesce(tgh.MODIFIEDDATE, ''))
      and dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, '')) > dateadd(second, coalesce(vig.MODIFIEDTIME, ''), coalesce(vig.MODIFIEDDATE, '')) then dateadd(second, coalesce(ctg.MODIFIEDTIME, ''), coalesce(ctg.MODIFIEDDATE, ''))
 end
end
end
end
end
end
from dbo.CUSTTABLE                           cst
left join dbo.CUSTOMERGROUP             csg on csg.CUSTOMERGROUP       = cst.CUSTOMERGROUP
and csg.COMPANYID           = cst.COMPANYID
left join dbo.CASHDISCOUNT              chd on chd.CASHDISCOUNTCODE    = cst.CASHDISCOUNT
and chd.COMPANYID           = cst.COMPANYID
left join dbo.COMMISSIONSALESGROUPTABLE cms on cms.GROUPID             = cst.SALESGROUP
and cms.COMPANYID           = cst.COMPANYID
left join dbo.CUSTSTATISTICSGROUPTABLE  ctg on ctg.CUSTSTATISTICSGROUP = cst.STATISTICSGROUP
and ctg.COMPANYID           = cst.COMPANYID
left join dbo.CUSTPAYMMODETABLETABLE    cpm on cpm.PAYMMODE            = cst.PAYMMODE
and cpm.COMPANYID           = cst.COMPANYID;