quote:

---

Originally posted by Sonu619

In Minv and MaxV = 0-1 is (Months). Lets say Tomorrow users change the requirement to (0-6 Months) At least my query runs fine.

Here is the e.g. i want to see all Created Date One Month back from getdate(). Like first solution i am checking all created date one year (MinV-MaxV = 0-12) back from GETDATE().

Make Sense?

---

Select
D2.City,
D2.Code,
D1.CreateDate
FROM Date2 D2
Inner Join DATE1 D1 On ','+replace(replace(D1.Code,'.',''),' ','')+',' like '%,'+D2.Code+',%'
Where D1.CreateDate BETWEEN 
	DATEADD(mm,COALESCE(-CAST(maxv AS INT),12),dateadd(mm,datediff(mm,0,GETDATE()),0)) 
	AND DATEADD(mm,COALESCE(-CAST(minv AS INT),0),dateadd(mm,datediff(mm,0,GETDATE()),0))	

WHERE 
	D1.CreateDate >= DATEADD(mm,COALESCE(-CAST(maxv AS INT),12),dateadd(mm,datediff(mm,0,GETDATE()),0)) 
	AND D1.CreateDate < DATEADD(mm,COALESCE(-CAST(minv AS INT),0),dateadd(mm,datediff(mm,0,GETDATE()),0))	

quote:

---

Originally posted by Sonu619

Thanks I will keep in mind next time. James is there any way i can keep same query. Does Not Matter when user change MinV and MaxV values. However thats for sure MinV is always less then Maxv.Mean Minv value is always Upper Bound and Maxv value is always Lower Bound.

---

quote:

---

Originally posted by James K

You should not have to make any change to the code as long as the 1 and 36 (or whatever are the values you want to use) are listed in the table. It is assuming that there is only one row for each city in the City table.

Now that I am looking at the query a second time, you should change the WHERE clause slightly as follows:

WHERE 
	D1.CreateDate >= DATEADD(mm,COALESCE(-CAST(maxv AS INT),12),dateadd(mm,datediff(mm,0,GETDATE()),0)) 
	AND D1.CreateDate < DATEADD(mm,COALESCE(-CAST(minv AS INT),0),dateadd(mm,datediff(mm,0,GETDATE()),0))	

This is to avoid including the first day of the current month in the results.

By the way, if in your original question you had posted the create table and insert statements along with the desired results like you did in your later post, it would have been much easier for someone to post the correct answer - you would have gotten accurate and faster responses.

---