类型：ms sql
ip：172.20.0.8
数据库：RGDB （erp-xin）
用户：sa
密码：Shich#62296690

语句：比价1
/****** Script for SelectTopNRows command from SSMS  ******/
use [erp-xin]
SELECT b.sfdj_rq,a.sfdjmx_sfdjid,a.sfdjmx_drmd,a.sfdjmx_gys,sfdj_dcck,sfdjmx_wlmc,sfdjmx_wldw,sfdjmx_wlsl,sfdjmx_wldj,sfdjmx_wlje,sfdj_czy,sfdj_czsj
  FROM [RGDB].[dbo].[REP_SFDJMX] a left join [RGDB].[dbo].[REP_SFDJ] b on a.sfdjmx_sfdjid=b.sfdj_id
  where sfdj_rq ='2021-10-26' and a.sfdjmx_gys in ('贝壳（汤玉）','小李蚌（李明旺）','鑫伟鲍鱼（陈彩恋）','龙虾（郑海滨）','梭子蟹（李安湘）','三文鱼（王秀红）',
  '海景水产（梁建邦）','南安水产（陈玉秀）','青膏蟹（谢石华）','小宁波（何爱伟）','海升（郑小蝶）','德缘燕鲍翅行','上海豚棠贸易商行','云淡水产（林书强）','象山冰鲜（林松国）',
  '文浩水产（陈侃）','乐记水产（乐月桂）','老杨贝壳（金家产）','厦门冰鲜（林凤丽）','政凤水产（薛政凤）','榕港囤货','昌记冻品(吴昌华）','创鲜冻品（黄国彪）','小罗冻品（左亚娟）',
  '厚强贸易（张信东）','厨博汇（解敏）','许鲜家禽（许广枝）','斌详食品（潘晓博）','逸希水产行（吴凯阳）','厨新食材（谢萍）','神户牛肉（陈开明）','黄金虾堡（周伟）',
  '龚记水产（龚其祥）','木瓜（张丽红）','虾仁（吕小斌）','中天行（俞其泽）','阿宾食品（许慧珍）','丛林菌菇（孙有雯)','鹿舌（史树全）','三平（朱海燕）','黑毛猪肉（李贻华）',
  '五谷杂粮（郑春生）','张泽羊肉（陈银花）','台州豆腐（罗雪）','陈氏鱼翅（陈能发）','陈登云','红膏大闸蟹（裴树礼）')

use [erp-xin]
SELECT b.sfdj_rq,a.sfdjmx_sfdjid,a.sfdjmx_drmd,a.sfdjmx_gys,sfdj_dcck,sfdjmx_wlmc,sfdjmx_wldw,sfdjmx_wlsl,sfdjmx_wldj,sfdjmx_wlje,sfdj_czy,sfdj_czsj
  FROM [RGDB].[dbo].[REP_SFDJMX] a left join [RGDB].[dbo].[REP_SFDJ] b on a.sfdjmx_sfdjid=b.sfdj_id
  where sfdj_rq between '2021-11-10' AND '2021-11-12' and a.sfdjmx_gys in ('贝壳（汤玉）','小李蚌（李明旺）','鑫伟鲍鱼（陈彩恋）','龙虾（郑海滨）','梭子蟹（李安湘）','三文鱼（王秀红）',
  '海景水产（梁建邦）','南安水产（陈玉秀）','青膏蟹（谢石华）','小宁波（何爱伟）','海升（郑小蝶）','德缘燕鲍翅行','上海豚棠贸易商行','云淡水产（林书强）','象山冰鲜（林松国）',
  '文浩水产（陈侃）','乐记水产（乐月桂）','老杨贝壳（金家产）','厦门冰鲜（林凤丽）','政凤水产（薛政凤）','榕港囤货','昌记冻品(吴昌华）','创鲜冻品（黄国彪）','小罗冻品（左亚娟）',
  '厚强贸易（张信东）','厨博汇（解敏）','许鲜家禽（许广枝）','斌详食品（潘晓博）','逸希水产行（吴凯阳）','厨新食材（谢萍）','神户牛肉（陈开明）','黄金虾堡（周伟）',
  '龚记水产（龚其祥）','木瓜（张丽红）','虾仁（吕小斌）','中天行（俞其泽）','阿宾食品（许慧珍）','丛林菌菇（孙有雯)','鹿舌（史树全）','三平（朱海燕）','黑毛猪肉（李贻华）',
  '五谷杂粮（郑春生）','张泽羊肉（陈银花）','台州豆腐（罗雪）','陈氏鱼翅（陈能发）','陈登云','红膏大闸蟹（裴树礼）')


use [erp-xin]
SELECT b.sfdj_rq,a.sfdjmx_sfdjid,a.sfdjmx_drmd,a.sfdjmx_gys,sfdj_dcck,sfdjmx_wlmc,sfdjmx_wldw,sfdjmx_wlsl,sfdjmx_wldj,sfdjmx_wlje,sfdj_czy,sfdj_czsj
  FROM [RGDB].[dbo].[REP_SFDJMX] a left join [RGDB].[dbo].[REP_SFDJ] b on a.sfdjmx_sfdjid=b.sfdj_id
  where sfdj_rq between '2021-11-1' AND '2021-11-15' and a.sfdjmx_gys in ('家禽（吴邦宏)','猪肉（张春华)','水果（胡红波）','鸡蛋（陶梦强)','猪肉（李贻华）','鸡蛋（严永干）',
  '家禽（王霞）','蔬菜（宋木根）','猪肉（陶巧英）','水果（周仁枝）','鸡蛋（罗丽）','水果（魏东松）','水果(罗展)','猪肉（王富明）','鸡蛋(朱红龙)',
  '蔬菜（武金红）','家禽（李贻华）','鸡蛋（马健光）','蔬菜(周木根）','猪肉（李光前）','家禽（左作春）','鸡蛋（吴国华）','豆制品（范祖维）','豆制品(李贻华)',
  '鸡蛋（丁元方）','水果（李国庆）','鸡蛋（严永干）')

use [erp-xin]
SELECT b.sfdj_rq,a.sfdjmx_sfdjid,a.sfdjmx_drmd,a.sfdjmx_gys,sfdj_dcck,sfdjmx_wlmc,sfdjmx_wldw,sfdjmx_wlsl,sfdjmx_wldj,sfdjmx_wlje,sfdj_czy,sfdj_czsj
  FROM [RGDB].[dbo].[REP_SFDJMX] a left join [RGDB].[dbo].[REP_SFDJ] b on a.sfdjmx_sfdjid=b.sfdj_id
  where sfdj_rq between '2021-11-1' AND '2021-11-15' and a.sfdjmx_gys in ('家禽（吴邦宏)','猪肉（张春华)','水果（胡红波）','猪肉（李贻华）','家禽（王霞）','蔬菜（宋木根）','猪肉（陶巧英）','水果（周仁枝）','水果（魏东松）','水果(罗展)','猪肉（王富明）',
  '蔬菜（武金红）','家禽（李贻华）','蔬菜(周木根）','猪肉（李光前）','家禽（左作春）','水果（李国庆）')

use [erp-xin]
SELECT b.sfdj_rq,a.sfdjmx_sfdjid,a.sfdjmx_drmd,a.sfdjmx_gys,sfdj_dcck,sfdjmx_wlmc,sfdjmx_wldw,sfdjmx_wlsl,sfdjmx_wldj,sfdjmx_wlje,sfdj_czy,sfdj_czsj
  FROM [RGDB].[dbo].[REP_SFDJMX] a left join [RGDB].[dbo].[REP_SFDJ] b on a.sfdjmx_sfdjid=b.sfdj_id
  where sfdj_rq between '2021-11-1' AND '2021-11-15' and a.sfdjmx_gys in ('家禽（吴邦宏)','猪肉（张春华)','水果（胡红波）','猪肉（李贻华）','家禽（王霞）','猪肉（陶巧英）','水果（周仁枝）','水果（魏东松）','水果(罗展)','猪肉（王富明）','家禽（李贻华）','猪肉（李光前）','家禽（左作春）','水果（李国庆）','水果（武金红）')

use [erp-xin]
SELECT b.sfdj_rq,a.sfdjmx_sfdjid,a.sfdjmx_drmd,a.sfdjmx_gys,sfdj_dcck,sfdjmx_wlmc,sfdjmx_wldw,sfdjmx_wlsl,sfdjmx_wldj,sfdjmx_wlje,sfdj_czy,sfdj_czsj
  FROM [RGDB].[dbo].[REP_SFDJMX] a left join [RGDB].[dbo].[REP_SFDJ] b on a.sfdjmx_sfdjid=b.sfdj_id
  where sfdj_rq between '2021-12-1' AND '2021-12-31' and a.sfdjmx_gys in ('佬广食品有限公司')

按门店订单号日期查询金额
select c.Store_JC,b.sfdj_rq,b.sfdj_id,sfdjmx_gys,SUM(sfdjmx_wlje) from REP_SFDJMX a
left join REP_SFDJ b on a.sfdjmx_sfdjid=b.sfdj_id
left join View_Base_Store c on b.sfdj_drmd=c.Store_ID
where sfdj_drmd in (13,16) and b.sfdj_rq between '2021-03-01' and '2021-03-02' and sfdjmx_gys in ('游春生','猪肉（李贻华）')
group by Store_JC,b.sfdj_rq,b.sfdj_id,sfdjmx_gys
order by Store_JC,sfdj_rq,sfdjmx_gys




问题表

select b.Store_JC,c.deptname , a.ysdj_shrq,a.ysdj_shr,d.gysxx_jc,a.ysdj_clmc,a.ysdj_wtms,a.ysdj_cljg,a.ysdj_czry,a.ysdj_czsj
from Base_YSDJ a
left join  Base_StoreInfo b  on a.ysdj_sid=b.Store_ID
left join  Base_Dept c on a.ysdj_shbmindex=c.Table_Index
left join Base_GYSXX d on a.ysdj_gysxxindex=d.Table_Index
where ysdj_visible=1 and ysdj_shrq between '2021-11-01' and '2021-11-30'
order by  b.Store_ID,c.orderid,a.ysdj_shrq

查找电子单据记账日期错误
select * from Base_PZZX where LEFT(pzzx_pzid,2)='21' and (MONTH(pzzx_djrq) <> MONTH(pzzx_jzrq)) AND pzzx_sid='1'
凭证中心查询
select * from Base_PZZX WHERE pzzx_jzrq BETWEEN '2021-11-1' AND '2021-11-30' AND pzzx_sid='1'

打款查询
Select * from(  
Select a.*,ISNULL(b.gysxx_jc,'')bxlx,ISNULL(c.SHLX_Name,'')shlx, 
d.jjcd_Name jjcd,e.Store_Name mdmc,f.skzh_name+' | '+g.bank_name+' - '+f.skzh_zh skzhinfo,h.jszh_mc, 
case fkbx_dkzt when -1 then '未打款' when 0 then '打款中' when 1 then '已打款' end dkzt from View_REP_FKDJ a 
left join Base_GYSXX b on a.fkbx_bxlxindex=b.Table_Index 
left join Base_SHLX c on a.fkbx_shlxindex=c.Table_Index  
left join Base_JJCD d on a.fkbx_jjcdindex=d.Table_Index  
left join Base_StoreInfo e on a.fkbx_sid=e.Store_ID      
left join Base_SKZH f on a.fkbx_skzhindex=f.Table_Index  
left join Base_Bank g on f.skzh_bankindex=g.Table_Index  
left join Base_JSZH h on a.fkbx_jszhindex=h.Table_Index  
where fkbx_visible=1 and fkbx_djrq between '2021-09-01' and '2021-12-30' and fkbx_sid in (1,2,3,4,5,6,7,8,10,11,12,13,14,15,16))djcx 
where 1=1 

入库部门汇总
select sfdjmx_drmd drmd,sfdjmx_gys gys,sfdjmx_drck drck,SUM(sfdjmx_wlje) drje from REP_SFDJMX a left join REP_SFDJ b on a.sfdjmx_sfdjid=b.sfdj_id
where sfdj_djlx='入库单' and sfdjmx_drmd='12' and sfdj_rq between '2021-12-01' and '2021-12-31'
group by sfdjmx_drmd,sfdjmx_gys,sfdjmx_drck
order by sfdjmx_drmd,sfdjmx_gys,sfdjmx_drck

（包括1211）
select sfdjmx_drmd drmd,sfdjmx_gys gys,sfdjmx_drck drck,SUM(sfdjmx_wlje) drje from REP_SFDJMX a left join REP_SFDJ b on a.sfdjmx_sfdjid=b.sfdj_id
where sfdj_djlx='入库单' and sfdjmx_drmd='12' and sfdj_rq between '2021-12-01' and '2021-12-31' and left(sfdjmx_kmbm,4)='1211'
group by sfdjmx_drmd,sfdjmx_gys,sfdjmx_drck
order by sfdjmx_drmd,sfdjmx_gys,sfdjmx_drck


物料明细


select a.zwzxmx_ztid,a.zwzxmx_grname,a.zwzxmx_zy,a.zwzxmx_jfje,a.zwzxmx_bmname from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-11-1 'and '2021-11-30'and LEFT(zwzxmx_kmbm,6)='550110'




毛利表科目
select * from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid='807' and LEFT(zwzxmx_kmbm,4)='1211'

gop公式
select * from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid IN('811','711') and LEFT(zwzxmx_kmbm,1)='5'
  

SELECT a.zwzxmx_jzrq,a.zwzxmx_sid,b.zwzx_bh,zwzxmx_ztid,a.zwzxmx_zy,a.zwzxmx_kmbm,a.zwzxmx_jfje,a.zwzxmx_dfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid IN('811','711') and LEFT(zwzxmx_kmbm,1)='5'
 



SELECT a.zwzxmx_jzrq,a.zwzxmx_sid,b.zwzx_bh,zwzxmx_ztid,a.zwzxmx_zy,a.zwzxmx_kmbm,a.zwzxmx_jfje,a.zwzxmx_dfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-9-01' and '2021-9-30' and zwzxmx_ztid IN('813','713','714','814','715','815') and LEFT(zwzxmx_kmbm,1)='5'
  

查单据号里面的关键词

SELECT 
   n.Store_JC,
    a.data_area,
 a.order_code,   
 a.order_time,
 b.NAME as base_name,
 c.SHORT_NAME AS supplier_name,
 a.materiel_code,
 a.NAME,
 m.name as sort_name,
 
 a.unit_name,    
 (case when unit_type=1 then NUMBER else MIN_NUMBER/unit_cf end) as number,    
 (case when MIN_NUMBER=0 then 0.00 else (case when unit_type=1 then total_money/NUMBER else total_money/(MIN_NUMBER/unit_cf) end) end) as price,
 total_money AS total_money
FROM VKC_CG_LIST a INNER JOIN
 KC_BASE b ON a.BASE_CODE = b.CODE INNER JOIN
 KC_SUPPLIER c ON a.supplier_code = c.CODE 
 INNER JOIN ALL_MATERIEL_SORT m on a.small_sort=m.code 
 left join [RGDB].[dbo].[Base_StoreInfo] n on n.Store_KCBM=a.data_area
where a.is_check=1 and a.NAME like '%盆%'  and m.name like '%原材料-其他成本%' and
((CONVERT(char(10),a.order_time, 120)>='2021-08-01') and 
(CONVERT(char(10),a.order_time, 120)<='2021-03-31'))
order by a.data_area,a.small_sort,a.sort_no,a.name,a.order_time



毛利表引用


select '内部调拨' lx,zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%内部调拨%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all

select '异店调拨',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%异店调拨%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all

select '总仓配送',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%总仓配送%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all

select '赠送',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%赠送%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all

select '营销活动',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%营销活动%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all 

select '直拨',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-3-01' and '2021-3-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and (zwzxmx_zy not like '%总仓配送%')
and (zwzxmx_zy not like '%异店调拨%') and  (zwzxmx_zy not like '%内部调拨%') and  (zwzxmx_zy not like '%赠送%') and  (zwzxmx_zy not like '%营销活动%')
group by zwzxmx_kmbm,zwzxmx_kmname




毛利表引用（校验）


select SUM(zwzxmx_jfje) from (


select '内部调拨' lx,zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%内部调拨%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all

select '异店调拨',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%异店调拨%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all

select '总仓配送',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and zwzxmx_zy like '%总仓配送%'
group by zwzxmx_kmbm,zwzxmx_kmname

union all 

select '其他',zwzxmx_kmbm,zwzxmx_kmname,SUM(zwzxmx_jfje)zwzxmx_jfje from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211' and (zwzxmx_zy not like '%总仓配送%')
and (zwzxmx_zy not like '%异店调拨%') and  (zwzxmx_zy not like '%内部调拨%')
group by zwzxmx_kmbm,zwzxmx_kmname


)a



select SUM(zwzxmx_jfje) from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-1-01' and '2021-1-31' and zwzxmx_ztid='704' and LEFT(zwzxmx_kmbm,4)='1211'





某个类别材料进货

SELECT
   n.Store_JC,
    a.data_area,
 a.order_code,   
 a.order_time,
 b.NAME as base_name,
 c.SHORT_NAME AS supplier_name,
 a.materiel_code,
 a.NAME,
 m.name as sort_name,
 
 a.unit_name,    
 (case when unit_type=1 then NUMBER else MIN_NUMBER/unit_cf end) as number,    
 (case when MIN_NUMBER=0 then 0.00 else (case when unit_type=1 then total_money/NUMBER else total_money/(MIN_NUMBER/unit_cf) end) end) as price,
 total_money AS total_money
FROM VKC_CG_LIST a INNER JOIN
 KC_BASE b ON a.BASE_CODE = b.CODE INNER JOIN
 KC_SUPPLIER c ON a.supplier_code = c.CODE 
 INNER JOIN ALL_MATERIEL_SORT m on a.small_sort=m.code 
 left join [RGDB].[dbo].[Base_StoreInfo] n on n.Store_KCBM=a.data_area
where a.is_check=1 and a.NAME like '%盆%'  and m.name like '%原材料-其他成本%' and
((CONVERT(char(10),a.order_time, 120)>='2021-01-01') and 
(CONVERT(char(10),a.order_time, 120)<='2021-04-31'))
order by a.data_area,a.small_sort,a.sort_no,a.name,a.order_time


SELECT
   n.Store_JC,
    a.data_area,
 a.order_code,   
 a.order_time,
 b.NAME as base_name,
 c.SHORT_NAME AS supplier_name,
 a.materiel_code,
 a.NAME,
 m.name as sort_name,
 
 a.unit_name,    
 (case when unit_type=1 then NUMBER else MIN_NUMBER/unit_cf end) as number,    
 (case when MIN_NUMBER=0 then 0.00 else (case when unit_type=1 then total_money/NUMBER else total_money/(MIN_NUMBER/unit_cf) end) end) as price,
 total_money AS total_money
FROM VKC_CG_LIST a INNER JOIN
 KC_BASE b ON a.BASE_CODE = b.CODE INNER JOIN
 KC_SUPPLIER c ON a.supplier_code = c.CODE 
 INNER JOIN ALL_MATERIEL_SORT m on a.small_sort=m.code 
 left join [RGDB].[dbo].[Base_StoreInfo] n on n.Store_KCBM=a.data_area
where a.is_check=1 and a.NAME like '%盆%'  and m.name like '%礼盒类-其他礼盒%' and
((CONVERT(char(10),a.order_time, 120)>='2021-01-01') and 
(CONVERT(char(10),a.order_time, 120)<='2021-04-31'))
order by a.data_area,a.small_sort,a.sort_no,a.name,a.order_time






查询电子单据编号内的做账明细

select a.zwzxmx_ztid,a.zwzxmx_zy,a.zwzxmx_kmbm,a.zwzxmx_kmname,a.zwzxmx_jfje,a.zwzxmx_dfje,a.zwzxmx_bmname,a.zwzxmx_grname ,a.zwzxmx_gysname from Base_ZWZXMX a
left join Base_ZWZX b on a.zwzxmx_zwzxid=b.zwzx_id
where zwzx_rzrq between '2021-11-01' and '2021-12-31'and a.zwzxmx_pzid='212021110401339'

查询电子单据对应的fk单据号
select * from REP_FKBXDJ where fkbxdj_sfdjid in ('212021050200782','212021053011393','212021053011429')

入库材料汇总
select * from (
select b.Store_JC,sfdjmx_drck,sfdjmx_wlmc,SUM(sfdjmx_wlje)sfdjmx_wlje from REP_SFDJMX a
left join Base_StoreInfo b on a.sfdjmx_drmd=b.Store_ID
left join REP_SFDJ c on a.sfdjmx_sfdjid=c.sfdj_id
where YEAR(c.sfdj_rq)=2021 and MONTH(c.sfdj_rq)=5 and sfdjmx_wllb like '%原材料%'
group by b.Store_JC,sfdjmx_drck,sfdjmx_wlmc) a
where 1=1 and Store_JC='花园' and sfdjmx_drck='点心'

select * from (
select b.Store_JC,sfdjmx_drck,sfdjmx_wlmc,SUM(sfdjmx_wlje)sfdjmx_wlje,sfdjmx_kmname from REP_SFDJMX a
left join Base_StoreInfo b on a.sfdjmx_drmd=b.Store_ID
left join REP_SFDJ c on a.sfdjmx_sfdjid=c.sfdj_id
where YEAR(c.sfdj_rq)=2021 and MONTH(c.sfdj_rq)=5 and sfdj_djlx='入库单'
group by b.Store_JC,sfdjmx_drck,sfdjmx_wlmc,sfdjmx_kmname) a
where 1=1 and Store_JC='徐汇' and sfdjmx_drck='海派'



盘点表

SELECT  
      e.Store_JC,
      a.DATA_AREA,
      a.ORDER_CODE,
      b.ORDER_TIME,
      b.BASE_CODE,
      c.NAME,
      b.IS_CHECK,
      case when left(a.MATERIEL_CODE,5)='00101' then '成本' else '费用' end 类别,
      a.MATERIEL_CODE,
      f.NAME,
      d.name,
      d.spec,
      g.NAME,
     a.STOCK_NUMBER,
      a.cost_price,
      a.check_number,
	 a.cost_money
  FROM [erp-xin].[dbo].[KC_CHECK_LIST] a
  left join [erp-xin].[dbo].[KC_CHECK] b on a.ORDER_CODE=b.CODE
  left join [erp-xin].[dbo].[KC_BASE] c on c.CODE=b.BASE_CODE
  left join [erp-xin].[dbo].[ALL_MATERIEL] d on d.code=a.MATERIEL_CODE 
  left join [RGDB].[dbo].[Base_StoreInfo] e on a.DATA_AREA=e.Store_KCBM
  left join [erp-xin].[dbo].[ALL_MATERIEL_SORT] f on f.CODE=d.SORT_CODE
  left join [erp-xin].[dbo].all_unit  g on a.UNIT_CODE=g.CODE
 where Store_ID<>9 and ORDER_TIME='2021-05-31' and Store_ID<17 AND c.name='外卖档口'
 order by e.Store_Order,c.NAME


果汁成本


select t2.Store_JC,t1.* ,
case when menu_name like '%西瓜%' then '西瓜' when menu_name like '%橙%' then '橙子' else '哈密瓜' end zhbi ,
case when menu_name like '%西瓜%' then '3.5' when menu_name like '%橙%' then '2' else '3.5' end zhbi ,
(case when menu_name like '%西瓜%' then '3.5' when menu_name like '%橙%' then '2' else '3.5' end ) * quantity dbjl
from (
select sid,menu_name,menu_unit,
SUM(quantity)quantity,sum(order_discount) order_discount from SC_RA_Details
 where tj_name='外包果汁' and report_date between '2021-07-01' and '2021-07-31' and menu_name in (
 '新奇仕橙汁','消暑西瓜汁（冷）','消暑西瓜汁（大厅）','清甜哈密瓜汁（冷）','清甜哈密瓜汁(大厅)','柳橙汁（冷）','柳橙汁（包房）','海南西瓜汁') and menu_unit='扎'
group by sid,menu_name,menu_unit) t1
left join Base_StoreInfo t2 on t1.sid=t2.Store_ID
order by sid,menu_name desc

查询打包盒


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT menu_name,menu_unit,b.Store_Name,SUM(quantity)quantity,sum(order_discount)order_discount
  FROM [RGDB].[dbo].[SC_RA_Details] a
  left join [RGDB].[dbo].[Base_StoreInfo] b on a.sid=b.Store_ID
  where menu_id in ('11057','4026')
  and report_date>='2021-07-01' and quantity>0 and order_discount>0
  group by menu_name,b.Store_Name,menu_unit

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT menu_name,menu_unit,b.Store_Name,SUM(quantity)quantity,sum(order_discount)order_discount
  FROM [RGDB].[dbo].[SC_RA_Details] a
  left join [RGDB].[dbo].[Base_StoreInfo] b on a.sid=b.Store_ID
  where menu_id in ('11057','4026')
  and report_date BETWEEN '2021-09-01' and '2021-09-30' AND quantity>0 and order_discount>0
  group by menu_name,b.Store_Name,menu_unit

查询菜品类别
select b.Store_JC,a.* from SC_RA_Details  a
left join Base_StoreInfo b on a.sid=b.Store_ID
where category_name='营销活动' and report_date between  '2021-09-01' and '2021-09-30' 

调味品
SELECT 
   n.Store_JC,
    a.data_area,
 a.order_code,   
 a.order_time,
 b.NAME as base_name,
 c.SHORT_NAME AS supplier_name,
 a.materiel_code,
 a.NAME,
 m.name as sort_name,
 
 a.unit_name,    
 (case when unit_type=1 then NUMBER else MIN_NUMBER/unit_cf end) as number,    
 (case when MIN_NUMBER=0 then 0.00 else (case when unit_type=1 then total_money/NUMBER else total_money/(MIN_NUMBER/unit_cf) end) end) as price,
 total_money AS total_money
FROM VKC_CG_LIST a INNER JOIN
 KC_BASE b ON a.BASE_CODE = b.CODE INNER JOIN
 KC_SUPPLIER c ON a.supplier_code = c.CODE 
 INNER JOIN ALL_MATERIEL_SORT m on a.small_sort=m.code 
 left join [RGDB].[dbo].[Base_StoreInfo] n on n.Store_KCBM=a.data_area
where a.is_check=1 and m.name like '%原材料-调味品%' and
((CONVERT(char(10),a.order_time, 120)>='2021-10-01') and 
(CONVERT(char(10),a.order_time, 120)<='2021-10-31'))
order by a.data_area,a.small_sort,a.sort_no,a.name,a.order_time



--仓库绩效
SELECT c.Store_JC,CODE,b.sfdj_djlx,b.sfdj_gys,ORDER_TIME,MAKE_TIME,MAKE_MAN_NAME,CHECK_TIME,CHECK_MAN_NAME,b.sfdj_czsj,sfdj_czy 
from(
select CODE,ORDER_TIME,MAKE_TIME,MAKE_MAN_NAME,CHECK_TIME,CHECK_MAN_NAME FROM [erp-xin].[dbo].[KC_CGSH_ORDER] 
union all
SELECT CODE,ORDER_TIME,MAKE_TIME,MAKE_MAN_NAME,CHECK_TIME,CHECK_MAN_NAME FROM [erp-xin].[dbo].[KC_WLDB_ORDER] 
)a
left join REP_SFDJ b on a.CODE=b.sfdj_id
left join Base_StoreInfo c on b.sfdj_drmd=c.Store_ID
  where ORDER_TIME between '2021-09-01' and '2021-09-30' and c.Store_ID<17 and c.Store_ID<>9 
  order by c.Store_Order,CODE 

仓库绩效新编码
SELECT c.Store_JC,CODE,b.sfdj_djlx,b.sfdj_gys,ORDER_TIME,MAKE_TIME,DATEDIFF(DAY,ORDER_TIME,MAKE_TIME),MAKE_MAN_NAME,CHECK_TIME,CHECK_MAN_NAME,b.sfdj_czsj,DATEDIFF(DAY,ORDER_TIME,sfdj_czsj),sfdj_czy 
from(
select CODE,ORDER_TIME,MAKE_TIME,MAKE_MAN_NAME,CHECK_TIME,CHECK_MAN_NAME FROM [erp-xin].[dbo].[KC_CGSH_ORDER] 
union all
SELECT CODE,ORDER_TIME,MAKE_TIME,MAKE_MAN_NAME,CHECK_TIME,CHECK_MAN_NAME FROM [erp-xin].[dbo].[KC_WLDB_ORDER] 
)a
left join REP_SFDJ b on a.CODE=b.sfdj_id
left join Base_StoreInfo c on b.sfdj_drmd=c.Store_ID
  where ORDER_TIME between '2021-08-01' and '2021-08-31' and c.Store_ID<17 and c.Store_ID<>9 
  order by c.Store_Order,CODE 


电子单据未提交

select c.Store_JC dcmd,b.Store_JC drmd,a.sfdj_djlx,a.sfdj_id,a.sfdj_rq,a.sfdj_gys,a.sfdj_je,a.sfdj_czy
from REP_SFDJ a (nolock)
left join View_Base_Store b on a.sfdj_drmd=b.Store_ID
left join View_Base_Store c on a.sfdj_dcmd=c.Store_ID
where sfdj_czlc=-1 and YEAR(sfdj_rq)='2021' and month(sfdj_rq)='8' order by a.sfdj_drmd,sfdj_rq


已提交单据时间差
select b.Store_JC drmd,a.sfdj_czsj,a.sfdj_tjsj,datediff(DAY, a.sfdj_czsj, a.sfdj_tjsj) daycount,
a.sfdj_djlx,a.sfdj_id,a.sfdj_rq,a.sfdj_gys,a.sfdj_je,a.sfdj_czy,a.sfdj_tjry
from REP_SFDJ a (nolock)
left join View_Base_Store b on a.sfdj_drmd=b.Store_ID
where sfdj_czlc>-1 and YEAR(sfdj_rq)='2021' and month(sfdj_rq)='8'


退回记录
select c.Store_JC drmd,a.*,b.sfdj_tjry,b.sfdj_tjsj,b.sfdj_rq,b.sfdj_gys from rep_czls a
left join REP_SFDJ b on a.czls_djid=b.sfdj_id
left join View_Base_Store c on b.sfdj_drmd=c.Store_ID
where (LEFT(czls_djid,2)='21' or LEFT(czls_djid,2)='23' or LEFT(czls_djid,2)='31' or 
LEFT(a.czls_djid,2)='42' or LEFT(a.czls_djid,2)='48') and czls_czsm='退回'
and YEAR(b.sfdj_rq)='2021' and month(b.sfdj_rq)='8' order by c.Store_JC,czls_djid




2121  （查询匹配错）

select b.kmwh_jdlx,a.*,  
case b.kmwh_jdlx when 0 then ISNULL(c.qcjf,0)-ISNULL(c.qcdf,0) else ISNULL(c.qcdf,0)-ISNULL(c.qcjf,0) end qcje,                
case b.kmwh_jdlx when 0 then ISNULL(c.qcjf,0)-ISNULL(c.qcdf,0)+pzzx_jfje-pzzx_dfje else ISNULL(c.qcdf,0)-ISNULL(c.qcjf,0)+pzzx_dfje-pzzx_jfje end qmje 
from(


select '2121' kmbm,gyswh_gysbm,gyswh_name,ISNULL(pzzx_jfje,0)pzzx_jfje,ISNULL(pzzx_dfje,0)pzzx_dfje from Base_GYSWH a           
left join (                                                                                                                    
select pzzx_sid,pzzx_ztid,pzzx_kmbm,pzzx_kmname,pzzx_gysbm,SUM(pzzx_jfje)pzzx_jfje,SUM(pzzx_dfje)pzzx_dfje from View_Base_ZWZX 
where pzzx_sid='12' and pzzx_ztid='711' and pzzx_kmbm='2121' and pzzx_jzrq between '2021-01-01' and '2021-01-31' and pzzx_bh<>'0'
group by pzzx_sid,pzzx_ztid,pzzx_kmbm,pzzx_kmname,pzzx_gysbm                                                                   
) b on a.gyswh_sid=b.pzzx_sid and a.gyswh_ztid=b.pzzx_ztid and a.gyswh_gysbm=b.pzzx_gysbm                                       
where gyswh_sid='12' and gyswh_ztid='711'

) a   

                                                                               
left join (
select top 1 kmwh_kmdm,kmwh_jdlx from View_Base_KMWH where kmwh_sid='12' and kmwh_ztid='711' and kmwh_kmdm='2121'
)b  
on a.kmbm=b.kmwh_kmdm 



left join (
select pzzx_gysbm,SUM(a.pzzx_jfje)qcjf, SUM(a.pzzx_dfje)qcdf from View_Base_ZWZX a                                             
left join View_Base_KMWH b on a.pzzx_ztid=b.kmwh_ztid and a.pzzx_kmbm=b.kmwh_kmdm                                              
where pzzx_sid='12' and pzzx_ztid='711' and pzzx_jzrq < '2021-01-01' and pzzx_kmbm='2121' and pzzx_bh<>'0' group by pzzx_gysbm
) c on a.gyswh_gysbm=c.pzzx_gysbm         



order by gyswh_gysbm 


供应商编码丢失

select a.Table_Index,a.zwzxmx_pzid,a.zwzxmx_sid,a.zwzxmx_ztid,a.zwzxmx_jzrq,a.zwzxmx_zy,a.zwzxmx_jfje,a.zwzxmx_dfje,
a.zwzxmx_gysbm,a.zwzxmx_gysindex,a.zwzxmx_gysname,b.gyswh_name,c.gyswh_name,c.gyswh_ztid,c.gyswh_gysbm
from Base_ZWZXMX a 
left join Base_GYSWH b on a.zwzxmx_ztid=b.gyswh_ztid and a.zwzxmx_gysbm=b.gyswh_gysbm
left join Base_GYSWH c on a.zwzxmx_gysindex=c.Table_Index
where a.zwzxmx_ztid='814' and a.zwzxmx_kmbm='2121' and a.zwzxmx_jzrq between '2019-01-01' and '2021-01-31'
and ISNULL(b.gyswh_name,'')=''



特定桌台金额
select b.Store_JC,a.* from SC_RA_Orders a
left join Base_StoreInfo b on a.sid=b.Store_ID
 where table_name like '%经理%' and report_date  between '2021-04-01' and '2021-04-30'

经理餐分派系

select Store_JC,t1.tj_name,sum(order_discount)sr from SC_RA_Details t1
left join (
select b.Store_JC,a.* from SC_RA_Orders a
left join Base_StoreInfo b on a.sid=b.Store_ID
 where table_name like '%经理%' and report_date  between '2021-04-01' and '2021-04-30') t2 on t1.sid=t2.sid and t1.order_id=t2.id
 where t1.report_date  between '2021-04-01' and '2021-04-30' and t2.Store_JC is not null
 group by Store_JC,t1.tj_name
 order by Store_JC,t1.tj_name



虾仁卤鸽
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT menu_name,menu_unit,b.Store_Name,SUM(quantity)quantity,sum(order_discount)order_discount
  FROM [RGDB].[dbo].[SC_RA_Details] a
  left join [RGDB].[dbo].[Base_StoreInfo] b on a.sid=b.Store_ID
  where menu_id in ('2619','4482','4451','2600')
  and report_date>='2021-06-01' and quantity>0 and order_discount>0
  group by menu_name,b.Store_Name,menu_unit

供应商货款审批
select mdmc,fkbx_fksm,shlx,fkbx_sfje,skzh_khh,skzh_name,skzh_zh
from
(select djsh.* from ( 
Select a.*,ISNULL(b.gysxx_jc,'')bxlx,ISNULL(c.SHLX_Name,'')shlx, 
ISNULL(d.jjcd_Name,'')jjcd,ISNULL(e.Store_JC,'')mdmc,            
g.bank_name,f.skzh_khh,f.skzh_name,f.skzh_zh                      
from View_REP_FKDJ a                                                 
left join Base_GYSXX b on a.fkbx_bxlxindex=b.Table_Index             
left join Base_SHLX c on a.fkbx_shlxindex=c.Table_Index              
left join Base_JJCD d on a.fkbx_jjcdindex=d.Table_Index              
left join View_Base_Store e on a.fkbx_sid=e.Store_ID                 
left join Base_SKZH f on a.fkbx_skzhindex=f.Table_Index              
left join Base_Bank g on f.skzh_bankindex=g.Table_Index            
where (fkbx_czlc between 1 and 6) and fkbx_sptg=0 and fkbx_splc='0'
) djsh) djcx where 1=1

付款单据对应的流水
select a.*,b.* from Base_YHLSDYFK a
left join Base_YHLS b on a.yhlsdyfk_yhlsindex=b.Table_Index

where yhlsdyfk_fkbxid='FK2021102000004'

付款单据对应的流水（正负号）
select case b.yhls_jylx when '转出' then yhls_jyje*-1 else yhls_jyje end,a.*,b.* from Base_YHLSDYFK a
left join Base_YHLS b on a.yhlsdyfk_yhlsindex=b.Table_Index

where yhlsdyfk_fkbxid='FK2021102000004'

收款单据对应的流水（正负号）
select a.*,b.*,c.* from Base_YHLSDY a
left join REP_SKD b on a.yhlsdy_objectindex=b.Table_Index
left join Base_YHLS c on a.yhlsdy_yhlsindex=c.Table_Index
where a.yhlsdy_objecttype=2 and b.skd_id='SK2021120300023'

工资误差
declare @nd int, @yd int ,@n int 
set @nd='2021'   
set @yd='12'  
select q2.Store_JC,q1.*,q1.gdgz+q1.jxkh+q1.tc+q1.jl-q1.yfgz cyyz from(
select t1.*,isnull(t3.gdgz,0)-isnull(t2.gdgz,0)gdgz,isnull(t3.jxkh,0)-isnull(t2.jxkh,0)jxkh,isnull(t3.tc,0)-isnull(t2.tc,0)tc,isnull(t3.jl,0)-isnull(t2.jl,0)jl,isnull(t3.gzdr_yfgz,0)-isnull(t2.gzdr_yfgz,0)yfgz from (
select distinct gzdr_nd,gzdr_yd,gzdr_sid,gzdr_xm,gzdr_bm
 from (
select * from Base_GZDR 
 where  gzdr_gzpc=1 and gzdr_nd=@nd and gzdr_yd=@yd
 union all
 select * from Base_GZDR 
 where  gzdr_gzpc=2 and gzdr_nd=@nd and gzdr_yd=@yd
 )m)t1
 left join (
 select gzdr_nd,gzdr_yd,gzdr_sid,gzdr_xm,gzdr_bm,gzdr_gz+gzdr_qqjf+gzdr_bsygz gdgz,gzdr_jxgz+gzdr_wskh jxkh,gzdr_zptscjl+gzdr_tch tc,gzdr_tc+gzdr_jl+gzdr_cjpcygc+gzdr_kf jl ,gzdr_yfgz
from Base_GZDR 
where  gzdr_gzpc=1 and gzdr_nd=@nd and gzdr_yd=@yd)t2 on t1.gzdr_nd=t2.gzdr_nd and t1.gzdr_yd=t2.gzdr_yd and t1.gzdr_sid=t2.gzdr_sid and t1.gzdr_xm=t2.gzdr_xm and t1.gzdr_bm=t2.gzdr_bm
left join (
 select gzdr_nd,gzdr_yd,gzdr_sid,gzdr_xm,gzdr_bm,gzdr_gz+gzdr_qqjf+gzdr_bsygz gdgz,gzdr_jxgz+gzdr_wskh jxkh,gzdr_zptscjl+gzdr_tch tc,gzdr_tc+gzdr_jl+gzdr_cjpcygc+gzdr_kf jl ,gzdr_yfgz
from Base_GZDR 
where  gzdr_gzpc=2 and gzdr_nd=@nd and gzdr_yd=@yd)t3 on t1.gzdr_nd=t3.gzdr_nd and t1.gzdr_yd=t3.gzdr_yd and t1.gzdr_sid=t3.gzdr_sid and t1.gzdr_xm=t3.gzdr_xm and t1.gzdr_bm=t3.gzdr_bm
)q1
left join Base_StoreInfo q2 on q1.gzdr_sid=q2.Store_ID
where q1.gdgz+q1.jxkh+q1.tc+q1.jl+q1.yfgz<>0

海鲜进销存

  declare 
  @sid nvarchar(50), 
  @MONTH_NAME nvarchar(100) ,
  @ksrq date,
  @jsrq date
 set @sid='3'  
 set @MONTH_NAME='2022年7月'
  set @ksrq='2022-07-01'
   set @jsrq='2022-07-31'

---主表
   select w1.*,isnull(w2.CK_NUMBER,0)shsl,isnull(w3.zhjl,0)xssl,isnull(w2.CK_NUMBER,0)-isnull(w3.zhjl,0) cys from hxjxc_zb w1
   left join (
--消耗表
 select t2.hxjxc_zbtable,t1.Store_ID,sum(CK_NUMBER)CK_NUMBER,sum(CK_MONEY)CK_MONEY from (
 SELECT c.Store_ID,c.Store_JC,[COST_ID],[BASE_CODE],d.NAME ckname,[MATERIEL_CODE],b.NAME,e.NAME unit, CK_NUMBER,CK_MONEY,[END_MONEY] ,[END_COST]
  FROM [erp-xin].[dbo].[KC_COST_LIST] a
  left join  [erp-xin].[dbo].[ALL_MATERIEL] b on a.MATERIEL_CODE=b.CODE
  left join [erp-xin].[dbo].[KC_BASE] d on a.BASE_CODE=d.CODE
  left join  [RGDB].[dbo].[Base_StoreInfo] c on a.DATA_AREA=c.Store_KCBM
  left join [erp-xin].[dbo].ALL_UNIT e on b.MAIN_UNIT_CODE=e.CODE
  where cost_id=(
  select a.ID from [erp-xin].[dbo].KC_COST a
  left join [RGDB].[dbo].[Base_StoreInfo] b  on a.DATA_AREA=b.Store_KCBM
  where b.Store_ID=@sid  and a.MONTH_NAME=@MONTH_NAME)
and d.NAME='海鲜池' 
) t1
left join hxjxc_dyrk t2 on t1.MATERIEL_CODE=t2.rkcode

where t2.hxjxc_zbtable is not null
group by t2.hxjxc_zbtable,t1.Store_ID


) w2 on w1.Table_index=w2.hxjxc_zbtable

left join (
--销售表

select m1.hxjxc_zbtable,sid,sum(zhjl)zhjl,sum(order_discount)order_discount from (
SELECT b.hxjxc_zbhsbl*quantity zhjl,b.hxjxc_zbtable,b.hxjxc_zbhsbl,a.* FROM [RGDB].[dbo].[SC_RA_Details] a
left join hxjxc_dyxs b on a.menu_id=b.xsbm

where sid=@sid and report_date between @ksrq and @jsrq and tj_name='海鲜' and b.hxjxc_zbtable is not null
) m1
group by m1.hxjxc_zbtable,m1.sid



)w3 on w1.Table_index=w3.hxjxc_zbtable
 

 where isnull(w2.CK_NUMBER,0)<>isnull(w3.zhjl,0) 
 order by w3.order_discount desc