Home Forums SQL Server 2012 SQL 2012 - General How to use date trunc or date function to select a date range for a month on month view? RE: How to use date trunc or date function to select a date range for a month on month view?

  • July 29, 2015 at 11:18 am

    #1815816

    J Livingston SQL (7/29/2015)

    you havent given any us any scripts to set up sample data plus the DATE_TRUNC is not MS SQL

    but heres an idea for MS SQL

    SELECT TOP 10000

    CustomerID = CAST(Abs(Checksum(Newid()) % 90000 + 1) AS INT),

    TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2012', '2016'), '2012')

    INTO #TransData

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    CROSS JOIN sys.all_columns ac3

    SELECT YEAR(TransDate) AS CYear, MONTH(TransDate) as CMonth, COUNT(CustomerID) AS CustCnt

    FROM #TransData

    WHERE DAY(Transdate) < = 23 ---- alter accordingly

    GROUP BY YEAR(TransDate), MONTH(TransDate)

    ORDER BY YEAR(TransDate), MONTH(TransDate)

    Or, based on the snippet provided:

    SELECT

    month(campaign_date) CalendarMonth

    ,COUNT(numberofresponders)

    ,othermetric

    ,othermetric

    FROM

    table

    WHERE

    campaign_date >= '2015-01-01' and

    day(campaign_date) between 1 and 23

    group by

    month(campaign_date)

    order by

    month(campaign_date);

    By the way, neither of these may be the most performant or scalable solutions. Without more details as to what you are trying to accomplish, DDL for the table(s) involved, sample data for the tables, and expected results all you are going to get are shots in the dark.