I am teaching myself MS-SQL and I am trying to find different ways to find the Count of Paid and Unpaid Claims for 2012 grouped by Region from these 3 tables. If there is a returned date, the claim is unpaid if the returned date is null then the claim is paid.
I will attach the code I have ran, but I am not sure if there are better ways to do it.
Thanks.
Here is the code:
SET dateformat ymd;
CREATE TABLE Claims
(
ClaimID INT,
SubID INT,
[Claim Date] DATETIME
);
CREATE TABLE Phoneship
(
ClaimID INT,
[Shipping Number] INT,
[Claim Date] DATETIME,
[Ship Date] DATETIME,
[Returned Date] DATETIME
);
CREATE TABLE Enrollment
(
SubID INT,
Enrollment_Date DATETIME,
Channel NVARCHAR(255),
Region NVARCHAR(255),
Status FLOAT,
Drop_Date DATETIME
);
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (102,
201,
'2011-10-13 00:00:00',
'2011-10-14 00:00:00',
NULL);
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (103,
202,
'2011-11-02 00:00:00',
'2011-11-03 00:00:00',
'2011-11-20 00:00:00');
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (103,
203,
'2011-11-02 00:00:00',
'2011-11-22 00:00:00',
NULL);
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (105,
204,
'2012-01-16 00:00:00',
'2012-01-17 00:00:00',
NULL);
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (106,
205,
'2012-02-15 00:00:00',
'2012-02-16 00:00:00',
'2012-02-26 00:00:00');
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (106,
206,
'2012-02-15 00:00:00',
'2012-02-27 00:00:00',
'2012-03-06 00:00:00');
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (107,
207,
'2012-03-12 00:00:00',
'2012-03-13 00:00:00',
NULL);
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (108,
208,
'2012-05-11 00:00:00',
'2012-05-12 00:00:00',
NULL);
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (109,
209,
'2012-05-13 00:00:00',
'2012-05-14 00:00:00',
'2012-05-28 00:00:00');
INSERT INTO [Phoneship]
([ClaimID],
[Shipping Number],
[Claim Date],
[Ship Date],
[Returned Date])
VALUES (109,
210,
'2012-05-13 00:00:00',
'2012-05-30 00:00:00',
NULL);
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (101,
12345678,
'2011-03-06 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (102,
12347190,
'2011-10-13 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (103,
12348723,
'2011-11-02 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (104,
12349745,
'2011-11-09 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (105,
12347190,
'2012-01-16 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (106,
12349234,
'2012-02-15 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (107,
12350767,
'2012-03-12 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (108,
12350256,
'2012-05-11 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (109,
12347701,
'2012-05-13 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (110,
12350256,
'2012-05-15 00:00:00');
INSERT INTO [Claims]
([ClaimID],
[SubID],
[Claim Date])
VALUES (111,
12350767,
'2012-06-30 00:00:00');
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12345678,
'2011-01-05 00:00:00',
'Retail',
'Southeast',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12346178,
'2011-03-13 00:00:00',
'Indirect Dealers',
'West',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12346679,
'2011-05-19 00:00:00',
'Indirect Dealers',
'Southeast',
0,
'2012-03-15 00:00:00');
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12347190,
'2011-07-25 00:00:00',
'Retail',
'Northeast',
0,
'2012-05-21 00:00:00');
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12347701,
'2011-08-14 00:00:00',
'Indirect Dealers',
'West',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12348212,
'2011-09-30 00:00:00',
'Retail',
'West',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12348723,
'2011-10-20 00:00:00',
'Retail',
'Southeast',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12349234,
'2012-01-06 00:00:00',
'Indirect Dealers',
'West',
0,
'2012-02-14 00:00:00');
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12349745,
'2012-01-26 00:00:00',
'Retail',
'Northeast',
0,
'2012-04-15 00:00:00');
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12350256,
'2012-02-11 00:00:00',
'Retail',
'Southeast',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12350767,
'2012-03-02 00:00:00',
'Indirect Dealers',
'West',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12351278,
'2012-04-18 00:00:00',
'Retail',
'Midwest',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12351789,
'2012-05-08 00:00:00',
'Indirect Dealers',
'West',
0,
'2012-07-04 00:00:00');
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12352300,
'2012-06-24 00:00:00',
'Retail',
'Midwest',
1,
NULL);
INSERT INTO [Enrollment]
([SubID],
[Enrollment_Date],
[Channel],
[Region],
[Status],
[Drop_Date])
VALUES (12352811,
'2012-06-25 00:00:00',
'Retail',
'Southeast',
1,
NULL);
And Query1
SELECT Count(ClaimID) AS 'Paid Claim',
(SELECT Count(ClaimID)
FROM dbo.phoneship
WHERE [returned date] IS NOT NULL) AS 'Unpaid Claim'
FROM dbo.Phoneship
WHERE [Returned Date] IS NULL
GROUP BY claimid
Query2
SELECT Count(*) AS 'Paid Claims',
(SELECT Count(*)
FROM dbo.Phoneship
WHERE [Returned Date] IS NOT NULL) AS 'Unpaid Claims'
FROM dbo.Phoneship
WHERE [Returned Date] IS NULL;
Query3
Select Distinct(C.[Shipping Number]), Count(C.ClaimID) AS 'COUNT ClaimID',
A.Region, A.SubID
From dbo.HSEnrollment A
Inner Join dbo.Claims B On A.SubId = B.SubId
Inner Join dbo.Phoneship C On B.ClaimID = C.ClaimID
Where C.[Returned Date] IS NULL
Group By A.Region, A.Subid, C.ClaimID, C.[Shipping Number] Order By A.Region
