|
地板
楼主 |
发表于 2024-7-1 16:54:01
|
只看该作者
[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode SELECT gadQualityNum, gadHeatNum, barCode into #tmp FROM W_StockInDetail wsid INNER JOIN W_StockIn si ON si.siID = wsid.siID INNER JOIN B_InOutType iot ON iot.iotID = si.iotID LEFT OUTER JOIN C_GoodsAcceptDetail cgad ON wsid.gadID = cgad.gadID WHERE Tag = '原材料' AND ISNULL(siZi,'') = '' AND iotName <> '退货出库'
SELECT sidOldCode,'有' AS ifBack into #tmp2 FROM W_StockInDetail Group BY sidOldCode
SELECT sodpaID, wsod.sodID,sodWeight,sodApplyCount,sodApplyWeight,sodPlanPrice,sodPlanMoney ,sodPrice,sodMoney,sodPriceNoTax,sodMoneyNoTax,sodRemark,wsod.soNum,iotID,soPickDept,soPickPerson,soTeam ,CASE sodCount WHEN 0 THEN 0 ELSE sodWeight/sodCount END AS sUnitWeight,CASE soDate WHEN '1900-1-1' THEN '' ELSE CONVERT(VARCHAR(10),soDate,120) END AS soDate,soRed,Writer,sodProject,sodTowerType,sodTaskNum,sodProduceNum,sodcNum,CASE wsod.WriteDate WHEN '1900-1-1' THEN '' ELSE CONVERT(VARCHAR(10),wsod.WriteDate,120) END AS WriteDate,soID,Locker,CASE wsod.LockDate WHEN '1900-1-1' THEN '' ELSE CONVERT(VARCHAR(10),wsod.LockDate,120) END AS LockDate,wsod.State,soRemark,wsod.mfName,wsod.barCode,wsod.mfSpec,wsod.mfNum,wsod.gsName,wsod.zName,wsod.wID,wsod.wName,wsod.indexNum,wsod.iotName,wsod.mfID,wsod.etName,wsod.lName,wsod.mfUnit,wsod.mcName,sodLength,sodWidth,sodCount,Keeper,CASE OutDate WHEN '1900-1-1' THEN '' ELSE CONVERT(VARCHAR(10),OutDate,120) END AS OutDate,sodDiameter,sodThick,sodRealWeight,sodUse ,wsod.mcNum,wsod.mfQuickNum,sodAccountType,mfcCode ,sodpaState ,approveState,CASE sodBackTime WHEN '1900-1-1' THEN '' ELSE CONVERT(VARCHAR(10),sodBackTime,120) END AS sodBackTime,Valid,sodApplyID,sodrddDeal,sodrddPriceNew,sodUseDept ,gadQualityNum,gadHeatNum,ISNULL(ifBack,'') AS ifBack,isnull(t.soNum,'') as soApplyNum,ptTaskType into #tmp3 FROM W_StockOutView AS wsod LEFT OUTER JOIN #tmp cgad ON wsod.barCode = cgad.barCode LEFT OUTER JOIN #tmp2 wsid2 ON wsid2 .sidOldCode = wsod.barCode left join ( select soNum,sodid from W_StockPickDetail wspd left join W_StockPick wsp on wspd.soID = wsp.soID) t on t.sodid = wsod.sodapplyid WHERE (1=1) AND soApply = 0 AND Tag = '原材料' AND Tag1 = '' AND sodproducenum LIKE '%20231209-01%' ORDER BY CASE Writer WHEN '' THEN 1 ELSE 2 END ASC, soDate DESC,sodID DESC
select * from #tmp3
drop table #tmp
drop table #tmp2
drop table #tmp3
找钢材报告
[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode SELECT Physics2,matFactoryDate,mtsManufacturer,mtsPatch,tadQualityNum,lName,mtsNum,mfName,etName,mfSpec, C,Si,Mn,P,S,V,Nb,Ti,Cr,YieldPoint,TensileStrength,Elongation,ImpactTest,ImpactTest1,ImpactTest2,mtDate as DETECTION_TIME,ItemStr3 as DETECTION_USER FROM [fzyk1].[fzyk].dbo.q_materialTestReportView where mtDate > DATEADD(mm, -60, getdate()) GROUP BY Physics2,matFactoryDate,mtsManufacturer,mtsPatch,tadQualityNum,lName,mtsNum,mfName,etName,mfSpec, C,Si,Mn,P,S,V,Nb,Ti,Cr,YieldPoint,TensileStrength,Elongation,ImpactTest,ImpactTest1,ImpactTest2,mtDate,ItemStr3 ORDER BY mtDate desc
|
|