November 8, 2006 at 1:25 am
Hi All,
Here is part of my script:
SELECT B.SALSTERR,
A.LOCNCODE,
A.ITEMNMBR,
A.ITEMDESC,
ITMSHNAM,
ITMCLSCD,
convert (varchar(10),DOCDATE,111) AS DOCDATE,
B.COMMNTID,
SUM(QUANTITY) AS QUANTITY
FROM SOP10200 A (NOLOCK)
INNER JOIN SOP10100 B (NOLOCK)
ON A.SOPNUMBE = B.SOPNUMBE
INNER JOIN IV00101 G (NOLOCK)
ON G.ITEMNMBR = A.ITEMNMBR
WHERE A.SOPTYPE = 2 AND DOCDATE = @DATE AND
B.SALSTERR = '355'
GROUP BY A.LOCNCODE, B.SALSTERR, A.ITEMNMBR, A.ITEMDESC, ITMSHNAM, ITMCLSCD, DOCDATE, B.COMMNTID
Now the result set includes locncode's that will either be 355C99 or 355Q01. The first 3 digits are the location codes, the letter (C or Q) pertain to separate branch descriptions and the last 2 digits are the route code.
I am writing a report with this query, and i need to separate the Q and C results, to be able to make the report easier to read. What i would like is to add a description to the salsterr column (eg. 355 - Aeroton for C and 355 - Isando for Q)
If anybody can give me assistance, i would appreciate it!
Thanks!
November 8, 2006 at 2:04 am
You can do something like this :
Select SALSTERR + ' - ' + case substring(LOCNCODE,4,1) when 'C' THEN 'Aeroton'
WHEN 'Q' THEN 'Isando'
ELSE 'OTHER' END AS AREA,*
from SOP10200
However, I would create a new table that links the sales territory to the description.
Create table tbl_Area_Desc (area char(10),description char(30))
insert into tbl_area_desc values ('355Q','Isando')
insert into tbl_area_desc values ('355C','Aeroton')
Select salsterr + description
from sop1002
Join tbl_area_desc d on d.area = left(Locncode,4)
Will
November 8, 2006 at 2:09 am
would this do the trick?
SELECT 'SALSTERR' = case when substring(A.LOCNCODE, 4, 1) = 'C'
then A.SALSTER + ' - Aeroton'
else A.SALSTER + ' - Isando' end,
A.LOCNCODE,
A.ITEMNMBR,
A.ITEMDESC,
ITMSHNAM,
ITMCLSCD,
convert (varchar(10),DOCDATE,111) AS DOCDATE,
B.COMMNTID,
SUM(QUANTITY) AS QUANTITY
FROM SOP10200 A (NOLOCK)
INNER JOIN SOP10100 B (NOLOCK)
ON A.SOPNUMBE = B.SOPNUMBE
INNER JOIN IV00101 G (NOLOCK)
ON G.ITEMNMBR = A.ITEMNMBR
WHERE A.SOPTYPE = 2 AND DOCDATE = @DATE AND
B.SALSTERR = '355'
GROUP BY A.LOCNCODE, B.SALSTERR, A.ITEMNMBR, A.ITEMDESC, ITMSHNAM, ITMCLSCD, DOCDATE, B.COMMNTID
November 8, 2006 at 2:19 am
Have a look ofn this....I think it would full file your requirement.
SELECT 'SALSTERR' = SubString(A.LOCNCODE,1,3) + ' - ' + case when substring(A.LOCNCODE, 4, 1) = 'C'
then A.SALSTER + 'Aeroton'
else A.SALSTER + 'Isando' end,
A.LOCNCODE,
A.ITEMNMBR,
A.ITEMDESC,
ITMSHNAM,
ITMCLSCD,
convert (varchar(10),DOCDATE,111) AS DOCDATE,
B.COMMNTID,
SUM(QUANTITY) AS QUANTITY
FROM SOP10200 A (NOLOCK)
INNER JOIN SOP10100 B (NOLOCK)
ON A.SOPNUMBE = B.SOPNUMBE
INNER JOIN IV00101 G (NOLOCK)
ON G.ITEMNMBR = A.ITEMNMBR
WHERE A.SOPTYPE = 2 AND DOCDATE = @DATE AND
B.SALSTERR = '355'
GROUP BY A.LOCNCODE, B.SALSTERR, A.ITEMNMBR, A.ITEMDESC, ITMSHNAM, ITMCLSCD, DOCDATE, B.COMMNTID
cheers
November 8, 2006 at 2:32 am
Excellant! Thanks a stack, this worked perfectly!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply