קאוד: וועל אויס אלע
select * from(
SELECT POSTSO.EXTREFNUMBER as Invoice, SO.NUM as "SO Num", replace(replace(POSTORDERSTATUS.NAME,'For Invoice','Pending'),'Invoice','Posted') as Status, sum(POSTSOITEM.TOTALPRICE) as Amount, cast(postso.datecreated as date) as Fulfilled,
case when iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name like 'BlueTarp%' then 'BlueTarp' end as BlueTarp,
bta.info as BTAuth,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name) as "Account Name",
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name as "Customer Path"
FROM POSTORDERSTATUS POSTORDERSTATUS, POSTSO POSTSO, POSTSOITEM POSTSOITEM, SO SO
left join customvarchar bta on bta.customfieldid = 53 and bta.recordid = so.id
INNER JOIN customer ON so.customerid = customer.id
left join customer parent on parent.id = customer.parentid
left join customer gparent on gparent.id = parent.parentid
left join customer ggparent on ggparent.id = gparent.parentid
left join customer gggparent on gggparent.id = ggparent.parentid
WHERE SO.ID = POSTSO.SOID AND POSTSO.STATUSID = POSTORDERSTATUS.ID AND POSTSOITEM.POSTSOID = POSTSO.ID
and (cast(postso.dateposted as date) between ? and ? +.9999 OR postso.dateposted is null)
Group By POSTSO.EXTREFNUMBER, SO.NUM, POSTORDERSTATUS.NAME, postso.datecreated,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name),
bta.info,
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name
)main
Order By main.Status
