har lidt problemer med følgende: DG fordeling= case dækningsbidrag/omsætning > 0.4 then 'Over 40%' else 'Ved ikke' end.
både beregningen for dækningsbidrag og omsætning bliver brugt i samme forespørgsel uden problemer. Systemet skriver noget med forventede ikke when efter select kolonne listen.
Syntaks brugt.
'DG fordeling' = case when (INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS. COSTAMOUNTADJUSTMENT)/(CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) >0.4 then 'Over 40%' else 'Ved ikke' end,
Vil gerne kunne bygge flere dg fordelinger på så jeg har brug for <>= muligheder også-
bliver nødt til at hente den når jeg kommer hjem. Men er der et problem hvis enten dækningsbidrag eller omsætning er 0? Så skal jeg måske teste for det først inden jeg laver beregningen.
SELECT DISTINCT month(CUSTINVOICETRANS.INVOICEDATE) AS 'måned', INVENTTABLE.ITEMGROUPID as Varegruppenr, INVENTTABLE.ITEMGROUPID+'-'+inventitemgroup.name as Varegruppe,year(CUSTINVOICETRANS.INVOICEDATE)*100+month(CUSTINVOICETRANS.INVOICEDATE) AS 'år-måned', Datename(Quarter,custinvoicejour.invoicedate) as 'Kalender-kvartal', CUSTINVOICETRANS.DATAAREAID AS 'Regnskab', CUSTINVOICEJOUR.ORDERACCOUNT AS Kundenummer, CUSTINVOICEJOUR.INVOICEACCOUNT AS 'Fakturakonto', CUSTINVOICEJOUR.DATAAREAID+'-'+CUSTINVOICEJOUR.INVOICEACCOUNT AS 'Land-Fakturakonto', CUSTINVOICEJOUR.DLVCOUNTRY AS 'Leveringsland', CUSTINVOICETRANS.INVOICEID AS 'Fakturanummer', CUSTINVOICETRANS.INVOICEID+custinvoicetrans.dataareaid AS 'Fakturanummer-land', CUSTINVOICETRANS.LINENUM AS 'Linienr', CUSTINVOICETRANS.CURRENCYCODE AS 'Valuta', datename(year,CUSTINVOICETRANS.INVOICEDATE) AS 'År', CUSTINVOICETRANS.QTY AS 'Linieantal', CUSTINVOICEJOUR.INVOICINGNAME AS 'Fakturanavn', CUSTINVOICEJOUR.INVOICINGNAME+custinvoicejour.dataareaid AS 'Fakturanavn-land', -INVENTTRANS.QTY AS 'Antal', CUSTINVOICETRANS.ITEMID AS 'Varenummer', CUSTINVOICETRANS.NAME AS 'Varenavn', CUSTINVOICETRANS.ITEMID+'-'+custinvoicetrans.name AS 'Varenummer-Navn', INVENTITEMGROUP.NAME AS 'Varegruppenavn', DB= CASE custinvoicetrans.dataareaid WHEN 'se' THEN (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+(INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS.COSTAMOUNTADJUSTMENT))*0.8144 when 'no' then (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+(INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS. COSTAMOUNTADJUSTMENT))*0.9172 ELSE (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+(INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS. COSTAMOUNTADJUSTMENT)) END, 'DB fordeling'= CASE when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+INVENTTRANS. COSTAMOUNTADJUSTMENT) <-5000 then 'under -5.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+INVENTTRANS. COSTAMOUNTADJUSTMENT) between -5000 and 0 then '-5.000 til 0' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+INVENTTRANS. COSTAMOUNTADJUSTMENT) between 0 and 10000 then '0 til 10.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+INVENTTRANS. COSTAMOUNTADJUSTMENT) between 10000 and 30000 then '10.000 til 30.000'when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+INVENTTRANS. COSTAMOUNTADJUSTMENT) between 30000 and 50000 then '30.000 til 50.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty+INVENTTRANS. COSTAMOUNTADJUSTMENT) >50000 then 'Over 50.000'else 'Ved ikke' end, Vareforbrug= CASE custinvoicetrans.dataareaid WHEN 'se' THEN (INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS. COSTAMOUNTADJUSTMENT)*0.8144 when 'no' then (INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS. COSTAMOUNTADJUSTMENT)*0.9172 ELSE (INVENTTRANS.COSTAMOUNTPOSTED+INVENTTRANS. COSTAMOUNTADJUSTMENT) END, Genberegning = CASE custinvoicetrans.dataareaid WHEN 'se' THEN COSTAMOUNTADJUSTMENT*0.8144 when 'no' then COSTAMOUNTADJUSTMENT*0.9172 ELSE COSTAMOUNTADJUSTMENT END, Moms= CASE custinvoicetrans.dataareaid WHEN 'se' THEN CUSTINVOICETRANS.LINEAMOUNTTAXMST*0.8144 when 'no' then CUSTINVOICETRANS.LINEAMOUNTTAXMST*0.9172 ELSE CUSTINVOICETRANS.LINEAMOUNTTAXMST END, Stykpris = CASE custinvoicetrans.dataareaid WHEN 'se' THEN CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*0.8144 when 'no' then CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*0.9172 ELSE CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY END, Omsætning= CASE custinvoicetrans.dataareaid WHEN 'se' THEN CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty*0.8144 when 'no' then CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty*0.9172 ELSE CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty END, Linierabat = CASE custinvoicetrans.dataareaid WHEN 'se' THEN CUSTINVOICETRANS.LINEDISC*0.8144 when 'no' then CUSTINVOICETRANS.LINEDISC*0.9172 ELSE CUSTINVOICETRANS.LINEDISC END , 'Omsætningsfordeling'= CASE when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) <-5000 then 'under -5.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) between -5000 and 0 then '-5.000 til 0' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) between 0 and 10000 then '0 til 10.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) between 10000 and 30000 then '10.000 til 30.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) between 30000 and 50000 then '30.000 til 50.000' when (CUSTINVOICETRANS.LINEAMOUNTMST/CUSTINVOICETRANS.QTY*-inventtrans.qty) >50000 then 'Over 50.000'else 'Ved ikke' end, Rabat= CASE custinvoicetrans.dataareaid WHEN 'se' THEN CUSTINVOICETRANS.SUMLINEDISCMST*0.8144 when 'no' then CUSTINVOICETRANS.SUMLINEDISCMST*0.9172 ELSE CUSTINVOICETRANS.SUMLINEDISCMST END, Intercompany = CASE when custinvoicejour.invoicingname like '%Applia%' then 'Ja' else 'Nej' end, Omsætningsland = case CUSTINVOICEJOUR.DLVCOUNTRY when 'NO' then 'NO' when 'SE' then 'SE' When 'FI' then 'FI' else 'DK' end, Regnskabsår=case year(CUSTINVOICETRANS.INVOICEDATE)*100+month(CUSTINVOICETRANS.INVOICEDATE) when 200506 then '04/05' when 200507 then '05/06' when 200508 then '05/06' when 200509 then '05/06' when 200510 then '05/06' when 200511 then '05/06' when 200512 then '05/06' when 200601 then '05/06' when 200602 then '05/06' when 200603 then '05/06' when 200604 then '05/06' when 200605 then '05/06' when 200606 then '05/06' when 200607 then '06/07' when 200608 then '06/07' when 200609 then '06/07' when 200610 then '06/07' when 200611 then '06/07' when 200612 then '06/07' when 200701 then '06/07' when 200702 then '06/07' when 200703 then '06/07' when 200704 then '06/07' when 200705 then '06/07' when 200706 then '06/07' when 200707 then '07/08' when 200708 then '07/08' when 200709 then '07/08' when 200710 then '07/08' when 200711 then '07/08' when 200712 then '07/08' when 200801 then '07/08' when 200802 then '07/08' when 200803 then '07/08' when 200804 then '07/08' when 200805 then '07/08' when 200806 then '07/08' else 'Ved ikke' end, Kvartal= CASE month(CUSTINVOICETRANS.INVOICEDATE) WHEN 7 THEN '1. kvartal' when 8 THEN '1. kvartal' when 9 THEN '1. kvartal' when 10 THEN '2. kvartal' when 11 THEN '2. kvartal' when 12 THEN '2. kvartal' when 1 THEN '3. kvartal' when 2 THEN '3. kvartal' when 3 THEN '3. kvartal' when 4 THEN '4. kvartal' when 5 THEN '4. kvartal' when 6 then '4. kvartal' else 'ukendt måned' end FROM axdb_sp4.dbo.CUSTINVOICEJOUR CUSTINVOICEJOUR, axdb_sp4.dbo.CUSTINVOICETRANS CUSTINVOICETRANS, axdb_sp4.dbo.INVENTITEMGROUP INVENTITEMGROUP, axdb_sp4.dbo.INVENTTABLE INVENTTABLE, axdb_sp4.dbo.INVENTTRANS INVENTTRANS, axdb_sp4.dbo.INVENTTRANSPOSTING INVENTTRANSPOSTING, axdb_sp4.dbo.SALESTABLE SALESTABLE WHERE INVENTTRANSPOSTING.INVENTTRANSID = CUSTINVOICETRANS.INVENTTRANSID AND INVENTTRANSPOSTING.INVENTTRANSID = INVENTTRANS.INVENTTRANSID AND INVENTTABLE.DATAAREAID = CUSTINVOICETRANS.DATAAREAID AND INVENTTABLE.ITEMID = CUSTINVOICETRANS.ITEMID AND INVENTTABLE.ITEMID = INVENTTRANS.ITEMID AND INVENTTABLE.ITEMID = INVENTTRANSPOSTING.ITEMID AND CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID AND CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND INVENTTRANS.INVENTTRANSID = CUSTINVOICETRANS.INVENTTRANSID AND INVENTTRANS.INVOICEID = CUSTINVOICETRANS.INVOICEID AND SALESTABLE.DATAAREAID = CUSTINVOICETRANS.DATAAREAID AND SALESTABLE.SALESID = CUSTINVOICETRANS.SALESID AND INVENTITEMGROUP.DATAAREAID = INVENTTABLE.DATAAREAID AND INVENTITEMGROUP.ITEMGROUPID = INVENTTABLE.ITEMGROUPID AND ((CUSTINVOICETRANS.INVOICEID<>'') AND (INVENTTRANSPOSTING.ACCOUNT<>'') and (not custinvoicetrans.dataareaid='åp1' ) and (not custinvoicetrans.dataareaid='å2')) ORDER BY year(CUSTINVOICETRANS.INVOICEDATE)*100+month(CUSTINVOICETRANS.INVOICEDATE) DESC
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
