Post reply

Trying to group 2 rows into a single line

  • July 22, 2019 at 8:12 am

    #3664963

    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.

  • July 22, 2019 at 8:15 am

    #3664965

    Sorry for the double post.

    Gotten a timeout message and thought it didn't went through.

  • July 22, 2019 at 11:17 am

    #3665006

    You need to only GROUP BY STRXREFCODE

    How to post data/code on a forum to get the best help.

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

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