web sales sql query

Unknown said:

OK - you SQL scripting gurus.. I need to run a query that pulls all sales under mos 52 and 57, showing just date and amount. I am sure this is not difficult for those that know how to do this, but I'm not there yet in my SQL knowledge. Would anyone mind assisting me with this easy query?

Here some sample code. Is this what you had in mind?

select order_dt,
    sum(tot_due_amt) "tot_due_sum"
from t_order
where mos in (52,57)
group by order_dt
order by order_dt

yes - thats great! thanks. How then do I get the total of sales by month for each yr?

Unknown said:

yes - thats great! thanks. How then do I get the total of sales by month for each yr?

That's a bit more challenging. I would use a custom calendar table, which I read about here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

... which is very handy for stuff like this. One quick way could be something like this:

select  left(convert(varchar(20), order_dt, 100),3) "month",
    sum(tot_due_amt) "tot_due_sum"
from t_order
where mos in (52,57)
group by left(convert(varchar(20), order_dt, 100),3)
order by month

Unknown said:

yes - thats great! thanks. How then do I get the total of sales by month for each yr?

That's a bit more challenging. I would use a custom calendar table, which I read about here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

... which is very handy for stuff like this. One quick way could be something like this:

select  left(convert(varchar(20), order_dt, 100),3) "month",
    sum(tot_due_amt) "tot_due_sum"
from t_order
where mos in (52,57)
group by left(convert(varchar(20), order_dt, 100),3)
order by month

I know this is kind of an old post, but just for future reference, there's actually a simpler way to get the month than the old "pluck it out of a string" trick... there's a month function. :)

Observe!

select  year(order_dt) "year", month(order_dt) "month",

    sum(tot_due_amt) "tot_due_sum"

from t_order

where mos in (52,57)

group by year(order_dt),  month(order_dt)

order by [year], [month]

Yeah, I agree the month function is a nice choice, but since it only returns a digital month, I tend to use the string method for super-quick code as above to get me to a three-char month in as few lines of code as possible. :-)

datename(mm,order_dt) will give you the name of the month...