sp

USE [DM]
GO
/****** Object:  StoredProcedure [dbo].[spSalesTarget]    Script Date: 10/17/2012 09:41:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSalesTarget]
 --@QType int=0, @ID Varchar(50)=NULL,  @IDName Varchar(100)=NULL
@QType int=0, @Year int=NULL,  @Month int=NULL

AS
BEGIN
if(@QType=0)
begin
Select ProductID,ProductName,GenericName,PackSize,TP  from dbo.vw_Product

Select '2010' as Year union select '2011' as year union Select '2012' as Year union Select '2013' as Year union select '2014' as year union
Select '2015' as Year union select '2016' as year union Select '2017' as Year union select '2018' as year union Select '2019' as Year union
select '2020' as year

select 'Jan' as Month, 1 as ID union select 'Feb' as Month, 2 as ID union select 'Mar' as Month, 3 as ID union select 'Apr' as Month, 4 as ID union
select 'May' as Month, 5 as ID union select 'Jun' as Month, 6 as ID union select 'Jul' as Month, 7 as ID union select 'Aug' as Month, 8 as ID union
select 'Sep' as Month, 9 as ID union select 'Oct' as Month, 10 as ID union select 'Nov' as Month, 11 as ID union select 'Dec' as Month, 12 as ID order by ID

--select * from STMaster

--Select * from STChild
END
if(@QType=1)
BEGIN
select Year, Month, IsLocked, Location, MachineNameIP, Transfered, HOTransfered, Addedby, DateAdded, Updatedby, DateUpdated
from STMaster where Year=@Year and Month=@Month

select s.Year, s.Month, s.ProductID,   p.ProductName, p.GenericName, p.PackSize, s.TP, s.TargetQty, s.actualqty, s.TotalValue,  s.actualvalue , s.Location, s.MachineNameIP, s.Transfered, s.HOTransfered, s.Addedby, s.DateAdded, s.Updatedby, s.DateUpdated
from STChild as s  join vw_Product as p on  s.ProductID = p.ProductID
      where Year=@Year and Month=@Month

END
if(@QType=2)
BEGIN
declare @PreDate datetime,@PreYear int,@PreMonth int
select @PreDate=DATEADD("dd",-1,convert(datetime,CONVERT(varchar(10),@Month)+'/01/'+CONVERT(varchar(10),@Year),101))
Select @PreYear=YEAR(@PreDate)
Select @PreMonth=Month(@PreDate)
--select @PreDate,@PreYear,@PreMonth
---
select s.Year, s.Month, s.ProductID,   p.ProductName, p.GenericName, p.PackSize, s.TP, s.TargetQty, s.actualqty, s.TotalValue,  s.actualvalue , s.Location, s.MachineNameIP, s.Transfered, s.HOTransfered, s.Addedby, s.DateAdded, s.Updatedby, s.DateUpdated
from STChild as s  join vw_Product as p on  s.ProductID = p.ProductID
      where Year=@PreYear and Month=@PreMonth

END
END