DECLARE @FromDate     AS DATETIME,
        @ToDate       AS DATETIME,
        @CompanyCode         VARCHAR(30)
        
DECLARE @SQL                 NVARCHAR(MAX)
SET @FromDate = '05-05-13'
SET @ToDate = '05-31-13'			
SET @CompanyCode = 'US'
set @sql = 'SELECT *,
	CASE 
		WHEN [Gen_ Prod_ Posting Group] ="RETAIL" THEN "RETAIL"
		WHEN [Gen_ Prod_ Posting Group] ="DEPOSIT" THEN "DEPOSIT"
		WHEN [Gen_ Prod_ Posting Group] ="EMPTY" THEN "EMPTY"
		WHEN [Gen_ Prod_ Posting Group] ="EXC" THEN "EXC"
		ELSE "DISCOUNT"
	END AS GPPG'
set @sql =@sql  +' INTO #Temp2'
set @sql =@sql  +'FROM  ['+@CompanyCode+'$Value Entry]'
set @sql =@sql  + 'WHERE  [Posting Date] BETWEEN @FromDate AND @ToDate'
set @sql =@sql  + 'AND ([Document Type] = 2 OR [Document Type] = 4)'
set @sql =@sql  +'AND [Source Code] = "SALES"
			 AND (
               [Gen_ Prod_ Posting Group] <> "DEPOSIT"
               AND [Gen_ Prod_ Posting Group] <> "EMPTY"
	           )'
set @sql =@sql  +'AND [Item No_]                       LIKE "10%"'
set @sql =@sql  +'SELECT [Item No_],
					[Description],
					[Item Ledger Entry Type],
					[Gen_ Prod_ Posting Group],
					[Location Code],
					[Document Type],'
set @sql =@sql  +'CASE WHEN GPPG="RETAIL" THEN SUM([Valued Quantity])   ELSE 0 END           AS "Quantity",'
set @sql =@sql  +'CASE WHEN GPPG="RETAIL" THEN SUM([Sales Amount (Actual)])    ELSE 0 END  AS "Amount",'
set @sql =@sql  +'	   SUM([Sales Tax Amount (Actual)])  AS "Excise Tax Amount",'
set @sql =@sql  +'      CASE WHEN GPPG="DISCOUNT" THEN SUM([Valued Quantity])    ELSE 0 END           AS "Discount Quantity",'
set @sql =@sql  +'      CASE WHEN GPPG="DISCOUNT" THEN SUM([Discount Amount])    ELSE 0 END           AS "Discount Amount",'
set @sql =@sql  +'SUM([Valued Quantity in HL])      AS "Quantity HL"'
set @sql =@sql  +'FROM  #Temp2'
set @sql =@sql  +'GROUP BY
					[Item No_],
					[Description],
					[Item Ledger Entry Type],
					[Location Code],
					[Gen_ Prod_ Posting Group],
					[Document Type], GPPG  

DROP TABLE #Temp2'

exec sp_executesql @sql

quote:

---

Originally posted by dinhson_vn

it's print all code

---