Category: sql server transactsql
SqlDev12 on Thu, 11 Sep 2014 17:36:37
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?
Tom Phillips on Thu, 11 Sep 2014 18:00:57
You should use a calendar table
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-08', 43), -- Easter Sunday
('2007-04-10', 45); --Tuesday
To compute the business days from Thursday of this week to next
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:
SqlDev12 on Mon, 15 Sep 2014 14:54:14
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
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.
pituach on Mon, 15 Sep 2014 15:00:50
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
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
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.
pituach on Sat, 27 Sep 2014 07:32:13
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.