SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating the total for the month


Calculating the total for the month

Author
Message
ByronOne
ByronOne
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 461
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
Andy Hyslop
Andy Hyslop
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3517 Visits: 3058
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100822 Visits: 18175
ByronOne (9/14/2012)
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


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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ByronOne
ByronOne
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 461
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
yuvipoy
yuvipoy
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3949 Visits: 1443
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100822 Visits: 18175
ByronOne (9/17/2012)
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



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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ByronOne
ByronOne
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 461
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?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100822 Visits: 18175
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ByronOne
ByronOne
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 461
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100822 Visits: 18175
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search