T-SQL Get 3 Month Interval : Trying to count Property_IDs over a close_dt Interval

Question

Trying to count Property_IDs over a close_dt Interval

select distinct pd.state AS StateName, zw.CountyName AS [County Name]
,sum(case when pc.close_dt >= dateName(MM,dateadd(MM,-3,GetDate()))then 1 
else 0 end) AS [0-3 Months]
,sum(case when pc.close_dt >= dateName(MM,dateadd(MM,-6,GetDate())) 
                and pc.close_dt < dateName(MM,dateadd(MM,-3,GetDate())) then 
1 else 0 end) AS [3-6 Months]  
 from resnet_mysql.dbo.property_details pd (Nolock)
join resnet.dbo.ZipCodesView zw (nolock)
    on CAST(LEFT(pd.zip, 5) AS varchar) = CAST(zw.ZipCodeID AS varchar)
join resnet_mysql.dbo.property_closings pc (nolock)
    on pd.property_id = pc.property_id
group by pd.state, zw.countyName, pc.close_dt

How can I get the 3 month interval from the previous 3 month interval value? So it will be 3-6 months?

I want it to look like this.

But I get this error.

Bad Habits to Kick : Using shorthand with date/time operations - Aaron Bertrand — SqlZim
Why would you be trying to compare a date to the name of a month? — Gordon Linoff
oh dang. I'm trying to convert MYSQL to T_SQL. THe MySQL code looks like. — IeeTeY
SUM(pc.close_dt >= CURDATE() - INTERVAL 6 MONTH AND pc.close_dt < CURDATE() - INTERVAL 3 MONTH) AS 3-6 Months, — IeeTeY

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-04-07T15:50:10

I am thinking you want something like this:

select pd.state AS StateName, zw.CountyName AS [County Name],
       sum(case when pc.close_dt >= dateadd(month, -3, GetDate()) then 1 
                else 0
           end) AS [0-3 Months]
       sum(case when pc.close_dt >= dateadd(month, -6, GetDate()) and
                     pc.close_dt < dateadd(month, -3, GetDate())
                then 1
                else 0
           end) AS [3-6 Months]  
from resnet_mysql.dbo.property_details pd join
     resnet.dbo.ZipCodesView zw 
     on LEFT(pd.zip, 5) = CAST(zw.ZipCodeID as VARCHAR(5)) join
     resnet_mysql.dbo.property_closings pc 
     on pd.property_id = pc.property_id
group by pd.state, zw.countyName;

Your original code has so many errors, it is hard to list them:

DATENAME() returns a string. Why would you want to compare that to a date?
You are aggregating based on date ranges. You don't want to include the date in the GROUP BY.
LEFT() already returns a string; there is no need to convert it.
You probably don't want to compare a string version of zip code to a numeric id. But if you do, the conversion should specify the length.
WITH (NOLOCK) is not recommended unless you actually know what you are doing.

T-SQL Get 3 Month Interval : Trying to count Property_IDs over a close_dt Interval

1 Answers