03. februar 2008 - 19:57
Der er
3 kommentarer
CASE med økonomisk beregning.
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å-
09. februar 2008 - 19:40
#3
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
