Post reply

Assistance with fixing the output of a query to be used for a datafeed.

  • February 26, 2018 at 7:03 am

    #336341

    I have written the query below which returns the data in format shown in the screenshot shown below. For the data feed that this will be used for the supplier has asked us to return one line per order so therefore the part I am stuck on is the best way to return a list of the products the customer purchased merged into 1 column with each product separated by a semi-colon. Any help that can be provided would be greatly appreciated.


    DECLARE @startdate datetime = '01 Oct 2017',
            @enddate datetime = '02 Oct 2017';

    WITH parents as (select productcode,replace(name,'~','') as Product_Name from product
    where name like '~%')
    --,

    --Total_Amount as(
                    select distinct    co.BTEmail as Email,
                            co.BTFirstName as FirstName,
                            co.BTLastName as LastName,
                            co.BTCountry as Country,
                            cct.transactiondate as 'Date Of Transaction',
                            '' as 'Travel Date',
                            vvsl.ShopName as Shop_name,
                            co.CurrencyCode,
                            --co.ordernumber as Order_Number,
                            --co.OrderDate as Order_Date,
                            --pr.ProductCode,
                            --pr.sku,
                            --par.Product_Name as Product
                            --ol.QtyOrdered as Quantity
                             par.Product_Name
                    from customerorder co
                    inner join WebSite wb on co.WebSiteId=wb.WebSiteId
                    inner join vw_VB_OrderLines ol on co.CustomerOrderId = ol.CustomerOrderId
                    inner join CreditCardTransaction cct with (nolock) on co.CustomerOrderId=cct.CustomerOrderId
                    inner join VB_VW_Shoplist vvsl on co.WebSiteId=vvsl.WebSiteId
                    inner join Product pr on ol.ProductId=pr.ProductId
                    inner join parents par on pr.ProductCode=par.ProductCode
                    inner join Currencies curr with(nolock) on co.currencycode= curr.currencycode and (month(curr.updated) = month(co.orderdate) and year(curr.updated) = year(co.orderdate))
    Where (co.Status='Submitted' or co.Status='Complete' or co.Status='Processing' or co.Status='Review')
    --and co.termscode =''
    and cct.TransactionType = 'SALE'
    and cct.Result <> 'NOTSET' and cct.Result <> 'WAITIDEAL' and cct.Result <> 'WAITCUP'
    and cct.RespMsg <> 'NOTSET'
    and cct.RespMsg <> 'REFUSED'
    and cct.RespMsg <> 'WAIT3D'
    and cct.RespMsg <> 'ERROR'
    and cct.Status <>'N/A'
    and cct.TransactionDate >= @startdate
    and cct.TransactionDate < @enddate
    order by BTEmail

    Current Output:

    Desired Output: ( I have not merged the 2nd and 3rd customer as they seem to have been created with separate accounts) 

  • February 26, 2018 at 7:35 am

    #1981089

    This may help you.

    John

  • February 26, 2018 at 7:48 am

    #1981094

    Thanks for the suggestion. This is one of the options I am looking at. I am just trying to find a good way to have all the other columns plus the one that needs to concatenated.

  • February 28, 2018 at 3:01 pm

    #1981563

    Try this on for size:DECLARE @startdate AS datetime = '01 Oct 2017',
            @enddate AS datetime = '02 Oct 2017';

    SELECT DISTINCT
        co.BTEmail AS Email,
        co.BTFirstName AS FirstName,
        co.BTLastName AS LastName,
        co.BTCountry AS Country,
        cct.transactiondate AS 'Date Of Transaction',
        '' AS [Travel Date],
        vvsl.ShopName AS Shop_name,
        co.CurrencyCode,
        STUFF(
            (
            SELECT ';' + REPLACE(name, '~', '')
            FROM product AS P
            WHERE P.productcode = pr.ProductCode
            ), 1, 1, '') AS Product_Name
    FROM customerorder AS co
        INNER JOIN WebSite AS wb
            ON co.WebSiteId = wb.WebSiteId
        INNER JOIN vw_VB_OrderLines AS ol
            ON co.CustomerOrderId = ol.CustomerOrderId
        INNER JOIN CreditCardTransaction AS cct WITH(NOLOCK)
            ON co.CustomerOrderId = cct.CustomerOrderId
        INNER JOIN VB_VW_Shoplist AS vvsl
            ON co.WebSiteId = vvsl.WebSiteId
        INNER JOIN Product AS pr
            ON ol.ProductId = pr.ProductId
        INNER JOIN Currencies AS curr WITH(NOLOCK)
            ON co.currencycode = curr.currencycode
            AND MONTH(curr.updated) = MONTH(co.orderdate)
            AND YEAR(curr.updated) = YEAR(co.orderdate)
    WHERE co.[Status] IN ('Submitted', 'Complete', 'Processing', 'Review')
        AND cct.TransactionType = 'SALE'
        AND cct.Result NOT IN ('NOTSET', 'WAITIDEAL', 'WAITCUP')
        AND cct.RespMsg NOT IN ('NOTSET', 'REFUSED', 'WAIT3D', 'ERROR')
        --AND cct.[Status] <>'N/A'
        AND cct.TransactionDate >= @startdate
        AND cct.TransactionDate < @enddate
    ORDER BY BTEmail;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 1, 2018 at 6:48 am

    #1981622

    Hi Steve,

    I have just had a go at testing the above script you kindly provided. When I ran it I got the error below:

    Msg 512, Level 16, State 1, Line 4
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Regards

    Russell

  • March 1, 2018 at 8:50 am

    #1981642

    mcdba@russweb.co.uk - Thursday, March 1, 2018 6:48 AM

    Hi Steve,

    I have just had a go at testing the above script you kindly provided. When I ran it I got the error below:

    Msg 512, Level 16, State 1, Line 4
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Regards

    Russell

    Oops!   I forgot a critical element - the FOR XML PATH piece for the subquery in the SELECT.   This happens more easily when I can't actually execute the query involved.
    Here's the corrected query:DECLARE @startdate AS datetime = '01 Oct 2017',
       @enddate AS datetime = '02 Oct 2017';

    SELECT DISTINCT
      co.BTEmail AS Email,
      co.BTFirstName AS FirstName,
      co.BTLastName AS LastName,
      co.BTCountry AS Country,
      cct.transactiondate AS 'Date Of Transaction',
      '' AS [Travel Date],
      vvsl.ShopName AS Shop_name,
      co.CurrencyCode,
      STUFF(
       (
       SELECT ';' + REPLACE(name, '~', '')
       FROM product AS P
       WHERE P.productcode = pr.ProductCode
            FOR XML PATH('')
       ), 1, 1, '') AS Product_Name
    FROM customerorder AS co
      INNER JOIN WebSite AS wb
       ON co.WebSiteId = wb.WebSiteId
      INNER JOIN vw_VB_OrderLines AS ol
       ON co.CustomerOrderId = ol.CustomerOrderId
      INNER JOIN CreditCardTransaction AS cct WITH(NOLOCK)
       ON co.CustomerOrderId = cct.CustomerOrderId
      INNER JOIN VB_VW_Shoplist AS vvsl
       ON co.WebSiteId = vvsl.WebSiteId
      INNER JOIN Product AS pr
       ON ol.ProductId = pr.ProductId
      INNER JOIN Currencies AS curr WITH(NOLOCK)
       ON co.currencycode = curr.currencycode
       AND MONTH(curr.updated) = MONTH(co.orderdate)
       AND YEAR(curr.updated) = YEAR(co.orderdate)
    WHERE co.[Status] IN ('Submitted', 'Complete', 'Processing', 'Review')
      AND cct.TransactionType = 'SALE'
      AND cct.Result NOT IN ('NOTSET', 'WAITIDEAL', 'WAITCUP')
      AND cct.RespMsg NOT IN ('NOTSET', 'REFUSED', 'WAIT3D', 'ERROR')
      --AND cct.[Status] <>'N/A'
      AND cct.TransactionDate >= @startdate
      AND cct.TransactionDate < @enddate
    ORDER BY BTEmail;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 1, 2018 at 9:07 am

    #1981650

    Hi Steve,

    Thanks for quick response it is working now. I imagine it can be quite awkward to help when you cannot actually run the query.I just need to merge in the view to give the parent product name and I will be good to go.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply