采购管理 （sql日期应用案例）

USE [AisLock20120221]
GO
/****** Object:  StoredProcedure [dbo].[AAAAA_DDMangement]    Script Date: 05/07/2012 08:31:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER  date: <2012/01/12,,>
-- Description:	<采购管理,,>
-- =============================================


alter  procedure [dbo].[AAAAA_CGManage]
	@Year varchar(4),
	@Month varchar(2)
as
begin
declare @FromDate1  int

if @Year='' begin set @Year=Year(GETDATE()) end
if @Month='' begin set @Month=Month(GETDATE()) end
if datename(qq,@Year+'-'+@Month+'-1')=1 begin set @FromDate1=10 end
else if datename(qq,@Year+'-'+@Month+'-1')=2 begin set @FromDate1=1 end
else if datename(qq,@Year+'-'+@Month+'-1')=3 begin set @FromDate1=4 end
else if datename(qq,@Year+'-'+@Month+'-1')=4 begin set @FromDate1=7 end

select distinct   t3.FName as 供应商名称,
t2.FNumber 物料代码,t2.FName as 品名,t2.FModel as 规格,BQ.本期采购价格,
SQ.上期采购价格,NC.年初采购价格,SJD.上季度采购价格,BN.本年采购均价
  from ICStockBillEntry t1 inner join ICStockBill v1 on t1.FInterID=v1.FInterID
inner join t_ICItemcore t2 on t1.FItemID=t2.FItemID
inner join t_Supplier t3 on v1.FSupplyID=t3.FItemID
left join (
select   FItemID,SUM(FAmount)/sum(Fqty) as 本期采购价格   from   icstockbillentry t1
inner join ICStockBill v1 on t1.FInterID=v1.FInterID
  where  YEAR(FDate)=@Year and  month(FDate)=@Month
  group by FItemID having sum(Fqty)<>0  
) as BQ on BQ.FItemID=t1.FItemID
left join (
select   FItemID,SUM(FAmount)/sum(Fqty) as 上期采购价格   from   icstockbillentry t1
inner join ICStockBill v1 on t1.FInterID=v1.FInterID
  where   YEAR(FDate)=@Year and  month(FDate)= month(dateadd(month,-1,@Year+'-'+@Month+'-1'))
  group by FItemID having sum(Fqty)<>0  
) as SQ on SQ.FItemID=t1.FItemID
left join (
select   FItemID,SUM(FAmount)/sum(Fqty) as 年初采购价格   from   icstockbillentry t1
inner join ICStockBill v1 on t1.FInterID=v1.FInterID
  where   YEAR(FDate)=@Year and  month(FDate)='1'
  group by FItemID having sum(Fqty)<>0 
) as NC on NC.FItemID=t1.FItemID
left join (
select  FItemID,SUM(FAmount)/sum(Fqty) as 上季度采购价格   from   icstockbillentry t1
inner join ICStockBill v1 on t1.FInterID=v1.FInterID
  where   YEAR(FDate)=@Year and  month(FDate) between @FromDate1
   and @FromDate1+2
  group by FItemID having sum(Fqty)<>0 
) as SJD on SJD.FItemID=t1.FItemID
left join (
  select  FItemID,SUM(FAmount)/sum(Fqty) as 本年采购均价   from   icstockbillentry t1
inner join ICStockBill v1 on t1.FInterID=v1.FInterID
  where   YEAR(FDate)=@Year 
  group by FItemID having sum(Fqty)<>0 
)as BN on BN.FItemID=t1.FItemID
where FCancellation=0 

end