--************************************************************************** --*　売上データの出力範囲を取得 --*　 --************************************************************************** [lselectRecords] select mc.CD_CUSTOMER ,mc.name_customer FROM m_customer mc LEFT OUTER JOIN t_sale ts ON (mc.CD_CUSTOMER = ts.CD_CUSTOMER) WHERE ts.DT_SALE >= '&2' and ts.DT_SALE <= '&3' and (('&4' = '') or (ts.CD_CUSTOMER >= TO_NUMBER('&4'))) and (('&5' = '') or (ts.CD_CUSTOMER <= TO_NUMBER('&5'))) and (('&6' = '') or (ts.CD_DERIVERY >= TO_NUMBER('&6'))) and (('&7' = '') or (ts.CD_DERIVERY >= TO_NUMBER('&7'))) and (('&8' = '') or (ts.CD_GREAD >= TO_NUMBER('&8'))) and ts.FLG_DELETE = 0 --入金データ union select mc.CD_CUSTOMER ,mc.name_customer FROM m_customer mc LEFT OUTER JOIN t_deposit td ON (mc.CD_CUSTOMER = td.CD_CUSTOMER) WHERE td.DT_DEPOSIT >= '&2' and td.DT_DEPOSIT <= '&3' and (('&4' = '') or (td.CD_CUSTOMER >= TO_NUMBER('&4'))) and (('&5' = '') or (td.CD_CUSTOMER <= TO_NUMBER('&5'))) and (('&6' = '') or (td.CD_DERIVERY >= TO_NUMBER('&6'))) and (('&7' = '') or (td.CD_DERIVERY >= TO_NUMBER('&7'))) and td.DIV_DELETE = 0 --************************************************************************** --*　全得意先の売上データを取得 --*　 --************************************************************************** [lselectRecords_TotalCustomer] SELECT -- 単価 round((CASE WHEN SUM(TS.WEIGHT_SALE01)=0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END )::numeric,1) UNIT_PRICE_SALE1 ,round((CASE WHEN SUM(TS.WEIGHT_SALE02)=0 THEN SUM(TS.MONEY_SALE02) ELSE SUM(TS.MONEY_SALE02) / SUM(TS.WEIGHT_SALE02) END )::numeric,1) UNIT_PRICE_SALE2 ,round((CASE WHEN SUM(TS.WEIGHT_SALE03)=0 THEN SUM(TS.MONEY_SALE03) ELSE SUM(TS.MONEY_SALE03) / SUM(TS.WEIGHT_SALE03) END )::numeric,1) UNIT_PRICE_SALE3 ,round((CASE WHEN SUM(TS.WEIGHT_SALE04)=0 THEN SUM(TS.MONEY_SALE04) ELSE SUM(TS.MONEY_SALE04) / SUM(TS.WEIGHT_SALE04) END )::numeric,1) UNIT_PRICE_SALE4 ,round((CASE WHEN SUM(TS.WEIGHT_SALE05)=0 THEN SUM(TS.MONEY_SALE05) ELSE SUM(TS.MONEY_SALE05) / SUM(TS.WEIGHT_SALE05) END )::numeric,1) UNIT_PRICE_SALE5 ,round((CASE WHEN SUM(TS.WEIGHT_SALE06)=0 THEN SUM(TS.MONEY_SALE06) ELSE SUM(TS.MONEY_SALE06) / SUM(TS.WEIGHT_SALE06) END )::numeric,1) UNIT_PRICE_SALE6 ,round((CASE WHEN SUM(TS.WEIGHT_SALE07)=0 THEN SUM(TS.MONEY_SALE07) ELSE SUM(TS.MONEY_SALE07) / SUM(TS.WEIGHT_SALE07) END )::numeric,1) UNIT_PRICE_SALE7 ,round((CASE WHEN SUM(TS.WEIGHT_SALE08)=0 THEN SUM(TS.MONEY_SALE08) ELSE SUM(TS.MONEY_SALE08) / SUM(TS.WEIGHT_SALE08) END )::numeric,1) UNIT_PRICE_SALE8 ,round((CASE WHEN SUM(TS.WEIGHT_SALE09)=0 THEN SUM(TS.MONEY_SALE09) ELSE SUM(TS.MONEY_SALE09) / SUM(TS.WEIGHT_SALE09) END )::numeric,1) UNIT_PRICE_SALE9 ,round((CASE WHEN SUM(TS.WEIGHT_SALE10)=0 THEN SUM(TS.MONEY_SALE10) ELSE SUM(TS.MONEY_SALE10) / SUM(TS.WEIGHT_SALE10) END )::numeric,1) UNIT_PRICE_SALE10 ,round((CASE WHEN SUM(TS.WEIGHT_SALE11)=0 THEN SUM(TS.MONEY_SALE11) ELSE SUM(TS.MONEY_SALE11) / SUM(TS.WEIGHT_SALE11) END )::numeric,1) UNIT_PRICE_SALE11 ,round((CASE WHEN SUM(TS.WEIGHT_SALE12)=0 THEN SUM(TS.MONEY_SALE12) ELSE SUM(TS.MONEY_SALE12) / SUM(TS.WEIGHT_SALE12) END )::numeric,1) UNIT_PRICE_SALE12 -- 月別の数量 ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 -- 月別の売上金額 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 -- 月別の消費税 ,trunc((SUM(TS.TAX01)/1000)+0.5) TAX1 ,trunc((SUM(TS.TAX02)/1000)+0.5) TAX2 ,trunc((SUM(TS.TAX03)/1000)+0.5) TAX3 ,trunc((SUM(TS.TAX04)/1000)+0.5) TAX4 ,trunc((SUM(TS.TAX05)/1000)+0.5) TAX5 ,trunc((SUM(TS.TAX06)/1000)+0.5) TAX6 ,trunc((SUM(TS.TAX07)/1000)+0.5) TAX7 ,trunc((SUM(TS.TAX08)/1000)+0.5) TAX8 ,trunc((SUM(TS.TAX09)/1000)+0.5) TAX9 ,trunc((SUM(TS.TAX10)/1000)+0.5) TAX10 ,trunc((SUM(TS.TAX11)/1000)+0.5) TAX11 ,trunc((SUM(TS.TAX12)/1000)+0.5) TAX12 -- 月別の入金額 ,trunc((SUM(TD.MONEY_DEPOSIT01)/1000)+0.5) MONEY_DEPOSIT1 ,trunc((SUM(TD.MONEY_DEPOSIT02)/1000)+0.5) MONEY_DEPOSIT2 ,trunc((SUM(TD.MONEY_DEPOSIT03)/1000)+0.5) MONEY_DEPOSIT3 ,trunc((SUM(TD.MONEY_DEPOSIT04)/1000)+0.5) MONEY_DEPOSIT4 ,trunc((SUM(TD.MONEY_DEPOSIT05)/1000)+0.5) MONEY_DEPOSIT5 ,trunc((SUM(TD.MONEY_DEPOSIT06)/1000)+0.5) MONEY_DEPOSIT6 ,trunc((SUM(TD.MONEY_DEPOSIT07)/1000)+0.5) MONEY_DEPOSIT7 ,trunc((SUM(TD.MONEY_DEPOSIT08)/1000)+0.5) MONEY_DEPOSIT8 ,trunc((SUM(TD.MONEY_DEPOSIT09)/1000)+0.5) MONEY_DEPOSIT9 ,trunc((SUM(TD.MONEY_DEPOSIT10)/1000)+0.5) MONEY_DEPOSIT10 ,trunc((SUM(TD.MONEY_DEPOSIT11)/1000)+0.5) MONEY_DEPOSIT11 ,trunc((SUM(TD.MONEY_DEPOSIT12)/1000)+0.5) MONEY_DEPOSIT12 -- 調整額は売上調整額と伝票の運賃と入金調整額とする。 -- 日別の伝票調整額 + 日別の運賃 - 日別の入金調整額 ,trunc((SUM(TS.MONEY_ADJUST01 + TS.MONEY_FREIGHT01 - TD.MONEY_DEPOSIT_ADJ01 )/1000)+0.5) ADJUST1 ,trunc((SUM(TS.MONEY_ADJUST02 + TS.MONEY_FREIGHT02 - TD.MONEY_DEPOSIT_ADJ02 )/1000)+0.5) ADJUST2 ,trunc((SUM(TS.MONEY_ADJUST03 + TS.MONEY_FREIGHT03 - TD.MONEY_DEPOSIT_ADJ03 )/1000)+0.5) ADJUST3 ,trunc((SUM(TS.MONEY_ADJUST04 + TS.MONEY_FREIGHT04 - TD.MONEY_DEPOSIT_ADJ04 )/1000)+0.5) ADJUST4 ,trunc((SUM(TS.MONEY_ADJUST05 + TS.MONEY_FREIGHT05 - TD.MONEY_DEPOSIT_ADJ05 )/1000)+0.5) ADJUST5 ,trunc((SUM(TS.MONEY_ADJUST06 + TS.MONEY_FREIGHT06 - TD.MONEY_DEPOSIT_ADJ06 )/1000)+0.5) ADJUST6 ,trunc((SUM(TS.MONEY_ADJUST07 + TS.MONEY_FREIGHT07 - TD.MONEY_DEPOSIT_ADJ07 )/1000)+0.5) ADJUST7 ,trunc((SUM(TS.MONEY_ADJUST08 + TS.MONEY_FREIGHT08 - TD.MONEY_DEPOSIT_ADJ08 )/1000)+0.5) ADJUST8 ,trunc((SUM(TS.MONEY_ADJUST09 + TS.MONEY_FREIGHT09 - TD.MONEY_DEPOSIT_ADJ09 )/1000)+0.5) ADJUST9 ,trunc((SUM(TS.MONEY_ADJUST10 + TS.MONEY_FREIGHT10 - TD.MONEY_DEPOSIT_ADJ10 )/1000)+0.5) ADJUST10 ,trunc((SUM(TS.MONEY_ADJUST11 + TS.MONEY_FREIGHT11 - TD.MONEY_DEPOSIT_ADJ11 )/1000)+0.5) ADJUST11 ,trunc((SUM(TS.MONEY_ADJUST12 + TS.MONEY_FREIGHT12 - TD.MONEY_DEPOSIT_ADJ12 )/1000)+0.5) ADJUST12 FROM m_customer mc LEFT OUTER JOIN ( SELECT TS02.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の売上データ -- 月別の数量 ,SUM(TS02.WEIGHT_SALE01) WEIGHT_SALE01 ,SUM(TS02.WEIGHT_SALE02) WEIGHT_SALE02 ,SUM(TS02.WEIGHT_SALE03) WEIGHT_SALE03 ,SUM(TS02.WEIGHT_SALE04) WEIGHT_SALE04 ,SUM(TS02.WEIGHT_SALE05) WEIGHT_SALE05 ,SUM(TS02.WEIGHT_SALE06) WEIGHT_SALE06 ,SUM(TS02.WEIGHT_SALE07) WEIGHT_SALE07 ,SUM(TS02.WEIGHT_SALE08) WEIGHT_SALE08 ,SUM(TS02.WEIGHT_SALE09) WEIGHT_SALE09 ,SUM(TS02.WEIGHT_SALE10) WEIGHT_SALE10 ,SUM(TS02.WEIGHT_SALE11) WEIGHT_SALE11 ,SUM(TS02.WEIGHT_SALE12) WEIGHT_SALE12 -- 月別の売上金額 ,SUM(TS02.MONEY_SALE01) MONEY_SALE01 ,SUM(TS02.MONEY_SALE02) MONEY_SALE02 ,SUM(TS02.MONEY_SALE03) MONEY_SALE03 ,SUM(TS02.MONEY_SALE04) MONEY_SALE04 ,SUM(TS02.MONEY_SALE05) MONEY_SALE05 ,SUM(TS02.MONEY_SALE06) MONEY_SALE06 ,SUM(TS02.MONEY_SALE07) MONEY_SALE07 ,SUM(TS02.MONEY_SALE08) MONEY_SALE08 ,SUM(TS02.MONEY_SALE09) MONEY_SALE09 ,SUM(TS02.MONEY_SALE10) MONEY_SALE10 ,SUM(TS02.MONEY_SALE11) MONEY_SALE11 ,SUM(TS02.MONEY_SALE12) MONEY_SALE12 -- 月別の消費税 ,SUM(TS02.TAX01) TAX01 ,SUM(TS02.TAX02) TAX02 ,SUM(TS02.TAX03) TAX03 ,SUM(TS02.TAX04) TAX04 ,SUM(TS02.TAX05) TAX05 ,SUM(TS02.TAX06) TAX06 ,SUM(TS02.TAX07) TAX07 ,SUM(TS02.TAX08) TAX08 ,SUM(TS02.TAX09) TAX09 ,SUM(TS02.TAX10) TAX10 ,SUM(TS02.TAX11) TAX11 ,SUM(TS02.TAX12) TAX12 -- 月別の伝票調整額 ,SUM(TS02.MONEY_ADJUST01) MONEY_ADJUST01 ,SUM(TS02.MONEY_ADJUST02) MONEY_ADJUST02 ,SUM(TS02.MONEY_ADJUST03) MONEY_ADJUST03 ,SUM(TS02.MONEY_ADJUST04) MONEY_ADJUST04 ,SUM(TS02.MONEY_ADJUST05) MONEY_ADJUST05 ,SUM(TS02.MONEY_ADJUST06) MONEY_ADJUST06 ,SUM(TS02.MONEY_ADJUST07) MONEY_ADJUST07 ,SUM(TS02.MONEY_ADJUST08) MONEY_ADJUST08 ,SUM(TS02.MONEY_ADJUST09) MONEY_ADJUST09 ,SUM(TS02.MONEY_ADJUST10) MONEY_ADJUST10 ,SUM(TS02.MONEY_ADJUST11) MONEY_ADJUST11 ,SUM(TS02.MONEY_ADJUST12) MONEY_ADJUST12 -- 月別の運賃 ,SUM(TS02.MONEY_FREIGHT01) MONEY_FREIGHT01 ,SUM(TS02.MONEY_FREIGHT02) MONEY_FREIGHT02 ,SUM(TS02.MONEY_FREIGHT03) MONEY_FREIGHT03 ,SUM(TS02.MONEY_FREIGHT04) MONEY_FREIGHT04 ,SUM(TS02.MONEY_FREIGHT05) MONEY_FREIGHT05 ,SUM(TS02.MONEY_FREIGHT06) MONEY_FREIGHT06 ,SUM(TS02.MONEY_FREIGHT07) MONEY_FREIGHT07 ,SUM(TS02.MONEY_FREIGHT08) MONEY_FREIGHT08 ,SUM(TS02.MONEY_FREIGHT09) MONEY_FREIGHT09 ,SUM(TS02.MONEY_FREIGHT10) MONEY_FREIGHT10 ,SUM(TS02.MONEY_FREIGHT11) MONEY_FREIGHT11 ,SUM(TS02.MONEY_FREIGHT12) MONEY_FREIGHT12 FROM( -- 得意先別納品先ごとのデータ SELECT TS01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の売上データ -- 月別の数量 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE12 -- 月別の売上金額 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE12 -- 月別の消費税 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.TAX ELSE 0 END TAX01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.TAX ELSE 0 END TAX02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.TAX ELSE 0 END TAX03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.TAX ELSE 0 END TAX04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.TAX ELSE 0 END TAX05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.TAX ELSE 0 END TAX06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.TAX ELSE 0 END TAX07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.TAX ELSE 0 END TAX08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.TAX ELSE 0 END TAX09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.TAX ELSE 0 END TAX10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.TAX ELSE 0 END TAX11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.TAX ELSE 0 END TAX12 -- 月別の伝票調整額 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST12 -- 月別の運賃 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT12 FROM T_SALE TS01 WHERE TS01.DT_SALE >= '&2' AND TS01.DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (('&8' = '') OR (TS01.CD_GREAD = ('&8' ::text) ::numeric)) ) TS02 GROUP BY TS02.CD_CUSTOMER ) ts ON (MC.CD_CUSTOMER = TS.CD_CUSTOMER) LEFT OUTER JOIN ( SELECT TD02.CD_CUSTOMER CD_CUSTOMER ,SUM(TD02.MONEY_DEPOSIT01) MONEY_DEPOSIT01 ,SUM(TD02.MONEY_DEPOSIT02) MONEY_DEPOSIT02 ,SUM(TD02.MONEY_DEPOSIT03) MONEY_DEPOSIT03 ,SUM(TD02.MONEY_DEPOSIT04) MONEY_DEPOSIT04 ,SUM(TD02.MONEY_DEPOSIT05) MONEY_DEPOSIT05 ,SUM(TD02.MONEY_DEPOSIT06) MONEY_DEPOSIT06 ,SUM(TD02.MONEY_DEPOSIT07) MONEY_DEPOSIT07 ,SUM(TD02.MONEY_DEPOSIT08) MONEY_DEPOSIT08 ,SUM(TD02.MONEY_DEPOSIT09) MONEY_DEPOSIT09 ,SUM(TD02.MONEY_DEPOSIT10) MONEY_DEPOSIT10 ,SUM(TD02.MONEY_DEPOSIT11) MONEY_DEPOSIT11 ,SUM(TD02.MONEY_DEPOSIT12) MONEY_DEPOSIT12 -- 月別の売上調整額 ,SUM(TD02.MONEY_DEPOSIT_ADJ01) MONEY_DEPOSIT_ADJ01 ,SUM(TD02.MONEY_DEPOSIT_ADJ02) MONEY_DEPOSIT_ADJ02 ,SUM(TD02.MONEY_DEPOSIT_ADJ03) MONEY_DEPOSIT_ADJ03 ,SUM(TD02.MONEY_DEPOSIT_ADJ04) MONEY_DEPOSIT_ADJ04 ,SUM(TD02.MONEY_DEPOSIT_ADJ05) MONEY_DEPOSIT_ADJ05 ,SUM(TD02.MONEY_DEPOSIT_ADJ06) MONEY_DEPOSIT_ADJ06 ,SUM(TD02.MONEY_DEPOSIT_ADJ07) MONEY_DEPOSIT_ADJ07 ,SUM(TD02.MONEY_DEPOSIT_ADJ08) MONEY_DEPOSIT_ADJ08 ,SUM(TD02.MONEY_DEPOSIT_ADJ09) MONEY_DEPOSIT_ADJ09 ,SUM(TD02.MONEY_DEPOSIT_ADJ10) MONEY_DEPOSIT_ADJ10 ,SUM(TD02.MONEY_DEPOSIT_ADJ11) MONEY_DEPOSIT_ADJ11 ,SUM(TD02.MONEY_DEPOSIT_ADJ12) MONEY_DEPOSIT_ADJ12 FROM ( SELECT TD01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の入金データ -- 月別の売上入金 ,CASE WHEN TD01.DTS_MONTH=01 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT01 ,CASE WHEN TD01.DTS_MONTH=02 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT02 ,CASE WHEN TD01.DTS_MONTH=03 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT03 ,CASE WHEN TD01.DTS_MONTH=04 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT04 ,CASE WHEN TD01.DTS_MONTH=05 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT05 ,CASE WHEN TD01.DTS_MONTH=06 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT06 ,CASE WHEN TD01.DTS_MONTH=07 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT07 ,CASE WHEN TD01.DTS_MONTH=08 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT08 ,CASE WHEN TD01.DTS_MONTH=09 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT09 ,CASE WHEN TD01.DTS_MONTH=10 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT10 ,CASE WHEN TD01.DTS_MONTH=11 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT11 ,CASE WHEN TD01.DTS_MONTH=12 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT12 -- 月別の売上調整額 ,CASE WHEN TD01.DTS_MONTH=01 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ01 ,CASE WHEN TD01.DTS_MONTH=02 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ02 ,CASE WHEN TD01.DTS_MONTH=03 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ03 ,CASE WHEN TD01.DTS_MONTH=04 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ04 ,CASE WHEN TD01.DTS_MONTH=05 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ05 ,CASE WHEN TD01.DTS_MONTH=06 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ06 ,CASE WHEN TD01.DTS_MONTH=07 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ07 ,CASE WHEN TD01.DTS_MONTH=08 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ08 ,CASE WHEN TD01.DTS_MONTH=09 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ09 ,CASE WHEN TD01.DTS_MONTH=10 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ10 ,CASE WHEN TD01.DTS_MONTH=11 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ11 ,CASE WHEN TD01.DTS_MONTH=12 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ12 FROM T_DEPOSIT TD01 where TD01.DT_DEPOSIT >= '&2' AND TD01.DT_DEPOSIT <= '&3' AND TD01.DIV_DELETE = 0 ) TD02 GROUP BY TD02.CD_CUSTOMER ) td ON (MC.CD_CUSTOMER = TD.CD_CUSTOMER) WHERE MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) AND (('&4' = '') OR (MC.CD_CUSTOMER >= ('&4' ::text) ::numeric)) AND (('&5' = '') OR (MC.CD_CUSTOMER <= ('&5' ::text) ::numeric)) --************************************************************************** --*　得意先、納品先別の売上データを取得 --*　グラフ表示用(得意先の降順で出力)　　2004/05/06　新規追加　　konishi --************************************************************************** [lselectRecords_customer_data] SELECT MC.CD_CUSTOMER ,MC.NAME_CUSTOMER ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 FROM M_CUSTOMER MC,( SELECT TS01.CD_CUSTOMER ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE12 FROM T_SALE TS01 WHERE DT_SALE >= '&2' and DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (( '&8' = '' ) OR (TS01.CD_GREAD = ('&8' ::text) ::numeric )) group by CD_CUSTOMER ) TS WHERE (('&4' = '') OR (MC.CD_CUSTOMER >= ('&4' ::text) ::numeric)) AND (('&5' = '') OR (MC.CD_CUSTOMER <= ('&5' ::text) ::numeric)) AND MC.CD_CUSTOMER = TS.CD_CUSTOMER AND MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) GROUP BY MC.CD_CUSTOMER, MC.NAME_CUSTOMER ORDER BY MC.CD_CUSTOMER desc , MC.NAME_CUSTOMER desc --************************************************************************** --*　得意先、納品先別の売上データを取得 --*　 --************************************************************************** -- 納品先別の売上計（当月分）(得意先別シートの明細部分) [lselectRecords_customer_saledata] SELECT MC.CD_CUSTOMER ,MC.NAME_CUSTOMER ,round((CASE WHEN SUM(TS.WEIGHT_SALE01)=0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END )::numeric,1) UNIT_PRICE_SALE1 ,round((CASE WHEN SUM(TS.WEIGHT_SALE02)=0 THEN SUM(TS.MONEY_SALE02) ELSE SUM(TS.MONEY_SALE02) / SUM(TS.WEIGHT_SALE02) END )::numeric,1) UNIT_PRICE_SALE2 ,round((CASE WHEN SUM(TS.WEIGHT_SALE03)=0 THEN SUM(TS.MONEY_SALE03) ELSE SUM(TS.MONEY_SALE03) / SUM(TS.WEIGHT_SALE03) END )::numeric,1) UNIT_PRICE_SALE3 ,round((CASE WHEN SUM(TS.WEIGHT_SALE04)=0 THEN SUM(TS.MONEY_SALE04) ELSE SUM(TS.MONEY_SALE04) / SUM(TS.WEIGHT_SALE04) END )::numeric,1) UNIT_PRICE_SALE4 ,round((CASE WHEN SUM(TS.WEIGHT_SALE05)=0 THEN SUM(TS.MONEY_SALE05) ELSE SUM(TS.MONEY_SALE05) / SUM(TS.WEIGHT_SALE05) END )::numeric,1) UNIT_PRICE_SALE5 ,round((CASE WHEN SUM(TS.WEIGHT_SALE06)=0 THEN SUM(TS.MONEY_SALE06) ELSE SUM(TS.MONEY_SALE06) / SUM(TS.WEIGHT_SALE06) END )::numeric,1) UNIT_PRICE_SALE6 ,round((CASE WHEN SUM(TS.WEIGHT_SALE07)=0 THEN SUM(TS.MONEY_SALE07) ELSE SUM(TS.MONEY_SALE07) / SUM(TS.WEIGHT_SALE07) END )::numeric,1) UNIT_PRICE_SALE7 ,round((CASE WHEN SUM(TS.WEIGHT_SALE08)=0 THEN SUM(TS.MONEY_SALE08) ELSE SUM(TS.MONEY_SALE08) / SUM(TS.WEIGHT_SALE08) END )::numeric,1) UNIT_PRICE_SALE8 ,round((CASE WHEN SUM(TS.WEIGHT_SALE09)=0 THEN SUM(TS.MONEY_SALE09) ELSE SUM(TS.MONEY_SALE09) / SUM(TS.WEIGHT_SALE09) END )::numeric,1) UNIT_PRICE_SALE9 ,round((CASE WHEN SUM(TS.WEIGHT_SALE10)=0 THEN SUM(TS.MONEY_SALE10) ELSE SUM(TS.MONEY_SALE10) / SUM(TS.WEIGHT_SALE10) END )::numeric,1) UNIT_PRICE_SALE10 ,round((CASE WHEN SUM(TS.WEIGHT_SALE11)=0 THEN SUM(TS.MONEY_SALE11) ELSE SUM(TS.MONEY_SALE11) / SUM(TS.WEIGHT_SALE11) END )::numeric,1) UNIT_PRICE_SALE11 ,round((CASE WHEN SUM(TS.WEIGHT_SALE12)=0 THEN SUM(TS.MONEY_SALE12) ELSE SUM(TS.MONEY_SALE12) / SUM(TS.WEIGHT_SALE12) END )::numeric,1) UNIT_PRICE_SALE12 ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 ,trunc((SUM(TS.TAX01)/1000)+0.5) TAX1 ,trunc((SUM(TS.TAX02)/1000)+0.5) TAX2 ,trunc((SUM(TS.TAX03)/1000)+0.5) TAX3 ,trunc((SUM(TS.TAX04)/1000)+0.5) TAX4 ,trunc((SUM(TS.TAX05)/1000)+0.5) TAX5 ,trunc((SUM(TS.TAX06)/1000)+0.5) TAX6 ,trunc((SUM(TS.TAX07)/1000)+0.5) TAX7 ,trunc((SUM(TS.TAX08)/1000)+0.5) TAX8 ,trunc((SUM(TS.TAX09)/1000)+0.5) TAX9 ,trunc((SUM(TS.TAX10)/1000)+0.5) TAX10 ,trunc((SUM(TS.TAX11)/1000)+0.5) TAX11 ,trunc((SUM(TS.TAX12)/1000)+0.5) TAX12 ,trunc((SUM(TS.MONEY_DEPOSIT01)/1000)+0.5) MONEY_DEPOSIT1 ,trunc((SUM(TS.MONEY_DEPOSIT02)/1000)+0.5) MONEY_DEPOSIT2 ,trunc((SUM(TS.MONEY_DEPOSIT03)/1000)+0.5) MONEY_DEPOSIT3 ,trunc((SUM(TS.MONEY_DEPOSIT04)/1000)+0.5) MONEY_DEPOSIT4 ,trunc((SUM(TS.MONEY_DEPOSIT05)/1000)+0.5) MONEY_DEPOSIT5 ,trunc((SUM(TS.MONEY_DEPOSIT06)/1000)+0.5) MONEY_DEPOSIT6 ,trunc((SUM(TS.MONEY_DEPOSIT07)/1000)+0.5) MONEY_DEPOSIT7 ,trunc((SUM(TS.MONEY_DEPOSIT08)/1000)+0.5) MONEY_DEPOSIT8 ,trunc((SUM(TS.MONEY_DEPOSIT09)/1000)+0.5) MONEY_DEPOSIT9 ,trunc((SUM(TS.MONEY_DEPOSIT10)/1000)+0.5) MONEY_DEPOSIT10 ,trunc((SUM(TS.MONEY_DEPOSIT11)/1000)+0.5) MONEY_DEPOSIT11 ,trunc((SUM(TS.MONEY_DEPOSIT12)/1000)+0.5) MONEY_DEPOSIT12 ,trunc((SUM(TS.MONEY_ADJUST01 + TS.MONEY_FREIGHT01 - TS.MONEY_DEPOSIT_ADJ01 )/1000)+0.5) ADJUST1 ,trunc((SUM(TS.MONEY_ADJUST02 + TS.MONEY_FREIGHT02 - TS.MONEY_DEPOSIT_ADJ02 )/1000)+0.5) ADJUST2 ,trunc((SUM(TS.MONEY_ADJUST03 + TS.MONEY_FREIGHT03 - TS.MONEY_DEPOSIT_ADJ03 )/1000)+0.5) ADJUST3 ,trunc((SUM(TS.MONEY_ADJUST04 + TS.MONEY_FREIGHT04 - TS.MONEY_DEPOSIT_ADJ04 )/1000)+0.5) ADJUST4 ,trunc((SUM(TS.MONEY_ADJUST05 + TS.MONEY_FREIGHT05 - TS.MONEY_DEPOSIT_ADJ05 )/1000)+0.5) ADJUST5 ,trunc((SUM(TS.MONEY_ADJUST06 + TS.MONEY_FREIGHT06 - TS.MONEY_DEPOSIT_ADJ06 )/1000)+0.5) ADJUST6 ,trunc((SUM(TS.MONEY_ADJUST07 + TS.MONEY_FREIGHT07 - TS.MONEY_DEPOSIT_ADJ07 )/1000)+0.5) ADJUST7 ,trunc((SUM(TS.MONEY_ADJUST08 + TS.MONEY_FREIGHT08 - TS.MONEY_DEPOSIT_ADJ08 )/1000)+0.5) ADJUST8 ,trunc((SUM(TS.MONEY_ADJUST09 + TS.MONEY_FREIGHT09 - TS.MONEY_DEPOSIT_ADJ09 )/1000)+0.5) ADJUST9 ,trunc((SUM(TS.MONEY_ADJUST10 + TS.MONEY_FREIGHT10 - TS.MONEY_DEPOSIT_ADJ10 )/1000)+0.5) ADJUST10 ,trunc((SUM(TS.MONEY_ADJUST11 + TS.MONEY_FREIGHT11 - TS.MONEY_DEPOSIT_ADJ11 )/1000)+0.5) ADJUST11 ,trunc((SUM(TS.MONEY_ADJUST12 + TS.MONEY_FREIGHT12 - TS.MONEY_DEPOSIT_ADJ12 )/1000)+0.5) ADJUST12 FROM M_CUSTOMER MC,( SELECT TS01.CD_CUSTOMER ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.TAX ELSE 0 END ) TAX01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.TAX ELSE 0 END ) TAX02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.TAX ELSE 0 END ) TAX03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.TAX ELSE 0 END ) TAX04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.TAX ELSE 0 END ) TAX05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.TAX ELSE 0 END ) TAX06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.TAX ELSE 0 END ) TAX07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.TAX ELSE 0 END ) TAX08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.TAX ELSE 0 END ) TAX09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.TAX ELSE 0 END ) TAX10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.TAX ELSE 0 END ) TAX11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.TAX ELSE 0 END ) TAX12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT12 ,0 MONEY_DEPOSIT01 ,0 MONEY_DEPOSIT02 ,0 MONEY_DEPOSIT03 ,0 MONEY_DEPOSIT04 ,0 MONEY_DEPOSIT05 ,0 MONEY_DEPOSIT06 ,0 MONEY_DEPOSIT07 ,0 MONEY_DEPOSIT08 ,0 MONEY_DEPOSIT09 ,0 MONEY_DEPOSIT10 ,0 MONEY_DEPOSIT11 ,0 MONEY_DEPOSIT12 ,0 MONEY_DEPOSIT_ADJ01 ,0 MONEY_DEPOSIT_ADJ02 ,0 MONEY_DEPOSIT_ADJ03 ,0 MONEY_DEPOSIT_ADJ04 ,0 MONEY_DEPOSIT_ADJ05 ,0 MONEY_DEPOSIT_ADJ06 ,0 MONEY_DEPOSIT_ADJ07 ,0 MONEY_DEPOSIT_ADJ08 ,0 MONEY_DEPOSIT_ADJ09 ,0 MONEY_DEPOSIT_ADJ10 ,0 MONEY_DEPOSIT_ADJ11 ,0 MONEY_DEPOSIT_ADJ12 FROM T_SALE TS01 WHERE DT_SALE >= '&2' and DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (('&8' = '') OR (TS01.CD_GREAD = ('&8' ::text) ::numeric)) group by CD_CUSTOMER union SELECT TD01.CD_CUSTOMER CD_CUSTOMER ,0 WEIGHT_SALE01 ,0 WEIGHT_SALE02 ,0 WEIGHT_SALE03 ,0 WEIGHT_SALE04 ,0 WEIGHT_SALE05 ,0 WEIGHT_SALE06 ,0 WEIGHT_SALE07 ,0 WEIGHT_SALE08 ,0 WEIGHT_SALE09 ,0 WEIGHT_SALE10 ,0 WEIGHT_SALE11 ,0 WEIGHT_SALE12 ,0 MONEY_SALE01 ,0 MONEY_SALE02 ,0 MONEY_SALE03 ,0 MONEY_SALE04 ,0 MONEY_SALE05 ,0 MONEY_SALE06 ,0 MONEY_SALE07 ,0 MONEY_SALE08 ,0 MONEY_SALE09 ,0 MONEY_SALE10 ,0 MONEY_SALE11 ,0 MONEY_SALE12 ,0 TAX01 ,0 TAX02 ,0 TAX03 ,0 TAX04 ,0 TAX05 ,0 TAX06 ,0 TAX07 ,0 TAX08 ,0 TAX09 ,0 TAX10 ,0 TAX11 ,0 TAX12 ,0 MONEY_ADJUST01 ,0 MONEY_ADJUST02 ,0 MONEY_ADJUST03 ,0 MONEY_ADJUST04 ,0 MONEY_ADJUST05 ,0 MONEY_ADJUST06 ,0 MONEY_ADJUST07 ,0 MONEY_ADJUST08 ,0 MONEY_ADJUST09 ,0 MONEY_ADJUST10 ,0 MONEY_ADJUST11 ,0 MONEY_ADJUST12 ,0 MONEY_FREIGHT01 ,0 MONEY_FREIGHT02 ,0 MONEY_FREIGHT03 ,0 MONEY_FREIGHT04 ,0 MONEY_FREIGHT05 ,0 MONEY_FREIGHT06 ,0 MONEY_FREIGHT07 ,0 MONEY_FREIGHT08 ,0 MONEY_FREIGHT09 ,0 MONEY_FREIGHT10 ,0 MONEY_FREIGHT11 ,0 MONEY_FREIGHT12 ,sum(CASE WHEN TD01.DTS_MONTH = 01 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT01 ,sum(CASE WHEN TD01.DTS_MONTH = 02 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT02 ,sum(CASE WHEN TD01.DTS_MONTH = 03 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT03 ,sum(CASE WHEN TD01.DTS_MONTH = 04 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT04 ,sum(CASE WHEN TD01.DTS_MONTH = 05 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT05 ,sum(CASE WHEN TD01.DTS_MONTH = 06 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT06 ,sum(CASE WHEN TD01.DTS_MONTH = 07 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT07 ,sum(CASE WHEN TD01.DTS_MONTH = 08 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT08 ,sum(CASE WHEN TD01.DTS_MONTH = 09 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT09 ,sum(CASE WHEN TD01.DTS_MONTH = 10 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT10 ,sum(CASE WHEN TD01.DTS_MONTH = 11 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT11 ,sum(CASE WHEN TD01.DTS_MONTH = 12 THEN CASE WHEN TD01.DIV_DEPOSIT = 0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT12 ,sum(CASE WHEN TD01.DTS_MONTH = 01 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ01 ,sum(CASE WHEN TD01.DTS_MONTH = 02 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ02 ,sum(CASE WHEN TD01.DTS_MONTH = 03 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ03 ,sum(CASE WHEN TD01.DTS_MONTH = 04 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ04 ,sum(CASE WHEN TD01.DTS_MONTH = 05 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ05 ,sum(CASE WHEN TD01.DTS_MONTH = 06 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ06 ,sum(CASE WHEN TD01.DTS_MONTH = 07 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ07 ,sum(CASE WHEN TD01.DTS_MONTH = 08 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ08 ,sum(CASE WHEN TD01.DTS_MONTH = 09 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ09 ,sum(CASE WHEN TD01.DTS_MONTH = 10 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ10 ,sum(CASE WHEN TD01.DTS_MONTH = 11 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ11 ,sum(CASE WHEN TD01.DTS_MONTH = 12 THEN CASE WHEN TD01.DIV_DEPOSIT = 1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END) MONEY_DEPOSIT_ADJ12 FROM T_DEPOSIT TD01 WHERE TD01.DT_DEPOSIT >= '&2' AND TD01.DT_DEPOSIT <= '&3' AND TD01.DIV_DELETE = 0 group by CD_CUSTOMER ) TS WHERE (('&4' = '') OR (MC.CD_CUSTOMER >= ('&4' ::text) ::numeric)) AND (('&5' = '') OR (MC.CD_CUSTOMER <= ('&5' ::text) ::numeric)) AND MC.CD_CUSTOMER = TS.CD_CUSTOMER AND MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) GROUP BY MC.CD_CUSTOMER, MC.NAME_CUSTOMER --************************************************************************** --*　得意先別、納品先別の売上データを取得 --*　 --************************************************************************** [lselectRecords_TotalDerivery] SELECT -- 単価 round((CASE WHEN SUM(TS.WEIGHT_SALE01)=0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END )::numeric,1) UNIT_PRICE_SALE1 ,round((CASE WHEN SUM(TS.WEIGHT_SALE02)=0 THEN SUM(TS.MONEY_SALE02) ELSE SUM(TS.MONEY_SALE02) / SUM(TS.WEIGHT_SALE02) END )::numeric,1) UNIT_PRICE_SALE2 ,round((CASE WHEN SUM(TS.WEIGHT_SALE03)=0 THEN SUM(TS.MONEY_SALE03) ELSE SUM(TS.MONEY_SALE03) / SUM(TS.WEIGHT_SALE03) END )::numeric,1) UNIT_PRICE_SALE3 ,round((CASE WHEN SUM(TS.WEIGHT_SALE04)=0 THEN SUM(TS.MONEY_SALE04) ELSE SUM(TS.MONEY_SALE04) / SUM(TS.WEIGHT_SALE04) END )::numeric,1) UNIT_PRICE_SALE4 ,round((CASE WHEN SUM(TS.WEIGHT_SALE05)=0 THEN SUM(TS.MONEY_SALE05) ELSE SUM(TS.MONEY_SALE05) / SUM(TS.WEIGHT_SALE05) END )::numeric,1) UNIT_PRICE_SALE5 ,round((CASE WHEN SUM(TS.WEIGHT_SALE06)=0 THEN SUM(TS.MONEY_SALE06) ELSE SUM(TS.MONEY_SALE06) / SUM(TS.WEIGHT_SALE06) END )::numeric,1) UNIT_PRICE_SALE6 ,round((CASE WHEN SUM(TS.WEIGHT_SALE07)=0 THEN SUM(TS.MONEY_SALE07) ELSE SUM(TS.MONEY_SALE07) / SUM(TS.WEIGHT_SALE07) END )::numeric,1) UNIT_PRICE_SALE7 ,round((CASE WHEN SUM(TS.WEIGHT_SALE08)=0 THEN SUM(TS.MONEY_SALE08) ELSE SUM(TS.MONEY_SALE08) / SUM(TS.WEIGHT_SALE08) END )::numeric,1) UNIT_PRICE_SALE8 ,round((CASE WHEN SUM(TS.WEIGHT_SALE09)=0 THEN SUM(TS.MONEY_SALE09) ELSE SUM(TS.MONEY_SALE09) / SUM(TS.WEIGHT_SALE09) END )::numeric,1) UNIT_PRICE_SALE9 ,round((CASE WHEN SUM(TS.WEIGHT_SALE10)=0 THEN SUM(TS.MONEY_SALE10) ELSE SUM(TS.MONEY_SALE10) / SUM(TS.WEIGHT_SALE10) END )::numeric,1) UNIT_PRICE_SALE10 ,round((CASE WHEN SUM(TS.WEIGHT_SALE11)=0 THEN SUM(TS.MONEY_SALE11) ELSE SUM(TS.MONEY_SALE11) / SUM(TS.WEIGHT_SALE11) END )::numeric,1) UNIT_PRICE_SALE11 ,round((CASE WHEN SUM(TS.WEIGHT_SALE12)=0 THEN SUM(TS.MONEY_SALE12) ELSE SUM(TS.MONEY_SALE12) / SUM(TS.WEIGHT_SALE12) END )::numeric,1) UNIT_PRICE_SALE12 -- 月別の数量 ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 -- 月別の売上金額 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 -- 月別の消費税 ,trunc((SUM(TS.TAX01)/1000)+0.5) TAX1 ,trunc((SUM(TS.TAX02)/1000)+0.5) TAX2 ,trunc((SUM(TS.TAX03)/1000)+0.5) TAX3 ,trunc((SUM(TS.TAX04)/1000)+0.5) TAX4 ,trunc((SUM(TS.TAX05)/1000)+0.5) TAX5 ,trunc((SUM(TS.TAX06)/1000)+0.5) TAX6 ,trunc((SUM(TS.TAX07)/1000)+0.5) TAX7 ,trunc((SUM(TS.TAX08)/1000)+0.5) TAX8 ,trunc((SUM(TS.TAX09)/1000)+0.5) TAX9 ,trunc((SUM(TS.TAX10)/1000)+0.5) TAX10 ,trunc((SUM(TS.TAX11)/1000)+0.5) TAX11 ,trunc((SUM(TS.TAX12)/1000)+0.5) TAX12 -- 月別の入金額 ,trunc((SUM(TD.MONEY_DEPOSIT01)/1000)+0.5) MONEY_DEPOSIT1 ,trunc((SUM(TD.MONEY_DEPOSIT02)/1000)+0.5) MONEY_DEPOSIT2 ,trunc((SUM(TD.MONEY_DEPOSIT03)/1000)+0.5) MONEY_DEPOSIT3 ,trunc((SUM(TD.MONEY_DEPOSIT04)/1000)+0.5) MONEY_DEPOSIT4 ,trunc((SUM(TD.MONEY_DEPOSIT05)/1000)+0.5) MONEY_DEPOSIT5 ,trunc((SUM(TD.MONEY_DEPOSIT06)/1000)+0.5) MONEY_DEPOSIT6 ,trunc((SUM(TD.MONEY_DEPOSIT07)/1000)+0.5) MONEY_DEPOSIT7 ,trunc((SUM(TD.MONEY_DEPOSIT08)/1000)+0.5) MONEY_DEPOSIT8 ,trunc((SUM(TD.MONEY_DEPOSIT09)/1000)+0.5) MONEY_DEPOSIT9 ,trunc((SUM(TD.MONEY_DEPOSIT10)/1000)+0.5) MONEY_DEPOSIT10 ,trunc((SUM(TD.MONEY_DEPOSIT11)/1000)+0.5) MONEY_DEPOSIT11 ,trunc((SUM(TD.MONEY_DEPOSIT12)/1000)+0.5) MONEY_DEPOSIT12 -- 調整額は売上調整額と伝票の運賃と入金調整額とする。 -- 日別の伝票調整額 + 日別の運賃 - 日別の入金調整額 ,trunc((SUM(TS.MONEY_ADJUST01 + TS.MONEY_FREIGHT01 - TD.MONEY_DEPOSIT_ADJ01 )/1000)+0.5) ADJUST1 ,trunc((SUM(TS.MONEY_ADJUST02 + TS.MONEY_FREIGHT02 - TD.MONEY_DEPOSIT_ADJ02 )/1000)+0.5) ADJUST2 ,trunc((SUM(TS.MONEY_ADJUST03 + TS.MONEY_FREIGHT03 - TD.MONEY_DEPOSIT_ADJ03 )/1000)+0.5) ADJUST3 ,trunc((SUM(TS.MONEY_ADJUST04 + TS.MONEY_FREIGHT04 - TD.MONEY_DEPOSIT_ADJ04 )/1000)+0.5) ADJUST4 ,trunc((SUM(TS.MONEY_ADJUST05 + TS.MONEY_FREIGHT05 - TD.MONEY_DEPOSIT_ADJ05 )/1000)+0.5) ADJUST5 ,trunc((SUM(TS.MONEY_ADJUST06 + TS.MONEY_FREIGHT06 - TD.MONEY_DEPOSIT_ADJ06 )/1000)+0.5) ADJUST6 ,trunc((SUM(TS.MONEY_ADJUST07 + TS.MONEY_FREIGHT07 - TD.MONEY_DEPOSIT_ADJ07 )/1000)+0.5) ADJUST7 ,trunc((SUM(TS.MONEY_ADJUST08 + TS.MONEY_FREIGHT08 - TD.MONEY_DEPOSIT_ADJ08 )/1000)+0.5) ADJUST8 ,trunc((SUM(TS.MONEY_ADJUST09 + TS.MONEY_FREIGHT09 - TD.MONEY_DEPOSIT_ADJ09 )/1000)+0.5) ADJUST9 ,trunc((SUM(TS.MONEY_ADJUST10 + TS.MONEY_FREIGHT10 - TD.MONEY_DEPOSIT_ADJ10 )/1000)+0.5) ADJUST10 ,trunc((SUM(TS.MONEY_ADJUST11 + TS.MONEY_FREIGHT11 - TD.MONEY_DEPOSIT_ADJ11 )/1000)+0.5) ADJUST11 ,trunc((SUM(TS.MONEY_ADJUST12 + TS.MONEY_FREIGHT12 - TD.MONEY_DEPOSIT_ADJ12 )/1000)+0.5) ADJUST12 FROM m_customer mc LEFT OUTER JOIN ( SELECT TS02.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の売上データ -- 月別の数量 ,SUM(TS02.WEIGHT_SALE01) WEIGHT_SALE01 ,SUM(TS02.WEIGHT_SALE02) WEIGHT_SALE02 ,SUM(TS02.WEIGHT_SALE03) WEIGHT_SALE03 ,SUM(TS02.WEIGHT_SALE04) WEIGHT_SALE04 ,SUM(TS02.WEIGHT_SALE05) WEIGHT_SALE05 ,SUM(TS02.WEIGHT_SALE06) WEIGHT_SALE06 ,SUM(TS02.WEIGHT_SALE07) WEIGHT_SALE07 ,SUM(TS02.WEIGHT_SALE08) WEIGHT_SALE08 ,SUM(TS02.WEIGHT_SALE09) WEIGHT_SALE09 ,SUM(TS02.WEIGHT_SALE10) WEIGHT_SALE10 ,SUM(TS02.WEIGHT_SALE11) WEIGHT_SALE11 ,SUM(TS02.WEIGHT_SALE12) WEIGHT_SALE12 -- 月別の売上金額 ,SUM(TS02.MONEY_SALE01) MONEY_SALE01 ,SUM(TS02.MONEY_SALE02) MONEY_SALE02 ,SUM(TS02.MONEY_SALE03) MONEY_SALE03 ,SUM(TS02.MONEY_SALE04) MONEY_SALE04 ,SUM(TS02.MONEY_SALE05) MONEY_SALE05 ,SUM(TS02.MONEY_SALE06) MONEY_SALE06 ,SUM(TS02.MONEY_SALE07) MONEY_SALE07 ,SUM(TS02.MONEY_SALE08) MONEY_SALE08 ,SUM(TS02.MONEY_SALE09) MONEY_SALE09 ,SUM(TS02.MONEY_SALE10) MONEY_SALE10 ,SUM(TS02.MONEY_SALE11) MONEY_SALE11 ,SUM(TS02.MONEY_SALE12) MONEY_SALE12 -- 月別の消費税 ,SUM(TS02.TAX01) TAX01 ,SUM(TS02.TAX02) TAX02 ,SUM(TS02.TAX03) TAX03 ,SUM(TS02.TAX04) TAX04 ,SUM(TS02.TAX05) TAX05 ,SUM(TS02.TAX06) TAX06 ,SUM(TS02.TAX07) TAX07 ,SUM(TS02.TAX08) TAX08 ,SUM(TS02.TAX09) TAX09 ,SUM(TS02.TAX10) TAX10 ,SUM(TS02.TAX11) TAX11 ,SUM(TS02.TAX12) TAX12 -- 月別の伝票調整額 ,SUM(TS02.MONEY_ADJUST01) MONEY_ADJUST01 ,SUM(TS02.MONEY_ADJUST02) MONEY_ADJUST02 ,SUM(TS02.MONEY_ADJUST03) MONEY_ADJUST03 ,SUM(TS02.MONEY_ADJUST04) MONEY_ADJUST04 ,SUM(TS02.MONEY_ADJUST05) MONEY_ADJUST05 ,SUM(TS02.MONEY_ADJUST06) MONEY_ADJUST06 ,SUM(TS02.MONEY_ADJUST07) MONEY_ADJUST07 ,SUM(TS02.MONEY_ADJUST08) MONEY_ADJUST08 ,SUM(TS02.MONEY_ADJUST09) MONEY_ADJUST09 ,SUM(TS02.MONEY_ADJUST10) MONEY_ADJUST10 ,SUM(TS02.MONEY_ADJUST11) MONEY_ADJUST11 ,SUM(TS02.MONEY_ADJUST12) MONEY_ADJUST12 -- 月別の運賃 ,SUM(TS02.MONEY_FREIGHT01) MONEY_FREIGHT01 ,SUM(TS02.MONEY_FREIGHT02) MONEY_FREIGHT02 ,SUM(TS02.MONEY_FREIGHT03) MONEY_FREIGHT03 ,SUM(TS02.MONEY_FREIGHT04) MONEY_FREIGHT04 ,SUM(TS02.MONEY_FREIGHT05) MONEY_FREIGHT05 ,SUM(TS02.MONEY_FREIGHT06) MONEY_FREIGHT06 ,SUM(TS02.MONEY_FREIGHT07) MONEY_FREIGHT07 ,SUM(TS02.MONEY_FREIGHT08) MONEY_FREIGHT08 ,SUM(TS02.MONEY_FREIGHT09) MONEY_FREIGHT09 ,SUM(TS02.MONEY_FREIGHT10) MONEY_FREIGHT10 ,SUM(TS02.MONEY_FREIGHT11) MONEY_FREIGHT11 ,SUM(TS02.MONEY_FREIGHT12) MONEY_FREIGHT12 FROM ( -- 得意先別納品先ごとのデータ SELECT TS01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の売上データ -- 月別の数量 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END WEIGHT_SALE12 -- 月別の売上金額 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END MONEY_SALE12 -- 月別の消費税 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.TAX ELSE 0 END TAX01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.TAX ELSE 0 END TAX02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.TAX ELSE 0 END TAX03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.TAX ELSE 0 END TAX04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.TAX ELSE 0 END TAX05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.TAX ELSE 0 END TAX06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.TAX ELSE 0 END TAX07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.TAX ELSE 0 END TAX08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.TAX ELSE 0 END TAX09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.TAX ELSE 0 END TAX10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.TAX ELSE 0 END TAX11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.TAX ELSE 0 END TAX12 -- 月別の伝票調整額 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_ADJUST ELSE 0 END MONEY_ADJUST12 -- 月別の運賃 ,CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT01 ,CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT02 ,CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT03 ,CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT04 ,CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT05 ,CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT06 ,CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT07 ,CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT08 ,CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT09 ,CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT10 ,CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT11 ,CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_FREIGHT ELSE 0 END MONEY_FREIGHT12 FROM T_SALE TS01 WHERE TS01.DT_SALE >= '&2' AND TS01.DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (('&8' = '') OR (TS01.CD_GREAD = ('&8' ::text) ::numeric)) ) TS02 GROUP BY TS02.CD_CUSTOMER ) ts ON (MC.CD_CUSTOMER = TS.CD_CUSTOMER) LEFT OUTER JOIN ( SELECT TD02.CD_CUSTOMER CD_CUSTOMER ,SUM(TD02.MONEY_DEPOSIT01) MONEY_DEPOSIT01 ,SUM(TD02.MONEY_DEPOSIT02) MONEY_DEPOSIT02 ,SUM(TD02.MONEY_DEPOSIT03) MONEY_DEPOSIT03 ,SUM(TD02.MONEY_DEPOSIT04) MONEY_DEPOSIT04 ,SUM(TD02.MONEY_DEPOSIT05) MONEY_DEPOSIT05 ,SUM(TD02.MONEY_DEPOSIT06) MONEY_DEPOSIT06 ,SUM(TD02.MONEY_DEPOSIT07) MONEY_DEPOSIT07 ,SUM(TD02.MONEY_DEPOSIT08) MONEY_DEPOSIT08 ,SUM(TD02.MONEY_DEPOSIT09) MONEY_DEPOSIT09 ,SUM(TD02.MONEY_DEPOSIT10) MONEY_DEPOSIT10 ,SUM(TD02.MONEY_DEPOSIT11) MONEY_DEPOSIT11 ,SUM(TD02.MONEY_DEPOSIT12) MONEY_DEPOSIT12 -- 月別の売上調整額 ,SUM(TD02.MONEY_DEPOSIT_ADJ01) MONEY_DEPOSIT_ADJ01 ,SUM(TD02.MONEY_DEPOSIT_ADJ02) MONEY_DEPOSIT_ADJ02 ,SUM(TD02.MONEY_DEPOSIT_ADJ03) MONEY_DEPOSIT_ADJ03 ,SUM(TD02.MONEY_DEPOSIT_ADJ04) MONEY_DEPOSIT_ADJ04 ,SUM(TD02.MONEY_DEPOSIT_ADJ05) MONEY_DEPOSIT_ADJ05 ,SUM(TD02.MONEY_DEPOSIT_ADJ06) MONEY_DEPOSIT_ADJ06 ,SUM(TD02.MONEY_DEPOSIT_ADJ07) MONEY_DEPOSIT_ADJ07 ,SUM(TD02.MONEY_DEPOSIT_ADJ08) MONEY_DEPOSIT_ADJ08 ,SUM(TD02.MONEY_DEPOSIT_ADJ09) MONEY_DEPOSIT_ADJ09 ,SUM(TD02.MONEY_DEPOSIT_ADJ10) MONEY_DEPOSIT_ADJ10 ,SUM(TD02.MONEY_DEPOSIT_ADJ11) MONEY_DEPOSIT_ADJ11 ,SUM(TD02.MONEY_DEPOSIT_ADJ12) MONEY_DEPOSIT_ADJ12 FROM ( SELECT TD01.CD_CUSTOMER CD_CUSTOMER -- 得意先別納品先別の入金データ -- 月別の売上入金 ,CASE WHEN TD01.DTS_MONTH=01 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT01 ,CASE WHEN TD01.DTS_MONTH=02 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT02 ,CASE WHEN TD01.DTS_MONTH=03 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT03 ,CASE WHEN TD01.DTS_MONTH=04 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT04 ,CASE WHEN TD01.DTS_MONTH=05 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT05 ,CASE WHEN TD01.DTS_MONTH=06 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT06 ,CASE WHEN TD01.DTS_MONTH=07 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT07 ,CASE WHEN TD01.DTS_MONTH=08 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT08 ,CASE WHEN TD01.DTS_MONTH=09 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT09 ,CASE WHEN TD01.DTS_MONTH=10 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT10 ,CASE WHEN TD01.DTS_MONTH=11 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT11 ,CASE WHEN TD01.DTS_MONTH=12 THEN CASE WHEN TD01.DIV_DEPOSIT=0 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT12 -- 月別の売上調整額 ,CASE WHEN TD01.DTS_MONTH=01 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ01 ,CASE WHEN TD01.DTS_MONTH=02 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ02 ,CASE WHEN TD01.DTS_MONTH=03 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ03 ,CASE WHEN TD01.DTS_MONTH=04 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ04 ,CASE WHEN TD01.DTS_MONTH=05 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ05 ,CASE WHEN TD01.DTS_MONTH=06 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ06 ,CASE WHEN TD01.DTS_MONTH=07 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ07 ,CASE WHEN TD01.DTS_MONTH=08 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ08 ,CASE WHEN TD01.DTS_MONTH=09 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ09 ,CASE WHEN TD01.DTS_MONTH=10 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ10 ,CASE WHEN TD01.DTS_MONTH=11 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ11 ,CASE WHEN TD01.DTS_MONTH=12 THEN CASE WHEN TD01.DIV_DEPOSIT=1 THEN TD01.MONEY_DEPOSIT ELSE 0 END ELSE 0 END MONEY_DEPOSIT_ADJ12 FROM T_DEPOSIT TD01 WHERE TD01.DT_DEPOSIT >= '&2' AND TD01.DT_DEPOSIT <= '&3' AND TD01.DIV_DELETE = 0 ) TD02 GROUP BY TD02.CD_CUSTOMER ) td ON (MC.CD_CUSTOMER = TD.CD_CUSTOMER) WHERE MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9' ::text) ::numeric)) --************************************************************************** --*　得意先、納品先別の売上データを取得 --*　グラフ表示用 (納品先の降順で出力)　　2004/05/06　新規追加　　konishi --************************************************************************** -- 納品先別の売上計（当月分）(得意先別シートの明細部分) [lselectRecords_derivery_data] SELECT MC.CD_CUSTOMER,MD.CD_DERIVERY ,MC.NAME_CUSTOMER,MD.NAME_DERIVERY -- 日別の数量 ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 -- 日別の売上金額 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 FROM M_CUSTOMER MC ,M_DERIVERY MD,( SELECT TS02.CD_CUSTOMER CD_CUSTOMER ,TS02.CD_DERIVERY CD_DERIVERY -- 得意先別納品先別の売上データ -- 日別の数量 ,SUM(TS02.WEIGHT_SALE01) WEIGHT_SALE01 ,SUM(TS02.WEIGHT_SALE02) WEIGHT_SALE02 ,SUM(TS02.WEIGHT_SALE03) WEIGHT_SALE03 ,SUM(TS02.WEIGHT_SALE04) WEIGHT_SALE04 ,SUM(TS02.WEIGHT_SALE05) WEIGHT_SALE05 ,SUM(TS02.WEIGHT_SALE06) WEIGHT_SALE06 ,SUM(TS02.WEIGHT_SALE07) WEIGHT_SALE07 ,SUM(TS02.WEIGHT_SALE08) WEIGHT_SALE08 ,SUM(TS02.WEIGHT_SALE09) WEIGHT_SALE09 ,SUM(TS02.WEIGHT_SALE10) WEIGHT_SALE10 ,SUM(TS02.WEIGHT_SALE11) WEIGHT_SALE11 ,SUM(TS02.WEIGHT_SALE12) WEIGHT_SALE12 -- 日別の売上金額 ,SUM(TS02.MONEY_SALE01) MONEY_SALE01 ,SUM(TS02.MONEY_SALE02) MONEY_SALE02 ,SUM(TS02.MONEY_SALE03) MONEY_SALE03 ,SUM(TS02.MONEY_SALE04) MONEY_SALE04 ,SUM(TS02.MONEY_SALE05) MONEY_SALE05 ,SUM(TS02.MONEY_SALE06) MONEY_SALE06 ,SUM(TS02.MONEY_SALE07) MONEY_SALE07 ,SUM(TS02.MONEY_SALE08) MONEY_SALE08 ,SUM(TS02.MONEY_SALE09) MONEY_SALE09 ,SUM(TS02.MONEY_SALE10) MONEY_SALE10 ,SUM(TS02.MONEY_SALE11) MONEY_SALE11 ,SUM(TS02.MONEY_SALE12) MONEY_SALE12 FROM ( -- 得意先別納品先ごとのデータ SELECT TS01.CD_CUSTOMER,TS01.CD_DERIVERY -- 得意先別納品先別の売上データ -- 月別の数量 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE12 -- 月別の売上金額 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE12 FROM T_SALE TS01 WHERE DT_SALE >= '&2' and DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (('&8' = '') OR (TS01.CD_GREAD = ('&8' ::text) ::numeric)) group by CD_CUSTOMER , TS01.CD_DERIVERY order by CD_CUSTOMER , TS01.CD_DERIVERY ) TS02 GROUP BY TS02.CD_CUSTOMER,TS02.CD_DERIVERY ) TS WHERE MC.CD_CUSTOMER = MD.CD_CUSTOMER AND MD.CD_CUSTOMER = TS.CD_CUSTOMER AND MD.CD_DERIVERY = TS.CD_DERIVERY AND MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) AND MD.DT_START = ( SELECT MAX(MD01.DT_START) FROM M_DERIVERY MD01 WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY) AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9' ::text) ::numeric)) GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY ORDER BY MC.CD_CUSTOMER desc , MD.CD_DERIVERY desc , MC.NAME_CUSTOMER desc , MD.NAME_DERIVERY desc --************************************************************************** --*　得意先、納品先別の売上データを取得 --*　 --************************************************************************** -- 納品先別の売上計（当月分）(得意先別シートの明細部分) [lselectRecords_derivery_saledata] SELECT MC.CD_CUSTOMER,MD.CD_DERIVERY ,MC.NAME_CUSTOMER,MD.NAME_DERIVERY -- 単価 ,round((CASE WHEN SUM(TS.WEIGHT_SALE01)=0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END )::numeric,1) UNIT_PRICE_SALE1 ,round((CASE WHEN SUM(TS.WEIGHT_SALE02)=0 THEN SUM(TS.MONEY_SALE02) ELSE SUM(TS.MONEY_SALE02) / SUM(TS.WEIGHT_SALE02) END )::numeric,1) UNIT_PRICE_SALE2 ,round((CASE WHEN SUM(TS.WEIGHT_SALE03)=0 THEN SUM(TS.MONEY_SALE03) ELSE SUM(TS.MONEY_SALE03) / SUM(TS.WEIGHT_SALE03) END )::numeric,1) UNIT_PRICE_SALE3 ,round((CASE WHEN SUM(TS.WEIGHT_SALE04)=0 THEN SUM(TS.MONEY_SALE04) ELSE SUM(TS.MONEY_SALE04) / SUM(TS.WEIGHT_SALE04) END )::numeric,1) UNIT_PRICE_SALE4 ,round((CASE WHEN SUM(TS.WEIGHT_SALE05)=0 THEN SUM(TS.MONEY_SALE05) ELSE SUM(TS.MONEY_SALE05) / SUM(TS.WEIGHT_SALE05) END )::numeric,1) UNIT_PRICE_SALE5 ,round((CASE WHEN SUM(TS.WEIGHT_SALE06)=0 THEN SUM(TS.MONEY_SALE06) ELSE SUM(TS.MONEY_SALE06) / SUM(TS.WEIGHT_SALE06) END )::numeric,1) UNIT_PRICE_SALE6 ,round((CASE WHEN SUM(TS.WEIGHT_SALE07)=0 THEN SUM(TS.MONEY_SALE07) ELSE SUM(TS.MONEY_SALE07) / SUM(TS.WEIGHT_SALE07) END )::numeric,1) UNIT_PRICE_SALE7 ,round((CASE WHEN SUM(TS.WEIGHT_SALE08)=0 THEN SUM(TS.MONEY_SALE08) ELSE SUM(TS.MONEY_SALE08) / SUM(TS.WEIGHT_SALE08) END )::numeric,1) UNIT_PRICE_SALE8 ,round((CASE WHEN SUM(TS.WEIGHT_SALE09)=0 THEN SUM(TS.MONEY_SALE09) ELSE SUM(TS.MONEY_SALE09) / SUM(TS.WEIGHT_SALE09) END )::numeric,1) UNIT_PRICE_SALE9 ,round((CASE WHEN SUM(TS.WEIGHT_SALE10)=0 THEN SUM(TS.MONEY_SALE10) ELSE SUM(TS.MONEY_SALE10) / SUM(TS.WEIGHT_SALE10) END )::numeric,1) UNIT_PRICE_SALE10 ,round((CASE WHEN SUM(TS.WEIGHT_SALE11)=0 THEN SUM(TS.MONEY_SALE11) ELSE SUM(TS.MONEY_SALE11) / SUM(TS.WEIGHT_SALE11) END )::numeric,1) UNIT_PRICE_SALE11 ,round((CASE WHEN SUM(TS.WEIGHT_SALE12)=0 THEN SUM(TS.MONEY_SALE12) ELSE SUM(TS.MONEY_SALE12) / SUM(TS.WEIGHT_SALE12) END )::numeric,1) UNIT_PRICE_SALE12 -- 日別の数量 ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 -- 日別の売上金額 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 -- 日別の消費税 ,trunc((SUM(TS.TAX01)/1000)+0.5) TAX1 ,trunc((SUM(TS.TAX02)/1000)+0.5) TAX2 ,trunc((SUM(TS.TAX03)/1000)+0.5) TAX3 ,trunc((SUM(TS.TAX04)/1000)+0.5) TAX4 ,trunc((SUM(TS.TAX05)/1000)+0.5) TAX5 ,trunc((SUM(TS.TAX06)/1000)+0.5) TAX6 ,trunc((SUM(TS.TAX07)/1000)+0.5) TAX7 ,trunc((SUM(TS.TAX08)/1000)+0.5) TAX8 ,trunc((SUM(TS.TAX09)/1000)+0.5) TAX9 ,trunc((SUM(TS.TAX10)/1000)+0.5) TAX10 ,trunc((SUM(TS.TAX11)/1000)+0.5) TAX11 ,trunc((SUM(TS.TAX12)/1000)+0.5) TAX12 FROM M_CUSTOMER MC ,M_DERIVERY MD,( SELECT TS02.CD_CUSTOMER CD_CUSTOMER ,TS02.CD_DERIVERY CD_DERIVERY -- 得意先別納品先別の売上データ -- 日別の数量 ,SUM(TS02.WEIGHT_SALE01) WEIGHT_SALE01 ,SUM(TS02.WEIGHT_SALE02) WEIGHT_SALE02 ,SUM(TS02.WEIGHT_SALE03) WEIGHT_SALE03 ,SUM(TS02.WEIGHT_SALE04) WEIGHT_SALE04 ,SUM(TS02.WEIGHT_SALE05) WEIGHT_SALE05 ,SUM(TS02.WEIGHT_SALE06) WEIGHT_SALE06 ,SUM(TS02.WEIGHT_SALE07) WEIGHT_SALE07 ,SUM(TS02.WEIGHT_SALE08) WEIGHT_SALE08 ,SUM(TS02.WEIGHT_SALE09) WEIGHT_SALE09 ,SUM(TS02.WEIGHT_SALE10) WEIGHT_SALE10 ,SUM(TS02.WEIGHT_SALE11) WEIGHT_SALE11 ,SUM(TS02.WEIGHT_SALE12) WEIGHT_SALE12 -- 日別の売上金額 ,SUM(TS02.MONEY_SALE01) MONEY_SALE01 ,SUM(TS02.MONEY_SALE02) MONEY_SALE02 ,SUM(TS02.MONEY_SALE03) MONEY_SALE03 ,SUM(TS02.MONEY_SALE04) MONEY_SALE04 ,SUM(TS02.MONEY_SALE05) MONEY_SALE05 ,SUM(TS02.MONEY_SALE06) MONEY_SALE06 ,SUM(TS02.MONEY_SALE07) MONEY_SALE07 ,SUM(TS02.MONEY_SALE08) MONEY_SALE08 ,SUM(TS02.MONEY_SALE09) MONEY_SALE09 ,SUM(TS02.MONEY_SALE10) MONEY_SALE10 ,SUM(TS02.MONEY_SALE11) MONEY_SALE11 ,SUM(TS02.MONEY_SALE12) MONEY_SALE12 -- 日別の消費税 ,SUM(TS02.TAX01) TAX01 ,SUM(TS02.TAX02) TAX02 ,SUM(TS02.TAX03) TAX03 ,SUM(TS02.TAX04) TAX04 ,SUM(TS02.TAX05) TAX05 ,SUM(TS02.TAX06) TAX06 ,SUM(TS02.TAX07) TAX07 ,SUM(TS02.TAX08) TAX08 ,SUM(TS02.TAX09) TAX09 ,SUM(TS02.TAX10) TAX10 ,SUM(TS02.TAX11) TAX11 ,SUM(TS02.TAX12) TAX12 -- 日別の伝票調整額 ,SUM(TS02.MONEY_ADJUST01) MONEY_ADJUST01 ,SUM(TS02.MONEY_ADJUST02) MONEY_ADJUST02 ,SUM(TS02.MONEY_ADJUST03) MONEY_ADJUST03 ,SUM(TS02.MONEY_ADJUST04) MONEY_ADJUST04 ,SUM(TS02.MONEY_ADJUST05) MONEY_ADJUST05 ,SUM(TS02.MONEY_ADJUST06) MONEY_ADJUST06 ,SUM(TS02.MONEY_ADJUST07) MONEY_ADJUST07 ,SUM(TS02.MONEY_ADJUST08) MONEY_ADJUST08 ,SUM(TS02.MONEY_ADJUST09) MONEY_ADJUST09 ,SUM(TS02.MONEY_ADJUST10) MONEY_ADJUST10 ,SUM(TS02.MONEY_ADJUST11) MONEY_ADJUST11 ,SUM(TS02.MONEY_ADJUST12) MONEY_ADJUST12 -- 日別の運賃 ,SUM(TS02.MONEY_FREIGHT01) MONEY_FREIGHT01 ,SUM(TS02.MONEY_FREIGHT02) MONEY_FREIGHT02 ,SUM(TS02.MONEY_FREIGHT03) MONEY_FREIGHT03 ,SUM(TS02.MONEY_FREIGHT04) MONEY_FREIGHT04 ,SUM(TS02.MONEY_FREIGHT05) MONEY_FREIGHT05 ,SUM(TS02.MONEY_FREIGHT06) MONEY_FREIGHT06 ,SUM(TS02.MONEY_FREIGHT07) MONEY_FREIGHT07 ,SUM(TS02.MONEY_FREIGHT08) MONEY_FREIGHT08 ,SUM(TS02.MONEY_FREIGHT09) MONEY_FREIGHT09 ,SUM(TS02.MONEY_FREIGHT10) MONEY_FREIGHT10 ,SUM(TS02.MONEY_FREIGHT11) MONEY_FREIGHT11 ,SUM(TS02.MONEY_FREIGHT12) MONEY_FREIGHT12 FROM ( -- 得意先別納品先ごとのデータ SELECT TS01.CD_CUSTOMER,TS01.CD_DERIVERY -- 得意先別納品先別の売上データ -- 月別の数量 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE12 -- 月別の売上金額 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE12 -- 月別の消費税 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.TAX ELSE 0 END ) TAX01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.TAX ELSE 0 END ) TAX02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.TAX ELSE 0 END ) TAX03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.TAX ELSE 0 END ) TAX04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.TAX ELSE 0 END ) TAX05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.TAX ELSE 0 END ) TAX06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.TAX ELSE 0 END ) TAX07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.TAX ELSE 0 END ) TAX08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.TAX ELSE 0 END ) TAX09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.TAX ELSE 0 END ) TAX10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.TAX ELSE 0 END ) TAX11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.TAX ELSE 0 END ) TAX12 -- 月別の伝票調整額 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST12 -- 月別の運賃 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT12 FROM T_SALE TS01 WHERE DT_SALE >= '&2' and DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (('&8' = '') OR (TS01.CD_GREAD = ('&8' ::text) ::numeric)) group by CD_CUSTOMER , TS01.CD_DERIVERY order by CD_CUSTOMER , TS01.CD_DERIVERY ) TS02 GROUP BY TS02.CD_CUSTOMER,TS02.CD_DERIVERY ) TS WHERE MC.CD_CUSTOMER = MD.CD_CUSTOMER AND MD.CD_CUSTOMER = TS.CD_CUSTOMER AND MD.CD_DERIVERY = TS.CD_DERIVERY AND MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) AND MD.DT_START = ( SELECT MAX(MD01.DT_START) FROM M_DERIVERY MD01 WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY) AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9' ::text) ::numeric)) GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY ORDER BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY --************************************************************************** --*　納品先別製品一覧の取得 --*　 --************************************************************************** [lselectRecords_derivery_product_data] SELECT MC.CD_CUSTOMER,MC.NAME_CUSTOMER ,MD.CD_DERIVERY,MD.NAME_DERIVERY ,MP.CD_PRODUCT ,MP.NAME_PRODUCT ,round((CASE WHEN SUM(TS.WEIGHT_SALE01)=0 THEN SUM(TS.MONEY_SALE01) ELSE SUM(TS.MONEY_SALE01) / SUM(TS.WEIGHT_SALE01) END )::numeric,1) UNIT_PRICE_SALE1 ,round((CASE WHEN SUM(TS.WEIGHT_SALE02)=0 THEN SUM(TS.MONEY_SALE02) ELSE SUM(TS.MONEY_SALE02) / SUM(TS.WEIGHT_SALE02) END )::numeric,1) UNIT_PRICE_SALE2 ,round((CASE WHEN SUM(TS.WEIGHT_SALE03)=0 THEN SUM(TS.MONEY_SALE03) ELSE SUM(TS.MONEY_SALE03) / SUM(TS.WEIGHT_SALE03) END )::numeric,1) UNIT_PRICE_SALE3 ,round((CASE WHEN SUM(TS.WEIGHT_SALE04)=0 THEN SUM(TS.MONEY_SALE04) ELSE SUM(TS.MONEY_SALE04) / SUM(TS.WEIGHT_SALE04) END )::numeric,1) UNIT_PRICE_SALE4 ,round((CASE WHEN SUM(TS.WEIGHT_SALE05)=0 THEN SUM(TS.MONEY_SALE05) ELSE SUM(TS.MONEY_SALE05) / SUM(TS.WEIGHT_SALE05) END )::numeric,1) UNIT_PRICE_SALE5 ,round((CASE WHEN SUM(TS.WEIGHT_SALE06)=0 THEN SUM(TS.MONEY_SALE06) ELSE SUM(TS.MONEY_SALE06) / SUM(TS.WEIGHT_SALE06) END )::numeric,1) UNIT_PRICE_SALE6 ,round((CASE WHEN SUM(TS.WEIGHT_SALE07)=0 THEN SUM(TS.MONEY_SALE07) ELSE SUM(TS.MONEY_SALE07) / SUM(TS.WEIGHT_SALE07) END )::numeric,1) UNIT_PRICE_SALE7 ,round((CASE WHEN SUM(TS.WEIGHT_SALE08)=0 THEN SUM(TS.MONEY_SALE08) ELSE SUM(TS.MONEY_SALE08) / SUM(TS.WEIGHT_SALE08) END )::numeric,1) UNIT_PRICE_SALE8 ,round((CASE WHEN SUM(TS.WEIGHT_SALE09)=0 THEN SUM(TS.MONEY_SALE09) ELSE SUM(TS.MONEY_SALE09) / SUM(TS.WEIGHT_SALE09) END )::numeric,1) UNIT_PRICE_SALE9 ,round((CASE WHEN SUM(TS.WEIGHT_SALE10)=0 THEN SUM(TS.MONEY_SALE10) ELSE SUM(TS.MONEY_SALE10) / SUM(TS.WEIGHT_SALE10) END )::numeric,1) UNIT_PRICE_SALE10 ,round((CASE WHEN SUM(TS.WEIGHT_SALE11)=0 THEN SUM(TS.MONEY_SALE11) ELSE SUM(TS.MONEY_SALE11) / SUM(TS.WEIGHT_SALE11) END )::numeric,1) UNIT_PRICE_SALE11 ,round((CASE WHEN SUM(TS.WEIGHT_SALE12)=0 THEN SUM(TS.MONEY_SALE12) ELSE SUM(TS.MONEY_SALE12) / SUM(TS.WEIGHT_SALE12) END )::numeric,1) UNIT_PRICE_SALE12 ,trunc((SUM(TS.WEIGHT_SALE01)/1000)+0.5) WEIGHT_SALE1 ,trunc((SUM(TS.WEIGHT_SALE02)/1000)+0.5) WEIGHT_SALE2 ,trunc((SUM(TS.WEIGHT_SALE03)/1000)+0.5) WEIGHT_SALE3 ,trunc((SUM(TS.WEIGHT_SALE04)/1000)+0.5) WEIGHT_SALE4 ,trunc((SUM(TS.WEIGHT_SALE05)/1000)+0.5) WEIGHT_SALE5 ,trunc((SUM(TS.WEIGHT_SALE06)/1000)+0.5) WEIGHT_SALE6 ,trunc((SUM(TS.WEIGHT_SALE07)/1000)+0.5) WEIGHT_SALE7 ,trunc((SUM(TS.WEIGHT_SALE08)/1000)+0.5) WEIGHT_SALE8 ,trunc((SUM(TS.WEIGHT_SALE09)/1000)+0.5) WEIGHT_SALE9 ,trunc((SUM(TS.WEIGHT_SALE10)/1000)+0.5) WEIGHT_SALE10 ,trunc((SUM(TS.WEIGHT_SALE11)/1000)+0.5) WEIGHT_SALE11 ,trunc((SUM(TS.WEIGHT_SALE12)/1000)+0.5) WEIGHT_SALE12 ,trunc((SUM(TS.MONEY_SALE01)/1000)+0.5) MONEY_SALE1 ,trunc((SUM(TS.MONEY_SALE02)/1000)+0.5) MONEY_SALE2 ,trunc((SUM(TS.MONEY_SALE03)/1000)+0.5) MONEY_SALE3 ,trunc((SUM(TS.MONEY_SALE04)/1000)+0.5) MONEY_SALE4 ,trunc((SUM(TS.MONEY_SALE05)/1000)+0.5) MONEY_SALE5 ,trunc((SUM(TS.MONEY_SALE06)/1000)+0.5) MONEY_SALE6 ,trunc((SUM(TS.MONEY_SALE07)/1000)+0.5) MONEY_SALE7 ,trunc((SUM(TS.MONEY_SALE08)/1000)+0.5) MONEY_SALE8 ,trunc((SUM(TS.MONEY_SALE09)/1000)+0.5) MONEY_SALE9 ,trunc((SUM(TS.MONEY_SALE10)/1000)+0.5) MONEY_SALE10 ,trunc((SUM(TS.MONEY_SALE11)/1000)+0.5) MONEY_SALE11 ,trunc((SUM(TS.MONEY_SALE12)/1000)+0.5) MONEY_SALE12 ,trunc((SUM(TS.TAX01)/1000)+0.5) TAX1 ,trunc((SUM(TS.TAX02)/1000)+0.5) TAX2 ,trunc((SUM(TS.TAX03)/1000)+0.5) TAX3 ,trunc((SUM(TS.TAX04)/1000)+0.5) TAX4 ,trunc((SUM(TS.TAX05)/1000)+0.5) TAX5 ,trunc((SUM(TS.TAX06)/1000)+0.5) TAX6 ,trunc((SUM(TS.TAX07)/1000)+0.5) TAX7 ,trunc((SUM(TS.TAX08)/1000)+0.5) TAX8 ,trunc((SUM(TS.TAX09)/1000)+0.5) TAX9 ,trunc((SUM(TS.TAX10)/1000)+0.5) TAX10 ,trunc((SUM(TS.TAX11)/1000)+0.5) TAX11 ,trunc((SUM(TS.TAX12)/1000)+0.5) TAX12 FROM m_customer mc LEFT OUTER JOIN m_derivery md ON (MC.CD_CUSTOMER = MD.CD_CUSTOMER) LEFT OUTER JOIN ( SELECT TS02.CD_CUSTOMER CD_CUSTOMER ,TS02.CD_DERIVERY CD_DERIVERY ,TS02.CD_PRODUCT CD_PRODUCT ,SUM(TS02.WEIGHT_SALE01) WEIGHT_SALE01 ,SUM(TS02.WEIGHT_SALE02) WEIGHT_SALE02 ,SUM(TS02.WEIGHT_SALE03) WEIGHT_SALE03 ,SUM(TS02.WEIGHT_SALE04) WEIGHT_SALE04 ,SUM(TS02.WEIGHT_SALE05) WEIGHT_SALE05 ,SUM(TS02.WEIGHT_SALE06) WEIGHT_SALE06 ,SUM(TS02.WEIGHT_SALE07) WEIGHT_SALE07 ,SUM(TS02.WEIGHT_SALE08) WEIGHT_SALE08 ,SUM(TS02.WEIGHT_SALE09) WEIGHT_SALE09 ,SUM(TS02.WEIGHT_SALE10) WEIGHT_SALE10 ,SUM(TS02.WEIGHT_SALE11) WEIGHT_SALE11 ,SUM(TS02.WEIGHT_SALE12) WEIGHT_SALE12 ,SUM(TS02.MONEY_SALE01) MONEY_SALE01 ,SUM(TS02.MONEY_SALE02) MONEY_SALE02 ,SUM(TS02.MONEY_SALE03) MONEY_SALE03 ,SUM(TS02.MONEY_SALE04) MONEY_SALE04 ,SUM(TS02.MONEY_SALE05) MONEY_SALE05 ,SUM(TS02.MONEY_SALE06) MONEY_SALE06 ,SUM(TS02.MONEY_SALE07) MONEY_SALE07 ,SUM(TS02.MONEY_SALE08) MONEY_SALE08 ,SUM(TS02.MONEY_SALE09) MONEY_SALE09 ,SUM(TS02.MONEY_SALE10) MONEY_SALE10 ,SUM(TS02.MONEY_SALE11) MONEY_SALE11 ,SUM(TS02.MONEY_SALE12) MONEY_SALE12 ,SUM(TS02.TAX01) TAX01 ,SUM(TS02.TAX02) TAX02 ,SUM(TS02.TAX03) TAX03 ,SUM(TS02.TAX04) TAX04 ,SUM(TS02.TAX05) TAX05 ,SUM(TS02.TAX06) TAX06 ,SUM(TS02.TAX07) TAX07 ,SUM(TS02.TAX08) TAX08 ,SUM(TS02.TAX09) TAX09 ,SUM(TS02.TAX10) TAX10 ,SUM(TS02.TAX11) TAX11 ,SUM(TS02.TAX12) TAX12 ,SUM(TS02.MONEY_ADJUST01) MONEY_ADJUST01 ,SUM(TS02.MONEY_ADJUST02) MONEY_ADJUST02 ,SUM(TS02.MONEY_ADJUST03) MONEY_ADJUST03 ,SUM(TS02.MONEY_ADJUST04) MONEY_ADJUST04 ,SUM(TS02.MONEY_ADJUST05) MONEY_ADJUST05 ,SUM(TS02.MONEY_ADJUST06) MONEY_ADJUST06 ,SUM(TS02.MONEY_ADJUST07) MONEY_ADJUST07 ,SUM(TS02.MONEY_ADJUST08) MONEY_ADJUST08 ,SUM(TS02.MONEY_ADJUST09) MONEY_ADJUST09 ,SUM(TS02.MONEY_ADJUST10) MONEY_ADJUST10 ,SUM(TS02.MONEY_ADJUST11) MONEY_ADJUST11 ,SUM(TS02.MONEY_ADJUST12) MONEY_ADJUST12 ,SUM(TS02.MONEY_FREIGHT01) MONEY_FREIGHT01 ,SUM(TS02.MONEY_FREIGHT02) MONEY_FREIGHT02 ,SUM(TS02.MONEY_FREIGHT03) MONEY_FREIGHT03 ,SUM(TS02.MONEY_FREIGHT04) MONEY_FREIGHT04 ,SUM(TS02.MONEY_FREIGHT05) MONEY_FREIGHT05 ,SUM(TS02.MONEY_FREIGHT06) MONEY_FREIGHT06 ,SUM(TS02.MONEY_FREIGHT07) MONEY_FREIGHT07 ,SUM(TS02.MONEY_FREIGHT08) MONEY_FREIGHT08 ,SUM(TS02.MONEY_FREIGHT09) MONEY_FREIGHT09 ,SUM(TS02.MONEY_FREIGHT10) MONEY_FREIGHT10 ,SUM(TS02.MONEY_FREIGHT11) MONEY_FREIGHT11 ,SUM(TS02.MONEY_FREIGHT12) MONEY_FREIGHT12 FROM( SELECT TS01.CD_CUSTOMER,TS01.CD_DERIVERY ,TS01.CD_PRODUCT ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.WEIGHT_SALE ELSE 0 END ) WEIGHT_SALE12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_SALE ELSE 0 END ) MONEY_SALE12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.TAX ELSE 0 END ) TAX01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.TAX ELSE 0 END ) TAX02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.TAX ELSE 0 END ) TAX03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.TAX ELSE 0 END ) TAX04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.TAX ELSE 0 END ) TAX05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.TAX ELSE 0 END ) TAX06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.TAX ELSE 0 END ) TAX07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.TAX ELSE 0 END ) TAX08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.TAX ELSE 0 END ) TAX09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.TAX ELSE 0 END ) TAX10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.TAX ELSE 0 END ) TAX11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.TAX ELSE 0 END ) TAX12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_ADJUST ELSE 0 END ) MONEY_ADJUST12 ,sum(CASE WHEN TS01.DTS_MONTH=01 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT01 ,sum(CASE WHEN TS01.DTS_MONTH=02 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT02 ,sum(CASE WHEN TS01.DTS_MONTH=03 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT03 ,sum(CASE WHEN TS01.DTS_MONTH=04 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT04 ,sum(CASE WHEN TS01.DTS_MONTH=05 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT05 ,sum(CASE WHEN TS01.DTS_MONTH=06 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT06 ,sum(CASE WHEN TS01.DTS_MONTH=07 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT07 ,sum(CASE WHEN TS01.DTS_MONTH=08 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT08 ,sum(CASE WHEN TS01.DTS_MONTH=09 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT09 ,sum(CASE WHEN TS01.DTS_MONTH=10 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT10 ,sum(CASE WHEN TS01.DTS_MONTH=11 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT11 ,sum(CASE WHEN TS01.DTS_MONTH=12 THEN TS01.MONEY_FREIGHT ELSE 0 END ) MONEY_FREIGHT12 FROM T_SALE TS01 WHERE DT_SALE >= '&2' and DT_SALE <= '&3' AND TS01.FLG_DELETE = 0 AND (('&8' = '') OR (TS01.CD_GREAD = ('&8' ::text) ::numeric)) group by CD_CUSTOMER , TS01.CD_DERIVERY , TS01.CD_PRODUCT order by CD_CUSTOMER , TS01.CD_DERIVERY , TS01.CD_PRODUCT ) TS02 GROUP BY TS02.CD_CUSTOMER,TS02.CD_DERIVERY ,TS02.CD_PRODUCT ) ts ON (MD.CD_CUSTOMER = TS.CD_CUSTOMER AND MD.CD_DERIVERY = TS.CD_DERIVERY) LEFT OUTER JOIN m_product mp ON (TS.CD_PRODUCT = MP.CD_PRODUCT) WHERE MC.DT_START = ( SELECT MAX(MC01.DT_START) FROM M_CUSTOMER MC01 WHERE MC01.CD_CUSTOMER = MC.CD_CUSTOMER) AND MD.DT_START = ( SELECT MAX(MD01.DT_START) FROM M_DERIVERY MD01 WHERE MD01.CD_DERIVERY = MD.CD_DERIVERY) AND MP.DT_START = ( SELECT MAX(MP01.DT_START) FROM M_PRODUCT MP01 WHERE MP01.CD_PRODUCT = MP.CD_PRODUCT) AND (('&9' = '') OR (MC.CD_CUSTOMER = ('&9' ::text) ::numeric)) and (('&10' = '') OR (TS.CD_DERIVERY = ('&10' ::text) ::numeric)) GROUP BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY , MP.CD_PRODUCT , MP.NAME_PRODUCT ORDER BY MC.CD_CUSTOMER, MD.CD_DERIVERY, MC.NAME_CUSTOMER, MD.NAME_DERIVERY , MP.CD_PRODUCT , MP.NAME_PRODUCT --************************************************************************** --*　前月までの差引残高を取得 --*　 --************************************************************************** [lselectRecords_zenzan] SELECT PF.CD_CUSTOMER ,SUM(PF.WEIGHT_SALE) WEIGHT_SALE ,SUM(PF.MONEY_SALE) MONEY_SALE ,SUM(PF.TAX) TAX ,SUM(PF.MONEY_ADJUST) MONEY_ADJUST ,SUM(PF.MONEY_FREIGHT) MONEY_FREIGHT ,SUM(PF.MONEY_DEPOSIT) MONEY_DEPOSIT ,SUM(PF.MONEY_ADJUST + PF.MONEY_FREIGHT - PF.MONEY_DEPOSIT_ADJUST) MONEY_DEPOSIT_ADJUST FROM( select TS.CD_CUSTOMER , sum(TS.UNIT_PRICE_SALE) UNIT_PRICE_SALE , sum(TS.WEIGHT_SALE) WEIGHT_SALE , sum(TS.MONEY_SALE) MONEY_SALE , sum(TS.TAX) TAX , coalesce(SUM(TS.MONEY_ADJUST),0) MONEY_ADJUST , coalesce(sum(TS.MONEY_FREIGHT),0) MONEY_FREIGHT , 0 MONEY_DEPOSIT , 0 MONEY_DEPOSIT_ADJUST from T_SALE TS where TS.DT_SALE < '&2' and TS.FLG_DELETE = 0 and (('&9' = '') OR (TS.CD_CUSTOMER = ('&9' ::text) ::numeric)) and (('&8' = '') OR (TS.CD_GREAD = ('&8' ::text) ::numeric)) GROUP BY TS.CD_CUSTOMER UNION select TD.CD_CUSTOMER ,0 UNIT_PRICE_SALE ,0 WEIGHT_SALE ,0 MONEY_SALE ,0 TAX ,0 MONEY_ADJUST ,0 MONEY_FREIGHT ,SUM(CASE WHEN TD.DIV_DEPOSIT=0 THEN TD.MONEY_DEPOSIT ELSE 0 END ) MONEY_DEPOSIT ,SUM(CASE WHEN TD.DIV_DEPOSIT=1 THEN TD.MONEY_DEPOSIT ELSE 0 END ) MONEY_DEPOSIT_ADJST FROM T_DEPOSIT TD WHERE TD.DT_DEPOSIT < '&2' and (('&9' = '') OR (TD.CD_CUSTOMER = ('&9' ::text) ::numeric)) AND DIV_DELETE = 0 GROUP BY TD.CD_CUSTOMER ) PF GROUP BY PF.CD_CUSTOMER