September 23, 2008 at 7:14 am
Hi - New to all this.
Our database stores transactions on policies. Some polices are sold for one year, others pro rated. The table has a startdate and enddate of each policy. Each policy can start at anytime throughout the year, hence have different start dates.
How can I pull just the annual policies sold (ie: those with 365 days of cover, no less)?
Thank you in advance for your help with this.
September 23, 2008 at 7:26 am
September 23, 2008 at 7:36 am
select datediff(dd,sdate,edate)from table where datediff(dd,sdate,edate) =365
where sdate is startdate and edate date enddate
September 23, 2008 at 7:51 am
vyas (9/23/2008)
select datediff(dd,sdate,edate)from table where datediff(dd,sdate,edate) =365where sdate is startdate and edate date enddate
The above is not quite right. If you do the following:
select datediff(dd,'2008-01-01','2008-12-31')
the result is:
365
If you do the following:
select datediff(dd,'2007-01-01','2007-12-31')
the result is:
364
In the where clause, I'd do something more like this:
datediff(dd,startdate,enddate) + 1 >= 365
Even then, there may be a better way to do this.
😎
September 23, 2008 at 2:00 pm
or using the year part,
where datediff(year,startdate,dateadd(d,1,enddate))>=1
or datediff(year,startdate, enddate)>=1
depending on which one actually meets your definition
[font="Courier New"]ZenDada[/font]
September 23, 2008 at 2:13 pm
ZenDada (9/23/2008)
or using the year part,where datediff(year,startdate,dateadd(d,1,enddate))>=1
or datediff(year,startdate, enddate)>=1
depending on which one actually meets your definition
This won't work if partial years span years. Example, datediff(yy,'2008-10-01','2009-01-31) = 1.
😎
September 23, 2008 at 2:32 pm
Ah! Sure enough! Silly me!
I guess you could compare the two dates with dateadd(year, 1, '2008-10-01')
though, right? I am thinking that would account for leap years where 365 days would not?
[font="Courier New"]ZenDada[/font]
September 23, 2008 at 2:45 pm
Actually, what I think is that the OP needs to post additional information, including sample data and expected results. This would greatly assist in determining a solution.
😎
September 24, 2008 at 1:45 am
Thanks so much for your time with this.
Example of data:
policy number startdate enddate policyholder policy
1000012 01/01/2008 31/12/2008 Joe bloggs CycleIns
1000013 12/08/2008 17/08/2008 Dave Smith TravelIns
1000014 04/03/2008 03/03/2009 Cnl Mustard MotorIns
1000015 15/08/2008 31/01/2009 Dr White CycleIns
I need a sample query to only lift record 2 and 4 as the others are not year long policies.
Hope this helps.
September 24, 2008 at 2:49 am
See http://www.sqlteam.com/article/datediff-function-demystified
DECLARE@Sample TABLE
(
polnum int,
startdate datetime,
enddate datetime,
policyholder varchar(200),
policy varchar(200)
)
Set dateformat dmy
INSERT@Sample
SELECT1000012, '01/01/2008', '31/12/2008', 'Joe bloggs', 'CycleIns' UNION ALL
SELECT1000013, '12/08/2008', '17/08/2008', 'Dave Smith', 'TravelIns' UNION ALL
SELECT1000014, '04/03/2008', '03/03/2009', 'Cnl Mustard', 'MotorIns' UNION ALL
SELECT1000015, '15/08/2008', '31/01/2009', 'Dr White', 'CycleIns'
SELECT*
FROM@Sample
WHEREdbo.fnyearsapart(startdate, enddate + 1) < 1
N 56°04'39.16"
E 12°55'05.25"
September 24, 2008 at 7:47 am
I'm getting confused. The original post asked to find records for year-long policies, yet the post with sample data seemed to be asking to find the policies that AREN'T year-long. Using the sample data (records 1 and 3 are year-long), here is one way to find those:
create table #Policy (
PolicyNumber int,
StartDate datetime,
EndDate datetime,
PolicyHolder varchar(50),
Policy varchar(50));
insert into #Policy (
PolicyNumber,
StartDate,
EndDate,
PolicyHolder,
Policy)
select 1000012,'2008-01-01','2008-12-31','Joe Bloggs' ,'CycleIns' union all
select 1000013,'2008-08-12','2008-08-17','Dave Smith' ,'TravelIns' union all
select 1000014,'2008-03-04','2009-03-03','Cnl Mustard','MotorIns' union all
select 1000015,'2008-08-15','2009-01-31','Dr White','CycleIns';
select
*
from
#Policy;
select
*
from
#Policy
where
EndDate = dateadd(yy, 1, StartDate) - 1;
drop table #Policy;
To find those that are not year long, the following would work:
create table #Policy (
PolicyNumber int,
StartDate datetime,
EndDate datetime,
PolicyHolder varchar(50),
Policy varchar(50));
insert into #Policy (
PolicyNumber,
StartDate,
EndDate,
PolicyHolder,
Policy)
select 1000012,'2008-01-01','2008-12-31','Joe Bloggs' ,'CycleIns' union all
select 1000013,'2008-08-12','2008-08-17','Dave Smith' ,'TravelIns' union all
select 1000014,'2008-03-04','2009-03-03','Cnl Mustard','MotorIns' union all
select 1000015,'2008-08-15','2009-01-31','Dr White','CycleIns';
select
*
from
#Policy;
select
*
from
#Policy
where
EndDate < dateadd(yy, 1, StartDate) - 1;
drop table #Policy;
😎
September 24, 2008 at 7:53 am
I need a sample query to only lift record 2 and 4 as the others are not year long policies.
Lynn,
I believe that the records, 2 and 4, correspond to 1000012 and 1000014.
Also I prefer the DATEADD answer to the DATEDIFF as it's a bit more human readable that an entire year is being looked for. I don't believe that there'll be any performance difference between the two.
September 24, 2008 at 8:02 am
Adrian Nichols (9/24/2008)
I need a sample query to only lift record 2 and 4 as the others are not year long policies.
Lynn,
I believe that the records, 2 and 4, correspond to 1000012 and 1000014.
Also I prefer the DATEADD answer to the DATEDIFF as it's a bit more human readable that an entire year is being looked for. I don't believe that there'll be any performance difference between the two.
Could be, it comes down to one's interpretation of records 2 and 4. I wasn't looking at the last number in the policy number. I was looking at the actually records as provided.
😎
September 24, 2008 at 8:08 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply