Help with Script

  • Hi All, I need help figuring out what I'm doing wrong in this code.

    I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.

    For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.

    My question is, how to I introduce a new select or join from another table without duplicating the original data?

    Thanks in advance!

    select

    first_stop.actual_departure ‘Start'

    , last_stop.actual_departure 'End'

    , last_stop.city_name 'End city'

    , last_stop.state 'End state'

    , last_stop.zip_code 'End zip'

    , first_stop.city_name ‘Start city'

    , first_stop.state 'Start state'

    , first_stop.zip_code 'Start zip'

    , datediff (day, last_stop.actual_departure, first_stop.actual_departure) 'Days in Transit'

    , orders.id 'Order number'

    , case

    when (sales.dis = 'a' or sales.dis = 'b' or sales.dis = 'c')

    then sales.amount

    else 0

    end 'Discounts'

    , orders.total_charge 'Total charges'

    from

    orders left outer join stop last_stop on last_stop_id = last_stop.id

    left outer join stop first_stop on first_stop_id = first_stop.id

    left outer join customer on customer_id = customer.id

    left outer join sales on orders.id = sales.reference

    where

    orders.status = 'D'

    and (first_stop.actual_departure >= {ts '2015-01-01 00:00:00'} and first_stop.actual_departure <= {ts '2015-01-31 23:59:59'})

  • Is sales.Dis ever NULL?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • What about this?

    select

    first_stop.actual_departure 'Start'

    , last_stop.actual_departure 'End'

    , last_stop.city_name 'End city'

    , last_stop.state 'End state'

    , last_stop.zip_code 'End zip'

    , first_stop.city_name 'Start city'

    , first_stop.state 'Start state'

    , first_stop.zip_code 'Start zip'

    , datediff (day, last_stop.actual_departure, first_stop.actual_departure) 'Days in Transit'

    , orders.id 'Order number'

    , ISNULL( s.Discounts, 0) 'Discounts'

    , orders.total_charge 'Total charges'

    from

    orders left outer join stop last_stop on last_stop_id = last_stop.id

    left outer join stop first_stop on first_stop_id = first_stop.id

    left outer join customer on customer_id = customer.id

    OUTER APPLY( SELECT SUM(amount) AS Discounts FROM sales

    WHERE orders.id = sales.reference

    AND sales.dis IN( 'a', 'b', 'c')

    GROUP BY sales.reference) s

    where

    orders.status = 'D'

    and (first_stop.actual_departure >= {ts '2015-01-01 00:00:00'} and first_stop.actual_departure <= {ts '2015-01-31 23:59:59'})

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Sigerson (2/19/2015)


    Is sales.Dis ever NULL?

    Never Null.

  • Luis Cazares (2/19/2015)


    What about this?

    select

    first_stop.actual_departure 'Start'

    , last_stop.actual_departure 'End'

    , last_stop.city_name 'End city'

    , last_stop.state 'End state'

    , last_stop.zip_code 'End zip'

    , first_stop.city_name 'Start city'

    , first_stop.state 'Start state'

    , first_stop.zip_code 'Start zip'

    , datediff (day, last_stop.actual_departure, first_stop.actual_departure) 'Days in Transit'

    , orders.id 'Order number'

    , ISNULL( s.Discounts, 0) 'Discounts'

    , orders.total_charge 'Total charges'

    from

    orders left outer join stop last_stop on last_stop_id = last_stop.id

    left outer join stop first_stop on first_stop_id = first_stop.id

    left outer join customer on customer_id = customer.id

    OUTER APPLY( SELECT SUM(amount) AS Discounts FROM sales

    WHERE orders.id = sales.reference

    AND sales.dis IN( 'a', 'b', 'c')

    GROUP BY sales.reference) s

    where

    orders.status = 'D'

    and (first_stop.actual_departure >= {ts '2015-01-01 00:00:00'} and first_stop.actual_departure <= {ts '2015-01-31 23:59:59'})

    it's coming back with sales.amount could not be bound.... hmmmmm

  • Do you have anything else in the code?

    Did you run the exact same code I posted?

    Note that I changed the JOIN to an APPLY and the column expression for 'Discounts'.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (2/19/2015)


    Do you have anything else in the code?

    Did you run the exact same code I posted?

    Note that I changed the JOIN to an APPLY and the column expression for 'Discounts'.

    Nothing else in the code.

    A few changes assuming you didn't complete the spellings completely:

    On the ISNULL line, I put sales.discount instead of s.discount

    On the Outer apply statement, I put sales.discount instead of sales.dis

    And what does the 's' mean at the end of the outer apply statement? Should that be sales or just s?

  • sqlnoob2015 (2/19/2015)


    Luis Cazares (2/19/2015)


    Do you have anything else in the code?

    Did you run the exact same code I posted?

    Note that I changed the JOIN to an APPLY and the column expression for 'Discounts'.

    Nothing else in the code.

    A few changes assuming you didn't complete the spellings completely:

    On the ISNULL line, I put sales.discount instead of s.discount

    On the Outer apply statement, I put sales.discount instead of sales.dis

    And what does the 's' mean at the end of the outer apply statement? Should that be sales or just s?

    oops changed it back to dis. still error though

  • s is an alias for the resultset from the apply. It can be anything but I prefer it to be something that could be significant. On the ISNULL column you need to use that alias and the name of the column defined in the APPLY subquery

    You had sales.dis as the column used in your CASE.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (2/19/2015)


    s is an alias for the resultset from the apply. It can be anything but I prefer it to be something that could be significant. On the ISNULL column you need to use that alias and the name of the column defined in the APPLY subquery

    You had sales.dis as the column used in your CASE.

    OK, i finally got it to run. Thanks for your response and explanation. That makes since.

    However, now I do not have any totals in the Discounts column. It's all zeros. I know there are values because when i run a select statement with just the discounts, I get values.

  • discounts is basically coming back null.

  • Does this return any rows?

    select *

    from orders o

    JOIN stop first_stop on o.first_stop_id = first_stop.id

    JOIN sales s ON o.id = s.reference

    WHERE s.dis IN( 'a', 'b', 'c')

    AND o.status = 'D'

    AND first_stop.actual_departure >= {ts '2015-01-01 00:00:00'}

    AND first_stop.actual_departure <= {ts '2015-01-31 23:59:59'}

    If it does, the complete query should return discounts. If it doesn't, you don't have data available.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (2/20/2015)


    Does this return any rows?

    select *

    from orders o

    JOIN stop first_stop on o.first_stop_id = first_stop.id

    JOIN sales s ON o.id = s.reference

    WHERE s.dis IN( 'a', 'b', 'c')

    AND o.status = 'D'

    AND first_stop.actual_departure >= {ts '2015-01-01 00:00:00'}

    AND first_stop.actual_departure <= {ts '2015-01-31 23:59:59'}

    If it does, the complete query should return discounts. If it doesn't, you don't have data available.

    I got it figured out. Thanks a lot for your help on this!

Viewing 13 posts - 1 through 12 (of 12 total)

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