Question

SqlDev12 on Thu, 11 Sep 2014 17:36:37


Hi All,

Below is my query which would give me all the claims which are open for 19 days for today. But i want to change my query such that it should return me the claims open for today where today will be the 19th business day( it means exclude weekends or any public holidays).

SELECT						
									CLAIM_ID						AS ClaimId,
									ClaimNumber						AS ClaimNumber,
									ClaimOpenDate					AS ReportedDate,
									HS_DW_RV_CLAIMS.LossDate		AS LossDate,
									LossType						AS LossType,
									INTEGRATOR						AS CurrentAdjuster
  FROM								[CLAIMS].[dbo].[CLAIMS_TIMESTAMPS]
  Inner Join 
									[CLAIMS].[dbo].[HS_DW_RV_CLAIMS]				
									ON   CLAIMS_TIMESTAMPS.CLAIM_NUMBER =  HS_DW_RV_CLAIMS.ClaimNumber
 where								ClaimStatus IN ('Open')
 and								(DATEDIFF(DAY,  ClaimOpenDate, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))) = 19

Order By							CLAIM_ID Asc

Can someone please help me with any ideas on this?

Thanks.


Replies

Tom Phillips on Thu, 11 Sep 2014 18:00:57


You should use a calendar table

Please see:

http://www.brianshowalter.com/calendar_tables

cnk_gr on Thu, 11 Sep 2014 18:03:08


You should use Calendar table keeping all the dates with a flag showing if it's holiday or weekend.

Then you can easily adjust your query to track how many working days have passed (for instance with a user defined function) about like this:

SELECT @WorkingDaysPassed = COUNT(*) FROM CalendarDays WHERE CalendarDate >= @DateOpened AND IsWorkingDay = 1 AND CalendarDate >= GETDATE()

Patrick Hurst on Thu, 11 Sep 2014 19:02:30


As already mentioned, you should use a calendar table.

Here's a sample of how to do it:

DECLARE @calendar TABLE (date DATE, holiday BIT, weekend BIT)

DECLARE @i INT = 0
SET NOCOUNT ON 
WHILE @i < 50
BEGIN
 INSERT INTO @calendar (date, holiday, weekend)
 VALUES  (DATEADD(DAY,@i,'2014-01-01'),0,CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,@i,'2014-01-01')) IN (1,7) THEN 1 ELSE 0 END)
 SET @i = @i + 1
END 
UPDATE @calendar SET holiday = 1 WHERE date = '2014-01-20'
SET NOCOUNT OFF

SELECT max(date)
  FROM (
        SELECT TOP 19 * 
          FROM @calendar
         WHERE date > '2014-01-02'
           AND holiday <> 1
           AND weekend <> 1
	   ) a

--CELKO-- on Thu, 11 Sep 2014 21:45:06



The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY, 
 julian_business_nbr INTEGER NOT NULL, 
 ...);

INSERT INTO Calendar 
VALUES ('2007-04-05', 42), 
 ('2007-04-06', 43), -- good Friday 
 ('2007-04-07', 43), 
 ('2007-04-08', 43), -- Easter Sunday 
 ('2007-04-09', 44), 
 ('2007-04-10', 45); --Tuesday


To compute the business days from Thursday of this week to next
 Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
  FROM Calendar AS C1, Calendar AS C2
 WHERE C1.cal_date = '2007-04-05',
   AND C2.cal_date = '2007-04-10'; 

Kalman Toth on Thu, 11 Sep 2014 23:41:42


Dan's calendar table blog:

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

SqlDev12 on Mon, 15 Sep 2014 14:54:14


Hi All,

Sorry i was busy last week with other project but now i am back on this one. So, as you guys have suggested i have created a calendar table using this link

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

Now, I am trying to get a query which must satisfy the following conditions :-

1) Claims open for 19 business days where sum(losspaidamt) = '0.00'

2) Claims open for 45 and 90 business days where sum(losspaidamt) = '0.00' or CLAIMS_FOLLOWUP_LETTER_COMPLIANCE.Total_Leter_Sent = 0.

My query must satisfy both the conditions.Below is what i have written so far

SELECT						
									CLAIM_ID						AS ClaimId,
									HS_DW_RV_CLAIMS.ClaimOpenDate	AS ReportedDate,
									HS_DW_RV_CLAIMS.LossDate		AS LossDate,
									LossType						AS LossType,
									INTEGRATOR						AS CurrentAdjuster,
									DATEDIFF(DAY,  HS_DW_RV_CLAIMS.ClaimOpenDate, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AS NoOfDaysClaimOpen,
									SUM(LossPaidAmt)				AS PaymentAmount,
									MAX(TransactionDate)			AS LastPaymentDate,
									SUM(LossRsvAmt)					AS OutstandingReserve
  FROM								[dbo].[CLAIMS_TIMESTAMPS]
  Inner Join 
									[dbo].[HS_DW_RV_CLAIMS]				
									ON   CLAIMS_TIMESTAMPS.CLAIM_NUMBER =  HS_DW_RV_CLAIMS.ClaimNumber
  Inner Join
									[dbo].[HS_DW_FT_LossTransaction]		
									ON	  HS_DW_FT_LossTransaction.ClaimKey = HS_DW_RV_CLAIMS.ClaimKey
  Inner Join						[dbo].[Calendar]
									ON	   CONVERT( date, HS_DW_RV_CLAIMS.ClaimOpenDate)    = Calendar.CalendarDate
  Left Outer Join					[CLAIMS].[dbo].[CLAIMS_FOLLOWUP_LETTER_COMPLIANCE]
									ON HS_DW_RV_CLAIMS.ClaimNumber    = CLAIMS_FOLLOWUP_LETTER_COMPLIANCE.Claim_number
 where								ClaimStatus IN ('Open')
 and								BusinessDay = 1 and CalendarYear = 2014
 Group By							CLAIM_ID,
									ClaimNumber,
									HS_DW_RV_CLAIMS.ClaimOpenDate,
									HS_DW_RV_CLAIMS.LossDate,
									LossType,
									INTEGRATOR 
Order By							CLAIM_ID Asc

So, if  we look in the where clause i have a condition for businessday = 1 in my calendar table i have businessday column.So, by using that i need to achieve it.

Can someone please help me with this?

If i am still unclear or if you have any questions please let me know.

Thanks

pituach on Mon, 15 Sep 2014 15:00:50


Hi,

Can you post a query to create your original table (not the calender table) and a query to insert some sample data? This will help to test your query and our solution.

SqlDev12 on Mon, 15 Sep 2014 15:18:33


Hi Pituach,

I have like 4 tables joined in this so i am not sure which table you are asking for, but i can post the sample data which my current query is returning which is below

ClaimId   ReportedDate   LossDate LossType CurrentAdjuster NoOfDaysClaimOpen PaymentAmount LastPaymentDate OutstandingReserve
1409746 1/2/2014 12/31/2013 Water Julia Evans 256 0 0 0
1409751 1/2/2014 12/31/2013 Water Renee Robbins 256 0 0 0
1409755 1/2/2014 12/31/2013 Fire Daniel Sector 256 0 0 0
1409759 1/2/2014 1/1/2014 Smoke Mandy Zucchero 256 0 0 0
1409768 1/2/2014 5/16/2013 Injuries Glenette Ford-Ovins 256 0 0 0

Now if we see the number of days open its 256 in this sample dataset , and i want to just have 19 days withoutpayment and 45,90 days without payment or any letter sent out.

I hope this helps.

Thanks

pituach on Sat, 27 Sep 2014 07:32:13


Hi,

This is text and not sample data :-(

Any case, without the tables we can not query any data. So I will ask again: please post a create table query for all relevant tables and a queries to insert the sample data.