September 14, 2012 at 3:03 am
Hi Everyone
I have a table in SQL Server that contains sales performance by items sold by the week i which they were sold. The only date field in the table is the week end date. What I would like to do is calculate the monthly totals more precisely. At the moment I just add the weeks which fall within a particular month ignoring the fact that sales may fall in two different months ie week ending 2nd September currently falls within September in my sales summary when in fact 5 days of sales were actually in August. Is there any sort of formula I could use to rectify this situation?
Your help as ever would be much appreciated.
BO
September 14, 2012 at 3:23 am
Hi
If you only have a week end date and no other joins you can make to other tables to identify when the sales were made this doesn't sound possible I'm afraid..
You could take an average sales per day figure and add that to the previous months figure and deduct from the next but this is still inaccurate.
If possible (and I acknowledge this may not be possible for you) I would change the structure of your table and take it to a transactional level whereby you can identify individual sales..
Someone else may have other ideas however..
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 14, 2012 at 8:50 am
ByronOne (9/14/2012)
Hi EveryoneI have a table in SQL Server that contains sales performance by items sold by the week i which they were sold. The only date field in the table is the week end date. What I would like to do is calculate the monthly totals more precisely. At the moment I just add the weeks which fall within a particular month ignoring the fact that sales may fall in two different months ie week ending 2nd September currently falls within September in my sales summary when in fact 5 days of sales were actually in August. Is there any sort of formula I could use to rectify this situation?
Your help as ever would be much appreciated.
BO
I hope accuracy is not important. You should be able to use some date math and average the totals for the week. I might be able to help but I would need some details first. DDL (create table scripts) sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2012 at 3:24 am
Hi Sean
Thanks for getting back to me and I hope you'll be able to help.
Ok, here is the DDL you were looking for:
IF OBJECT_ID('TempDB..#MyTable') IS NOT NULL
BEGIN
DROP TABLE #MyTable
END
CREATE TABLE [dbo].[#MyTable]
(
[WeekEndDate] [date] NULL,
[site] [varchar](5) NULL,
[ItemDescription] [varchar](75) NULL,
[PlanItemsSold] [int] NULL,
[ActualItemsSold] [int] NULL
)
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #MyTable (WeekEndDate, [site], ItemDescription, PlanItemsSold, ActualItemsSold)
SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item1', '10', '7'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item2', '5', '3'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item3', '21', '7'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'South', 'Item1', '14', '3'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'West', 'Item2', '11', '7'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'West', 'Item1', '17', '9'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'North', 'Item1', '21', '32'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'East', 'Item1', '13', '19'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #MyTable OFF
September 17, 2012 at 5:04 am
Can you give little bit more accurate data what you need u have only given Aug data,can you provide Aug and Sep data and what is the final solution you are expecting. in the above example it is little bit confusion that why asking for Aug and Sep data.
Thanks
September 17, 2012 at 7:35 am
ByronOne (9/17/2012)
Hi SeanThanks for getting back to me and I hope you'll be able to help.
Ok, here is the DDL you were looking for:
IF OBJECT_ID('TempDB..#MyTable') IS NOT NULL
BEGIN
DROP TABLE #MyTable
END
CREATE TABLE [dbo].[#MyTable]
(
[WeekEndDate] [date] NULL,
[site] [varchar](5) NULL,
[ItemDescription] [varchar](75) NULL,
[PlanItemsSold] [int] NULL,
[ActualItemsSold] [int] NULL
)
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #MyTable (WeekEndDate, [site], ItemDescription, PlanItemsSold, ActualItemsSold)
SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item1', '10', '7'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item2', '5', '3'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item3', '21', '7'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'South', 'Item1', '14', '3'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'West', 'Item2', '11', '7'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'West', 'Item1', '17', '9'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'North', 'Item1', '21', '32'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'East', 'Item1', '13', '19'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #MyTable OFF
So what is the desired output based on this sample data and maybe an explanation of how that should happen?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2012 at 1:18 pm
Clearly this is only a small sample of the data but at present I create a report in SSRS that references this table via a stored procedure. The report shows sales for the past week, current month and year to date. The weekly info is fine as I can identify by the WeekEndDate and the YTD figures are essentially everything but the issue arises with the monthly figures. Currently I use a filter in SSRS to pick out any sales from the current month but of course this is a little inaccurate when half a week I in one month and half a week is in another. Now I know that any mathematical solution is not going to b 100% accurate - given the table this is not possible - but I just wondered whether there was a better solution to the current method. Also the current solution requires the filter on the report to be changed every month. Obit possible for the filter to update automatically by looking at the the latest month date in the table and using that to filter?
September 17, 2012 at 1:28 pm
ByronOne (9/17/2012)
Clearly this is only a small sample of the data but at present I create a report in SSRS that references this table via a stored procedure. The report shows sales for the past week, current month and year to date. The weekly info is fine as I can identify by the WeekEndDate and the YTD figures are essentially everything but the issue arises with the monthly figures. Currently I use a filter in SSRS to pick out any sales from the current month but of course this is a little inaccurate when half a week I in one month and half a week is in another. Now I know that any mathematical solution is not going to b 100% accurate - given the table this is not possible - but I just wondered whether there was a better solution to the current method. Also the current solution requires the filter on the report to be changed every month. Obit possible for the filter to update automatically by looking at the the latest month date in the table and using that to filter?
I am not so much worried about a big spread of data to work with but I would ask a third time for what you expect the results to be based on your sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2012 at 2:42 am
Sean
I realised this morning that the data I provided wasn't much use on account of it not containing the the actual problem so I've added a few more lines.
Ok, the data now contains dates for 3 weeks: weeke ending 5th Aug 12, 12th Aug 12 and 19th Aug 12. In the SSRS report this data would be summarised as follows:
week ending 5th August = 62 PlanItemsSold & 67 ActualItemsSold; week ending 12th August = 61 PlanItemsSold & 27 ActualItemsSold; week ending 19th August = 51 PlanItemsSold & 66 ActualItemsSold.
I'm happy with 12th and 19th totals but weeke dning 5th August is not very accurate since only 5 out of its 7 days fall within August (the other 2 falling within July). So what I'd like is to be able to somehow recalculate these figures so that only 5/7 of the total are calculated ie week ending 5th August should now = 44 PlanItemsSold & 48 ActualItemsSold.
I wrongly said yesterday that I needed the monthly to change but of course if the week ending totals change then the monthly total will correspondingly change anyway.
Hope this all makes sense.
Thanks again.
BO
IF OBJECT_ID('TempDB..#MyTable') IS NOT NULL
BEGIN
DROP TABLE #MyTable
END
CREATE TABLE [dbo].[#MyTable]
(
[WeekEndDate] [date] NULL,
[site] [varchar](10) NULL,
[ItemDescription] [varchar](75) NULL,
[PlanItemsSold] [int] NULL,
[ActualItemsSold] [int] NULL
)
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #MyTable (WeekEndDate, [site], ItemDescription, PlanItemsSold, ActualItemsSold)
SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item1', '10', '7'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item2', '5', '3'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item3', '21', '7'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'South', 'Item1', '14', '3'
UNION ALL SELECT 'Aug 12 2012 12:00AM', 'West', 'Item2', '11', '7'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'West', 'Item1', '17', '9'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'North', 'Item1', '21', '32'
UNION ALL SELECT 'Aug 19 2012 12:00AM', 'East', 'Item1', '13', '19'
UNION ALL SELECT 'Aug 5 2012 12:00AM', 'West', 'Item2', '11', '7'
UNION ALL SELECT 'Aug 5 2012 12:00AM', 'West', 'Item1', '17', '9'
UNION ALL SELECT 'Aug 5 2012 12:00AM', 'North', 'Item1', '21', '32'
UNION ALL SELECT 'Aug 5 2012 12:00AM', 'East', 'Item1', '13', '19'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #MyTable OFF
select * from #MyTable
drop table #MyTable
September 18, 2012 at 8:20 am
Something like this should get you started.
select
WeekEndDate,
floor(((DATEDIFF(dd, dateadd(mm, datediff(mm, 0, WeekEndDate), 0), WeekEndDate) + 1) / 7.0) * SUM(PlanItemsSold)) as PlanItemsSold,
floor(((DATEDIFF(dd, dateadd(mm, datediff(mm, 0, WeekEndDate), 0), WeekEndDate) + 1) / 7.0) * SUM(ActualItemsSold)) as ActualItemsSold
from #MyTable
group by WeekEndDate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2012 at 8:37 am
Thanks for all your help Sean - really appreciated....
BO
September 18, 2012 at 8:38 am
ByronOne (9/18/2012)
Thanks for all your help Sean - really appreciated....BO
You are welcome. Hope that works for you. More importantly, do you understand what it is doing?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2012 at 8:56 am
Yes, I can see what it's doing.
I just needed a starting point which is often my problem...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply