Post reply

MySQL To TSQL

  • April 17, 2021 at 8:01 am

    #3872311

    Please assist in transforming this mysql code to tsq please?

     

    SELECT date(o.created_datetime) as Date, od.product_uid as 'Product UID',

    p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as 'Unit of Measure',

    ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as 'Units Sold',

    ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as 'Sales Volume',

    ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as 'Sales Value'

    FROM order_detail od

    left join order o on od.order_uid=o.uid

    left join product p on od.product_uid=p.uid

    where o.status in ('D',1,2,3,4,5)

    and not od.label ='Plastic Bag'

    and date(o.created_datetime) >= '2020-04-01'

  • April 17, 2021 at 8:32 am

    #3872326

    date(o.created_datetime)

    change to

    convert(date, o.created_datetime)

    if(od.amended_quantity is not null, od.amended_quantity, od.quantity)

    change to

    case when od.amended_quantity is not null then od.amended_quantity else od.quantity end

    regarding "and date(o.created_datetime) >= '2020-04-01'" - this should be changed as using a function on the column will prevent any index with that column from being used to filter

    I also prefer to explicitly convert the string to date(time)

    so change it to "and o.created_datetime >= convert(datetime, '2020-04-01', 120)

  • April 17, 2021 at 9:45 am

    #3872346

    Please give me the completed converted TSQL code?

  • April 17, 2021 at 1:51 pm

    #3872414

    If you want people on here to do your work for you, are you willing to send your paycheck to them?

Viewing 4 posts - 1 through 3 (of 3 total)

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