June 5, 2012 at 9:48 am
hi
this is my DDL.
declare @temp5 table
(
Id int,
FromDate datetime,
ThruDate datetime)
result
id FromDate Thrudate
1 2010-09-30 00:00:00.000 2010-10-31 00:00:00.000
2 2010-10-31 00:00:00.000 2010-11-30 00:00:00.000
3 2010-11-30 00:00:00.000 2010-12-31 00:00:00.000
4 2008-12-31 00:00:00.000 2009-01-31 00:00:00.000
5 2009-01-31 00:00:00.000 2009-02-28 00:00:00.000
6 2009-02-28 00:00:00.000 2009-03-31 00:00:00.000
7 2009-03-31 00:00:00.000 2009-04-30 00:00:00.000
i want to get data in my query from 2008-12-31 to 2009 -04-30
what condition do i need to put ,between i cant ,coz i want
2008-12-31 00:00:00.000 2009-01-31 00:00:00.000
2009-01-31 00:00:00.000 2009-02-28 00:00:00.000
2009-02-28 00:00:00.000 2009-03-31 00:00:00.000
2009-03-31 00:00:00.000 2009-04-30 00:00:00.000
Any help?
June 5, 2012 at 10:03 am
So you want entries where the FromDate is after 2008-12-31 and the ThruDate is before 2009-04-30?
If you don't need to worry about time (ie - the time portion will always be 00:00:00):
select FromDate, ThruDate
from @temp5
where FromDate >= '2008-12-31'
and ThruDate <= '2009-04-30'
If you want to include everything for 2009-04-30 regardless of time just add a day to it:
select FromDate, ThruDate
from @temp5
where FromDate >= '2008-12-31'
and ThruDate < DATEADD(dd,1,'2009-04-30') --since the less than will exclude anything where it's the day of 4-30.
Or, if you're on a version that supports the DATE type you can just convert the column to date:
select FromDate, ThruDate
from @temp5
where FromDate >= '2008-12-31'
and cast(ThruDate as DATE) <= '2009-04-30' --since the less than will exclude anything where it's the day of 4-30.
June 5, 2012 at 10:32 am
thanks but what if i get data like this,
2010-09-30 00:00:00.0002010-10-31 00:00:00.000
2010-10-31 00:00:00.0002010-11-30 00:00:00.000
2010-11-30 00:00:00.0002010-12-31 00:00:00.000
2008-12-31 00:00:00.0002009-01-31 00:00:00.000
2009-01-31 00:00:00.0002009-02-28 00:00:00.000
2009-02-28 00:00:00.0002009-03-31 00:00:00.000
2009-03-31 00:00:00.0002009-04-30 00:00:00.000
2009-04-30 00:00:00.0002009-05-31 00:00:00.000
2009-05-31 00:00:00.0002009-06-30 00:00:00.000
2009-06-30 00:00:00.0002009-07-01 00:00:00.000
2009-07-01 00:00:00.0002009-07-02 00:00:00.000
2009-07-02 00:00:00.0002009-07-03 00:00:00.000
2009-07-03 00:00:00.0002009-07-04 00:00:00.000
2009-07-04 00:00:00.0002009-07-05 00:00:00.000
2009-07-05 00:00:00.0002009-07-06 00:00:00.000
2009-07-06 00:00:00.0002009-07-07 00:00:00.000
2009-07-07 00:00:00.0002009-07-08 00:00:00.000
2009-07-08 00:00:00.0002009-07-09 00:00:00.000
2009-07-09 00:00:00.0002009-07-10 00:00:00.000
2009-07-10 00:00:00.0002009-07-11 00:00:00.000
2009-07-11 00:00:00.0002009-07-12 00:00:00.000
2009-07-12 00:00:00.0002009-07-13 00:00:00.000
2009-07-13 00:00:00.0002009-07-14 00:00:00.000
2009-07-14 00:00:00.0002009-07-15 00:00:00.000
2009-07-15 00:00:00.0002009-07-16 00:00:00.000
2009-07-16 00:00:00.0002009-07-17 00:00:00.000
2009-07-17 00:00:00.0002009-07-18 00:00:00.000
2009-07-18 00:00:00.0002009-07-19 00:00:00.000
2009-07-19 00:00:00.0002009-07-20 00:00:00.000
2009-07-20 00:00:00.0002009-07-21 00:00:00.000
2009-07-21 00:00:00.0002009-07-22 00:00:00.000
2009-07-22 00:00:00.0002009-07-23 00:00:00.000
2009-07-23 00:00:00.0002009-07-24 00:00:00.000
2009-07-24 00:00:00.0002009-07-25 00:00:00.000
2009-07-25 00:00:00.0002009-07-26 00:00:00.000
2009-07-26 00:00:00.0002009-07-27 00:00:00.000
2009-07-27 00:00:00.0002009-07-28 00:00:00.000
2009-07-28 00:00:00.0002009-07-29 00:00:00.000
2009-07-29 00:00:00.0002009-07-30 00:00:00.000
2009-07-30 00:00:00.0002009-07-31 00:00:00.000
2009-07-31 00:00:00.0002009-08-01 00:00:00.000
2009-08-01 00:00:00.0002009-08-02 00:00:00.000
2009-08-02 00:00:00.0002009-08-03 00:00:00.000
2009-08-03 00:00:00.0002009-08-04 00:00:00.000
2009-08-04 00:00:00.0002009-08-05 00:00:00.000
2009-08-05 00:00:00.0002009-08-06 00:00:00.000
2009-08-06 00:00:00.0002009-08-07 00:00:00.000
2009-08-07 00:00:00.0002009-08-08 00:00:00.000
2009-08-08 00:00:00.0002009-08-09 00:00:00.000
2009-08-09 00:00:00.0002009-08-10 00:00:00.000
2009-08-10 00:00:00.0002009-08-11 00:00:00.000
2009-08-11 00:00:00.0002009-08-12 00:00:00.000
2009-08-12 00:00:00.0002009-08-13 00:00:00.000
2009-08-13 00:00:00.0002009-08-14 00:00:00.000
2009-08-14 00:00:00.0002009-08-15 00:00:00.000
2009-08-15 00:00:00.0002009-08-16 00:00:00.000
2009-08-16 00:00:00.0002009-08-17 00:00:00.000
2009-08-17 00:00:00.0002009-08-18 00:00:00.000
2009-08-18 00:00:00.0002009-08-19 00:00:00.000
2009-08-19 00:00:00.0002009-08-20 00:00:00.000
2009-08-20 00:00:00.0002009-08-21 00:00:00.000
2009-08-21 00:00:00.0002009-08-22 00:00:00.000
2009-08-22 00:00:00.0002009-08-23 00:00:00.000
2009-08-23 00:00:00.0002009-08-24 00:00:00.000
2009-08-24 00:00:00.0002009-08-25 00:00:00.000
2009-08-25 00:00:00.0002009-08-26 00:00:00.000
2009-08-26 00:00:00.0002009-08-27 00:00:00.000
2009-08-27 00:00:00.0002009-08-28 00:00:00.000
2009-08-28 00:00:00.0002009-08-29 00:00:00.000
2009-08-29 00:00:00.0002009-08-30 00:00:00.000
2009-08-30 00:00:00.0002009-08-31 00:00:00.000
2009-08-31 00:00:00.0002009-09-01 00:00:00.000
2009-09-01 00:00:00.0002009-09-02 00:00:00.000
2009-09-02 00:00:00.0002009-09-03 00:00:00.000
2009-09-03 00:00:00.0002009-09-04 00:00:00.000
2009-09-04 00:00:00.0002009-09-05 00:00:00.000
2009-09-05 00:00:00.0002009-09-06 00:00:00.000
2009-09-06 00:00:00.0002009-09-07 00:00:00.000
2009-09-07 00:00:00.0002009-09-08 00:00:00.000
2009-09-08 00:00:00.0002009-09-09 00:00:00.000
2009-09-09 00:00:00.0002009-09-10 00:00:00.000
2009-09-10 00:00:00.0002009-09-11 00:00:00.000
2009-09-11 00:00:00.0002009-09-12 00:00:00.000
2009-09-12 00:00:00.0002009-09-13 00:00:00.000
2009-09-13 00:00:00.0002009-09-14 00:00:00.000
2009-09-14 00:00:00.0002009-09-15 00:00:00.000
2009-09-15 00:00:00.0002009-09-16 00:00:00.000
2009-09-16 00:00:00.0002009-09-17 00:00:00.000
2009-09-17 00:00:00.0002009-09-18 00:00:00.000
2009-09-18 00:00:00.0002009-09-19 00:00:00.000
2009-09-19 00:00:00.0002009-09-20 00:00:00.000
2009-09-20 00:00:00.0002009-09-21 00:00:00.000
2009-09-21 00:00:00.0002009-09-22 00:00:00.000
2009-09-22 00:00:00.0002009-09-23 00:00:00.000
2009-09-23 00:00:00.0002009-09-24 00:00:00.000
2009-09-24 00:00:00.0002009-09-25 00:00:00.000
2009-09-25 00:00:00.0002009-09-26 00:00:00.000
2009-09-26 00:00:00.0002009-09-27 00:00:00.000
2009-09-27 00:00:00.0002009-09-28 00:00:00.000
2009-09-28 00:00:00.0002009-09-29 00:00:00.000
2009-09-29 00:00:00.0002009-09-30 00:00:00.000
2009-09-30 00:00:00.0002009-10-01 00:00:00.000
2009-10-01 00:00:00.0002009-10-02 00:00:00.000
2009-10-02 00:00:00.0002009-10-03 00:00:00.000
2009-10-03 00:00:00.0002009-10-04 00:00:00.000
2009-10-04 00:00:00.0002009-10-05 00:00:00.000
2009-10-05 00:00:00.0002009-10-06 00:00:00.000
2009-10-06 00:00:00.0002009-10-07 00:00:00.000
2009-10-07 00:00:00.0002009-10-08 00:00:00.000
2009-10-08 00:00:00.0002009-10-09 00:00:00.000
2009-10-09 00:00:00.0002009-10-10 00:00:00.000
2009-10-10 00:00:00.0002009-10-11 00:00:00.000
2009-10-11 00:00:00.0002009-10-12 00:00:00.000
2009-10-12 00:00:00.0002009-10-13 00:00:00.000
2009-10-13 00:00:00.0002009-10-14 00:00:00.000
2009-10-14 00:00:00.0002009-10-15 00:00:00.000
2009-10-15 00:00:00.0002009-10-16 00:00:00.000
2009-10-16 00:00:00.0002009-10-17 00:00:00.000
2009-10-17 00:00:00.0002009-10-18 00:00:00.000
2009-10-18 00:00:00.0002009-10-19 00:00:00.000
2009-10-19 00:00:00.0002009-10-20 00:00:00.000
2009-10-20 00:00:00.0002009-10-21 00:00:00.000
2009-10-21 00:00:00.0002009-10-22 00:00:00.000
2009-10-22 00:00:00.0002009-10-23 00:00:00.000
2009-10-23 00:00:00.0002009-10-24 00:00:00.000
2009-10-24 00:00:00.0002009-10-25 00:00:00.000
2009-10-25 00:00:00.0002009-10-26 00:00:00.000
2009-10-26 00:00:00.0002009-10-27 00:00:00.000
2009-10-27 00:00:00.0002009-10-28 00:00:00.000
2009-10-28 00:00:00.0002009-10-29 00:00:00.000
2009-10-29 00:00:00.0002009-10-30 00:00:00.000
2009-10-30 00:00:00.0002009-10-31 00:00:00.000
2009-10-31 00:00:00.0002009-11-01 00:00:00.000
2009-11-01 00:00:00.0002009-11-02 00:00:00.000
2009-11-02 00:00:00.0002009-11-03 00:00:00.000
2009-11-03 00:00:00.0002009-11-04 00:00:00.000
2009-11-04 00:00:00.0002009-11-05 00:00:00.000
2009-11-05 00:00:00.0002009-11-06 00:00:00.000
2009-11-06 00:00:00.0002009-11-07 00:00:00.000
2009-11-07 00:00:00.0002009-11-08 00:00:00.000
2009-11-08 00:00:00.0002009-11-09 00:00:00.000
2009-11-09 00:00:00.0002009-11-10 00:00:00.000
2009-11-10 00:00:00.0002009-11-11 00:00:00.000
2009-11-11 00:00:00.0002009-11-12 00:00:00.000
2009-11-12 00:00:00.0002009-11-13 00:00:00.000
2009-11-13 00:00:00.0002009-11-14 00:00:00.000
2009-11-14 00:00:00.0002009-11-15 00:00:00.000
2009-11-15 00:00:00.0002009-11-16 00:00:00.000
2009-11-16 00:00:00.0002009-11-17 00:00:00.000
2009-11-17 00:00:00.0002009-11-18 00:00:00.000
2009-11-18 00:00:00.0002009-11-19 00:00:00.000
2009-11-19 00:00:00.0002009-11-20 00:00:00.000
2009-11-20 00:00:00.0002009-11-21 00:00:00.000
2009-11-21 00:00:00.0002009-11-22 00:00:00.000
2009-11-22 00:00:00.0002009-11-23 00:00:00.000
2009-11-23 00:00:00.0002009-11-24 00:00:00.000
2009-11-24 00:00:00.0002009-11-25 00:00:00.000
2009-11-25 00:00:00.0002009-11-26 00:00:00.000
2009-11-26 00:00:00.0002009-11-27 00:00:00.000
2009-11-27 00:00:00.0002009-11-28 00:00:00.000
2009-11-28 00:00:00.0002009-11-29 00:00:00.000
2009-11-29 00:00:00.0002009-11-30 00:00:00.000
2009-11-30 00:00:00.0002009-12-01 00:00:00.000
2009-12-01 00:00:00.0002009-12-02 00:00:00.000
2009-12-02 00:00:00.0002009-12-03 00:00:00.000
2009-12-03 00:00:00.0002009-12-04 00:00:00.000
2009-12-04 00:00:00.0002009-12-05 00:00:00.000
2009-12-05 00:00:00.0002009-12-06 00:00:00.000
2009-12-06 00:00:00.0002009-12-07 00:00:00.000
2009-12-07 00:00:00.0002009-12-08 00:00:00.000
2009-12-08 00:00:00.0002009-12-09 00:00:00.000
2009-12-09 00:00:00.0002009-12-10 00:00:00.000
2009-12-10 00:00:00.0002009-12-11 00:00:00.000
2009-12-11 00:00:00.0002009-12-12 00:00:00.000
2009-12-12 00:00:00.0002009-12-13 00:00:00.000
2009-12-13 00:00:00.0002009-12-14 00:00:00.000
2009-12-14 00:00:00.0002009-12-15 00:00:00.000
2009-12-15 00:00:00.0002009-12-16 00:00:00.000
2009-12-16 00:00:00.0002009-12-17 00:00:00.000
2009-12-17 00:00:00.0002009-12-18 00:00:00.000
2009-12-18 00:00:00.0002009-12-19 00:00:00.000
2009-12-19 00:00:00.0002009-12-20 00:00:00.000
2009-12-20 00:00:00.0002009-12-21 00:00:00.000
2009-12-21 00:00:00.0002009-12-22 00:00:00.000
2009-12-22 00:00:00.0002009-12-23 00:00:00.000
2009-12-23 00:00:00.0002009-12-24 00:00:00.000
2009-12-24 00:00:00.0002009-12-25 00:00:00.000
2009-12-25 00:00:00.0002009-12-26 00:00:00.000
2009-12-26 00:00:00.0002009-12-27 00:00:00.000
2009-12-27 00:00:00.0002009-12-28 00:00:00.000
2009-12-28 00:00:00.0002009-12-29 00:00:00.000
2009-12-29 00:00:00.0002009-12-30 00:00:00.000
2009-12-30 00:00:00.0002009-12-31 00:00:00.000
2009-12-31 00:00:00.0002010-01-01 00:00:00.000
2010-01-01 00:00:00.0002010-01-02 00:00:00.000
2010-01-02 00:00:00.0002010-01-03 00:00:00.000
2010-01-03 00:00:00.0002010-01-04 00:00:00.000
2010-01-04 00:00:00.0002010-01-05 00:00:00.000
2010-01-05 00:00:00.0002010-01-06 00:00:00.000
2010-01-06 00:00:00.0002010-01-07 00:00:00.000
2010-01-07 00:00:00.0002010-01-08 00:00:00.000
2010-01-08 00:00:00.0002010-01-09 00:00:00.000
2010-01-09 00:00:00.0002010-01-10 00:00:00.000
2010-01-10 00:00:00.0002010-01-11 00:00:00.000
2010-01-11 00:00:00.0002010-01-12 00:00:00.000
2010-01-12 00:00:00.0002010-01-13 00:00:00.000
2010-01-13 00:00:00.0002010-01-14 00:00:00.000
2010-01-14 00:00:00.0002010-01-15 00:00:00.000
2010-01-15 00:00:00.0002010-01-16 00:00:00.000
2010-01-16 00:00:00.0002010-01-17 00:00:00.000
2010-01-17 00:00:00.0002010-01-18 00:00:00.000
2010-01-18 00:00:00.0002010-01-19 00:00:00.000
2010-01-19 00:00:00.0002010-01-20 00:00:00.000
2010-01-20 00:00:00.0002010-01-21 00:00:00.000
2010-01-21 00:00:00.0002010-01-22 00:00:00.000
2010-01-22 00:00:00.0002010-01-23 00:00:00.000
2010-01-23 00:00:00.0002010-01-24 00:00:00.000
2010-01-24 00:00:00.0002010-01-25 00:00:00.000
2010-01-25 00:00:00.0002010-01-26 00:00:00.000
2010-01-26 00:00:00.0002010-01-27 00:00:00.000
2010-01-27 00:00:00.0002010-01-28 00:00:00.000
2010-01-28 00:00:00.0002010-01-29 00:00:00.000
2010-01-29 00:00:00.0002010-01-30 00:00:00.000
2010-01-30 00:00:00.0002010-01-31 00:00:00.000
2010-01-31 00:00:00.0002010-02-01 00:00:00.000
2010-02-01 00:00:00.0002010-02-02 00:00:00.000
2010-02-02 00:00:00.0002010-02-03 00:00:00.000
2010-02-03 00:00:00.0002010-02-04 00:00:00.000
2010-02-04 00:00:00.0002010-02-05 00:00:00.000
2010-02-05 00:00:00.0002010-02-06 00:00:00.000
2010-02-06 00:00:00.0002010-02-07 00:00:00.000
2010-02-07 00:00:00.0002010-02-08 00:00:00.000
2010-02-08 00:00:00.0002010-02-09 00:00:00.000
2010-02-09 00:00:00.0002010-02-10 00:00:00.000
2010-02-10 00:00:00.0002010-02-11 00:00:00.000
2010-02-11 00:00:00.0002010-02-12 00:00:00.000
2010-02-12 00:00:00.0002010-02-13 00:00:00.000
2010-02-13 00:00:00.0002010-02-14 00:00:00.000
2010-02-14 00:00:00.0002010-02-15 00:00:00.000
2010-02-15 00:00:00.0002010-02-16 00:00:00.000
2010-02-16 00:00:00.0002010-02-17 00:00:00.000
2010-02-17 00:00:00.0002010-02-18 00:00:00.000
2010-02-18 00:00:00.0002010-02-19 00:00:00.000
2010-02-19 00:00:00.0002010-02-20 00:00:00.000
2010-02-20 00:00:00.0002010-02-21 00:00:00.000
2010-02-21 00:00:00.0002010-02-22 00:00:00.000
2010-02-22 00:00:00.0002010-02-23 00:00:00.000
2010-02-23 00:00:00.0002010-02-24 00:00:00.000
2010-02-24 00:00:00.0002010-02-25 00:00:00.000
2010-02-25 00:00:00.0002010-02-26 00:00:00.000
2010-02-26 00:00:00.0002010-02-27 00:00:00.000
2010-02-27 00:00:00.0002010-02-28 00:00:00.000
2010-02-28 00:00:00.0002010-03-01 00:00:00.000
2010-03-01 00:00:00.0002010-03-02 00:00:00.000
2010-03-02 00:00:00.0002010-03-03 00:00:00.000
2010-03-03 00:00:00.0002010-03-04 00:00:00.000
2010-03-04 00:00:00.0002010-03-05 00:00:00.000
2010-03-05 00:00:00.0002010-03-06 00:00:00.000
2010-03-06 00:00:00.0002010-03-07 00:00:00.000
2010-03-07 00:00:00.0002010-03-08 00:00:00.000
2010-03-08 00:00:00.0002010-03-09 00:00:00.000
2010-03-09 00:00:00.0002010-03-10 00:00:00.000
2010-03-10 00:00:00.0002010-03-11 00:00:00.000
2010-03-11 00:00:00.0002010-03-12 00:00:00.000
2010-03-12 00:00:00.0002010-03-13 00:00:00.000
2010-03-13 00:00:00.0002010-03-14 00:00:00.000
2010-03-14 00:00:00.0002010-03-15 00:00:00.000
2010-03-15 00:00:00.0002010-03-16 00:00:00.000
2010-03-16 00:00:00.0002010-03-17 00:00:00.000
2010-03-17 00:00:00.0002010-03-18 00:00:00.000
2010-03-18 00:00:00.0002010-03-19 00:00:00.000
2010-03-19 00:00:00.0002010-03-20 00:00:00.000
2010-03-20 00:00:00.0002010-03-21 00:00:00.000
2010-03-21 00:00:00.0002010-03-22 00:00:00.000
2010-03-22 00:00:00.0002010-03-23 00:00:00.000
2010-03-23 00:00:00.0002010-03-24 00:00:00.000
2010-03-24 00:00:00.0002010-03-25 00:00:00.000
2010-03-25 00:00:00.0002010-03-26 00:00:00.000
2010-03-26 00:00:00.0002010-03-27 00:00:00.000
2010-03-27 00:00:00.0002010-03-28 00:00:00.000
2010-03-28 00:00:00.0002010-03-29 00:00:00.000
2010-03-29 00:00:00.0002010-03-30 00:00:00.000
2010-03-30 00:00:00.0002010-03-31 00:00:00.000
2010-03-31 00:00:00.0002010-04-01 00:00:00.000
2010-04-01 00:00:00.0002010-04-02 00:00:00.000
2010-04-02 00:00:00.0002010-04-03 00:00:00.000
2010-04-03 00:00:00.0002010-04-04 00:00:00.000
2010-04-04 00:00:00.0002010-04-05 00:00:00.000
2010-04-05 00:00:00.0002010-04-06 00:00:00.000
2010-04-06 00:00:00.0002010-04-07 00:00:00.000
2010-04-07 00:00:00.0002010-04-08 00:00:00.000
2010-04-08 00:00:00.0002010-04-09 00:00:00.000
2010-04-09 00:00:00.0002010-04-10 00:00:00.000
2010-04-10 00:00:00.0002010-04-11 00:00:00.000
2010-04-11 00:00:00.0002010-04-12 00:00:00.000
2010-04-12 00:00:00.0002010-04-13 00:00:00.000
2010-04-13 00:00:00.0002010-04-14 00:00:00.000
2010-04-14 00:00:00.0002010-04-15 00:00:00.000
2010-04-15 00:00:00.0002010-04-16 00:00:00.000
2010-04-16 00:00:00.0002010-04-17 00:00:00.000
2010-04-17 00:00:00.0002010-04-18 00:00:00.000
2010-04-18 00:00:00.0002010-04-19 00:00:00.000
2010-04-19 00:00:00.0002010-04-20 00:00:00.000
2010-04-20 00:00:00.0002010-04-21 00:00:00.000
2010-04-21 00:00:00.0002010-04-22 00:00:00.000
2010-04-22 00:00:00.0002010-04-23 00:00:00.000
2010-04-23 00:00:00.0002010-04-24 00:00:00.000
2010-04-24 00:00:00.0002010-04-25 00:00:00.000
2010-04-25 00:00:00.0002010-04-26 00:00:00.000
2010-04-26 00:00:00.0002010-04-27 00:00:00.000
2010-04-27 00:00:00.0002010-04-28 00:00:00.000
2010-04-28 00:00:00.0002010-04-29 00:00:00.000
2010-04-29 00:00:00.0002010-04-30 00:00:00.000
2010-04-30 00:00:00.0002010-05-01 00:00:00.000
2010-05-01 00:00:00.0002010-05-02 00:00:00.000
2010-05-02 00:00:00.0002010-05-03 00:00:00.000
2010-05-03 00:00:00.0002010-05-04 00:00:00.000
2010-05-04 00:00:00.0002010-05-05 00:00:00.000
2010-05-05 00:00:00.0002010-05-06 00:00:00.000
2010-05-06 00:00:00.0002010-05-07 00:00:00.000
2010-05-07 00:00:00.0002010-05-08 00:00:00.000
2010-05-08 00:00:00.0002010-05-09 00:00:00.000
2010-05-09 00:00:00.0002010-05-10 00:00:00.000
2010-05-10 00:00:00.0002010-05-11 00:00:00.000
2010-05-11 00:00:00.0002010-05-12 00:00:00.000
2010-05-12 00:00:00.0002010-05-13 00:00:00.000
2010-05-13 00:00:00.0002010-05-14 00:00:00.000
2010-05-14 00:00:00.0002010-05-15 00:00:00.000
2010-05-15 00:00:00.0002010-05-16 00:00:00.000
2010-05-16 00:00:00.0002010-05-17 00:00:00.000
2010-05-17 00:00:00.0002010-05-18 00:00:00.000
2010-05-18 00:00:00.0002010-05-19 00:00:00.000
2010-05-19 00:00:00.0002010-05-20 00:00:00.000
2010-05-20 00:00:00.0002010-05-21 00:00:00.000
2010-05-21 00:00:00.0002010-05-22 00:00:00.000
2010-05-22 00:00:00.0002010-05-23 00:00:00.000
2010-05-23 00:00:00.0002010-05-24 00:00:00.000
2010-05-24 00:00:00.0002010-05-25 00:00:00.000
2010-05-25 00:00:00.0002010-05-26 00:00:00.000
2010-05-26 00:00:00.0002010-05-27 00:00:00.000
2010-05-27 00:00:00.0002010-05-28 00:00:00.000
2010-05-28 00:00:00.0002010-05-29 00:00:00.000
2010-05-29 00:00:00.0002010-05-30 00:00:00.000
2010-05-30 00:00:00.0002010-05-31 00:00:00.000
2010-05-31 00:00:00.0002010-06-01 00:00:00.000
2010-06-01 00:00:00.0002010-06-02 00:00:00.000
2010-06-02 00:00:00.0002010-06-03 00:00:00.000
2010-06-03 00:00:00.0002010-06-04 00:00:00.000
2010-06-04 00:00:00.0002010-06-05 00:00:00.000
2010-06-05 00:00:00.0002010-06-06 00:00:00.000
2010-06-06 00:00:00.0002010-06-07 00:00:00.000
2010-06-07 00:00:00.0002010-06-08 00:00:00.000
2010-06-08 00:00:00.0002010-06-09 00:00:00.000
2010-06-09 00:00:00.0002010-06-10 00:00:00.000
2010-06-10 00:00:00.0002010-06-11 00:00:00.000
2010-06-11 00:00:00.0002010-06-12 00:00:00.000
2010-06-12 00:00:00.0002010-06-13 00:00:00.000
2010-06-13 00:00:00.0002010-06-14 00:00:00.000
2010-06-14 00:00:00.0002010-06-15 00:00:00.000
2010-06-15 00:00:00.0002010-06-16 00:00:00.000
2010-06-16 00:00:00.0002010-06-17 00:00:00.000
2010-06-17 00:00:00.0002010-06-18 00:00:00.000
2010-06-18 00:00:00.0002010-06-19 00:00:00.000
2010-06-19 00:00:00.0002010-06-20 00:00:00.000
2010-06-20 00:00:00.0002010-06-21 00:00:00.000
2010-06-21 00:00:00.0002010-06-22 00:00:00.000
2010-06-22 00:00:00.0002010-06-23 00:00:00.000
2010-06-23 00:00:00.0002010-06-24 00:00:00.000
2010-06-24 00:00:00.0002010-06-25 00:00:00.000
2010-06-25 00:00:00.0002010-06-26 00:00:00.000
2010-06-26 00:00:00.0002010-06-27 00:00:00.000
2010-06-27 00:00:00.0002010-06-28 00:00:00.000
2010-06-28 00:00:00.0002010-06-29 00:00:00.000
2010-06-29 00:00:00.0002010-06-30 00:00:00.000
2010-06-30 00:00:00.0002010-07-01 00:00:00.000
2010-07-01 00:00:00.0002010-07-02 00:00:00.000
2010-07-02 00:00:00.0002010-07-03 00:00:00.000
2010-07-03 00:00:00.0002010-07-04 00:00:00.000
2010-07-04 00:00:00.0002010-07-05 00:00:00.000
2010-07-05 00:00:00.0002010-07-06 00:00:00.000
2010-07-06 00:00:00.0002010-07-07 00:00:00.000
2010-07-07 00:00:00.0002010-07-08 00:00:00.000
2010-07-08 00:00:00.0002010-07-09 00:00:00.000
2010-07-09 00:00:00.0002010-07-10 00:00:00.000
2010-07-10 00:00:00.0002010-07-11 00:00:00.000
2010-07-11 00:00:00.0002010-07-12 00:00:00.000
2010-07-12 00:00:00.0002010-07-13 00:00:00.000
2010-07-13 00:00:00.0002010-07-14 00:00:00.000
2010-07-14 00:00:00.0002010-07-15 00:00:00.000
2010-07-15 00:00:00.0002010-07-16 00:00:00.000
2010-07-16 00:00:00.0002010-07-17 00:00:00.000
2010-07-17 00:00:00.0002010-07-18 00:00:00.000
2010-07-18 00:00:00.0002010-07-19 00:00:00.000
2010-07-19 00:00:00.0002010-07-20 00:00:00.000
2010-07-20 00:00:00.0002010-07-21 00:00:00.000
2010-07-21 00:00:00.0002010-07-22 00:00:00.000
2010-07-22 00:00:00.0002010-07-23 00:00:00.000
2010-07-23 00:00:00.0002010-07-24 00:00:00.000
2010-07-24 00:00:00.0002010-07-25 00:00:00.000
2010-07-25 00:00:00.0002010-07-26 00:00:00.000
2010-07-26 00:00:00.0002010-07-27 00:00:00.000
2010-07-27 00:00:00.0002010-07-28 00:00:00.000
2010-07-28 00:00:00.0002010-07-29 00:00:00.000
2010-07-29 00:00:00.0002010-07-30 00:00:00.000
2010-07-30 00:00:00.0002010-07-31 00:00:00.000
2010-07-31 00:00:00.0002010-08-31 00:00:00.000
2010-08-31 00:00:00.0002010-09-30 00:00:00.000
2010-09-30 00:00:00.0002010-10-31 00:00:00.000
2010-10-31 00:00:00.0002010-11-30 00:00:00.000
2010-11-30 00:00:00.0002010-12-31 00:00:00.000
here the dat value is repeating,
so first i want everything between 2010-09-30 and 2010-12-31
then in my second query i want 2008-12-31 and 2010-12-31
thanks
June 5, 2012 at 10:42 am
I'm not sure I'm getting how this is different from the last question. Just plug in the new numbers and run whichever query is most appropriate. However, I have a feeling you're getting at something else. What's the data set you're expected returned?
June 5, 2012 at 10:55 am
I'm not sure what you mean by the date is repeating. What do you want back? All rows?
Just change the dates in the queries as shown by cfradenburg.
SQL doesn't work in the order the rows are stored. In fact, it doesn't care.
June 5, 2012 at 11:16 am
ok.
what i want is you see the dates in bold is repeating at the end.
so i want 1 section from 09/30/2010 to 12/31/2010
and another 08/31/2008 to 12/31/2010
but in the second section 09/30/2010 to 12/31/2010 is repeating.
June 5, 2012 at 11:32 am
harri.reddy (6/5/2012)
ok.what i want is you see the dates in bold is repeating at the end.
so i want 1 section from 09/30/2010 to 12/31/2010
and another 08/31/2008 to 12/31/2010
but in the second section 09/30/2010 to 12/31/2010 is repeating.
This doesn't make sense. What is repeating here? Meaning what is the result set?
Are you saying you want two result sets? Or 1 result set that is a combination of two queries? You've got lots of dates here, and you say you want data from date 1 to date 2, but what data? All the rows that match? How is that not just a query between two dates?
Please show what you've done and then explain what this doesn't return that you want returned.
June 5, 2012 at 11:47 am
Are you looking for something like this?
SELECT FromDate, ThruDate
FROM @temp5
WHERE FromDate >= '2010-09-30'
AND CAST(ThruDate AS DATE) <= '2010-12-31'
UNION ALL
SELECT FromDate, ThruDate
FROM @temp5
WHERE FromDate >= '2010-09-30'
AND CAST(ThruDate AS DATE) <= '2010-12-31'
June 5, 2012 at 1:18 pm
thanks everybody.
i have 1 more que
my result set should be like this
account fromdate total bench1 bench2
1 09/30/2010 34.21 1 1
1 09/30/2010 34.21 1 1
3 12/31/2010 21 2 2
4 06/31/2010 21.32 3 3
but i am getting like this
1 09/30/2010 34.21 1 1
3 12/31/2010 21 2 2
4 06/31/2010 21.32 3 3
i want first 2 rows ,even if it is duplicate
insert into @temp3
select a. id, a.fromdate,a.total,
bench1,
bench2
from @temp5 a
join @TEMP1 b
on a.id = b.id
and a. fromdate = b.date
join @TEMP2 c
on c.id = b.id
and c.FromDate = b.Fromdate
do i need to use any different join????
June 5, 2012 at 1:48 pm
Can you post the full set of table variables you're using and queries to insert any starting data? It'll make it much easier to see what's going on and to test. I'm looking for something like this:
declare @temp5 table
(id int,
FromDate datetime,
ThruDate datetime)
insert into @temp5 values (1,'2010-09-30 00:00:00.000','2010-10-31 00:00:00.000')
insert into @temp5 values (2,'2010-10-31 00:00:00.000','2010-11-30 00:00:00.000')
insert into @temp5 values (3,'2010-11-30 00:00:00.000','2010-12-31 00:00:00.000')
I'm out for the rest of the day but I'll be able to take a look tomorrow.
June 5, 2012 at 2:16 pm
alright,here u go
declare @temp5 table
(id int,
ThruDate datetime,
total)
result of temp5
1 2010-09-30 00:00:00.000 20
1 2009-12-31 00:00:00.000 21.3
1 2009-12-31 00:00:00.000 21.3
1 2007-12-31 00:00:00.000 32
1 2005-12-31 00:00:00.000 16
1 2000-12-31 00:00:00.000 10
declare @temp1 table
(id int,
bench1 float,
ThruDate datetime)
id bench1 thrudate
11.22010-09-30 00:00:00.000
1NULL2009-12-31 00:00:00.000
1NULL2009-12-31 00:00:00.000
16.52007-12-31 00:00:00.000
1NULL2005-12-31 00:00:00.000
1NULL2000-12-31 00:00:00.000
declare @temp2 table
(id int,
bench2 float,
ThruDate datetime)
id bench2 thrudate
15.62010-09-30 00:00:00.000
1NULL2009-12-31 00:00:00.000
1NULL2009-12-31 00:00:00.000
17.72007-12-31 00:00:00.000
1NULL2005-12-31 00:00:00.000
1NULL2000-12-31 00:00:00.000
June 5, 2012 at 2:49 pm
Please post correct DDL. Don't try to avoid typing to make us type more.
Sample data need insert statements. There are formatting commands to the left of the edit box, please highlight your code and use those. Include the query you are running, and it's results and explain why those are wrong.
You are constantly shortcutting the information someone needs to help you. Don't write "que" for "question". Don't avoid formatting, and explain in detail, as you would for a new employee, what the data means, or what the query should return.
June 5, 2012 at 3:46 pm
u dont really need to know code,i just want to know which join will give me duplicate rows.
left,right ,full.
June 5, 2012 at 4:21 pm
harri.reddy (6/5/2012)
u dont really need to know code,i just want to know which join will give me duplicate rows.left,right ,full.
I don't know, why don't you just try each and see what it gets you?
June 5, 2012 at 4:26 pm
harri.reddy (6/5/2012)
u dont really need to know code,i just want to know which join will give me duplicate rows.left,right ,full.
Here is my suggestion, read the first article I reference below in my signature block. It walks you through the steps on what you need to post and how to do it for you to get the best possible answers to your questions. Anything else means you get less.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply