Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to get a sum of records in the last complete month

Answered How to get a sum of records in the last complete month

  • Tuesday, January 29, 2013 6:10 PM
     
     
    Sign In to Vote
    0

    Hi I am trying to get a sum of records (for example the number of jobs a car was serviced for) in the last complete month.

    data

    car service_date Service_Jobs

    honda 01/10/2012 2

    honda 03/11/2012 6

    honda 05/12/2012 1

    honda 21/12/2012 3

    honda 22/12/2012 1

    so what I am looking for is that between 01/12/2012 and 31/12/2012 the honda car was serviced 4 times.

    but I am not sure how to get the data for the last month:

    select Car, sum(jobs) from ServicesCarData

     

All Replies

  • Tuesday, January 29, 2013 6:35 PM
     
      Has Code
    Sign In to Vote
    1

    this is how you get the month end date for last onth

    Declare @enddt date;
    select @endDt= cast(cast(DATEPART(Year,GetDAte())as varchar(4))+REPLACE(str(datepart(month,getdate()),2),' ','0')+'01' as date));
    Declare @enddt date;
select @endDt= dateadd(day,-1,cast(DATEPART(Year,GetDAte())as varchar(4))+REPLACE(str(datepart(month,getdate()),2),' ','0')+'01');
select @endDt;
    select sub(jobs), car where date_column between @startdate and @enddate
    group by<< ....columns>>

    Regards
    Satheesh


     
  • Tuesday, January 29, 2013 6:39 PM
     
     Answered Has Code
    Sign In to Vote
    1
     
    DECLARE @ToDate DATETIME
DECLARE @FromDate DATETIME

SET @ToDate = CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(GETDATE())) + '/1/' + CONVERT(VARCHAR, YEAR(GETDATE())))
SET @FromDate = DATEADD(mm, -1, @ToDate)

SELECT Car, sum(jobs) 
FROM ServicesCarData
WHERE service_date >= @FromDate
AND service_date < @ToDate

     
  • Tuesday, January 29, 2013 6:42 PM
    Moderator
     
     
    Sign In to Vote
    0

    Try:

    select Car, sum(jobs) as sum_jobs
    from ServicesCarData
    where service_date >= dateadd(month, datediff(month, '19000101', (select max(service_date) from ServicesCarData)), '19000101')
    group by Car;


    The idea is to calculate the first date of the month for the maximum date in your table. Then use this value to filter the rows for the aggregation.

    AMB

    Some guidelines for posting questions...

     
  • Tuesday, January 29, 2013 6:46 PM
     
      Has Code
    Sign In to Vote
    0

    If you are using SQL Server 2012, Microsoft has added new date time functions to get LastDayof the month, firstday of the month, you can use those functions and write like below

    select Car, sum(jobs) from ServicesCarData
where service_date between Firstdayofmonth(service_date) and EOMONTH(service_date)

    Thanks & Regards Prasad DVR


    • Edited by DVR Prasad Tuesday, January 29, 2013 6:55 PM
    •  
     
  • Tuesday, January 29, 2013 6:49 PM
    Moderator
     
     
    Sign In to Vote
    0

    What is your SQL Server version? In SQL Server 2012 we have now nice function EOMONTH.

    See also last comment in this blog post

    http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/a-quick-look-at-the-1

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Tuesday, January 29, 2013 6:52 PM
    Moderator
     
     
    Sign In to Vote
    0
    No, there is only EOMONTH. I just spent some time trying to find BOMONTH or STARTOMONTH function but apparently only EOMONTH function exists + DateFromParts.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Tuesday, January 29, 2013 7:02 PM
     
      Has Code
    Sign In to Vote
    0

    first and last day Without cast/connvert

    select 
        GETDATE()
        , DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as StartDate
        , DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) as EndDate
    GO

    -- on 2012
    select 
        GETDATE()
        , DATEADD(day,1,EOMONTH(GETDATE(),-1)) as StartDate
        , EOMONTH(GETDATE(),0) as EndDate
    GO
    -- so the query can be like:
    select sum(jobs), car where date_column between DATEADD(day,1,EOMONTH(GETDATE(),-1)) and EOMONTH(GETDATE(),0)
    group by<< ....columns>>

    signature



    • Edited by pituach Tuesday, January 29, 2013 7:16 PM
    •  
     
  • Wednesday, January 30, 2013 12:35 PM
     
     
    Sign In to Vote
    0

    Please learn the ISO-8601 date format; it is the only one allowed in ANSI Standard SQL. Please learn to post DDL.

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE <pattern>),
     something_report_start_date DATE NOT NULL,
     something_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL