数据库查询委托药品消耗报表

解决方法： 第一步：select * from Oragnizations--查询组织机构id 第二步：select * from OtherCenterOragnizatiuons --查询第三方代煎中心id 第三步：执行一下语句，其中creatime可自定义填写目标时间 DECLARE @__searcher_OtherCenterId_0 uniqueIdentifier = 'c1815bc1-925c-43c4-10e3-08d9fce92834';--委托机构id DECLARE @__searcher_OragnizationId_1 uniqueIdentifier = 'E73045E3-73DD-4E55-AB90-113AE8E003C6';--组织机构id

SELECT [d].[DrugId], [n].[Name] AS [DrugName], [d].[OtherDrugCode] AS [HisDrugCode], [d].[OtherDrugName] AS [HisDrugName], COALESCE([d].InputPrice, 0.0) AS [InputPrice], COALESCE(SUM((COALESCE([d].InputPrice, 0.0) * [d].[UnitDose]) * CAST([d0].[Num] AS decimal(18,5))), 0.0) AS [TotalInputPrice], COALESCE(SUM([d].[UnitDose] * CAST([d0].[Num] AS decimal(18,2))), 0.0) AS [TotalDose], COALESCE(SUM(([d].[UnitDose] * CAST([d0].[Num] AS decimal(18,2))) * [n].[WeightSpec]), 0.0) AS [TotalWeight] FROM [DispenseDrugInfos] AS [d] INNER JOIN [DispensePrescInfos] AS [d0] ON [d].[PrescInfoId] = [d0].[ID] LEFT JOIN [MedDispenseOrders] AS [m] ON [d0].[OrderId] = [m].[ID] LEFT JOIN [NormalDrugs] AS [n] ON [d].[DrugId] = [n].[ID] WHERE (((([m].[otherCenterOragnizationId] = @__searcher_OtherCenterId_0) AND ([m].[OragnizationId] = @__searcher_OragnizationId_1)) AND ([m].[CreateTime] >= '2023-10-23T00:00:00.0000000+08:00')) AND ([m].[CreateTime] < '2023-10-26T00:00:00.0000000+08:00')) AND [m].[State] IN (0, 3) GROUP BY [d].[DrugId], [n].[Name], [d].[OtherDrugCode], [d].[OtherDrugName], COALESCE([d].InputPrice, 0.0)