on 10-13-2011 8:03 AM
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
dear Gordon,
I would like to see also discounts with zero value. This query shows only > 0. Is it possible to adjust it?
Thank you,
jonmar7
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try:
declare @cus as char(15)
set @cus= (select T9.cardcode from dbo.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 LEFT JOIN
OITB ON OSPG.ObjKey = OITB.ItmsGrpCod LEFT JOIN
OCRD ON OSPG.CardCode = OCRD.CardCode LEFT 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 JOIN
SPP1 b ON a.ItemCode = b.ItemCode AND a.CardCode = b.CardCode AND (b.ToDate IS NULL OR
b.ToDate >= GETDATE()) LEFT JOIN
OITM c ON a.ItemCode = c.ItemCode LEFT JOIN
OCRD d ON d.CardCode = a.CardCode LEFT JOIN
OCTG e ON e.GroupNum = d.GroupNum
WHERE (a.CardCode =@cus)
order by ItemName
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.