T-sql related help needed.

Question

T-sql related help needed.

Kritika arora 1

I have a UDF where I am generating current date like this month but I want 36 month old data but as soon as I enter my 37 month it should delete the first month data.My UDF loads data in one of the fact table where I need to show only previous month data.How to achieve this.
I did get 36 month data but the second condition is still a question mark to me.

Tom Phillips 17,786 Reputation points

2022-09-09T19:05:19.647+00:00

Without your UDF code and sample data and expected output, it is not possible to answer your question.
LiHongMSFT-4306 31,621 Reputation points

2022-09-20T07:27:15.237+00:00

Hi @Kritika arora
Do you have further question on this issue, could we offer more support?
If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Best regards,
LiHong

2 answers

Your answer

Tom Phillips 17,786 Reputation points

2022-09-09T19:05:19.647+00:00

Without your UDF code and sample data and expected output, it is not possible to answer your question.
LiHongMSFT-4306 31,621 Reputation points

2022-09-20T07:27:15.237+00:00

Hi @Kritika arora
Do you have further question on this issue, could we offer more support?
If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Best regards,
LiHong

Answer 1

LiHongMSFT-4306 31,621

Hi @Kritika arora

Can you help me with a query which gives previous month data and at the same time delete current month data

Not sure what you actually want. However, if it is a Table_Valued UDF, why not add a where filter to remove datas of current month.

select <column_list>  
from fn_UDF_Name()  
where date_column < dateadd(month,datediff(month,0,getdate()),0)

Best regards,
LiHong

0 comments

Answer 2

Kritika arora 1

Suppose
DATEADD(month,DATEDIFF(mm,0,DATEADD(mm,-36,GETDATE()))

This gives me 36 month data in my UDF but need to get below requirement in my fact table
Can you help me with a query which gives previous month data and at the same time delete current month data

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-09-10T08:08:43.443+00:00

Since a UDF can only read data, and not modify data, it is not clear to me what you mean with deleting a month.

We would need to see your current code. We would also need to see some test cases with expected results to understand what you are looking for.

Share via

T-sql related help needed.

2 answers

Your answer