July 22, 2019 at 8:01 am
Hi all, i am stuck trying to group 2 lines into 1.
Hope someone could guide me in the correct direction.
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 DTMTRADEDATE='2019-07-01'
and STRSALETYPE in ('I','W')
and DS.STRTRADECODE='YY01'
and INTSALEID='70837'
group by DS.DTMTRADEDATE,INTSALEID,IT.STRFAMILYCODE,IT.STRSUBGROUPCODE,STRDEPTCODE,IT.STRSUBGROUPCODE,STRXREFCODE,ds.DBLQTYSOLD
order by DS.DTMTRADEDATE,INTSALEID
July 22, 2019 at 9:29 am
Ahoi,
have you tried:
Query 1: only grouping by Article and the max values for the integer columns.
Query2: get Article and the non integer columns where is not NULL
Join Query 1 with Query 2 on Article and add the non integer colums from Query 2 into Query 1
I want to be the very best
Like no one ever was
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply