Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Re: Func. to down-convert extended ASCII character fields?

Expand Messages
  • Razvan Socol
    When I first read your question, I said it is impossible . After a little while I said nothing is impossible; some things are just too complicated to worth
    Message 1 of 5 , Apr 2, 2004
    • 0 Attachment
      When I first read your question, I said "it is impossible".
      After a little while I said "nothing is impossible; some things are just too complicated to worth it..."
      In SQL Server 2000, I wrote the following function:
       
      CREATE FUNCTION RemoveAccents(@Word nvarchar(4000)) RETURNS nvarchar(4000) AS
      BEGIN
       DECLARE @Letters TABLE (Letter CHAR(1) COLLATE Latin1_General_CS_AI PRIMARY KEY)
       
       DECLARE @Letter char(1)
       SET @Letter='A' COLLATE Latin1_General_CS_AI
       
       WHILE 1=1 BEGIN
        INSERT INTO @Letters VALUES (@Letter)
        IF @Letter='Z' BREAK
        SET @Letter=CHAR(ASCII(@Letter)+1)
       END
       
       SET @Letter='a' COLLATE Latin1_General_CS_AI
       WHILE 1=1 BEGIN
        INSERT INTO @Letters VALUES (@Letter)
        IF @Letter='z' BREAK
        SET @Letter=CHAR(ASCII(@Letter)+1)
       END
       
       DECLARE @NewWord nvarchar(4000)
       DECLARE @pos smallint
       
       SET @pos=1
       SET @NewWord=''
       
       WHILE @pos<=LEN(@Word) BEGIN
       
        SET @Letter=(SELECT Letter FROM @Letters WHERE Letter=SUBSTRING(@Word,@pos,1))
       
        SET @NewWord=@NewWord+ISNULL(@Letter,SUBSTRING(@Word,@pos,1))
        
        SET @pos=@pos+1
       END
       
       RETURN @NewWord
      END
       
      You can call this function with:
       
      SELECT ProductName, dbo.RemoveAccents(ProductName) FROM Northwind.dbo.Products
       
      Razvan
      ----- Original Message -----
      Sent: Sunday, March 28, 2004 1:50 AM
      Subject: Re: [SQLQueriesNoCode] Re: Func. to down-convert extended ASCII character fields?

      On Sat, 27 Mar 2004 00:29:32 -0000, hansve2000 wrote:
      >In SQL Server 2000, simply cast the value to an accent insensitive
      >collation.
      >
      >declare @nv nvarchar(20), @c varchar(20)
      >set @nv = 'Sřrensen'
      >set @c = 'Sorensen'
      >select @nv
      >select case when
      >cast(@nv as varchar(20)) collate SQL_Latin1_General_CP1_CI_AI = @c
      >then 'yes' else 'no'  end

      Hans,
      That's great, exactly what I was looking for to do the search comparisons.

      One question though, is colation only good for comparisons and sorting?  The
      following SELECT statement still returns 'Sřrensen'...

      select cast(@nv as varchar(20)) collate SQL_Latin1_General_CP1_CI_AI

      Can COLLATE be used dfferently to get it to kick out 'Sorensen'?

      -g

      Gary Pupurs <gp-dev@...> on 03/27/2004
    • John Jerles
      Hello All, I have a question regarding a VERY long query. Basically, the table contains a list of products and reasons for returns. The reasons for returns
      Message 2 of 5 , Apr 3, 2004
      • 0 Attachment
        Hello All,

        I have a question regarding a VERY long query. Basically, the table
        contains a list of products and reasons for returns. The reasons for
        returns as labeled A - P with a bit field that represents whether the
        return came from a consumer, or was returned from the retailer. There is a
        date returned field as well as a month made field. Below is the table layout:

        if exists (select * from dbo.sysobjects where id =
        object_id(N'[dbo].[tbl_Returns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        drop table [dbo].[tbl_Returns]
        GO

        CREATE TABLE [dbo].[tbl_Returns] (
        [Return_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
        [ReturnDate] [datetime] NULL ,
        [SKU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [MonthMade] [numeric](18, 0) NULL ,
        [Consumer] [bit] NULL ,
        [Retailer] [bit] NULL ,
        [A] [numeric](18, 0) NULL ,
        [B] [numeric](18, 0) NULL ,
        [C] [numeric](18, 0) NULL ,
        [D] [numeric](18, 0) NULL ,
        [E] [numeric](18, 0) NULL ,
        [F] [numeric](18, 0) NULL ,
        [G] [numeric](18, 0) NULL ,
        [H] [numeric](18, 0) NULL ,
        [I] [numeric](18, 0) NULL ,
        [J] [numeric](18, 0) NULL ,
        [K] [numeric](18, 0) NULL ,
        [L] [numeric](18, 0) NULL ,
        [M] [numeric](18, 0) NULL ,
        [N] [numeric](18, 0) NULL ,
        [O] [numeric](18, 0) NULL ,
        [P] [numeric](18, 0) NULL
        ) ON [PRIMARY]
        GO



        My client wants me to create a query that lists results in a report that
        gives him the following:

        SKU, Reason A Consumer for the Month/Retail for the Month/Total for the
        Month (Consumer YTD/Retail YTD/Total YTD), And the same for reasons B - P
        plus a total at the end of each row.

        Also, The YTD changes according to the month he selects. For example, if
        he chooses to look at Nov. 2003, the YTD would go from Dec. 2002 through
        Nov. 2003.

        So what I need to have is a query that is flexible enough to do
        this. Since this report only needs to go back 12 months (i.e. he would be
        able to view 2 years worth of historical data) I have made dynamic views
        that cover a 12 month span. In other words, there is a view for 1 year ago
        this month, 1 year ago from last month, 1 year ago from 2 months, etc...

        I have named the views v_(Month Number)_Total so I can dynamically pick the
        view I want to use. The problem is that I have to put the whole query into
        a string so I can execute it. The query is about 30,000 characters in length.

        I have tried breaking the query into 6,000 character chucks and then adding
        them together at the end, but it only runs the first 8000 characters. I
        have also tried using a TEXT parameter rather than a VarChar to hold the
        query, but SQL won't allow that. I am including the query below, please be
        gentle on me when you read it.

        If you have any suggestions on how to pull this off, I would love to hear it.

        Thanks,

        John

        Declare @SKU as VarChar(50)
        Declare @Month as VarChar(50)
        Declare @Year as VarChar(10)
        Declare @Q as VarChar(8000)
        Declare @Q2 as VarChar(8000)
        Declare @Q3 as VarChar(8000)
        Declare @Q4 as VarChar(8000)
        Declare @Q5 as VarChar(8000)

        Declare @tblC as VarChar(20)
        Declare @tblR as VarChar(20)
        Declare @tblT as VarChar(20)
        Set @Month = '12'
        Set @Year = '2003'
        Set @tblC = 'v_' + Convert(VarChar(10), DateDiff(m,
        Convert(DateTime, @Month + '/1/' + @Year), GetDate())) + '_Consumer'
        Set @tblR = 'v_' + Convert(VarChar(10), DateDiff(m,
        Convert(DateTime, @Month + '/1/' + @Year), GetDate())) + '_Retailer'
        Set @tblT = 'v_' + Convert(VarChar(10), DateDiff(m,
        Convert(DateTime, @Month + '/1/' + @Year), GetDate())) + '_Total'

        Set @Q = 'SELECT R1.SKU, tbl_Products.ProductName, CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(A), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(A), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(A), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(A), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(A), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(A), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS A,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(B), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(B), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(B), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(B), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(B), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(B), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS B,
        CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(C), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(C), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(C), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(C), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(C), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(C), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS C,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(D), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(D), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(D), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(D), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(D), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(D), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS D'
        Set @Q2 = ', CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(E), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(E), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(E), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(E), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(E), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(E), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS E,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(F), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(F), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(F), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(F), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(F), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(F), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS F,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(G), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(G), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(G), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(G), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(G), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(G), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS G,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(H), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(H), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(H), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(H), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(H), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT DISTINCT ISNULL(SUM(H), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS H'
        Set @Q3 = ', CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(I), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(I), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(I), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(I), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(I), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(I), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS I,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(J), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(J), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(J), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(J), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(J), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(J), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS J,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(K), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(K), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(K), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(K), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(K), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(K), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS K'
        Set @Q4 = ', CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(L), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(L), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(L), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(L), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(L), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(L), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS L,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(M), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(M), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(M), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(M), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(M), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(M), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS M,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(N), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(N), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(N), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(N), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(N), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(N), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS N,
        CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(O), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(O), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(O), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(O), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(O), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(O), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS O'
        Set @Q5 = ', CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(P), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(P), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(P), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) + ''('' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(P), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(P), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) +
        ''/'' + CONVERT(VarChar(10),
        (SELECT ISNULL(SUM(P), 0) AS A
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (DateDiff(yy, ReturnDate, GetDate()) < 1.01))) + '')'' AS P,
        CONVERT(VarChar(10),
        (SELECT (ISNULL(SUM(A), 0) + ISNULL(SUM(B),
        0) + ISNULL(SUM(C), 0) + ISNULL(SUM(D), 0) + ISNULL(SUM(E), 0) +
        ISNULL(SUM(F), 0)
        + ISNULL(SUM(G), 0) +
        ISNULL(SUM(H), 0) + ISNULL(SUM(I), 0) + ISNULL(SUM(J), 0) + ISNULL(SUM(K),
        0) + ISNULL(SUM(L), 0)
        + ISNULL(SUM(M), 0) +
        ISNULL(SUM(N), 0) + ISNULL(SUM(O), 0) + ISNULL(SUM(P), 0)) AS Expr16
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Consumer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT (ISNULL(SUM(A), 0) + ISNULL(SUM(B),
        0) + ISNULL(SUM(C), 0) + ISNULL(SUM(D), 0) + ISNULL(SUM(E), 0) +
        ISNULL(SUM(F), 0)
        + ISNULL(SUM(G), 0) +
        ISNULL(SUM(H), 0) + ISNULL(SUM(I), 0) + ISNULL(SUM(J), 0) + ISNULL(SUM(K),
        0) + ISNULL(SUM(L), 0)
        + ISNULL(SUM(M), 0) +
        ISNULL(SUM(N), 0) + ISNULL(SUM(O), 0) + ISNULL(SUM(P), 0)) AS Expr16
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (Retailer = 1) AND (MONTH(ReturnDate) = ' + @Month + ') AND
        (YEAR(ReturnDate) = ' + @Year + '))) + ''/'' + CONVERT(VarChar(10),
        (SELECT (ISNULL(SUM(A), 0) + ISNULL(SUM(B),
        0) + ISNULL(SUM(C), 0) + ISNULL(SUM(D), 0) + ISNULL(SUM(E), 0) +
        ISNULL(SUM(F), 0)
        + ISNULL(SUM(G), 0) +
        ISNULL(SUM(H), 0) + ISNULL(SUM(I), 0) + ISNULL(SUM(J), 0) + ISNULL(SUM(K),
        0) + ISNULL(SUM(L), 0)
        + ISNULL(SUM(M), 0) +
        ISNULL(SUM(N), 0) + ISNULL(SUM(O), 0) + ISNULL(SUM(P), 0)) AS Expr16
        FROM ' + @tblT + '
        WHERE (' + @tblT + '.Sku = R1.SKU)
        AND (MONTH(ReturnDate) = ' + @Month + ') AND (YEAR(ReturnDate) = ' + @Year
        + '))) AS Total
        FROM ' + @tblT + ' R1 LEFT OUTER JOIN
        tbl_Products ON R1.SKU =
        substring(tbl_Products.ProductSKU,1,5)
        WHERE (MONTH(R1.ReturnDate) = ' + @Month + ')
        GROUP BY R1.SKU, tbl_Products.ProductName'

        Execute (@Q + @Q2 + @Q3 + @Q4 + @Q5)
      Your message has been successfully submitted and would be delivered to recipients shortly.