CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ipandabear
ipandabear

Reputation: 11

For while statements in SQL

This question has bothered me for most of the day, just can't figure it out, and I don't really know which keywords to google for.

Let's say I have policies and their date, and I want to know if the policy falls within the first year since its inception or later years. it will have to find the earliest date for each policy, and then return 1 if the future date is within 1 year of the original date.

I know that we need to use for while statements but I don't know how to begin.

Thanks

For example:

+--------+-----------+
| Policy |   Date    |
+--------+-----------+
| CC1002 | 5/1/2012  |
| CC1002 | 6/1/2012  |
| CC1002 | 7/1/2012  |
| CC1002 | 8/1/2012  |
| CC1002 | 9/1/2012  |
| CC1002 | 10/1/2012 |
| CC1002 | 11/1/2012 |
| CC1002 | 12/1/2012 |
| CC1002 | 1/1/2013  |
| CC1002 | 2/1/2013  |
| CC1002 | 3/1/2013  |
| CC1002 | 4/1/2013  |
| CC1002 | 5/1/2013  |
| CC1002 | 6/1/2013  |
| CC1002 | 7/1/2013  |
| CC1002 | 8/1/2013  |
| CC1008 | 5/1/2012  |
| CC1008 | 6/1/2013  |
+--------+-----------+

Results 

+--------+-----------+-------------------+
| Policy |   Date    | YearfromInception |
+--------+-----------+-------------------+
| CC1002 | 5/1/2012  |                 1 |
| CC1002 | 6/1/2012  |                 1 |
| CC1002 | 7/1/2012  |                 1 |
| CC1002 | 8/1/2012  |                 1 |
| CC1002 | 9/1/2012  |                 1 |
| CC1002 | 10/1/2012 |                 1 |
| CC1002 | 11/1/2012 |                 1 |
| CC1002 | 12/1/2012 |                 1 |
| CC1002 | 1/1/2013  |                 1 |
| CC1002 | 2/1/2013  |                 1 |
| CC1002 | 3/1/2013  |                 1 |
| CC1002 | 4/1/2013  |                 1 |
| CC1002 | 5/1/2013  |                 2 |
| CC1002 | 6/1/2013  |                 2 |
| CC1002 | 7/1/2013  |                 2 |
| CC1002 | 8/1/2013  |                 2 |
| CC1008 | 5/1/2012  |                 1 |
| CC1008 | 6/1/2013  |                 2 |
+--------+-----------+-------------------+

Solved. This community is great!

Upvotes: 0

Views: 93

Answers (4)

Hogan
Hogan

Reputation: 70513

In SQL you don't use loops -- SQL works on sets. In this case you want to get the set of all starting dates for policies and then join that back to the original table to get your results. Like this:

SELECT mytable.policy, 
       (datediff(m,sub.inception,mytable.PolicyDate)/12)+1 as yearfrominception
FROM mytable
JOIN (
  SELECT Policy, Min(PolicyDate) as inception
  FROM mytable
  GROUP BY Policy
) as sub ON mytable.Policy = sub.Policy

Upvotes: 1

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Without subqueries:

CREATE TABLE #a
(
    policy VARCHAR(100),
    pDate DATE
)

INSERT INTO #a VALUES ('CC1002' ,'5/1/2012');
INSERT INTO #a VALUES ('CC1002', '6/1/2012');
INSERT INTO #a VALUES ('CC1002', '7/1/2012');
INSERT INTO #a VALUES ('CC1002', '8/1/2012');
INSERT INTO #a VALUES ('CC1002', '9/1/2012');
INSERT INTO #a VALUES ('CC1002', '10/1/2012');
INSERT INTO #a VALUES ('CC1002', '11/1/2012');
INSERT INTO #a VALUES ('CC1002', '12/1/2012');
INSERT INTO #a VALUES ('CC1002', '1/1/2013');
INSERT INTO #a VALUES ('CC1002', '2/1/2013');
INSERT INTO #a VALUES ('CC1002', '3/1/2013');
INSERT INTO #a VALUES ('CC1002', '4/1/2013');
INSERT INTO #a VALUES ('CC1002', '5/1/2013');
INSERT INTO #a VALUES ('CC1002', '6/1/2013');
INSERT INTO #a VALUES ('CC1002', '7/1/2013');
INSERT INTO #a VALUES ('CC1002', '8/1/2013');
INSERT INTO #a VALUES ('CC1008', '5/1/2012');
INSERT INTO #a VALUES ('CC1008', '6/1/2013');

SELECT a.policy ,
       a.pDate ,
       DATEDIFF(DAY,  MIN(a.pDate) OVER (PARTITION BY a.policy), a.pDate)/365+ 1

       FROM #a a

Upvotes: 1

Chipmonkey
Chipmonkey

Reputation: 865

You can do this a few different ways. You could set up a table with the MIN(Date) for each record:

WITH mindate (Policy, MinDate) as
    (select Policy, min(Date) as MinDate
    from mytable
    group by Policy)
select
    t1.Policy, t1.Date, DATEDIFF(year, t2.MinDate, t1.Date) + 1
from mytable t1 join mindate t2 on
    t1.Policy = t2.Policy

I consider this the more universal SQL approach (without using temporary tables), but I believe you can replace the WITH derived table with an analytical function: MIN(Date) over (partition by Policy):

select Policy,
DATEDIFF(year, Min(Date) over (partition by Policy), Date) + 1
from mytable

That's obviously shorter, but less portable if you move away from SQL Server (although support is growing). If performance becomes an issue, indexes or temporary tables could speed things up.

Upvotes: 0

JL Peyret
JL Peyret

Reputation: 12154

assuming you mean to return, for each policy/date, the number of years since the policy first existed...

You'll have to figure out the year difference function, date stuff is highly platform dependent (and you seem to want SQL Server and MySQL) and generally need some tweaking. In my code, it would return 0 years for the first inception row, to which you would add +1 to get your results.

no whiles required. something like this should work:

select Policy
,Date 
,function-datediff-in-years-for-your-db(
  Date, 
(select min(Date) from Policy_table s where s.Policy = Policy_table.Policy)
)
from Policy_table

Upvotes: 0

Related Questions