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

trying count property_ids on close_dt interval

select distinct pd.state statename, zw.countyname [county name] ,sum(case when pc.close_dt >= datename(mm,dateadd(mm,-3,getdate()))then 1  else 0 end) [0-3 months] ,sum(case when pc.close_dt >= datename(mm,dateadd(mm,-6,getdate()))                  , pc.close_dt < datename(mm,dateadd(mm,-3,getdate()))  1 else 0 end) [3-6 months]    resnet_mysql.dbo.property_details pd (nolock) join resnet.dbo.zipcodesview zw (nolock)     on cast(left(pd.zip, 5) varchar) = cast(zw.zipcodeid varchar) join resnet_mysql.dbo.property_closings pc (nolock)     on pd.property_id = pc.property_id group pd.state, zw.countyname, pc.close_dt 

how can 3 month interval previous 3 month interval value? 3-6 months?

i want this.

but error.

i thinking want this:

select pd.state statename, zw.countyname [county name],        sum(case when pc.close_dt >= dateadd(month, -3, getdate()) 1                  else 0            end) [0-3 months]        sum(case when pc.close_dt >= dateadd(month, -6, getdate()) ,                      pc.close_dt < dateadd(month, -3, getdate())                 1                 else 0            end) [3-6 months]   resnet_mysql.dbo.property_details pd join      resnet.dbo.zipcodesview zw       on left(pd.zip, 5) = cast(zw.zipcodeid varchar(5)) join      resnet_mysql.dbo.property_closings pc       on pd.property_id = pc.property_id group pd.state, zw.countyname; 

your original code has many errors, hard list them:

• datename() returns string. why want compare date?
• you aggregating based on date ranges. don't want include date in group by.
• left() returns string; there no need convert it.
• you don't want compare string version of zip code numeric id. if do, conversion should specify length.
• with (nolock) not recommended unless know doing.