July 22, 2019 at 8:12 am
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
July 22, 2019 at 8:15 am
Sorry for the double post.
Gotten a timeout message and thought it didn't went through.
July 22, 2019 at 11:17 am
You need to only GROUP BY STRXREFCODE
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy