Hi all, i am stuck trying to group 2 lines into 1.
Hope someone could guide me in the correct direction?
I've been trying for a week, starting from a minimal, it seems the issue is at CURSELLPRICE (Unable to group, it will return as 2 lines)
Select
STRXREFCODE as ARTICLE,
SUM((CASE WHEN strsaletype='I' then convert (decimal(5,0),ds.DBLQTYSOLD) else null end)) as [QTY SOLD],
SUM((CASE WHEN strsaletype='I' then convert(decimal(8,2),CURSELLPRICE1) else null end))*ds.DBLQTYSOLD as [UNIT PRICE],
MAX
(CASE WHEN strsaletype='W' then INTDISCOUNTCODE+INTOFFERCODE+INTSALESDISCOUNTCODE else NULL end) as [DISC CODE],
MAX
(CASE WHEN strsaletype='W' then DH.STRPOSDESCRIPTION else NULL end) as [Discount Name],
SUM((CASE WHEN strsaletype='W' then convert(decimal(8,2),CUROFFERDISCOUNT+CURDISCOUNT+CURSALESDISCOUNT) else NULL end))*ds.DBLQTYSOLD as [DISCOUNT],
SUM((CASE WHEN strsaletype='I' then convert(decimal(8,2),CURFOREIGNAMT) else null end)) as [GROSS AMT]
from DAILYSALES DS
left join DISCHDR as DH on DS.INTDISCOUNTCODE+INTOFFERCODE+INTSALESDISCOUNTCODE=DH.CTRCODE
left join ITEM as IT on DS.LINTITEMNUMBER=IT.LINTITEMNUMBER
where STRSALETYPE in ('I','W')
group by DS.DTMTRADEDATE,INTSALEID,IT.STRFAMILYCODE,IT.STRSUBGROUPCODE,STRDEPTCODE,IT.STRSUBGROUPCODE,STRXREFCODE,ds.DBLQTYSOLD
order by DS.DTMTRADEDATE,INTSALEID
Attachments:
You must be
logged in to view attached files.