September 2, 2015 at 9:14 am
I have a table with three columns: Customer, Date, and Sales. I need to get each customer sales by month for each year. The problem is that the Date column is an integer data-type field, which starts from 1 (July 1, 2004) to today's date 4080 (Sept 2, 2015). I thought about converting the Date field into a date format and then group that by month to get each customer sales by month, but I'm having trouble making it work using the Month and DateAdd function. Could someone help me with this?
September 2, 2015 at 9:23 am
Calculate the actual date something like this
SELECT DATEADD(day,[Date],'20040701') AS ActualDate
FROM MyTable
John
September 2, 2015 at 9:23 am
Post some code, DDL + the query you've tried.
I would start by just querying the date field and getting the results you need there, converted to the month and year. Once you have something like
2015 8
2015 7
2015 6
....
Then you can worry about summing sales by customer and grouping by the customer and your date fields.
September 2, 2015 at 11:23 am
I would do something like this:
with basedata as (
select
Customer,
dateadd(day,[Date],'20040630') SalesDate,
Sales
from
dbo.YourTable
)
select
Customer,
dateadd(month,datediff(month,0,SalesDate),0) CalendarMonth,
sum(Sales) MonthlySales
from
basedata
group by
Customer,
dateadd(month,datediff(month,0,SalesDate),0)
order by
Customer,
dateadd(month,datediff(month,0,SalesDate),0);
September 2, 2015 at 1:23 pm
Thanks for the replies. Looking at the suggestions given I end up with this query:
SELECT CUSTOMER, MONTH(DATEADD(DAY,DATE,'2004/07/01'))AS MONTH, YEAR(DATEADD(DAY, DATE,'2004/07/01'))AS YEAR, SUM (SALESAMOUNT) AS SALES
FROM MY_TABLE
GROUP BY MONTH(DATEADD(day,CDC,'2004/07/01')), YEAR(DATEADD(DAY, CDC, '2004/07/01')), CUSTOMER
ORDER BY YEAR(DATEADD(DAY, CDC, '2004/07/01')), MONTH(DATEADD(day,CDC, '2004/07/01')), CUSTOMER
The output shows customer sales grouped by month and then years. I verified the results by picking at random one customer's month of sales and running daily sales for 31 days for the same month and then adding the results. They matched! Life is good!
Thanks
September 2, 2015 at 1:34 pm
SQLServerRookie (9/2/2015)
Thanks for the replies. Looking at the suggestions given I end up with this query:SELECT CUSTOMER, MONTH(DATEADD(DAY,DATE,'2004/07/01'))AS MONTH, YEAR(DATEADD(DAY, DATE,'2004/07/01'))AS YEAR, SUM (SALESAMOUNT) AS SALES
FROM MY_TABLE
GROUP BY MONTH(DATEADD(day,CDC,'2004/07/01')), YEAR(DATEADD(DAY, CDC, '2004/07/01')), CUSTOMER
ORDER BY YEAR(DATEADD(DAY, CDC, '2004/07/01')), MONTH(DATEADD(day,CDC, '2004/07/01')), CUSTOMER
The output shows customer sales grouped by month and then years. I verified the results by picking at random one customer's month of sales and running daily sales for 31 days for the same month and then adding the results. They matched! Life is good!
Thanks
Only problem, if 1 represents 2004-07-01, adding 1 to 2004-07-01 will result in 2004-07-02. This will be an issue when you get to the end of each month. You should use 2004-06-30 as the base, or subtract 1 from [Date].
September 2, 2015 at 2:05 pm
You are right, it should be 2004/06/30. I made the changes on the server when I was testing the query and I forgot to change it in the text document where I have saved a copy. I apologize for the oversight.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply