Skip to Content
0
Oct 13, 2011 at 07:03 AM

group of discounts all

17 Views

Hello,

could you help me with this query, i want to see all groups of discounts, not only groups which are >0.

Thank you,

jonmar7

declare @cus as char(15)

set @cus= (select T9.cardcode from .[ocrd] T9 where T9.cardcode = N'[%0]')

SELECT 'Group' AS 'Type Discount',

OSPG.CardCode + ' '+ OCRD.CardName + CHAR(13) + OCRD.Address + CHAR(13)+ OCRD.ZipCode + ' ' + OCRD.City + CHAR(13) + 'Phone : ' + isnull(OCRD.Phone1,' ') + CHAR(13) + 'Fax : ' + isnull(OCRD.Fax,' ') + CHAR(13)+ 'VAT N° : ' + OCRD.LicTradNum + CHAR(13) + 'Kreditlimit : ' + CAST(CEILING(OCRD.CreditLine) AS nvarchar(19)) + CHAR(13) + 'Payment Terms : ' + OCTG.PymntGroup AS 'Business Partner',

'' AS itemnumber,

OITB.ItmsGrpNam AS 'ItemName',

OSPG.Discount AS Discount,

0 as price,

'' as Currency,

null AS Fromdate,

null AS Todate

FROM OSPG INNER JOIN

OITB ON OSPG.ObjKey = OITB.ItmsGrpCod INNER JOIN

OCRD ON OSPG.CardCode = OCRD.CardCode INNER JOIN

OCTG ON OCRD.GroupNum = OCTG.GroupNum

where ospg.cardcode = @cus

UNION ALL

SELECT 'Item' AS 'Type Discount', d.CardCode + ' ' + d.CardName + CHAR(13) + d.Address + CHAR(13) + d.ZipCode + ' ' + d.City + CHAR(13)

+ 'Phone : ' + isnull(d.Phone1,' ') + CHAR(13) + ' Fax : ' + isnull(d.Fax,' ') + CHAR(13) + ' VAT N : ' + d.LicTradNum + CHAR(13)

+ ' Kreditlimit : ' + CAST(CEILING(d.CreditLine) AS nvarchar(19)) + CHAR(13) + ' Payment Terms :' + e.PymntGroup AS ' Business Partner ',

a.ItemCode AS itemnumber, c.ItemName AS itemName, CASE WHEN a.expand = 'Y' THEN b.discount ELSE a.discount END AS Discount,

CASE WHEN a.expand = 'Y' THEN b.price ELSE a.Price END AS Expr1, b.Currency, b.FromDate, b.ToDate

FROM OSPP a LEFT OUTER JOIN

SPP1 b ON a.ItemCode = b.ItemCode AND a.CardCode = b.CardCode INNER JOIN

OITM c ON a.ItemCode = c.ItemCode INNER JOIN

OCRD d ON d.CardCode = a.CardCode INNER JOIN

OCTG e ON e.GroupNum = d.GroupNum

WHERE (b.ToDate IS NULL OR

b.ToDate >= GETDATE()) AND (a.CardCode = @cus)

order by ItemName