While Loop... - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

While Loop...

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-14 : 01:44:12

I am trying to create a stored procedure that will calculate the number of claims and total loss amount by quarterly ( every 3 months ).
For example, Let's say If i set parameters as following:
@beginjurydate = '20010101', @endinjurydate = '20011231' ,
the output should return looks like:


Date       Nbr of claims     Total loss_amt
------------------------------------------
01/01/01   1800                 2345567.90
04/01/01   2300                 4567899.98
07/01/01   2800                 5789324.00
10/01/01   3100                 8765245.64
12/31/01   4200                11087638.72

Here are the DDL.

CREATE TABLE [dbo].[claim] (
[clm_key] [varchar] (50) NOT NULL primary key,
[clm_nbr] [varchar] (50) not null ,
[clm_status] [char] (10) Not null,
[clm_injdate] [datetime] (10) not null,
[ref_location] [int] not null Foreigh key references location(location_key)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[loss] (
[loss_key] [varchar] (50) NOT NULL primary key,
[loss_claim] [varchar] (50) not null foreign key references claim(claim_key) ,
[los_amt] [decimal](18, 0) not null
)

go

CREATE TABLE [dbo].[location] (
[location_key] [int] NOT NULL primary key,
[loc1] [int] NULL ,
[loc2] [int] NULL ,
[loc3] [int] NULL
)
GO

--------------------------------------------------------------------

And, here is the part of stored procedure...



Create procedure Totlossamt_quarter(
                  @asofdate datetime,
                  @begindate datetime
                  @enddate   datetime
                 )

As

Declare @everyquarterly datetime
set @everyquarterly = every 3 months from @begindate

WHILE @everyquarterly <= @endinjurydate

Begin
select convert(varchar(10),@everyquarterly, 101) as @everyquarterly,sum(a.clmcount) as totclaimcount, sum(a.totloss)as totincurred 
from 
(select 1 as clmcount,clm_nbr,claim_key,clm_status,sum(loss_amt) as totloss   
from loss,claim,location 
where 
claim.ref_location = location.location_key and 
claim.claim_key = loss.loss_claim and 
loss_date < DATEADD(d,-1,@everyquarterly)  and 
loss_type = 'R' and 
location.loc1 = 1001 and 
clm_injdate between @beginjurydate and @endinjurydate
group by clm_nbr,claim_key,clm_status 
) as a 

End

Any help would be appreciated...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-14 : 07:44:51

A couple of comments here:
- I usually only count 4 quarters in a year. Somehow you have managed 5. Strange.
- Your fk in dbo.loss points to a column in dbo.claim that doesn't exist. I assume that's a type-o.
- You've included ddl for a locations table, but you've given no business rules for that. I've left it out of my solution.
- You've haven't fully explained how you want to handle the begin and end. Do you want to include claims on the enddate or not?

Taking all that into consideration and making assumptions, I have come up with this.


select
    dateadd(mm,((datepart(mm,c.clm_injdate)-1)/3)*3,dateadd(yy,datediff(yy,0,c.clm_injdate),0)) as 'Date',
    count(*) as 'Nbr of claims',
    sum(l.los_amt) as 'Total loss_amt'
from
    dbo.claim c
    inner join dbo.loss l
        on c.clm_key = l.loss_claim
where
    c.clm_injdate >= @begininjurydate and
    c.clm_injdate < @endinjurydate + 1
group by
    dateadd(mm,((datepart(mm,c.clm_injdate)-1)/3)*3,dateadd(yy,datediff(yy,0,c.clm_injdate),0))

Jay White
{0}

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-15 : 14:48:31

Maybe it is better

select
case datepart(mm,c.clm_injdate)
when 0 then '01/01/???'
...
else '12/31/???' ---- the '5th' quarter
end,
...
group by datepart(mm,c.clm_injdate) % 4 WITH ROLLUP

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-15 : 17:47:07

Thank you for all your responses.
This might be a similar question as above.
I have a query that produce the report that looks like:


Asofdate    the number of claims      Total loss
------------------------------------------------
10/13/2001      1032                 2343456.77

---------------------------------------------------------
Here is the DDL


CREATE TABLE [dbo].[claim] (
[clm_key] [varchar] (50) NOT NULL primary key,
[clm_nbr] [varchar] (50) not null ,
[clm_status] [char] (10) Not null,
[clm_injdate] [datetime] (10) not null,
[ref_location] [int] not null Foreigh key references location(location_key)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[loss] (
[loss_key] [varchar] (50) NOT NULL primary key,
[loss_claim] [varchar] (50) not null foreign key references claim(clm_key) ,
[loss_amt] [decimal](18, 0) not null,
[loss_date] [datetime] not null
) 

go

CREATE TABLE [dbo].[location] (
[location_key] [int] NOT NULL primary key,
[loc1] [int] NULL ,
[loc2] [int] NULL ,
[loc3] [int] NULL 
) 
GO
-----------------------------------------------------------------

, and here is the query


Declare @asofdate datetime
set @asofdate = '20011013'

select convert ( varchar(10),@quarterly, 101) as asofdate,
      count(a.clmnbr) as totclaims, sum(a.totloss) as totloss

From 

(
        SELECT (clm_nbr) as clmnbr,
        IsNull((SELECT SUM(loss_amt) FROM loss WHERE        
                 claim.clm_key=loss.loss_claim AND
	          loss.loss_type='R' AND
	          loss_date < = @asofdate),0) AS totloss

FROM claim, location

WHERE claim.ref_location=location.location_key and
      location.ref_loc1=3261 and
      claim.clm_key IN (SELECT loss.loss_claim 
	FROM loss
	WHERE loss_claim=clm_key AND
	 loss.loss_type = 'R'  and
	 loss.loss_date <= @quarterly)
and clm_injdate between  '20010221' and '20010731'

) a

----------------------------------------------------

I would like to create a report that produce the total number of claims and total loss amount based on asofdate(by every 3 months period)until @asofdate <= today.
The report should looks like below.

Asofdate number_of_claims Total_loss_ amount
--------------------------------------------------
10/13/01 1032 2343456.77
01/13/02 1500 3672681.65
04/13/03 1700 6782687.43
:
:
:
10/13/03 2300 113725265.51

Hope I explained clearly this time...

Thanks
Jung

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-15 : 17:52:14

--@quarterly need to be changed to @asofdate

Declare @asofdate datetime
set @asofdate = '20011013'

select convert ( varchar(10),@asofdate, 101) as asofdate,
count(a.clmnbr) as totclaims, sum(a.totloss) as totloss

From

(
SELECT (clm_nbr) as clmnbr,
IsNull((SELECT SUM(loss_amt) FROM loss WHERE
claim.clm_key=loss.loss_claim AND
loss.loss_type='R' AND
loss_date < = @asofdate),0) AS totloss

FROM claim, location

WHERE claim.ref_location=location.location_key and
location.ref_loc1=3261 and
claim.clm_key IN (SELECT loss.loss_claim
FROM loss
WHERE loss_claim=clm_key AND
loss.loss_type = 'R' and
loss.loss_date <= @asofdate)
and clm_injdate between '20010221' and '20010731'

) a

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-16 : 05:19:39

Not clearly seen from the above whether or not @asofdate can
only be within any of Quarter First months (Jan, Apr, Jul, Oct).
If not and e.g. @asofdate='20021111' then in what month the
next quarter begins - on January 11th or on February 11th?

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-16 : 10:51:03

Stoad! you're right. I shoud have explained more clearly.
@asofdate can be any date of months. But, the next @asofdate should be within any of First Months in Quarter (01/01, 04/01, 07/01, 10/01 -- I usually use the begining date of months)

If @asofdate = '20021111' , then the next @asofdate should begins on 01/01/2003 and next one 04/01/2003 and next one 07/01/03, and next 10/01/03,....

Until @asofdate <= Today's date

Thanks

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-16 : 14:07:48

DDL:
create table jung (dt datetime, k int)


dt            k           
------------- ----------- 
10/13/2002    1
10/22/2002    1
11/13/2002    1
12/29/2002    1
01/11/2003    1
01/13/2003    2
02/13/2003    2
04/07/2003    2
04/15/2003    2
07/12/2003    2
07/13/2003    2

Query:
declare @d datetime set @d='20021013'
select min(dt) mindt, sum(k) kk
from jung
group by datediff(qq, @d-day(@d)+1, dt-day(@d)+1)

Its result:


mindt                       kk          
--------------------------- ----------- 
2002-10-13 00:00:00.000     5
2003-01-13 00:00:00.000     6
2003-04-15 00:00:00.000     4
2003-07-13 00:00:00.000     2

Is it correct? What must be changed?

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-10-16 : 15:42:35

The result should be..


Asofdate      Number of claims    total loss
--------------------------------------------
2002-10-13          
2003-01-01       
2003-04-01
2003-07-01
2003-10-01

I just want to find out the way that the @asofdate can be dynamically generated inside of the query,so every time @asofdate change( every quarterly), the number of claims and the total loss amount will change as well.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-16 : 16:41:33

declare @d datetime set @d='20021013'

select
case datediff(qq,@d,dt) when 0 then convert(varchar(8),@d,1)
else
right('0'+cast(3*datepart(qq, min(dt))-2 as varchar(2)),2)+'/01/'+
right(cast(year(min(dt)) as varchar(4)),2)
end mindt,
sum(k) kk
from jung where dt>=@d
group by datediff(qq, @d, dt)

Result:


mindt      kk          
--------   ----------- 
10/13/02   4
01/01/03   5
04/01/03   4
07/01/03   4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources