Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-08 : 23:12:21
|
Hi guys, I have quick question,Here is my sample dataTABLE1ID,CITY,MValue,MValue1,NY,0,122,Chicago,0,1Table2CreatedDate2/2/20111/2/20102/2/2013First Requirement is If Created date is One year old from getdate display the valueHere 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 =1I 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
52326 Posts |
Posted - 2013-02-08 : 23:18:07
|
first requirement can be obtained as followsAND Table2.CreatedDate <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)second requirement looks exactly same to meAlso how can you've duplicate columns with same name in same tableI hope its just for your illustration.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-08 : 23:38:04
|
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 =1Sorry It was typo mistake Here is right tableTABLE1ID,CITY,MinV,MaxV1,NY,0,122,Chicago,0,1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 23:44:35
|
i cant understand your requirement here. I think seeing the way you're using it max and min values are kind of offsetthen it should beTable2.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 herehttp://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-09 : 00:35:46
|
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... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-09 : 01:18:14
|
what does that mean one month old date with MinV MaxV? give an example.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-09 : 11:05:06
|
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? |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-09 : 11:50:08
|
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 data2013-02-05 15:53:14.0472013-02-05 16:23:56.5332013-02-06 05:15:03.3532013-02-06 05:15:03.4472013-02-06 05:15:03.4702013-02-06 05:15:03.4972013-02-06 05:15:03.8032013-02-06 05:15:03.8802013-02-06 05:15:05.3372013-02-06 05:15:05.8002013-02-06 05:15:05.8872013-02-06 05:15:05.9802013-02-06 05:15:06.0102013-02-06 05:15:06.4002013-02-06 05:15:06.5872013-02-06 05:15:06.8402013-02-06 05:15:07.5032013-02-06 05:15:07.5732013-02-06 05:15:07.8372013-02-06 05:15:08.0602013-02-06 05:15:10.0472013-02-06 05:15:10.0902013-02-06 05:15:10.1902013-02-06 05:15:10.2372013-02-06 05:15:10.3002013-02-06 05:15:10.4372013-02-06 05:15:10.4872013-02-06 05:15:10.5572013-02-06 05:15:10.5932013-02-06 05:15:10.6202013-02-06 05:15:10.7002013-02-06 05:15:10.7472013-02-06 05:15:10.7832013-02-06 05:15:10.8132013-02-06 05:15:10.9002013-02-06 05:15:11.0402013-02-06 05:15:11.3172013-02-06 05:15:11.5302013-02-06 05:15:11.5602013-02-06 05:15:11.5902013-02-06 05:15:11.6272013-02-06 05:15:11.6602013-02-06 05:15:11.6902013-02-06 05:15:11.7932013-02-06 05:15:11.9032013-02-06 05:15:11.9572013-02-06 05:15:12.0302013-02-06 05:15:12.0902013-02-06 05:15:12.1572013-02-06 05:15:12.2402013-02-06 05:15:12.2672013-02-06 05:15:12.3132013-02-06 05:15:12.4402013-02-06 05:15:12.5072013-02-06 05:15:12.6172013-02-06 05:15:12.6772013-02-06 05:15:12.7202013-02-06 05:15:12.7602013-02-06 05:15:12.9272013-02-06 05:15:12.9472013-02-06 05:15:26.4972013-02-06 05:15:26.6072013-02-06 05:15:26.6932013-02-06 05:15:26.8032013-02-06 05:15:27.0802013-02-06 05:15:27.2672013-02-06 05:15:27.5702013-02-06 05:15:27.6002013-02-06 05:15:27.6702013-02-06 05:15:27.7802013-02-06 05:15:27.8532013-02-06 05:15:27.8832013-02-06 05:15:28.0372013-02-06 05:15:28.1172013-02-06 05:15:28.2632013-02-06 05:15:28.3802013-02-06 05:15:28.4602013-02-06 05:15:28.4802013-02-06 05:15:28.5002013-02-06 05:15:28.5202013-02-06 05:15:28.6672013-02-06 05:15:28.8072013-02-06 05:15:28.8602013-02-06 05:15:28.9972013-02-06 05:15:29.0402013-02-06 05:15:29.1332013-02-06 05:15:29.2132013-02-06 05:15:29.3172013-02-06 05:15:29.5472013-02-06 05:15:29.5802013-02-06 05:15:29.6032013-02-06 05:15:29.6202013-02-06 05:15:29.6472013-02-06 05:15:29.6702013-02-06 05:15:29.6972013-02-06 05:15:29.7802013-02-06 05:15:29.9072013-02-06 05:15:29.9932013-02-06 05:15:30.0272013-02-06 05:15:30.0572013-02-06 05:15:30.0832013-02-06 05:15:30.2102013-02-06 05:15:30.3072013-02-06 05:15:30.3402013-02-06 05:15:30.4432013-02-06 05:15:30.5202013-02-06 05:15:30.6572013-02-06 05:15:30.6972013-02-06 05:15:30.8302013-02-06 05:15:30.8602013-02-06 05:15:30.9672013-02-06 05:15:31.080Any Thoughts? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-09 : 12:30:18
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-09 : 13:20:54
|
So if it is 1-3 (MinV - MaxV) Mean 1 to 3 Month Back from current date. |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-09 : 13:59:56
|
Visakh, I create Sample Test Data. Please take a lookCreate 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 Date1select * from Date2--Final QuerySelect 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 beCity Code CreateDateNY 825 2012-02-09 00:00:00.000NY 825 2012-12-09 00:00:00.000KE 838 2012-02-09 00:00:00.000KE 838 2012-12-09 00:00:00.000Guide Me. Thank You... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-09 : 15:58:04
|
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?SelectD2.City,D2.Code,D1.CreateDateFROM Date2 D2Inner 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. |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-10 : 00:27:56
|
James Awesome. That's what i want.....Thank You.... |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-10 : 05:04:33
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-10 : 06:39:33
|
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. |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-10 : 21:32:02
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-10 : 21:47:31
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 00:27:51
|
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 passedOP was telling one year old data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|