SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Date Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 02/08/2013 :  23:12:21  Show Profile  Reply with Quote
Hi guys,

I have quick question,

Here is my sample data

TABLE1
ID,CITY,MValue,MValue
1,NY,0,12
2,Chicago,0,1

Table2
CreatedDate
2/2/2011
1/2/2010
2/2/2013

First Requirement is If Created date is One year old from getdate display the value
Here is my using

AND Table2.CreatedDate between DATEADD(MM,isnull(Table1.MValue,5),GETDATE()) and DATEADD(MM,0-isnull(Table1.MValue,0),GETDATE())
where id =1

I am not getting right data. Please guide me where i am wrong.

Second Requirement If Created date is One Month Old from Getdate().

Note:- In Table1 (MValue & MValue) is Month field (0,12)

Please let me know if need more info.This is just a part of SP. Its very hard and tuff to putt whole logic here.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/08/2013 :  23:18:07  Show Profile  Reply with Quote
first requirement can be obtained as follows

AND Table2.CreatedDate <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)


second requirement looks exactly same to me
Also how can you've duplicate columns with same name in same table
I hope its just for your illustration.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/08/2013 :  23:38:04  Show Profile  Reply with Quote
Visakh, Sorry It was Typo Mistake.
Table1 table is totally configurable, Admin can configure this table Today requirement is want to see the data last year from now. Tomorrow could be change. That is why i am using MinV and MaxV there.
Each field in the table1 has some kinda requirement. If you could please help me to right a date syntax that would be awesome.
Does this looks good to you?
AND Table2.CreatedDate between DATEADD(MM,isnull(Table1.MaxV,5),GETDATE()) and DATEADD(MM,0-isnull(Table1.MinV,0),GETDATE())
where id =1

Sorry It was typo mistake Here is right table

TABLE1
ID,CITY,MinV,MaxV
1,NY,0,12
2,Chicago,0,1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/08/2013 :  23:44:35  Show Profile  Reply with Quote
i cant understand your requirement here. I think seeing the way you're using it max and min values are kind of offset
then it should be

Table2.CreatedDate < DATEADD(mm,COALESCE(MaxValue+1,5) + DATEDIFF(mm,0,GETDATE()),0)
AND Table2.CreatedDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()) - COALESCE(MinValue,0),0)


see logic explained here

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/09/2013 :  00:35:46  Show Profile  Reply with Quote
My requirement is. If Table2.CreatedDate = Between (One year old from getdate(). But here i have to use MinV and MaxV because, if tomorrow requirement change user need to change or update Min and max value and my query runs fine.
Thanks for above sql syntax.
How i can get one month old date from getdate() with same MinV and MaxV, Mean (MinV = 0 and MaxV = 1)

Thanks for help. Really appreciate...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/09/2013 :  01:18:14  Show Profile  Reply with Quote
what does that mean one month old date with MinV MaxV? give an example.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/09/2013 :  11:05:06  Show Profile  Reply with Quote
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?
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/09/2013 :  11:50:08  Show Profile  Reply with Quote
Visakh I tried your code to provide
Table2.CreatedDate < DATEADD(mm,COALESCE(MaxValue+1,5) + DATEDIFF(mm,0,GETDATE()),0)
AND Table2.CreatedDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()) - COALESCE(MinValue,0),0)

and i am getting below data
2013-02-05 15:53:14.047
2013-02-05 16:23:56.533
2013-02-06 05:15:03.353
2013-02-06 05:15:03.447
2013-02-06 05:15:03.470
2013-02-06 05:15:03.497
2013-02-06 05:15:03.803
2013-02-06 05:15:03.880
2013-02-06 05:15:05.337
2013-02-06 05:15:05.800
2013-02-06 05:15:05.887
2013-02-06 05:15:05.980
2013-02-06 05:15:06.010
2013-02-06 05:15:06.400
2013-02-06 05:15:06.587
2013-02-06 05:15:06.840
2013-02-06 05:15:07.503
2013-02-06 05:15:07.573
2013-02-06 05:15:07.837
2013-02-06 05:15:08.060
2013-02-06 05:15:10.047
2013-02-06 05:15:10.090
2013-02-06 05:15:10.190
2013-02-06 05:15:10.237
2013-02-06 05:15:10.300
2013-02-06 05:15:10.437
2013-02-06 05:15:10.487
2013-02-06 05:15:10.557
2013-02-06 05:15:10.593
2013-02-06 05:15:10.620
2013-02-06 05:15:10.700
2013-02-06 05:15:10.747
2013-02-06 05:15:10.783
2013-02-06 05:15:10.813
2013-02-06 05:15:10.900
2013-02-06 05:15:11.040
2013-02-06 05:15:11.317
2013-02-06 05:15:11.530
2013-02-06 05:15:11.560
2013-02-06 05:15:11.590
2013-02-06 05:15:11.627
2013-02-06 05:15:11.660
2013-02-06 05:15:11.690
2013-02-06 05:15:11.793
2013-02-06 05:15:11.903
2013-02-06 05:15:11.957
2013-02-06 05:15:12.030
2013-02-06 05:15:12.090
2013-02-06 05:15:12.157
2013-02-06 05:15:12.240
2013-02-06 05:15:12.267
2013-02-06 05:15:12.313
2013-02-06 05:15:12.440
2013-02-06 05:15:12.507
2013-02-06 05:15:12.617
2013-02-06 05:15:12.677
2013-02-06 05:15:12.720
2013-02-06 05:15:12.760
2013-02-06 05:15:12.927
2013-02-06 05:15:12.947
2013-02-06 05:15:26.497
2013-02-06 05:15:26.607
2013-02-06 05:15:26.693
2013-02-06 05:15:26.803
2013-02-06 05:15:27.080
2013-02-06 05:15:27.267
2013-02-06 05:15:27.570
2013-02-06 05:15:27.600
2013-02-06 05:15:27.670
2013-02-06 05:15:27.780
2013-02-06 05:15:27.853
2013-02-06 05:15:27.883
2013-02-06 05:15:28.037
2013-02-06 05:15:28.117
2013-02-06 05:15:28.263
2013-02-06 05:15:28.380
2013-02-06 05:15:28.460
2013-02-06 05:15:28.480
2013-02-06 05:15:28.500
2013-02-06 05:15:28.520
2013-02-06 05:15:28.667
2013-02-06 05:15:28.807
2013-02-06 05:15:28.860
2013-02-06 05:15:28.997
2013-02-06 05:15:29.040
2013-02-06 05:15:29.133
2013-02-06 05:15:29.213
2013-02-06 05:15:29.317
2013-02-06 05:15:29.547
2013-02-06 05:15:29.580
2013-02-06 05:15:29.603
2013-02-06 05:15:29.620
2013-02-06 05:15:29.647
2013-02-06 05:15:29.670
2013-02-06 05:15:29.697
2013-02-06 05:15:29.780
2013-02-06 05:15:29.907
2013-02-06 05:15:29.993
2013-02-06 05:15:30.027
2013-02-06 05:15:30.057
2013-02-06 05:15:30.083
2013-02-06 05:15:30.210
2013-02-06 05:15:30.307
2013-02-06 05:15:30.340
2013-02-06 05:15:30.443
2013-02-06 05:15:30.520
2013-02-06 05:15:30.657
2013-02-06 05:15:30.697
2013-02-06 05:15:30.830
2013-02-06 05:15:30.860
2013-02-06 05:15:30.967
2013-02-06 05:15:31.080

Any Thoughts?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/09/2013 :  12:30:18  Show Profile  Reply with Quote
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?



so if its 1-3 means 1 month back to 3 month back from current date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/09/2013 :  13:20:54  Show Profile  Reply with Quote
So if it is 1-3 (MinV - MaxV) Mean 1 to 3 Month Back from current date.
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/09/2013 :  13:59:56  Show Profile  Reply with Quote
Visakh, I create Sample Test Data. Please take a look


Create Table Date1
(
ID INT ,
Code varchar(20),
CreateDate Datetime
)

Create Table Date2
(
ID INT ,
Code Varchar(20),
City Varchar(20),
MinV varchar(20),
MaxV varchar(20)
)

Insert INTO Date1 (ID,Code,CreateDate)
Values ('1','825,838','2/9/2012')
Insert INTO Date1 (ID,Code,CreateDate)
Values ('2','732','5/5/2012')
Insert INTO Date1 (ID,Code,CreateDate)
Values ('3','825,838','12/9/2012')
Insert INTO Date1 (ID,Code,CreateDate)
Values ('4','825,838','10/9/2011')




Insert Into Date2 (ID,Code,City,MinV,MaxV)
Values ('1','821','CA','0','0')

Insert Into Date2 (ID,Code,City,MinV,MaxV)
Values ('2','825','NY','0','12')

Insert Into Date2 (ID,Code,City,MinV,MaxV)
Values ('3','838','KE','0','12')

Select * from Date1
select * from Date2


--Final Query
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 < DATEADD(mm,COALESCE(D2.MaxV,5) + DATEDIFF(mm,0,GETDATE()),0)
AND D1.CreateDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()) - COALESCE(D2.MinV,0),0)

The final Result Should be
City Code CreateDate
NY 825 2012-02-09 00:00:00.000
NY 825 2012-12-09 00:00:00.000
KE 838 2012-02-09 00:00:00.000
KE 838 2012-12-09 00:00:00.000

Guide Me. Thank You...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 02/09/2013 :  15:58:04  Show Profile  Reply with Quote
Here is something that does give the sample result that you showed in your post. The logic I used is as follows:

1. Calculate the beginning of the current month.
2. Subtract MINV months from the result of step 1. This is the upper bound.
3. Subtract MAXV months from the result of step 1. This is the lower bound.
4. Pick the rows from Date1 where createdate is within the upper bound and lower bound and Code matches the code in Date2.

If this does not give you the results that you are looking for for other cases, can you post the logic to be used like I have written it above?
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))	
When you create the tables, if a column is really numeric, use a numeric data type such as INT - for example, MINV and MAXV columns are ideal candidates for INT data type rather than varchar.
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/10/2013 :  00:27:56  Show Profile  Reply with Quote
James Awesome. That's what i want.....

Thank You....
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/10/2013 :  05:04:33  Show Profile  Reply with Quote
Hey James,

Quick question. Let say my requirement change now i am looking one year back data and right now (Minv - Maxv = 0-12). Let say upper bound and lower bound values change (Minv- Maxv = 1 - 36) Do i have to change the query or its work fine as it is?

Thank You.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 02/10/2013 :  06:39:33  Show Profile  Reply with Quote
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.
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 02/10/2013 :  21:32:02  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 02/10/2013 :  21:47:31  Show Profile  Reply with Quote
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.

Didn't quite follow what you are asking. You CAN use the same query even when user changes the values of MINV and MAXV. It should work correctly. Try for yourself and change the MINV and MAXV values. If you find a case that does not seem to be giving the right answers, post that test data.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2013 :  00:27:51  Show Profile  Reply with Quote
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.


shouldnt - signs be outside COALESCE? otherwise it will add 12 months when nothing is passed
OP was telling one year old data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000