question

chukliz avatar image
chukliz asked

SQL views

I have a SQL views that is pulling data using MAX year and MAX month. I want to pull only data for a specific year and month. For example 2018 and month 01. How can I achieve this.

sql 2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

if you want to change the view to only retrieve that, then you don't need to MAX() anything, you just put that year and month in the WHERE clause.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chukliz avatar image
chukliz answered

Hi Jon,

I tried that but it did'nt work. Here is the script.

SELECT t.tenancy_ref, t.prop_ref,{ fn CONCAT(t.tenancy_ref, t.prop_ref)}AS ten_join, t.tenancy_type, t.current_balance, t.total_rent, t.cot, t.household_makeup, t.cal_month,

t.cal_year, t.hb_ytd, t.service_charge, t.rent, t.rent_group, t.start_reason, t.bencap_flag, t.bedtax_flag,

CASEWHEN t .tenancy_ref = uc.tenancy_ref THEN'UC'ELSENULLENDAS uc_flag,CASEWHEN t .tenancy_ref = uc.tenancy_ref THEN uc.start_dt ELSENULL

ENDAS uc_start_dt,ISNULL(max_age.max_age, 0)AS lead_age

FROM dbo.tenancies AS t LEFTOUTERJOIN

Projects.dbo.proj_uc_checks AS uc ON uc.tenancy_ref = t.tenancy_ref AND uc.cal_year ={ fn CONCAT(RTRIM(t.cal_month),RTRIM(t.cal_year))

}LEFTOUTERJOIN

(SELECT tenancy_ref,MAX(CAST(age ASfloat))AS max_age

FROM dbo.residents

WHERE (tenant ='True')AND(cal_Month =

(SELECTMAX(cal_month)AS Expr1

FROM dbo.tenancies AS tenancies_2

WHERE (cal_year =

(SELECTMAX(cal_year)AS Expr1

FROM dbo.tenancies AS tenancies_1))))AND(cal_Year =

(SELECTMAX(cal_year)AS Expr1

FROM dbo.tenancies AS tenancies_1))

GROUPBY tenancy_ref)AS max_age ON t.tenancy_ref = max_age.tenancy_ref

WHERE (t.cal_month =

(SELECTMAX(cal_month)AS Expr1

FROM dbo.tenancies AS tenancies_2

WHERE (cal_year =

(SELECTMAX(cal_year)AS Expr1

FROM dbo.tenancies AS tenancies_1))))AND(t.cal_year =

(SELECTMAX(cal_year)AS Expr1

FROM dbo.tenancies AS tenancies_1))

GO

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

Good lord. step one - throw that out and start over. Step two - can you tell me in a couple sentences just what you're trying to retrieve?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.