IS Null index match SQL - SQL Server Forums

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

IS Null index match SQL

Author

Topic

ajp43
Starting Member

6 Posts

Posted - 2015-04-10 : 07:12:34

Hello

I am fairly new to writing and using SQL - but have managed through other people to create some queries.

I have the below SQl query which works well - apart from the last but one column currently brings back some "blanks" - I worked out that I need to replace this with the same confirmed date as the rest of the Customer Order - but not sure how to do this

So - isnull("Confirmed Date",20) needs to change to isnull("Confirmed Date",("Confirmed Date" by "Customer Order Number")

In other words the blanks need to match the rest of the date that matches the rest of the Customer Order number

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.185
ELSE OOLINE.[Sales price]
END AS [Sales Price],
CASE
WHEN OOHEAD.[Currency] = 'EUR' THEN ISNULL(MITFAC.M9APPR / 1.185, 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 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
right([Order Created],2)+'/'+substring(convert(varchar(8),[Order Created]),5,2)+'/'+left([Order Created],4) as "Order Created",
right([Required date],2)+'/'+substring(convert(varchar(8),[Required date]),5,2)+'/'+left([Required date],4) as "Required Date",
right([Installation date],2)+'/'+substring(convert(varchar(8),[Installation date]),5,2)+'/'+left([Installation date],4) as "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,
right([Confirmed Date],2)+'/'+substring(convert(varchar(8),[Confirmed Date]),5,2)+'/'+left([Confirmed date],4) as "Confirmed Date",
isnull("Confirmed Date",20),
[Individual Item]
FROM cteData
ORDER BY [Customer Order Number];

ajp43
Starting Member

6 Posts

Posted - 2015-04-14 : 11:33:59

Does anyone know how to convert a blank to the date of the rest of the order

So - the below blanks on the confirmed date need to automatically match the rest of the same customer order.

Customer Order Number Customer No Customer Name Postcode Item Number Customers order number Confirmed Date
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ XX-6TRANSPORT HRR05385
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ 1007734 HRR05385 10/04/2015
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ CXVINTAGE-3GP HRR05385 10/04/2015
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ XX-6MACHINEPACK HRR05385
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ SERVKIT23 HRR05385 10/04/2015
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ XX-6SPECIAL HRR05385
1000289261 Y412972 COSTA - DERBY 1 DE1 2AZ XX-6SPECIAL HRR05385

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-14 : 13:27:15

[code]
update table
set [Confirmed Date] = GoodDate
from table t1
cross apply
(
select top 1 [Confirmed Date]
from table
where [Customer Order Number] = t1.[Customer Order Number]
and ISNULL([Confirmed Date], '') <> ''
) _(GoodDate)
where t1.ISNULL([Confirmed Date], '') = ''
[/code]

ajp43
Starting Member

6 Posts

Posted - 2015-04-15 : 03:57:34

Thanks for this.

As I'm new to this

Are you able to put the full query with the above query - as not sure how to put this is

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 09:26:16

Just before the ORDER BY clause:


    , right(GoodDate, 2) + '/' + substring(convert(VARCHAR(8), GoodDate), 5, 2) + '/' + left(GoodDate, 4) AS "Confirmed Date"
    , isnull("Confirmed Date", 20)
    , [Individual Item]
from ctedata t1
cross apply
(
    select max([[Confirmed Date])
    from ctedata
    where [Customer Order Number] = t1.[Customer Order Number]
      and ISNULL([Confirmed Date], '') <> ''
) _(GoodDate)

Note that there are many optimization and refactoring opportunities with your query:

1. replace FROM {oj( ... ) with FROM firstable OUTERJOIN (subquery) style coding
2. Refactor the big WHERE statement. eg.


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

clearly if OOLINE.[Item Number] LIKE 'CKD360/KB%'
then OOLINE.[Item Number] LIKE 'CKD360%'

so the longer one is redundant.


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

can be written in one line:


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

as can


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

which can be just


OR OOLINE.[Item Number] LIKE 'OPTION [HMNQ]%'
[/code]
3. The huge subquery inside the {oj(...)} makes my head spin.  Try to refactor this to simplify it.  Perhaps with new CTEs to break up the problem. Note that this is a business problem, so you need to understand thoroughly what your business rules.  No one else on the forum can do that, I'm afraid, since we are not inside your company. Perhaps first try to write out the rules in English as to what each table means to the query.

ajp43
Starting Member

6 Posts

Posted - 2015-04-15 : 10:08:40

This is confusing me as well.

Whatever way I put this in - this does not work

Are you able to put the complete query in - perhaps as you would do it.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 12:00:02

This is your query with the additional logic added. I also refactored the large WHERE clause as I suggested. Note that I cannot refactor the FROM {oj(...)} since that depends on your business rules. However, I urge you to change that to standard T-SQL. It will make maintenance easier.


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.185
            ELSE OOLINE.[Sales price]
            END AS [Sales Price]
        , CASE 
            WHEN OOHEAD.[Currency] = 'EUR'
                THEN ISNULL(MITFAC.M9APPR / 1.185, 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 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%' -- redundant
            OR OOLINE.[Item Number] LIKE 'CORALPB5CASING%'
            --OR OOLINE.[Item Number] LIKE 'COSKOSD%'    -- combined below
            --OR OOLINE.[Item Number] LIKE 'COSKOTD%'    -- combined below
            OR OOLINE.[Item Number] LIKE 'COSKO[ST]D%' -- combined form
            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%'      -- combined below
            --OR OOLINE.[Item Number] LIKE 'OPTION M%'      -- combined below
            --OR OOLINE.[Item Number] LIKE 'OPTION N%'      -- combined below
            --OR OOLINE.[Item Number] LIKE 'OPTION Q%'      -- combined below
            OR OOLINE.[Item Number] LIKE 'OPTION [HMNQ]%'   -- combined 
            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 right([Order Created], 2) + '/' + substring(convert(VARCHAR(8), [Order Created]), 5, 2) + '/' + left([Order Created], 4) AS "Order Created"
    , right([Required date], 2) + '/' + substring(convert(VARCHAR(8), [Required date]), 5, 2) + '/' + left([Required date], 4) AS "Required Date"
    , right([Installation date], 2) + '/' + substring(convert(VARCHAR(8), [Installation date]), 5, 2) + '/' + left([Installation date], 4) AS "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
    , right(GoodDate, 2) + '/' + substring(convert(VARCHAR(8), GoodDate), 5, 2) + '/' + left(GoodDate, 4) AS "Confirmed Date"
    , isnull("Confirmed Date", 20)
    , [Individual Item]
FROM ctedata t1
CROSS APPLY (
    SELECT max([[Confirmed Date])
    FROM ctedata
    WHERE [Customer Order Number] = t1.[Customer Order Number]
        AND ISNULL([Confirmed Date], '') <> ''
    ) _(GoodDate)
ORDER BY [Customer Order Number];

ajp43
Starting Member

6 Posts

Posted - 2015-04-16 : 11:06:52

Excellent - thank you for this - works well and quickly

ajp43
Starting Member

6 Posts

Posted - 2015-05-06 : 11:24:28

Hello

Update from the above - I now am working on the below - but finding that it still brings back blanks on some of the lines on confirmed dates.
These lines only have one or two lines and do not have an original confirmed date - so in this instance would need to be - if blank and no other confirmed date with the same Cust order number - then installation date

Below is the query I am working on

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
OOLINE.[Highest status - customer order] >= '66'
AND OOHEAD.[Customer Order Number] >= 1000189948
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 'CORALPB5CASING%'
OR OOLINE.[Item Number] LIKE 'COSKO[ST]D%'
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[HMNQ]%'
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 right([Order Created], 2) + '/' + substring(convert(VARCHAR(8), [Order Created]), 5, 2) + '/' + left([Order Created], 4) AS "Order Created"
, right([Required date], 2) + '/' + substring(convert(VARCHAR(8), [Required date]), 5, 2) + '/' + left([Required date], 4) AS "Required Date"
, right([Installation date], 2) + '/' + substring(convert(VARCHAR(8), [Installation date]), 5, 2) + '/' + left([Installation date], 4) AS "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],
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%],
[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
, right(GoodDate, 2) + '/' + substring(convert(VARCHAR(8), GoodDate), 5, 2) + '/' + left(GoodDate, 4) AS "Confirmed Date"
, [Individual Item]
FROM ctedata t1
CROSS APPLY (
SELECT max([Confirmed Date])
FROM ctedata
WHERE
[Customer Order Number] = t1.[Customer Order Number]
AND ISNULL([Confirmed Date], '') <> ''
) _(GoodDate)
ORDER BY [Customer Order Number];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources