Can Anyone Explain following code step by step please....
ALTER procedure [dbo].[amazon_Number1Direct_reports](@usname varchar(100),@date varchar(50))
as
begin
if (@date ='')
begin
DECLARE @sql VARCHAR(2000)
--declare @usname varchar(100)
SET @sql = '
SELECT ID,MPN,productname,updateddate,
MAX(CASE WHEN low = 1 THEN Merchants ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN baseprice ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN merchants ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN baseprice ELSE NULL END) AS HighPrice,
MAX(CASE merchants WHEN ''' + @usname + ''' THEN baseprice ELSE NULL END) AS MyPrice,
MAX(CASE merchants WHEN ''' + @usname + ''' THEN bottomlineprice ELSE NULL END) AS MyBottomprice
INTO ##Temp
FROM (
SELECT ID,MPN,productname,updateddate,
merchants,
baseprice,
bottomlineprice,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice asc) AS low
FROM [tbl_amazon1_master_merchant]
) AS t
GROUP BY ID,MPN,productname,updateddate
SELECT
m.MPN,
t1.product_description as Product_Title,
m.MyPrice,
m.MyBottomprice,
m.LowPrice,
case m.LowPriceMerchant when ''' + @usname + ''' then ''you'' else m.LowPriceMerchant end as LowPriceMerchant,
m.HighPrice,
case m.HighPriceMerchant when '''+ @usname+ ''' then ''you'' else m.HighPriceMerchant end as HighPriceMerchant
FROM ##Temp AS m
INNER JOIN tbl_amazon1_master_product AS t1 ON t1.ID = m.ID and datename(dd,t1.updated_date)= datename(dd,m.updateddate) and datename(mm,t1.updated_date)= datename(mm,m.updateddate)and datename(yy,t1.updated_date)=datename(yy,m.updateddate) and datename(dd,t1.updated_date)= datename(dd,getdate()) and datename(mm,t1.updated_date)=datename(mm,getdate()) and datename(yy,t1.updated_date)=datename(yy,getdate())
drop table ##temp'
exec (@sql)
end
else
begin
DECLARE @sql1 VARCHAR(2000)
--declare @usname varchar(100)
SET @sql1 = '
SELECT ID,MPN,productname,updateddate,
MAX(CASE WHEN low = 1 THEN Merchants ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN baseprice ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN merchants ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN baseprice ELSE NULL END) AS HighPrice,
MAX(CASE merchants WHEN ''' + @usname + ''' THEN baseprice ELSE NULL END) AS MyPrice,
MAX(CASE merchants WHEN ''' + @usname + ''' THEN bottomlineprice ELSE NULL END) AS MyBottomprice
INTO ##Temp
FROM (
SELECT ID,MPN,productname,updateddate,
merchants,
baseprice,
bottomlineprice,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice asc) AS low
FROM tbl_amazon1_master_merchant
) AS t
GROUP BY ID,MPN,productname,updateddate
SELECT
m.ID,
m.MPN,
t1.product_description as Product_Title,
m.MyPrice,
m.MyBottomprice,
m.LowPrice,
case m.LowPriceMerchant when ''' + @usname + ''' then ''you'' else m.LowPriceMerchant end as LowPriceMerchant,
m.HighPrice,
case m.HighPriceMerchant when '''+ @usname+ ''' then ''you'' else m.HighPriceMerchant end as HighPriceMerchant
FROM ##Temp AS m
INNER JOIN tbl_amazon1_master_product AS t1 ON t1.ID = m.ID and t1.updated_date=m.updateddate and m.updateddate=cast('''+@date+''' as datetime)
drop table ##temp'
--in (select updateddate from tbl_googleshopping2_master_merchant) and datepart(dd,t1.updated_date)= datepart(dd,cast('''+@date+''' as datetime)) and datepart(mm,t1.updated_date)=datepart(mm,cast('''+@date+''' as datetime)) and datepart(yy,t1.updated_date)=datepart(yy,cast('''+@date+''' as datetime))
--INNER JOIN [tbl_Pricegrabber_master_product] as t1 on t1.MobID = m.MobID and datepart(dd,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))= datepart(dd,getdate()) and datepart(mm,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))=datepart(mm,getdate()) and datepart(yy,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))=datepart(yy,getdate())
--INNER JOIN [tbl_Pricegrabber_master_product] as t1 on t1.MobID = m.MobID and datepart(dd,t1.Updated_Date)= datepart(dd,getdate()) and datepart(mm,t1.Updated_Date)=datepart(mm,getdate()) and datepart(yy,t1.Updated_Date)=datepart(yy,getdate())
exec (@sql1)
end
end