Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Calculating the total for the month Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 3:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:59 PM
Points: 143, Visits: 371
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
Post #1359124
Posted Friday, September 14, 2012 3:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:48 AM
Points: 689, Visits: 2,785
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
Post #1359130
Posted Friday, September 14, 2012 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's 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)
Post #1359354
Posted Monday, September 17, 2012 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:59 PM
Points: 143, Visits: 371
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

Post #1360046
Posted Monday, September 17, 2012 5:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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
Post #1360107
Posted Monday, September 17, 2012 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's 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)
Post #1360198
Posted Monday, September 17, 2012 1:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:59 PM
Points: 143, Visits: 371
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?

Post #1360422
Posted Monday, September 17, 2012 1:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's 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)
Post #1360428
Posted Tuesday, September 18, 2012 2:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:59 PM
Points: 143, Visits: 371
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
Post #1360626
Posted Tuesday, September 18, 2012 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 12,928, Visits: 12,347
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 Moden's 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)
Post #1360815
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse