Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Minus query not calculating correctly

Author  Topic 

Icehockey44
Starting Member

10 Posts

Posted - 2015-03-31 : 11:49:03

Hi All,

I am completely new to all of this, so please bare with me, as I am going to be asking a lot of basic questions!!

I have a query, which I thought would be really straight forward, but it appears not so...

I have a cost price and a sales price, and need to calculate the margin (Told you it was simple!)

the code that I am using is:

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

So basically it is giving me this:

(Sales Price)524.552 - (Cost Price)442.567 giving me (Margin)655.690, surely this should be 81.985

Can anyone see where it's falling over?

I look forward to your suggestions, as you are all a lot wiser than me at this.

Cheers

Donna

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 12:03:48
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!)
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-01 : 04:46:04
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!)



Sorry, wasn't sure what or how much detail would be required?!? I have ((Italic bold and Lime greened the bit that is not working correctly (although its not showing on my screen))) Didn't work. Now indicated with ** front and back!!

thanks in advance for any help

Donna

SELECT
right(OOHEAD.[Order date],2)+'/'+substring(convert(varchar(8),OOHEAD.[Order date]),5,2)+'/'+left(OOHEAD.[Order date],4) as "Order Created",
right(OOHEAD.[First delivery date],2)+'/'+substring(convert(varchar(8),OOHEAD.[First delivery date]),5,2)+'/'+left(OOHEAD.[First delivery date],4) as "Required date",
right(OOHEAD.[Last delivery date],2)+'/'+substring(convert(varchar(8),OOHEAD.[Last delivery date]),5,2)+'/'+left(OOHEAD.[Last delivery date],4) as "Installation date",
OOHEAD.[Customer Order Number],
OCUSMA.OKCUNO as "Customer No",
OCUSMA.OKCUNM as "Customer Name",
OCUSMA.OKPONO as "Postcode",
OOLINE.[Highest status - customer order] ,
OOLINE.[Item Number],
MITMAS.MMFUDS as "Description",
CSYTAB.CTTX15,
OOLINE.[Ordered quantity - basic U/M],
case when (OOHEAD.[Currency] = 'EUR') then (OOLINE.[Sales price]/1.25)
else
OOLINE.[Sales price] end as "Sales Price",
isnull(case when (OOHEAD.[Currency] = 'EUR') then (MITFAC.M9APPR/1.25)
else
MITFAC.M9APPR end,0) as "Cost Price",

*("Sales Price" - "Cost Price") as "Margin",*
COALESCE(("Sales Price" - "Cost price")/NULLIF(("Sales price"),0),0)*100 as "Margin%",
OOHEAD.[Statistics identity 1 customer],
MITFAC.M9FACI as "Facility",
CSYTAB_salesperson.CTTX40 as "Salesperson",
OCUSMA.OKCFC5 as "Regional",
OOHEAD.Warehouse,
OCUSMA.OKWHLO as "Location",
OCUSMA.OKPYNO as "Payer",
MITMAS.MMITTY as "Item Type",
OOHEAD.[Customer order type],
OOHEAD.[Currency],
isnull(MITFAC.M9FANO,0) as "On Hand",
OOHEAD.[Customers order number],
MITMAS.MMITCL as "Class",
OOHEAD.Payer as "Payer",
isnull((right(MITTRA.MTTRDT,2)+'/'+substring(convert(varchar(8),MITTRA.MTTRDT),5,2)+'/'+left(MITTRA.MTTRDT,4)),0) as "Confirmed Date",
MITTRA.MTBANO as "Individual Item"



FROM {oj (((((((bpw_live_staging.dbo.OCUSMA OCUSMA RIGHT OUTER JOIN OOHEAD OOHEAD
ON (OCUSMA.OKCONO=OOHEAD.Company) AND (OCUSMA.OKCUNO=OOHEAD.[Customer Number])) INNER JOIN OOLINE OOLINE
ON ((OOHEAD.Company=OOLINE.Company) AND (OOHEAD.Division=OOLINE.Division)) AND (OOHEAD.[Customer Order Number]=OOLINE.[Customer Order Number])) LEFT OUTER JOIN bpw_live_staging.dbo.CSYTAB CSYTAB_salesperson
ON (OOHEAD.Company=CSYTAB_salesperson.CTCONO) AND (OOHEAD.Salesperson=CSYTAB_salesperson.CTSTKY)) LEFT OUTER JOIN bpw_live_staging.dbo.MITMAS MITMAS
ON (OOLINE.Company=MITMAS.MMCONO) AND (OOLINE.[Item Number]=MITMAS.MMITNO)) LEFT OUTER JOIN MPLINE MPLINE
ON ((OOLINE.Company=MPLINE.Company) AND (OOLINE.[reference order number]=MPLINE.[Purchase Order Number])) AND (OOLINE.[Reference order line]=MPLINE.[Purchase Order Line])) LEFT OUTER JOIN bpw_live_staging.dbo.MITFAC MITFAC
ON ((OOLINE.Company=MITFAC.M9CONO) AND (OOLINE.Facility=MITFAC.M9FACI)) AND (OOLINE.[Item Number]=MITFAC.M9ITNO)) LEFT OUTER JOIN MITTRA MITTRA
ON ((OOLINE.Company=MITTRA.MTCONO) AND (OOLINE.[Customer Order Number]=MITTRA.MTRIDN)) AND (OOLINE.[line number (/OBPONR)]=MITTRA.MTRIDL)) LEFT OUTER JOIN bpw_live_staging.dbo.CSYTAB CSYTAB
ON (MITMAS.MMCONO=CSYTAB.CTCONO) AND (MITMAS.MMITGR=CSYTAB.CTSTKY)}
WHERE
OOHEAD.[Customer Order Number]='1000107221' AND
OOLINE.[Highest status - customer order]>='66' AND OOHEAD.Company=100 AND (CSYTAB.CTSTCO IS NULL OR CSYTAB.CTSTCO='ITGR') AND
OOHEAD.Division='BVS' AND NOT (OOLINE.[Item Number] LIKE 'B010213%' OR OOLINE.[Item Number] LIKE 'BEV0558B%' OR OOLINE.[Item Number] LIKE 'BIOPRE%' OR
OOLINE.[Item Number] LIKE 'CKD360%' OR OOLINE.[Item Number] LIKE 'CKD360/KB%' OR OOLINE.[Item Number] LIKE 'CORALPB5CASING%' OR OOLINE.[Item Number] LIKE 'COSKOSD%' OR
OOLINE.[Item Number] LIKE 'COSKOTD%' OR OOLINE.[Item Number] LIKE 'CXCOSTABEANS%' OR OOLINE.[Item Number] LIKE 'DAR-CAB-EXL%' OR OOLINE.[Item Number] LIKE 'DAR-CAB-SML%' OR
OOLINE.[Item Number] LIKE 'EVE1101206%' OR OOLINE.[Item Number] LIKE 'FL001%' OR OOLINE.[Item Number] LIKE 'MDBCHG%' 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 'PODHANDLE%' OR
OOLINE.[Item Number] LIKE 'PSSRCOMP%' OR OOLINE.[Item Number] LIKE 'RPC20%' OR OOLINE.[Item Number] LIKE 'S036267/%' OR OOLINE.[Item Number] LIKE 'VEN132%')
AND MITMAS.MMFUDS NOT LIKE '%CONVERSION%' AND OOHEAD.[Customer Order type] LIKE 'N%' AND (OOHEAD.Warehouse in ('050','060','Z50','Z60'))
ORDER BY OOHEAD.[Customer Order Number]
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 05:41:03
quote:
Originally posted by Icehockey44

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


Should be possible to put

[limegreen] some text [/limegreen]

around the relevant bit in case you want to re-edit your example.

[code] [/code] around the code itself would help with the formatting :)
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 05:47:32
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

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 05:50:36
P.S. if your tables are large? this query may run like a dog without some considerable effort on performance optimisation.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-01 : 06:01:31
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





The easy way is to use Derived table


select your_column_list

(
original statement
) as t


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Maithil
Starting Member

29 Posts

Posted - 2015-04-01 : 07:06:42
SELECT x.[Sales Price]-x.[Cost Price] 'Margin' , <Rest of fileds prefixed by x.>
from
(
SELECT
right(OOHEAD.[Order date],2)+'/'+substring(convert(varchar(8),OOHEAD.[Order date]),5,2)+'/'+left(OOHEAD.[Order date],4) as "Order Created",
right(OOHEAD.[First delivery date],2)+'/'+substring(convert(varchar(8),OOHEAD.[First delivery date]),5,2)+'/'+left(OOHEAD.[First delivery date],4) as "Required date",
right(OOHEAD.[Last delivery date],2)+'/'+substring(convert(varchar(8),OOHEAD.[Last delivery date]),5,2)+'/'+left(OOHEAD.[Last delivery date],4) as "Installation date",
OOHEAD.[Customer Order Number],
OCUSMA.OKCUNO as "Customer No",
OCUSMA.OKCUNM as "Customer Name",
OCUSMA.OKPONO as "Postcode",
OOLINE.[Highest status - customer order] ,
OOLINE.[Item Number],
MITMAS.MMFUDS as "Description",
CSYTAB.CTTX15,
OOLINE.[Ordered quantity - basic U/M],
case when (OOHEAD.[Currency] = 'EUR') then (OOLINE.[Sales price]/1.25)
else
OOLINE.[Sales price] end as "Sales Price",
isnull(case when (OOHEAD.[Currency] = 'EUR') then (MITFAC.M9APPR/1.25)
else
MITFAC.M9APPR end,0) as "Cost Price",

*("Sales Price" - "Cost Price") as "Margin",*
COALESCE(("Sales Price" - "Cost price")/NULLIF(("Sales price"),0),0)*100 as "Margin%",
OOHEAD.[Statistics identity 1 customer],
MITFAC.M9FACI as "Facility",
CSYTAB_salesperson.CTTX40 as "Salesperson",
OCUSMA.OKCFC5 as "Regional",
OOHEAD.Warehouse,
OCUSMA.OKWHLO as "Location",
OCUSMA.OKPYNO as "Payer",
MITMAS.MMITTY as "Item Type",
OOHEAD.[Customer order type],
OOHEAD.[Currency],
isnull(MITFAC.M9FANO,0) as "On Hand",
OOHEAD.[Customers order number],
MITMAS.MMITCL as "Class",
OOHEAD.Payer as "Payer",
isnull((right(MITTRA.MTTRDT,2)+'/'+substring(convert(varchar(8),MITTRA.MTTRDT),5,2)+'/'+left(MITTRA.MTTRDT,4)),0) as "Confirmed Date",
MITTRA.MTBANO as "Individual Item"



FROM {oj (((((((bpw_live_staging.dbo.OCUSMA OCUSMA RIGHT OUTER JOIN OOHEAD OOHEAD
ON (OCUSMA.OKCONO=OOHEAD.Company) AND (OCUSMA.OKCUNO=OOHEAD.[Customer Number])) INNER JOIN OOLINE OOLINE
ON ((OOHEAD.Company=OOLINE.Company) AND (OOHEAD.Division=OOLINE.Division)) AND (OOHEAD.[Customer Order Number]=OOLINE.[Customer Order Number])) LEFT OUTER JOIN bpw_live_staging.dbo.CSYTAB CSYTAB_salesperson
ON (OOHEAD.Company=CSYTAB_salesperson.CTCONO) AND (OOHEAD.Salesperson=CSYTAB_salesperson.CTSTKY)) LEFT OUTER JOIN bpw_live_staging.dbo.MITMAS MITMAS
ON (OOLINE.Company=MITMAS.MMCONO) AND (OOLINE.[Item Number]=MITMAS.MMITNO)) LEFT OUTER JOIN MPLINE MPLINE
ON ((OOLINE.Company=MPLINE.Company) AND (OOLINE.[reference order number]=MPLINE.[Purchase Order Number])) AND (OOLINE.[Reference order line]=MPLINE.[Purchase Order Line])) LEFT OUTER JOIN bpw_live_staging.dbo.MITFAC MITFAC
ON ((OOLINE.Company=MITFAC.M9CONO) AND (OOLINE.Facility=MITFAC.M9FACI)) AND (OOLINE.[Item Number]=MITFAC.M9ITNO)) LEFT OUTER JOIN MITTRA MITTRA
ON ((OOLINE.Company=MITTRA.MTCONO) AND (OOLINE.[Customer Order Number]=MITTRA.MTRIDN)) AND (OOLINE.[line number (/OBPONR)]=MITTRA.MTRIDL)) LEFT OUTER JOIN bpw_live_staging.dbo.CSYTAB CSYTAB
ON (MITMAS.MMCONO=CSYTAB.CTCONO) AND (MITMAS.MMITGR=CSYTAB.CTSTKY)}
WHERE
OOHEAD.[Customer Order Number]='1000107221' AND
OOLINE.[Highest status - customer order]>='66' AND OOHEAD.Company=100 AND (CSYTAB.CTSTCO IS NULL OR CSYTAB.CTSTCO='ITGR') AND
OOHEAD.Division='BVS' AND NOT (OOLINE.[Item Number] LIKE 'B010213%' OR OOLINE.[Item Number] LIKE 'BEV0558B%' OR OOLINE.[Item Number] LIKE 'BIOPRE%' OR
OOLINE.[Item Number] LIKE 'CKD360%' OR OOLINE.[Item Number] LIKE 'CKD360/KB%' OR OOLINE.[Item Number] LIKE 'CORALPB5CASING%' OR OOLINE.[Item Number] LIKE 'COSKOSD%' OR
OOLINE.[Item Number] LIKE 'COSKOTD%' OR OOLINE.[Item Number] LIKE 'CXCOSTABEANS%' OR OOLINE.[Item Number] LIKE 'DAR-CAB-EXL%' OR OOLINE.[Item Number] LIKE 'DAR-CAB-SML%' OR
OOLINE.[Item Number] LIKE 'EVE1101206%' OR OOLINE.[Item Number] LIKE 'FL001%' OR OOLINE.[Item Number] LIKE 'MDBCHG%' 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 'PODHANDLE%' OR
OOLINE.[Item Number] LIKE 'PSSRCOMP%' OR OOLINE.[Item Number] LIKE 'RPC20%' OR OOLINE.[Item Number] LIKE 'S036267/%' OR OOLINE.[Item Number] LIKE 'VEN132%')
AND MITMAS.MMFUDS NOT LIKE '%CONVERSION%' AND OOHEAD.[Customer Order type] LIKE 'N%' AND (OOHEAD.Warehouse in ('050','060','Z50','Z60'))

)
as x
ORDER BY x.[Customer Order Number]
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-01 : 07:17:00
Brilliant,

thanks Kristen, worked a treat.

Just another question?!?

When I change this part of the text from

OOLINE.[Highest status - customer order]>='66' to OOLINE.[Highest status - customer order]<'66'

It should bring back all the jobs that are on a status 65 and less, when this is ran on Crystal we only get items that are form 2015 onwards, but when running through SQL we only get 33 lines from 2013.

I theory we are just extracting the same data as the query above gives us, but with a lower status. Or would it be easier to write a new query altogether?

hope this makes sense.

thanks so much for your help

Donna
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 08:00:02
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


I agree.

Your query seems to be making a comparison against a String Value for <'66' rather than a numeric comparison. On that basis '65999999' would be "less" then '66', could that account for it?

Other thought is that there is a restriction, somewhere, on the number of ROWS returned. For example using

SELECT TOP 1000 ...

or

SET ROWCOUNT = 1000
SELECT ...
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-01 : 09:05:13
Some thoughts:

1. Try not to mix [column name] and "column2 name" formats. It makes the query a bit harder to read. Choose one format and stick with it. In T-SQL, try to use bracketed identifiers unless ANSI compatibility is a concern.
2. Try not to use ODBC expressions. e.g. FROM {oj(...)}. Instead use:


FROM table alias
OUTER JOIN
(
subquery
) alias


It's about readability

4. Speaking of readability, the whole thing would probably be quite a bit easier to read if you used CTEs to organize things in a WITH statement.

3. Your big CASE statement could be simpler:


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


The second one is redundant.


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


is the same as:


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%'


is the same as:


OR OOLINE.[Item Number] LIKE 'OPTION [HMNQ]%'
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-01 : 16:46:36
[code]WITH cteData
AS (
SELECT CONVERT(VARCHAR(10), OOHEAD.[Order date], 101) AS [Order Created],
CONVERT(VARCHAR(10), OOHEAD.[First delivery date], 101) AS [Required date],
CONVERT(VARCHAR(10), OOHEAD.[Last delivery date], 101) AS [Installation date],
OOHEAD.[Customer Order Number],
OCUSMA.OKCUNO AS [Customer No],
OCUSMA.OKCUNM AS [Customer Name],
OCUSMA.OKPONO AS Postcode,
OOLINE.[Highest status - customer order],
OOLINE.[Item Number],
MITMAS.MMFUDS AS [Description],
CSYTAB.CTTX15,
OOLINE.[Ordered quantity - basic U/M],
CASE
WHEN OOHEAD.[Currency] = 'EUR' THEN OOLINE.[Sales price] / 1.25
ELSE OOLINE.[Sales price]
END AS [Sales Price],
CASE
WHEN OOHEAD.[Currency] = 'EUR' THEN ISNULL(MITFAC.M9APPR / 1.25, 0)
ELSE ISNULL(MITFAC.M9APPR, 0)
END AS [Cost Price],
OOHEAD.[Statistics identity 1 customer],
MITFAC.M9FACI AS Facility,
CSYTAB_salesperson.CTTX40 AS Salesperson,
OCUSMA.OKCFC5 AS Regional,
OOHEAD.Warehouse,
OCUSMA.OKWHLO AS Location,
OCUSMA.OKPYNO AS Payer1,
MITMAS.MMITTY AS [Item Type],
OOHEAD.[Customer order type],
OOHEAD.[Currency],
ISNULL(MITFAC.M9FANO, 0) AS [On Hand],
OOHEAD.[Customers order number],
MITMAS.MMITCL AS Class,
OOHEAD.Payer AS Payer2,
CONVERT(VARCHAR(10), MITTRA.MTTRDT, 101) AS [Confirmed Date],
MITTRA.MTBANO AS [Individual Item]
FROM {oj (((((((bpw_live_staging.dbo.OCUSMA OCUSMA RIGHT OUTER JOIN OOHEAD OOHEAD
ON (OCUSMA.OKCONO=OOHEAD.Company) AND (OCUSMA.OKCUNO=OOHEAD.[Customer Number])) INNER JOIN OOLINE OOLINE
ON ((OOHEAD.Company=OOLINE.Company) AND (OOHEAD.Division=OOLINE.Division)) AND (OOHEAD.[Customer Order Number]=OOLINE.[Customer Order Number])) LEFT OUTER JOIN bpw_live_staging.dbo.CSYTAB CSYTAB_salesperson
ON (OOHEAD.Company=CSYTAB_salesperson.CTCONO) AND (OOHEAD.Salesperson=CSYTAB_salesperson.CTSTKY)) LEFT OUTER JOIN bpw_live_staging.dbo.MITMAS MITMAS
ON (OOLINE.Company=MITMAS.MMCONO) AND (OOLINE.[Item Number]=MITMAS.MMITNO)) LEFT OUTER JOIN MPLINE MPLINE
ON ((OOLINE.Company=MPLINE.Company) AND (OOLINE.[reference order number]=MPLINE.[Purchase Order Number])) AND (OOLINE.[Reference order line]=MPLINE.[Purchase Order Line])) LEFT OUTER JOIN bpw_live_staging.dbo.MITFAC MITFAC
ON ((OOLINE.Company=MITFAC.M9CONO) AND (OOLINE.Facility=MITFAC.M9FACI)) AND (OOLINE.[Item Number]=MITFAC.M9ITNO)) LEFT OUTER JOIN MITTRA MITTRA
ON ((OOLINE.Company=MITTRA.MTCONO) AND (OOLINE.[Customer Order Number]=MITTRA.MTRIDN)) AND (OOLINE.[line number (/OBPONR)]=MITTRA.MTRIDL)) LEFT OUTER JOIN bpw_live_staging.dbo.CSYTAB CSYTAB
ON (MITMAS.MMCONO=CSYTAB.CTCONO) AND (MITMAS.MMITGR=CSYTAB.CTSTKY)}
WHERE OOHEAD.[Customer Order Number] = '1000107221'
AND OOLINE.[Highest status - customer order] >= '66'
AND OOHEAD.Company = 100
AND (CSYTAB.CTSTCO IS NULL OR CSYTAB.CTSTCO = 'ITGR')
AND OOHEAD.Division = 'BVS'
AND NOT (
OOLINE.[Item Number] LIKE 'B010213%'
OR OOLINE.[Item Number] LIKE 'BEV0558B%'
OR OOLINE.[Item Number] LIKE 'BIOPRE%'
OR OOLINE.[Item Number] LIKE 'CKD360%'
OR OOLINE.[Item Number] LIKE 'CKD360/KB%'
OR OOLINE.[Item Number] LIKE 'CORALPB5CASING%'
OR OOLINE.[Item Number] LIKE 'COSKOSD%'
OR OOLINE.[Item Number] LIKE 'COSKOTD%'
OR OOLINE.[Item Number] LIKE 'CXCOSTABEANS%'
OR OOLINE.[Item Number] LIKE 'DAR-CAB-EXL%'
OR OOLINE.[Item Number] LIKE 'DAR-CAB-SML%'
OR OOLINE.[Item Number] LIKE 'EVE1101206%'
OR OOLINE.[Item Number] LIKE 'FL001%'
OR OOLINE.[Item Number] LIKE 'MDBCHG%'
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 'PODHANDLE%'
OR OOLINE.[Item Number] LIKE 'PSSRCOMP%'
OR OOLINE.[Item Number] LIKE 'RPC20%'
OR OOLINE.[Item Number] LIKE 'S036267/%'
OR OOLINE.[Item Number] LIKE 'VEN132%'
)
AND MITMAS.MMFUDS NOT LIKE '%CONVERSION%'
AND OOHEAD.[Customer Order type] LIKE 'N%'
AND OOHEAD.Warehouse IN ('050', '060', 'Z50', 'Z60')
)
SELECT [Order Created],
[Required date],
[Installation date],
[Customer Order Number],
[Customer No],
[Customer Name],
Postcode,
[Highest status - customer order],
[Item Number],
[Description],
CTTX15,
[Ordered quantity - basic U/M],
[Sales Price],
[Cost Price],
[Sales Price] - [Cost Price] AS Margin,
CASE
WHEN [Sales Price] = 0 THEN 0
ELSE ([Sales Price] - [Cost price]) / [Sales price]
END AS [Margin%],
[Statistics identity 1 customer],
Facility,
Salesperson,
Regional,
Warehouse,
Location,
Payer1 AS Payer,
[Item Type],
[Customer order type],
[Currency],
[On Hand],
[Customers order number],
Class,
Payer2 AS Payer,
[Confirmed Date],
[Individual Item]
FROM cteData
ORDER BY [Customer Order Number];[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-02 : 06:31:01
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!!!
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-02 : 06:38:58
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





Hi Kristen,

Yes this did sort the issue for the margin, but created another issue, in that it doesn't run all the lines, it seems to have them capped. the report should have around 40000 lines, when we put this correction in (for the margin) it only brings back 139 lines!!

any suggestions as to what might be causing this? im guessing this could be the same issue with our other report too!

sorry for all the questions

cheers

Donna
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 08:42:55
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!!!



In the cte (and your original query) there are two columns with the same alias:

OCUSMA.OKPYNO AS Payer,
...
OOHEAD.Payer,

Perhaps alias the second one Payer2, then change the final select to match.

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-02 : 09:17:50
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!!

The only thing that would reduce the number of rows in the report is a change to the JOINs or the WHERE clause.

(Or a SELECT TOP 139 ... or SET ROWCOUNT=139)

My suggestion was a change to the SELECT clause, so would not (itself) have any impact on the number of rows returned

There are several suggestions going on here, so it might be that the other changes are impacting the number of rows returned.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-03 : 03:30:28
Code fixed above.
The data handling is now much easier.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-15 : 10:21:49
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



thanks for this SwePeso.

I have another add though!!

I need to amend the part for the Margin, basically any Item Number that begins with CXVAL and CXMAJ needs to have the following formula applied to them (Sales Price - Costa Price )*1.05, anything else would remain Sales Price - cost Price.

I shall attempt this myself whilst awaiting yours or anyone elses replies.

I am guessing that this little addition would go somewhere, where the margin part of the query is already.

Once again thank you all soo much for your help on this.

Look forward to your replies

cheers

Donna
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-15 : 12:22:53
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%],
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-16 : 04:57:04
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%],




Amazing, thanks very much bitsmed, worked a treat.

I was putting stuff in the same area, but not the correct commands, but learning all the time.

thanks very much for your response and help.

cheers

Donna
Go to Top of Page

Icehockey44
Starting Member

10 Posts

Posted - 2015-04-16 : 05:32:08
Me again!!!!

I have another snaggy point!!!

we have a column which has a date in it. On some of the lines this come through as a blank, we need this to be populated with a date.

what I would like is where the 'Confirmed Date' is blank, then use the 'Installation Date' for this line.

Is this do-able?

I think this is the last stumbling point on my report.

thanks in advance for all of your help on this one. It is very much appreciated.

Donna

Go to Top of Page
    Next Page

- Advertisement -