Calculating the total for the month

  • 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

  • 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

  • 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 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/

  • 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

  • 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

  • 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 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/

  • 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?

  • 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/

  • 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

  • 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/

  • Thanks for all your help Sean - really appreciated....

    BO

  • 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/

  • 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