Unable to group 2 records into a single line result

  • louislsh

    SSC Journeyman

    Points: 75

    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

    Attachments:
    You must be logged in to view attached files.
  • ktflash

    Ten Centuries

    Points: 1093

    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 wanna be the very best
    Like no one ever was

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply