locked
T-SQL, Select Last Three Months and Next Month RRS feed

  • Question

  • User1045460610 posted

    I want to select the last three months and next month. If I add this statement I get nothing. Without the statement I get that last three months and September and October.

    CD.trm_end_dte < MONTH(dateadd(mm, +1, GetDate()))

    SELECT DISTINCT
    CD.id_num,NM.LAST_NAME LastName,NM.FIRST_NAME FirstName,CD.major_1,CD.DEGREE_CDE,CD.yr_cde,CD.trm_cde,CD.trm_begin_dte,CD.trm_end_dte,CD.FTE_OnCreditHours,
    SCH.SUBTERM_CDE,SCH.CRS_DIV,SCH.STUD_DIV,SCH.INSTITUT_DIV_CDE,CD.PROG_DESC,SCH.CRS_CDE,SCH.CRS_TITLE,SCH.REPEAT_FLAG,SCH.REPEAT_COUNT
    FROM PKRReporting.rpt.Census_Detail CD
    INNER
    JOIN TmsePrd.dbo.STUDENT_CRS_HIST SCH ON CD.id_num = SCH.id_num
    AND CD.yr_cde = SCH.YR_CDE
    AND CD.trm_cde = SCH.TRM_CDE
    INNER JOIN NAME_MASTER NM ON
    SCH.ID_NUM = NM.ID_NUM

    WHERE

    (MONTH(CD.trm_begin_dte) BETWEEN MONTH(dateadd(mm, -2, GetDate()))
    AND
    MONTH(dateadd(mm, +1, GetDate())))
    AND
    CD.trm_end_dte < MONTH(dateadd(mm, +1, GetDate()))
    AND
    YEAR(CD.trm_begin_dte) = YEAR(GetDate())
    AND REPEAT_COUNT > 2
    ORDER BY trm_end_dte,trm_begin_dte,LAST_NAME DESC

    Monday, July 22, 2019 1:42 PM

Answers

  • User-719153870 posted

    Hi Tom4IT,

    If I add this statement I get nothing.

    This may be that there is no data in your database that can satisfy all the above conditions at the same time.

    Or this expression is not executed successfully. We can't tell the data type of "CD.trm_end_dte", but if it's same with "CD.trm_begin_dte" the expression should be modified to:

    MONTH (CD.trm_end_dte)< MONTH (dateadd (mm,+1, GetDate())

    Without the statement I get that last three months and September and October.

    Whose month are you talking about, trm_begin_dte or trm_end_dte?

    Here i made a demo you can refer to:

    create table Datetable
    (
    begindate date,
    enddate date,
    )
    insert into Datetable values('2019-04-13','2019-06-11')
    insert into Datetable values('2019-05-16','2019-07-11')
    insert into Datetable values('2019-06-13','2019-07-11')
    insert into Datetable values('2019-07-16','2019-07-21')
    insert into Datetable values('2019-08-13','2019-08-15')
    insert into Datetable values('2019-08-16','2019-09-13')
    insert into Datetable values('2019-09-13','2019-09-16')
    
    select * from Datetable
    select * from Datetable where (MONTH(begindate) between MONTH(dateadd(mm,-2,GETDATE())) and MONTH(DATEADD(mm,+1,getdate()))) and month(enddate)<MONTH(dateadd(mm, +1, GetDate()))
    
    drop table Datetable

    In this demo, you can get:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 7:44 AM

All replies

  • User77042963 posted

    The primary file group your tempdb is sitting on does not have enough working space.  Are you able to increase the drive size for your tempdb to be happy?

    You can restart your machine or your SQL instance, you may be able to run your code code as is.

    Monday, July 22, 2019 2:07 PM
  • User753101303 posted

    Hi,

    For now you are running out of disk space.

    If it shouldn't (ie tempdb requires much more space than expected) it could point to a bad join statement which generate much more rows than expected. Similarly I always question the use of DISTINCT (often it seems to remove duplicates a well written querty shouldn't return anyway...

    Start maybe with SELECT COUNT(*) FROM ... rather than SELECT DISTINCT... to see if the query returns the expected row count or much more than that...

    Monday, July 22, 2019 2:54 PM
  • User-719153870 posted

    Hi Tom4IT,

    If I add this statement I get nothing.

    This may be that there is no data in your database that can satisfy all the above conditions at the same time.

    Or this expression is not executed successfully. We can't tell the data type of "CD.trm_end_dte", but if it's same with "CD.trm_begin_dte" the expression should be modified to:

    MONTH (CD.trm_end_dte)< MONTH (dateadd (mm,+1, GetDate())

    Without the statement I get that last three months and September and October.

    Whose month are you talking about, trm_begin_dte or trm_end_dte?

    Here i made a demo you can refer to:

    create table Datetable
    (
    begindate date,
    enddate date,
    )
    insert into Datetable values('2019-04-13','2019-06-11')
    insert into Datetable values('2019-05-16','2019-07-11')
    insert into Datetable values('2019-06-13','2019-07-11')
    insert into Datetable values('2019-07-16','2019-07-21')
    insert into Datetable values('2019-08-13','2019-08-15')
    insert into Datetable values('2019-08-16','2019-09-13')
    insert into Datetable values('2019-09-13','2019-09-16')
    
    select * from Datetable
    select * from Datetable where (MONTH(begindate) between MONTH(dateadd(mm,-2,GETDATE())) and MONTH(DATEADD(mm,+1,getdate()))) and month(enddate)<MONTH(dateadd(mm, +1, GetDate()))
    
    drop table Datetable

    In this demo, you can get:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 7:44 AM