Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating the total for the month Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, September 14, 2012 3:03 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, October 24, 2016 3:28 AM Points: 169, Visits: 454
 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
Post #1359124
 Posted Friday, September 14, 2012 3:23 AM
 Say Hey Kid Group: General Forum Members Last Login: Yesterday @ 4:59 AM Points: 698, Visits: 3,019
 HiIf 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
 SSCoach Group: General Forum Members Last Login: Yesterday @ 8:08 PM Points: 16,145, Visits: 16,850
 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.BOI 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 Group: General Forum Members Last Login: Monday, October 24, 2016 3:28 AM Points: 169, Visits: 454
 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 BEGINDROP TABLE #MyTableENDCREATE 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 Group: General Forum Members Last Login: Thursday, November 24, 2016 10:31 PM Points: 368, Visits: 1,267
 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
 SSCoach Group: General Forum Members Last Login: Yesterday @ 8:08 PM Points: 16,145, Visits: 16,850
 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 BEGINDROP TABLE #MyTableENDCREATE 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 Group: General Forum Members Last Login: Monday, October 24, 2016 3:28 AM Points: 169, Visits: 454
 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
 SSCoach Group: General Forum Members Last Login: Yesterday @ 8:08 PM Points: 16,145, Visits: 16,850
 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 Group: General Forum Members Last Login: Monday, October 24, 2016 3:28 AM Points: 169, Visits: 454
 SeanI 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 BEGINDROP TABLE #MyTableENDCREATE 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 OFFselect * from #MyTabledrop table #MyTable
Post #1360626
 Posted Tuesday, September 18, 2012 8:20 AM
 SSCoach Group: General Forum Members Last Login: Yesterday @ 8:08 PM Points: 16,145, Visits: 16,850
 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 ActualItemsSoldfrom #MyTablegroup 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

 Permissions