quote:

---

Originally posted by gbritton

please post your query.

Oh and you probably meant 'bear with me' (put up with my newbie questions) not 'bare with me' (get into our birthday suits together!)

---

quote:

---

Originally posted by Icehockey44

Didn't work. Now indicated with ** front and back!!

---

("Sales Price" - "Cost Price") as "Margin"

case when (OOHEAD.[Currency] = 'EUR') 
    then (OOLINE.[Sales price]/1.25)
    else OOLINE.[Sales price] 
    end
- isnull(case when (OOHEAD.[Currency] = 'EUR') 
              then (MITFAC.M9APPR/1.25)
              else MITFAC.M9APPR
              end,
         0)
 as "Margin"

quote:

---

Originally posted by Kristen

You can't do:

("Sales Price" - "Cost Price") as "Margin"

in your SELECT statement as those "column names" are only defined in the SELECT itself, so you need to use the original, underlying, column names instead. If I've read the code correctly that would be:

case when (OOHEAD.[Currency] = 'EUR') 
    then (OOLINE.[Sales price]/1.25)
    else OOLINE.[Sales price] 
    end
- isnull(case when (OOHEAD.[Currency] = 'EUR') 
              then (MITFAC.M9APPR/1.25)
              else MITFAC.M9APPR
              end,
         0)
 as "Margin"

same sort of thing with your "Margin%"

You could probably use an OUTER APPLY to provide those fields in a more oven-ready form to use within the SELECT statement

---

quote:

---

Originally posted by Icehockey44

In theory we are just extracting the same data as the query above gives us, but with a lower status

---

FROM table alias
OUTER JOIN 
(
    subquery
) alias

        OR OOLINE.[Item Number] LIKE 'CKD360%'
        OR OOLINE.[Item Number] LIKE 'CKD360/KB%'

        OR OOLINE.[Item Number] LIKE 'COSKOSD%'
        OR OOLINE.[Item Number] LIKE 'COSKOTD%'

        OR OOLINE.[Item Number] LIKE 'COSKO[ST]D%'

        OR OOLINE.[Item Number] LIKE 'OPTION H%'
        OR OOLINE.[Item Number] LIKE 'OPTION M%'
        OR OOLINE.[Item Number] LIKE 'OPTION N%'
        OR OOLINE.[Item Number] LIKE 'OPTION Q%'

        OR OOLINE.[Item Number] LIKE 'OPTION [HMNQ]%'

quote:

---

Originally posted by Kristen

You can't do:

("Sales Price" - "Cost Price") as "Margin"

in your SELECT statement as those "column names" are only defined in the SELECT itself, so you need to use the original, underlying, column names instead. If I've read the code correctly that would be:

case when (OOHEAD.[Currency] = 'EUR') 
    then (OOLINE.[Sales price]/1.25)
    else OOLINE.[Sales price] 
    end
- isnull(case when (OOHEAD.[Currency] = 'EUR') 
              then (MITFAC.M9APPR/1.25)
              else MITFAC.M9APPR
              end,
         0)
 as "Margin"

same sort of thing with your "Margin%"

You could probably use an OUTER APPLY to provide those fields in a more oven-ready form to use within the SELECT statement

---

quote:

---

Originally posted by Icehockey44

Hi SwePeso,

I get this error message when I enter this query:

Error: The column 'Payer' was specified multiple times for 'cteData'. (State:37000, Native Code: 1FDC)

Not sure where to start with correcting it!!!

---

quote:

---

Originally posted by Icehockey44

the report should have around 40000 lines, when we put this correction in (for the margin) it only brings back 139 lines!!

---

quote:

---

Originally posted by SwePeso

Code fixed above.
The data handling is now much easier.

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

		[Sales Price] - [Cost Price] AS Margin,
		CASE
			WHEN [Sales Price] = 0 THEN 0
			ELSE ([Sales Price] - [Cost price]) / [Sales price]
		END AS [Margin%],

		CASE
			WHEN [Item Number] LIKE 'CXVAL%'
			  OR [Item Number] LIKE 'CXMAJ%'
			THEN 1.05
			ELSE 1.00
		END * ([Sales Price] - [Cost Price]) AS Margin,
		CASE
			WHEN [Sales Price] = 0
			THEN 0
			WHEN [Item Number] LIKE 'CXVAL%'
			  OR [Item Number] LIKE 'CXMAJ%'
			THEN 1.05 * ([Sales Price] - [Cost Price]) / [Sales Price]
			ELSE ([Sales Price] - [Cost Price]) / [Sales Price]
		END AS [Margin%],

quote:

---

Originally posted by bitsmed

Replace these lines:

		[Sales Price] - [Cost Price] AS Margin,
		CASE
			WHEN [Sales Price] = 0 THEN 0
			ELSE ([Sales Price] - [Cost price]) / [Sales price]
		END AS [Margin%],

with these lines:

		CASE
			WHEN [Item Number] LIKE 'CXVAL%'
			  OR [Item Number] LIKE 'CXMAJ%'
			THEN 1.05
			ELSE 1.00
		END * ([Sales Price] - [Cost Price]) AS Margin,
		CASE
			WHEN [Sales Price] = 0
			THEN 0
			WHEN [Item Number] LIKE 'CXVAL%'
			  OR [Item Number] LIKE 'CXMAJ%'
			THEN 1.05 * ([Sales Price] - [Cost Price]) / [Sales Price]
			ELSE ([Sales Price] - [Cost Price]) / [Sales Price]
		END AS [Margin%],

---