Complex T-SQL Payroll Query From 3 Tables

Personally I feel TEMP tables are WAY over-used and a crutch.

I also wouldn't ever pull back individual result sets to the app layer then generate the report there.

I would look at a single query that joins all three tables and return a single result set to the .Net code.

DB Servers are designed to manipulate data.  That is one of the reasons you use databases in the first place.

@slightwv,

I understand what you're stating, and I do agree. The absolute biggest issue with this is overlapping billable entries. Currently the aggregation is being performed on the back-end and causing a lot of issue when running larger reports such as 6 months worth. I need to figure out a way to aggregate this information from the SQL Server side as the processing will be much faster. However, I do not understand how to accomplish this. I'm not able to find any good tutorials on how to accomplish this.

I'm not sure what aggregation is done.

I looked at the stored procedure you posted.  Sorry but  I don't know your system and really don't know enough SQL Server to be even a little dangerous so really cannot offer advice on how to properly tune it.  I do see a LOT of left joins.  I hope you've looked at them and made sure the all need to be left joins?

I did see three left joins to tbl_OverrideRateTypes that seems odd to me.  Not sure it that is a problem worth looking at or not.

I'm guessing you've looked at the execution plan to see where the problem tables might be?

Focus on the large ones doing full table scans.

Instead of trying to tune a massive query, I try to focus on the one or three tables causing the most issues and come up with small test SQL to tune those pieces, then incorporate them into the larger query to see if they help.

This Article was written by one of our top Oracle Experts and is sort of Oracle centric in the SQL but as posted in the article, the steps and thought processes transcend databases in general:
https://www.experts-exchange.com/articles/9869/How-to-Unravel-a-Tricky-Query.html

There is no aggregation in that stored procedure

But there is a mistake in how you define a date range:

DECLARE @RealEnd DATETIME = DateAdd(second, 59, DateAdd(minute, 59, DateAdd(hour, 23, @EndDate)))
...
WHERE
      (@StartDate IS NULL OR @EndDate IS NULL)
      OR
      (tbl_Claims.StartTime >= @StartDate AND tbl_Claims.EndTime <= @RealEnd)

There is a fallacy committed in the date range however, a datetime is precise to sub-second units and a datetime2 to even finer units. 23:59:59 IS NOT THE END OF A DAY

Just do it this way:


WHERE
      (@StartDate IS NULL OR @EndDate IS NULL)
      OR
      ( tbl_Claims.StartTime >= @StartDate AND tbl_Claims.EndTime < dateadd(dd,1,@StartDate) )

NOTE the use of LESS THAN

I've done some refactoring a database

So you understand how the tables should be joined together?

@PortletPaul

I think I've confused people...Yes, I understand how the tables should be joined together and I know there there isn't any aggregation being done in the current SP. My goal is to ultimately remove the aggregation from the VB.NET code and have SQL Server to the heavy lifting. However, there would be a few columns that would need concatenated as well. Columns such as the Notes column...

I marked your modification of the WHERE clause as helpful as it did add valuable information to my question(s) and will be applying that as well. I thought about using a CROSS JOIN, however, I cannot because I'm using a where clause. I'm still thinking that I should either create 3 separate stored procedures or use a temp table.

My expected output would be identical to the attached. The aggregation of overlapping entries is more of a nice to have than a requirement. However, managers will need to be able to run reports for 6 months at a time and currently they can't as the application times out due to there being 100k+ rows and the aggregation is being done in VB.NET currently. My primary focus is getting all the information into a single output though.

Sample-Output file
Sample-Output.xlsx

How is table "tbl_Claims" clustered?  Likely on an identity (the "always cluster on identity" myth destroys performance more than anything else in SQL Server).  

Based on this code, and hopefully this is general usage, it probably should be clustered on ( StartTime ) or (StartTime, EndTime).

The WHERE probably needs tweaking too.  For now I'll leave out any datetime "<" rather than "<=" issues:

WHERE
      ((@StartDate IS NULL OR tbl_Claims.StartTime >= @StartDate)
         AND
         (@EndDate IS NULL OR tbl_Claims.EndTime <= @RealEnd))
      AND
      tbl_ClaimDetails.Void = 0
      AND       
      tbl_Users.EmployeeID IS NOT NULL

Also, we'll need to look at forcing the (MAX) columns out of the table row and to separate storage.  Very technical, but I can give you the commands to do that.

Most definitely need to strongly consider -- i.e. do it! -- using data compression to speed up processing.  I suspect you could save quite a bit of storage, and thus load time, on your large tables here.

Finally, the other JOINs can also be tweaked, but I suspect they're not a big issue.

@ScottPletcher

Thank you for the very useful information. You are correct in that all JOINs are joined based off of an identity column rather than something else. I'm trying to study for my MCSA...proving difficult with all that's going on, so I'd love to work with you and learn how to use compression and change storage for the MAX columns.
I did change data types on the Expense and Hours tables so that their more appropriate...such as using date vs datetime for certain columnsas well as columns that do not need a varchar(max), I changed to something more reasonable.
As a note before proceeding, the varchar(max) columns are for claim notes so a user can enter notes regarding a claim entry that gets billed to the state(s).

I remain confused. You refer to aggregation (currently being performed in VB.NET) and recently mention  "overlapping" and supply us with 2 rows of the current output, which I presume do not display this "overlapping". So I still don't know what needs to be aggregated.

The elephant  on this page is that there is no raw data, so there is nothing that demonstrates what the actual problem/s is/are.

1. sample data (per table, not final result) is just that: a small set of rows. Ideally as inserts, or as parsable text.
2. expected result (manually prepared) based on the sample provided

those 2 things will clarify what you are seeking to do, and experience shows that providing those 2 things reduces the time to solution - a lot.

Short, Self Contained, Correct (Compilable), Example
If you are having a problem with some code and seeking help, preparing a Short, Self Contained, Correct Example (SSCCE) is very useful. But what is an SSCCE?

It is all in the name, really. Take a look at each part. The version prepared for others to see should be:

Short (Small) - Minimise bandwidth for the example, do not bore the audience.
Self Contained - Ensure everything is included, ready to go.
Correct - Copy, paste, (compile,) see is the aim.
Example - Displays the problem we are trying to solve.

http://www.sscce.org/

@PortletPaul
Ok, let me try this again. I've attached an Excel file that contains 4 worksheets. Each worksheet is named after the table they represent. The 4th tab is a sample expected result set. The T-SQL to create the tables I believe has already been attached.

To clarify the aggregate/overlap statements. In the claims table, there are start and end times. If a provider is providing services to more than one individual at a time, the start and end times will overlap. Thus needing to be combined into a single claim entry. So, if Mary was providing services to John and Jane and started to provide services to John on 5-20-2018 at 0900 and picked up Jane on 5-20-2018 at 0930, then dropped Jane off at 12:00 and dropped John of at 11:30. The claim should be combined so that the time is from 0900 - 12:00 and the hours worked would then be 3 hours vs paying the employee for 2.5 hours for John and 2.5 hours for Jane. The Claim Note for each claim should then be combined so those entries are displayed as a single claim. Hopefully that helps a bit.

Furthermore, the aggregation/overlap isn't being done with T-SQL but VB.NET. There isn't any functionality for that inside the stored procedure. To me, this is a very complex task.

Sample Data
Sample-Data-Plus-Results.xlsx

We should be able to arrive at that expected result with the sample data. Is that correct?

You only have expenses of 25 in the result by there is  161.11 in the supplied data

data:
+-------------------------+-------------------------+
|        StartTime        |         EndTime         |
+-------------------------+-------------------------+
| 2012-05-19 00:00:00.000 | 2012-05-19 23:59:00.000 |
| 2012-05-20 00:00:00.000 | 2012-05-20 23:59:00.000 |
| 2012-05-26 00:00:00.000 | 2012-05-26 23:59:00.000 |
| 2012-05-27 00:00:00.000 | 2012-05-27 23:59:00.000 |
| 2012-05-01 00:00:00.000 | 2012-05-01 23:59:00.000 |
| 2012-05-02 00:00:00.000 | 2012-05-02 23:59:00.000 |
| 2012-05-03 00:00:00.000 | 2012-05-03 23:59:00.000 |
| 2012-05-07 00:00:00.000 | 2012-05-07 23:59:00.000 |
| 2012-05-08 00:00:00.000 | 2012-05-08 23:59:00.000 |
| 2012-05-09 00:00:00.000 | 2012-05-09 23:59:00.000 |
+-------------------------+-------------------------+

result
+-------------------------+-------------------------+
|        StartTime        |         EndTime         |
+-------------------------+-------------------------+
| 2012-05-19 00:00:00.000 | 2012-05-19 23:00:00.000 |
| NULL                    | NULL                    |
+-------------------------+-------------------------+

Select allOpen in new window

Where does 2012-05-19 23:00:00.000 come from?

@PortletPaul

The sample expected result was just that...a manually created result so the expense amount isn't 100% of what's in the expense sample table. So, when the report is ran, an individual will have multiple claim entries and multiple expense entries. The expense amount is not a complete sum of expenses. It's the amount that the user paid out for that day of service.

The start and end times are entered from the front end. These times reflect when services were started and when services stopped.

>>If a provider is providing services to more than one individual at a time, the start and end times will overlap.

I don't see any of that in your sample data.  To make sure we account for it, can you add to your test case and expected results.

>>The sample expected result was just that...

I would really like 100% accurate results.  We will set up a test based on your provided sample data to produce the results you provide.  Anything else is a complete guess.  How will we know if we have it if we don't have the results you want?

Copy that. I'll put together better sample cases and results. I'll do this today and post later today.

I apologize for the delay. The Christmas holiday kept me busy. I've re-attached the Excel file and modified the worksheets accordingly. I've accounted for a claim that overlaps with time as well as modified all dates so they reflect a 2018 date. I've also modified the expense information on the expected results worksheet to match an expense in the expense worksheet. The overlapping items and the updated expense have been highlighted in yellow so they stand out and easier to locate/follow.

Modified Sample Data with Overlapping Times
Sample-Data-Plus-Results.xlsx

No problem on the delay.

I'm not seeing how the three tables join together.  Can you provide that information for me?

On the Expected results I don't see how you generated the OutcomeNotes values.  Seems to be a partial copy of the claims.notes columns from two different claims.

I'm also not seeing much aggregation in the expected results except for the Notes.

I saw your post before it was deleted so I'll comment on what I remember.

Now sure how the current output would help.  What I'm planning on doinf is creating tables based on the columns in the three worksheets: Claims, Claim-Details and Expenses.  Load the sample data into them. The write SQL to generate your expscted results.

Many times trying to explain requirments doesn't help.  There isn't enough time for us to understand your system at the necessary levels.  The raw data and results based on it is normally enough.

>>They only way that the tables are joined is by employee id

I don't see the EmployeeID column in the Claims and Claim-Details worksheets.

>>notes column would be concatenated.

For the notes column in the expected results tab it looks like Notes were copied from two different claims: 263698 and 263699.  Not seeing how that happens.

>>I mean the start and end times would be adjusted to the earliest start time and latest end time plus the claim.

So there will never be a case where John was picked up at 8AM and dropped off at 9AM.  Driver runs persona errands and picks up Mary at 11AM and drops her off at 12PM.  2 hours worked not 4.

@slightwv

I think the DDL statements for the tables were included in the original post. If not, I can provide those as well. You'll have to excuse some of this as this wasn't my project, I've inherited it. The original design had all these entries in a single database table among others. I'm trying to make things better, however, I can't do it all in a week :-).

>>For the notes column in the expected results tab it looks like Notes were copied from two different claims: 263698 and 263699.  Not seeing how that happens

Correct, I did copy the notes from 2 different claims. Which is what the concatenation does in VB.NET currently. The application is for home/community based services. So, a provider will enter 2 separate claim entries in order to bill the state correctly. However, for payroll, the claim entries would modify the start and end times to reflect the earliest start time and the latest stop time between claims, along with the provider notes for those claims.

>>I don't see the EmployeeID column in the Claims and Claim-Details worksheets.
Correct, the column there is the ProvidedBy. However, I'm attaching an updated file which I've correct the ProvidedBy column to now read EmployeeID (I'll have to make additional modifications to the DB once we get this stored proc squared away).

>>So there will never be a case where John was picked up at 8AM and dropped off at 9AM.  Driver runs persona errands and picks up Mary at 11AM and drops her off at 12PM.  2 hours worked not 4.

They may very well do that and there's currently no way to determine if they did or didn't. However, the providers only put in time that they are providing services, meaning have individuals with them.

Modified-Sample-Data
Sample-Data-Plus-Results.xlsx

>>I think the DDL statements for the tables were included in the original post
I must be missing something.  

I see Create-Hours-Table.sql with  CREATE TABLE [dbo].[tbl_Hours](      [TimeCardID] ...

I don't see any of that in Sample-Data-Plus-Results.xlsx

Anyway, I don't need the actual DDL.  I'm going to go by the spreadsheet.  For example:  I'll create a Claims table based on the Claims sheet, etc...

>>Correct, I did copy the notes from 2 different claims. Which is what the concatenation does in VB.NET currently

But what is in expected results isn't a complete copy of the two?  I don't see anything about Jackie Chan in the results.

>>So, a provider will enter 2 separate claim entries in order to bill the state correctly

So how do I know what claims roll up into a single result entry?

For example in the Expected results I see: CL-263698
How do I know what claims from the Claims sheet get pulled in?


>>However, I'm attaching an updated file which I've correct the ProvidedBy column to now read EmployeeID

I need nothing in the Claim-Details sheet?  I'm not seeing any of that data in the result sheet but I could be over looking it.  If it is there, I'll need how the details table joins to the other ones.

@slightwv

>>For example in the Expected results I see: CL-263698
How do I know what claims from the Claims sheet get pulled in?

The claims get pulled in based upon the start and end time along with the employee id. In the case of the sample data, claim 263698 and 263699 overlap. They overlap because of the Start and End times. One starts at 0800 and the other starts at 0830 for employee 99999. In the Excel sheet, change column I text from ProvidedBy to EmployeeID. The employee id is how we relate claims to a particular provider.

>>So how do I know what claims roll up into a single result entry?

I forgot to add that into the Excel sheet Expected-results tab. We could add another column ClaimOverlap and place the ClaimID numbers in that column if they overlap. This way, managers can also see what claims are overlapping.

>> They overlap because of the Start and End times.

So no one will ever work past midnight or start before midnight?  I can go by the physical date?  So ALL claims on 01/01/2018 for an employee are aggregated into one.

Forgot to confirm:
I need nothing in the Claim-Details sheet?

>>Forgot to confirm:
I need nothing in the Claim-Details sheet?

We will need the claim details table to determine if the claim is voided or not. We want to exclude all voided entries.

>>So no one will ever work past midnight or start before midnight?  I can go by the physical date?  So ALL claims on 01/01/2018 for an employee are aggregated into one.

Not at this time, no one will work past midnight or start before midnight. We must use the start and end time as well as the date. Not every claim for 01/01/2018 for employee 1 should overlap. Think of it like this. A provider is providing a service that has a 2:1 ration, meaning 2 individuals per 1 provider. If the provider has 2 individuals during that 2:1 service, then those claims will get combined into 1 claim.
Jane D. Is providing services to John Smith from 0900-1100 and at the same time provides services to John Doe from 0930 - 1130 on 01/01/2018. Now, Jane D. provides services to Mickey Mouse from 1400 - 1630 on 01/01/2018. The claim for Mickey Mouse should NOT be included in the aggregation. Only the claims for John Smith and John Doe.

>>We will need the claim details table to determine if the claim is voided or no

Then how does that join the claims table in your sample data?    I assume a void of 1 is not included and 0's are included?

>>Not every claim for 01/01/2018 for employee 1 should overlap

OK.  Then I cannot use the MIN and MAX dates.  :(  That would have made it too easy.

>> The claim for Mickey Mouse should NOT be included in the aggregation.

So there will be two report rows for Jane D?    This is sort of what I was asking when I asked about personal errands above.  If there is a time gap for the day, you get independent rows.  As long as the time is contagious, no matter how many entries, they are aggregated.


I don't have any access to SQL Server until evenings my time (EST).  I'm also not sure I can figure out the time overlap pieces in SQL Server SO, it might take a while or we may have to wait for Paul or Scott since they know SQL Server much better than I do.

@slightwv

The Claim Details table is joined by the claim details id number. The claim details id number doesn't have to be included in the output though.

Yeah, if it's too easy, probably can't use it :-(

>>So there will be two report rows for Jane D?

Correct. Sorry I misconstrued what you were asking. If there is a time gap, we will get independent rows.

>>I don't have any access to SQL Server until evenings my time (EST).  I'm also not sure I can figure out the time overlap pieces in SQL Server SO, it might take a while or we may have to wait for Paul or Scott since they know SQL Server much better than I do.

That's fine. I'm also in EST. I'll probably create 3 different stored procedures for now, as I'm on a tight deadline. However, if all of us can continue to work on this stored proc, that would be great.

>>The Claim Details table is joined by the claim details id number.

Thanks.  I completely missed that column!


I think I've exceeded my ability to post in a SQL Server Topic Area and I've asked Scott and Paul to come back and see if they can provide more SQL Server assistance.  I worked on this some last night and the path I went down likely won't port easily to SQL Server (Oracle guy trying to write SQL Server compliant SQL).

I basically converted the claim times into the actual minutes worked then you can look for continuous minutes in the day and get the total hours worked.  I can't do that in SQL that stands a chance of running in SQL Server.

To find gaps I believe the term you need is ISLANDS.  There is a LOT of information on the web on finding gaps and islands but nothing seemed like an exact fit for your requirements.

Hi. I have not tried to read all of the conversation so far, but I believe the key to this is determining "overlap" base on the StartTime and EndTime columns. In TSQL I suggest using an "apply" operator to address this. If EndTime is always present (NOT NULL) then you can use a CROSS APPLY, however I have assumed EndTime might be NULL so I have used an OUTER APPLY:

select 
     c.ClaimID, c.ProvidedBy, c.StartTime, c.EndTime, oa.ServiceStart, oa.ServiceEnd, c.ClaimDetailID, c.ServiceID, c.Duration
from claims c
outer apply (
    select min(t2.StartTime) ServiceStart, max(t2.EndTime) ServiceEnd
    from claims t2
    where t2.StartTime <= c.EndTime 
    and (t2.EndTime IS NULL OR t2.EndTime >= c.StartTime)
    ) oa
order by oa.ServiceStart, oa.ServiceEnd, c.StartTime, c.EndTime
;

Select allOpen in new window

Here, the calculated columns ServiceStart and ServiceEnd should correspond the the extremities of any contiguous block of time.

For example, I ADDED to rows to the sample claims data so that there are 2 different overlapping service periods:

+---+---------+------------+-------------------------+-------------------------+---------------+-----------+----------+
|   | ClaimID | ProvidedBy |        StartTime        |         EndTime         | ClaimDetailID | ServiceID | Duration |
+---+---------+------------+-------------------------+-------------------------+---------------+-----------+----------+
| 1 |  263698 |      99999 | 2018-05-19 08:00:00.000 | 2018-05-19 11:00:00.000 |        138283 |        61 |       12 |
| 2 |  263699 |      99999 | 2018-05-19 08:30:00.000 | 2018-05-19 12:00:00.000 |        138284 |        61 |       12 |
added 
| 3 | 3363698 |      99999 | 2018-05-19 14:00:00.000 | 2018-05-19 15:00:00.000 |        138283 |        61 |        8 |
| 4 | 4463699 |      99999 | 2018-05-19 14:30:00.000 | 2018-05-19 15:30:00.000 |        138284 |        61 |        8 |
+---+---------+------------+-------------------------+-------------------------+---------------+-----------+----------+

Select allOpen in new window

With the query seen above the results are:

+----+---------+------------+-------------------------+-------------------------+-------------------------+-------------------------+---------------+-----------+----------+
|    | ClaimID | ProvidedBy |        StartTime        |         EndTime         |      ServiceStart       |       ServiceEnd        | ClaimDetailID | ServiceID | Duration |
+----+---------+------------+-------------------------+-------------------------+-------------------------+-------------------------+---------------+-----------+----------+
|  1 |  263707 |      99999 | 2018-05-01 00:00:00.000 | 2018-05-01 23:59:00.000 | 2018-05-01 00:00:00.000 | 2018-05-01 23:59:00.000 |        138289 |        57 |        1 |
|  2 |  263708 |      99999 | 2018-05-02 00:00:00.000 | 2018-05-02 23:59:00.000 | 2018-05-02 00:00:00.000 | 2018-05-02 23:59:00.000 |        138290 |        57 |        1 |
|  3 |  263709 |      99999 | 2018-05-03 00:00:00.000 | 2018-05-03 23:59:00.000 | 2018-05-03 00:00:00.000 | 2018-05-03 23:59:00.000 |        138291 |        57 |        1 |
|  4 |  263710 |      99999 | 2018-05-07 00:00:00.000 | 2018-05-07 23:59:00.000 | 2018-05-07 00:00:00.000 | 2018-05-07 23:59:00.000 |        138292 |        57 |        1 |
|  5 |  263711 |      99999 | 2018-05-08 00:00:00.000 | 2018-05-08 23:59:00.000 | 2018-05-08 00:00:00.000 | 2018-05-08 23:59:00.000 |        138293 |        57 |        1 |
|  6 |  263712 |      99999 | 2018-05-09 00:00:00.000 | 2018-05-09 23:59:00.000 | 2018-05-09 00:00:00.000 | 2018-05-09 23:59:00.000 |        138294 |        57 |        1 |
|  7 |  263698 |      99999 | 2018-05-19 08:00:00.000 | 2018-05-19 11:00:00.000 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |        138283 |        61 |       12 |
|  8 |  263699 |      99999 | 2018-05-19 08:30:00.000 | 2018-05-19 12:00:00.000 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |        138284 |        61 |       12 |
|  9 | 3363698 |      99999 | 2018-05-19 14:00:00.000 | 2018-05-19 15:00:00.000 | 2018-05-19 14:00:00.000 | 2018-05-19 15:30:00.000 |        138283 |        61 |        8 |
| 10 | 4463699 |      99999 | 2018-05-19 14:30:00.000 | 2018-05-19 15:30:00.000 | 2018-05-19 14:00:00.000 | 2018-05-19 15:30:00.000 |        138284 |        61 |        8 |
| 11 |  263700 |      99999 | 2018-05-26 00:00:00.000 | 2018-05-26 23:59:00.000 | 2018-05-26 00:00:00.000 | 2018-05-26 23:59:00.000 |        138285 |        61 |        1 |
| 12 |  263701 |      99999 | 2018-05-27 00:00:00.000 | 2018-05-27 23:59:00.000 | 2018-05-27 00:00:00.000 | 2018-05-27 23:59:00.000 |        138286 |        61 |        1 |
+----+---------+------------+-------------------------+-------------------------+-------------------------+-------------------------+---------------+-----------+----------+

Select allOpen in new window

For all sample data, in SQL Server, please see:  https://rextester.com/WTS68608

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE Claims(
   ClaimID            INTEGER  NOT NULL PRIMARY KEY 
  ,ClaimDetailID      INTEGER  NOT NULL
  ,ServiceID          INTEGER  NOT NULL
  ,PSID               VARCHAR(4)
  ,Notes              VARCHAR(1033) NOT NULL
  ,StartTime          VARCHAR(23) NOT NULL
  ,EndTime            VARCHAR(23) NOT NULL
  ,Duration           INTEGER  NOT NULL
  ,ProvidedBy         INTEGER  NOT NULL
  ,LocationID         INTEGER  NOT NULL
  ,PatientPaidAmt     BIT  NOT NULL
  ,Billed             BIT  NOT NULL
  ,BilledAmt          NUMERIC(5,2) NOT NULL
  ,BilledDate         VARCHAR(4)
  ,BilledBy           VARCHAR(4)
  ,ClaimStatusID      VARCHAR(4)
  ,Paid               BIT 
  ,PaidAmt            NUMERIC(4,1)
  ,PaidDate           VARCHAR(23)
  ,PaidBy             INTEGER 
  ,Created            VARCHAR(23) NOT NULL
  ,CreatedBy          INTEGER  NOT NULL
  ,Modified           VARCHAR(16)
  ,ModifiedBy         INTEGER 
  ,OutcomeNotes       VARCHAR(4)
  ,GoalNotes          VARCHAR(109) NOT NULL
  ,CommunicationNotes VARCHAR(3) NOT NULL
  ,EquipmentNotes     VARCHAR(3) NOT NULL
  ,HealthNotes        VARCHAR(3) NOT NULL
  ,GOALS              VARCHAR(4)
  ,CURRENTGOALS       VARCHAR(293) NOT NULL
  ,Signatured         VARCHAR(4)
);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263698,138283,61,NULL,'Ricky woke up late. He came downstairs around 9:30 am.  He sat down and ate breakfast.  He appeared to be in a good state, not very anxious. After eating, Ricky hung out on the couch while I cleaned up the dishes.  I packed some orange slices and bottled water for our hike. Then we went to McConnell''s Mill for an extensive hike. We were in the caves and ravines, hiking along the trail for 3 hours. We snacked on the orange slices during our rests. Ricky did fantastic. He was always eager to explore the caves. Then we ate lunch and headed back home. Once home, Ricky showered and relaxed. He was spent after the hike. We watched a movie of his liking, then ate dinner. We played some Wii sports after the dishes were clean. He hung out with me until 9:30pm before going to his room.  His light was on until 11:45 and I could hear him laughing until his light was turned off. It was a good day with no signs of anger or much anxiety. He waved his hand twice when he was on the couch, but that was it.','2018-05-19 08:00:00.000','2018-05-19 11:00:00.000',12,99999,75,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-06-14 11:18:54.000',191,'2018-06-14 11:21',191,NULL,'see general notes','n/a','na/','n/a',NULL,'RICKY IS IN NEED OF 24 HOUR RESPITE SO THAT HE AND HIS PARENTS CAN HAVE A BREAK.  THERE IS ALOT OF STRESS ON THE FAMILY AT THIS TIME DUE TO A DRASTIC DECREASE IN RICKY''S SKILLS, ESPECIALLY IN THE LAST COUPLE MONTHS.RICKY IS IN NEED OF THIS SERVICE FOR RESPITE STAYS MORE THAN 2 DAYS AT A TIME.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263699,138284,61,NULL,'John slept in until 10am.  He was extremely tired when he came down for breakfast. After eating I tried to get John to go for a walk, but he said "No, I am fine".  Therefore, we hung around the house until lunch. We played Wii and watched TV. He had a couple laughing fits, but no angry outbursts. Then we ate and I was able to get him to agree to a walk around North Park. He was good for 1.5 hours before we went back home. On the walk we stopped a lot so Ricky could stare at the woods or off in the distance. Then we went home for dinner and a movie.  Ricky wanted to watch a comedy so we watched a Jackie Chan movie.  At night, Ricky showed no signs of anger or anxiety before retiring to his room for the night at 9:30om. I heard him talking and laughing at times, but he was not yelling at anyone or seem upset.','2018-05-19 08:30:00.000','2018-05-19 12:00:00.000',12,99999,75,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-06-14 11:25:35.000',191,NULL,NULL,NULL,'see general notes','n/a','n/a','n/a',NULL,'RICKY IS IN NEED OF 24 HOUR RESPITE SO THAT HE AND HIS PARENTS CAN HAVE A BREAK.  THERE IS ALOT OF STRESS ON THE FAMILY AT THIS TIME DUE TO A DRASTIC DECREASE IN RICKY''S SKILLS, ESPECIALLY IN THE LAST COUPLE MONTHS.RICKY IS IN NEED OF THIS SERVICE FOR RESPITE STAYS MORE THAN 2 DAYS AT A TIME.',NULL);

INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (3363698,138283,61,NULL,'later in the day'          ,'2018-05-19 14:00:00.000','2018-05-19 15:00:00.000',8,99999,75,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-06-14 11:18:54.000',191,'2018-06-14 11:21',191,NULL,'see general notes','n/a','na/','n/a',NULL,'RICKY IS IN NEED OF 24 HOUR RESPITE SO THAT HE AND HIS PARENTS CAN HAVE A BREAK.  THERE IS ALOT OF STRESS ON THE FAMILY AT THIS TIME DUE TO A DRASTIC DECREASE IN RICKY''S SKILLS, ESPECIALLY IN THE LAST COUPLE MONTHS.RICKY IS IN NEED OF THIS SERVICE FOR RESPITE STAYS MORE THAN 2 DAYS AT A TIME.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (4463699,138284,61,NULL,'aga- later in the same day','2018-05-19 14:30:00.000','2018-05-19 15:30:00.000',8,99999,75,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-06-14 11:25:35.000',191,NULL,NULL,NULL,'see general notes','n/a','n/a','n/a',NULL,'RICKY IS IN NEED OF 24 HOUR RESPITE SO THAT HE AND HIS PARENTS CAN HAVE A BREAK.  THERE IS ALOT OF STRESS ON THE FAMILY AT THIS TIME DUE TO A DRASTIC DECREASE IN RICKY''S SKILLS, ESPECIALLY IN THE LAST COUPLE MONTHS.RICKY IS IN NEED OF THIS SERVICE FOR RESPITE STAYS MORE THAN 2 DAYS AT A TIME.',NULL);


INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263700,138285,61,NULL,'Ricky stayed up late watching the Pittsburgh Pirates the night before so he slept in until 9:30am. He was moving a little slow, but his attitude was upbeat. After eating breakfast we went for a short 1.5 hour hike around Hartwood Acre. After the hike, which Ricky took few breaks during, we ate lunch back at the house. Then we met one of his friends at the Shaler Pool.  Ricky and his friend walked around in the pool together. Ricky would separate himself from his buddy and swim on his own, always returning through.  A few hours in the sun wore Ricky out; as we headed for home he took a nap in the car. He ate dinner then watched a movie with me. He sat on the couch and his attention drifted from his feet to the window to the TV.  He sat quietly until we went to a friend''s house to watch the UFC event. Ricky hung out with everyone, silently watching and occassionally getting up and walking across the room. He wanted to stay until 11:30.  Then we went home and he went to his room for bed. He did not even put on his light.','2018-05-26 00:00:00.000','2018-05-26 23:59:00.000',1,99999,75,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-06-14 11:34:23.000',191,'2018-06-14 11:35',191,NULL,'see general notes','n/a','n/a','n/a',NULL,'RICKY IS IN NEED OF 24 HOUR RESPITE SO THAT HE AND HIS PARENTS CAN HAVE A BREAK.  THERE IS ALOT OF STRESS ON THE FAMILY AT THIS TIME DUE TO A DRASTIC DECREASE IN RICKY''S SKILLS, ESPECIALLY IN THE LAST COUPLE MONTHS.RICKY IS IN NEED OF THIS SERVICE FOR RESPITE STAYS MORE THAN 2 DAYS AT A TIME.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263701,138286,61,NULL,'Ricky was extremely tired from his late night and busy day. He slept in until 10:30 am. When he woke up he was moving real slow. After some breakfast he had no desire to go for a walk. We had decided this was going to be a rest day so our planned trip to Kennywood would be fun. So we watched a couple of movies and played Wii for most of the day. After lunch around 2:00pm Ricky and I went for a walk up his street. Ricky was quiet and distant but he was extremely clam. He had no anger outbursts or giggle fits. Today was a laid back day in preparation for his eventful Memorial Day. After eating dinner around six Ricky and I finished watching Liar Liar. Ricky went to hed when the movie ended around 9:15pm and I didn''t hear him, but his light was on until 11:15pm','2018-05-27 00:00:00.000','2018-05-27 23:59:00.000',1,99999,75,0,1,260.1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-06-14 11:44:04.000',191,NULL,NULL,NULL,'see general notes','n/a','n/a','n/a',NULL,'RICKY IS IN NEED OF 24 HOUR RESPITE SO THAT HE AND HIS PARENTS CAN HAVE A BREAK.  THERE IS ALOT OF STRESS ON THE FAMILY AT THIS TIME DUE TO A DRASTIC DECREASE IN RICKY''S SKILLS, ESPECIALLY IN THE LAST COUPLE MONTHS.RICKY IS IN NEED OF THIS SERVICE FOR RESPITE STAYS MORE THAN 2 DAYS AT A TIME.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263707,138289,57,NULL,'Reality Orientation - current events, daily news, open discussion.','2018-05-01 00:00:00.000','2018-05-01 23:59:00.000',1,99999,78,0,1,58.39,NULL,NULL,NULL,1,55.2,'2018-08-20 13:18:17.000',191,'2018-06-14 13:57:28.000',191,NULL,NULL,NULL,'Practice for the talent show.','n/a','n/a','n/a',NULL,'This service will provide Valerie with a structured day program to assist her in increasing skills such as reality awareness, spelling, communication, and fine motor control by completing crafts, group activities and exercise classes.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263708,138290,57,NULL,'Reality Orientation - current events, daily news, open discussion.','2018-05-02 00:00:00.000','2018-05-02 23:59:00.000',1,99999,78,0,1,58.39,NULL,NULL,NULL,1,55.2,'2018-08-20 13:18:17.000',191,'2018-06-14 13:59:02.000',191,NULL,NULL,NULL,'Wacky Math Wednesday, paint props for the talent show, make a spring craft - paper flower','n/a','n/a','n/a',NULL,'This service will provide Valerie with a structured day program to assist her in increasing skills such as reality awareness, spelling, communication, and fine motor control by completing crafts, group activities and exercise classes.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263709,138291,57,NULL,'Reality Orientation - current events, daily news, open discussion.','2018-05-03 00:00:00.000','2018-05-03 23:59:00.000',1,99999,78,0,1,58.39,NULL,NULL,NULL,1,55.2,'2018-08-20 13:18:17.000',191,'2018-06-14 13:59:41.000',191,NULL,NULL,NULL,'Practice for the talent show','n/a','n/a','n/a',NULL,'This service will provide Valerie with a structured day program to assist her in increasing skills such as reality awareness, spelling, communication, and fine motor control by completing crafts, group activities and exercise classes.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263710,138292,57,NULL,'Reality Orientation - current events, daily news, open discussion.','2018-05-07 00:00:00.000','2018-05-07 23:59:00.000',1,99999,78,0,1,58.39,NULL,NULL,NULL,1,55.2,'2018-08-20 13:18:17.000',191,'2018-06-14 14:01:14.000',191,NULL,NULL,NULL,'Spanish word practice. Dance integrated exercise, paint musical instrument.','n/a','n/a','n/a',NULL,'This service will provide Valerie with a structured day program to assist her in increasing skills such as reality awareness, spelling, communication, and fine motor control by completing crafts, group activities and exercise classes.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263711,138293,57,NULL,'Reality Orientation - current events, daily news, open discussion.','2018-05-08 00:00:00.000','2018-05-08 23:59:00.000',1,99999,78,0,1,58.39,NULL,NULL,NULL,1,55.2,'2018-08-20 13:18:17.000',191,'2018-06-14 14:02:18.000',191,NULL,NULL,NULL,'Pet therapy - dog visits with Milo.  Talent show practice. social development opportunity. Activity planning.','n/a','n/a','n/a',NULL,'This service will provide Valerie with a structured day program to assist her in increasing skills such as reality awareness, spelling, communication, and fine motor control by completing crafts, group activities and exercise classes.',NULL);
INSERT INTO Claims(ClaimID,ClaimDetailID,ServiceID,PSID,Notes,StartTime,EndTime,Duration,ProvidedBy,LocationID,PatientPaidAmt,Billed,BilledAmt,BilledDate,BilledBy,ClaimStatusID,Paid,PaidAmt,PaidDate,PaidBy,Created,CreatedBy,Modified,ModifiedBy,OutcomeNotes,GoalNotes,CommunicationNotes,EquipmentNotes,HealthNotes,GOALS,CURRENTGOALS,Signatured) VALUES (263712,138294,57,NULL,'Reality Orientation - current events, daily news, open discussion.','2018-05-09 00:00:00.000','2018-05-09 23:59:00.000',1,99999,78,0,1,58.39,NULL,NULL,NULL,1,55.2,'2018-08-20 13:18:17.000',191,'2018-06-14 14:03:23.000',191,NULL,NULL,NULL,'Social development opportunity, Mothers day craft, movie day, birthday party, talent show practice.','n/a','n/a','n/a',NULL,'This service will provide Valerie with a structured day program to assist her in increasing skills such as reality awareness, spelling, communication, and fine motor control by completing crafts, group activities and exercise classes.',NULL);


CREATE TABLE ClaimDetails(
   ClaimDetailID INTEGER  NOT NULL PRIMARY KEY 
  ,ServiceTypeID INTEGER  NOT NULL
  ,BID           INTEGER  NOT NULL
  ,IndID         INTEGER  NOT NULL
  ,RecDate       VARCHAR(23) NOT NULL
  ,Notes         VARCHAR(4)
  ,Void          BIT  NOT NULL
  ,VoidDate      VARCHAR(4)
  ,VoidBy        VARCHAR(4)
  ,Approved      BIT  NOT NULL
  ,ApprovedDate  VARCHAR(23) NOT NULL
  ,ApprovedBy    INTEGER  NOT NULL
  ,Created       VARCHAR(4)
  ,CreatedBy     VARCHAR(4)
);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138283,7,35,1776,'2018-05-19 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:31:35.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138284,7,35,1776,'2018-05-19 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:31:35.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138285,7,35,1776,'2018-05-26 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:31:35.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138286,7,35,1776,'2018-05-27 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:31:35.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138289,5,35,1591,'2018-05-01 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:33:37.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138290,5,35,1591,'2018-05-02 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:33:37.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138291,5,35,1591,'2018-05-03 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:33:37.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138292,5,35,1591,'2018-05-07 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:33:37.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138293,5,35,1591,'2018-05-08 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:33:37.000',191,NULL,NULL);
INSERT INTO ClaimDetails(ClaimDetailID,ServiceTypeID,BID,IndID,RecDate,Notes,Void,VoidDate,VoidBy,Approved,ApprovedDate,ApprovedBy,Created,CreatedBy) VALUES (138294,5,35,1591,'2018-05-09 00:00:00.000',NULL,0,NULL,NULL,1,'2018-06-19 16:33:37.000',191,NULL,NULL);

CREATE TABLE expenses(
   ExpenseID          INTEGER  NOT NULL PRIMARY KEY 
  ,EmployeeID         INTEGER  NOT NULL
  ,ExpenseTypeID      INTEGER  NOT NULL
  ,ExpenseDate        VARCHAR(10) NOT NULL
  ,ExpenseAmount      NUMERIC(6,4) NOT NULL
  ,MilesDriven        INTEGER  NOT NULL
  ,MileageRate        NUMERIC(4,2) NOT NULL
  ,ExpenseNote        VARCHAR(44) NOT NULL
  ,InternalEventID    INTEGER  NOT NULL
  ,AssocService       VARCHAR(49) NOT NULL
  ,eSignature         VARCHAR(8) NOT NULL
  ,DateSubmitted      VARCHAR(16) NOT NULL
  ,ManagerVerified    BIT  NOT NULL
  ,VerifiedBy         INTEGER 
  ,VerificationDate   DATE 
  ,VerificationNote   VARCHAR(26)
  ,ManagerEditNote    VARCHAR(51)
  ,Approved           BIT  NOT NULL
  ,ApprovedBy         INTEGER 
  ,ApprovedDate       VARCHAR(16)
  ,Denied             BIT  NOT NULL
  ,DeniedDate         VARCHAR(4)
  ,DeniedBy           VARCHAR(4)
  ,IsVoided           BIT  NOT NULL
  ,VoidedBy           INTEGER 
  ,VoidedDate         VARCHAR(16)
  ,DeniedVoidedReason VARCHAR(29)
  ,Paid               BIT  NOT NULL
  ,PaidDate           VARCHAR(4)
  ,MarkedPaidBy       VARCHAR(4)
  ,ModifiedBy         INTEGER 
  ,ModifiedDate       VARCHAR(16)
  ,CheckNumber        BIT  NOT NULL
  ,eSignatureManager  VARCHAR(11)
  ,DocumentsVerified  BIT  NOT NULL
);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (2,99999,2,'15/19/2018',6.75,15,0.45,'Mileage one way',76,'TRAVEL TIME','DevAdmin','2018-11-30 11:26',1,99999,'2018-12-19','Verified expenses 7 and 2.','Mileage was actually 15 and not 10.',1,99999,'2018-12-19 20:20',0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,99999,'2018-12-19 19:54',0,'Trent Adams',1);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (3,99999,1,'2018-11-29',20,0,0,'Took John Doe to the movies.',1139,'1139 W7060 Ratio Beaver','DevAdmin','2018-11-30 11:36',1,99999,'2018-12-18','Testing Verification',NULL,1,99999,'2018-12-19 20:20',0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,'Trent Adams',1);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (4,99999,5,'2018-11-29',6,0,0,'Had to use toll roads and paid toll of 6.00.',76,'76 TRAVEL TIME','DevAdmin','2018-11-30 11:37',1,99999,'2018-12-18','Testing Verification',NULL,1,99999,'2018-12-19 20:20',0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,'Trent Adams',1);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (5,99999,3,'2018-09-01',15,0,0,'Lunch during training',88,'88 Orientation, Training, Shadowing, Meet & Greet','DevAdmin','2018-11-30 11:39',0,NULL,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,NULL,0);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (6,99999,3,'2018-09-02',15,0,0,'Lunch, day 2 of training',88,'88 Orientation, Training, Shadowing, Meet & Greet','DevAdmin','2018-11-30 11:40',0,NULL,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,NULL,0);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (7,99999,2,'2018-11-04',1.8225,4,0.45,'Mileage to and from Jane Does house.',76,'TRAVEL TIME','DevAdmin','2018-11-30 11:40',1,99999,'2018-12-19','Verified expenses 7 and 2.','Changed mileage from 25 to 20 as per mileage sheet.',1,99999,'2018-12-19 20:20',0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,99999,'2018-12-19 20:14',0,'Trent Adams',1);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (8,99999,2,'2018-09-02',13.5,30,0.45,'Mileage from John W home and back.',76,'76 TRAVEL TIME','DevAdmin','2018-11-30 11:42',0,NULL,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,NULL,0);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (9,99999,1,'2018-09-02',45,0,0,'Took Brandon B. to batting cages and lunch.',1138,'1138 W7060 Ratio Allegheny','DevAdmin','2018-11-30 11:44',0,NULL,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,0,NULL,0);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (10,99999,2,'2018-12-17',3.0375,7,0.45,'Mileage to and from John Boy''s home.',76,'TRAVEL TIME','DevAdmin','2018-12-19 15:41',0,0,NULL,'Verifying 2 entries','Changed verification note.',0,NULL,NULL,0,NULL,NULL,0,NULL,NULL,NULL,0,NULL,NULL,99999,'2018-12-19 22:23',0,'Trent Adams',1);
INSERT INTO expenses(ExpenseID,EmployeeID,ExpenseTypeID,ExpenseDate,ExpenseAmount,MilesDriven,MileageRate,ExpenseNote,InternalEventID,AssocService,eSignature,DateSubmitted,ManagerVerified,VerifiedBy,VerificationDate,VerificationNote,ManagerEditNote,Approved,ApprovedBy,ApprovedDate,Denied,DeniedDate,DeniedBy,IsVoided,VoidedBy,VoidedDate,DeniedVoidedReason,Paid,PaidDate,MarkedPaidBy,ModifiedBy,ModifiedDate,CheckNumber,eSignatureManager,DocumentsVerified) VALUES (11,99999,3,'2018-12-18',35,0,0,'Took John Boy to dinner and a movie.',1138,'W7060 Ratio Allegheny','DevAdmin','2018-12-19 15:41',0,0,NULL,NULL,NULL,0,NULL,NULL,0,NULL,NULL,1,99999,'2018-12-19 22:06','Voiding expense just because.',0,NULL,NULL,99999,'2018-12-19 22:06',0,'Trent Adams',1);


select c.ClaimID, c.ProvidedBy, c.StartTime, c.EndTime, c.ClaimDetailID, c.ServiceID, c.Duration
from claims c
where c.StartTime >= '2018-05-19' and c.StartTime < '2018-05-20'
;

select 
     c.ClaimID, c.ProvidedBy, c.StartTime, c.EndTime, oa.ServiceStart, oa.ServiceEnd, c.ClaimDetailID, c.ServiceID, c.Duration
from claims c
outer apply (
    select min(t2.StartTime) ServiceStart, max(t2.EndTime) ServiceEnd
    from claims t2
    where t2.StartTime <= c.EndTime 
    and (t2.EndTime IS NULL OR t2.EndTime >= c.StartTime)
    ) oa
order by oa.ServiceStart, oa.ServiceEnd, c.StartTime, c.EndTime
;

Select allOpen in new window

Trent, it would be helpful to know what version in SQL Server you use. I ask this in particular because text concatenation over multiple rows has been simplified in  SQL Server 2017 by STRING_AGG

If you are using any version prior to that a more verbose syntax is required.

Example with string_agg:

select 
     c.ProvidedBy, oa.ServiceStart, oa.ServiceEnd
     , min(c.ClaimID) claimid
     , min(c.ClaimDetailID)
     , string_agg(cast(c.ClaimID as varchar),',') WITHIN GROUP (order by c.ClaimID) AS ClaimIDs
     , string_agg(cast(c.ClaimDetailID as varchar),',') WITHIN GROUP (order by c.ClaimID) AS ClaimDetailIDs
     , string_agg(cast(c.ServiceID as varchar),',') WITHIN GROUP (order by c.ClaimID) AS serviceids
from claims c
outer apply (
    select min(t2.StartTime) ServiceStart, max(t2.EndTime) ServiceEnd
    from claims t2
    where t2.StartTime <= c.EndTime 
    and (t2.EndTime IS NULL OR t2.EndTime >= c.StartTime)
    ) oa
group by 
     c.ProvidedBy, oa.ServiceStart, oa.ServiceEnd
order by 
     c.ProvidedBy, oa.ServiceStart, oa.ServiceEnd
;

Select allOpen in new window

Results:

+-----------+-------------------------+-------------------------+---------+------------------+-----------------+----------------+------------+
|ProvidedBy |      ServiceStart       |       ServiceEnd        | claimid | (No column name) |    ClaimIDs     | ClaimDetailIDs | serviceids |
+-----------+-------------------------+-------------------------+---------+------------------+-----------------+----------------+------------+
|     99999 | 2018-05-01 00:00:00.000 | 2018-05-01 23:59:00.000 |  263707 |           138289 |          263707 |         138289 |         57 |
|     99999 | 2018-05-02 00:00:00.000 | 2018-05-02 23:59:00.000 |  263708 |           138290 |          263708 |         138290 |         57 |
|     99999 | 2018-05-03 00:00:00.000 | 2018-05-03 23:59:00.000 |  263709 |           138291 |          263709 |         138291 |         57 |
|     99999 | 2018-05-07 00:00:00.000 | 2018-05-07 23:59:00.000 |  263710 |           138292 |          263710 |         138292 |         57 |
|     99999 | 2018-05-08 00:00:00.000 | 2018-05-08 23:59:00.000 |  263711 |           138293 |          263711 |         138293 |         57 |
|     99999 | 2018-05-09 00:00:00.000 | 2018-05-09 23:59:00.000 |  263712 |           138294 |          263712 |         138294 |         57 |
|     99999 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |  263698 |           138283 |   263698,263699 |  138283,138284 |      61,61 |
|     99999 | 2018-05-19 14:00:00.000 | 2018-05-19 15:30:00.000 | 3363698 |           138283 | 3363698,4463699 |  138283,138284 |      61,61 |
|     99999 | 2018-05-26 00:00:00.000 | 2018-05-26 23:59:00.000 |  263700 |           138285 |          263700 |         138285 |         61 |
|     99999 | 2018-05-27 00:00:00.000 | 2018-05-27 23:59:00.000 |  263701 |           138286 |          263701 |         138286 |         61 |
+-----------+-------------------------+-------------------------+---------+------------------+-----------------+----------------+------------+

Select allOpen in new window

This example can be run online here

@PortletPaul,

Currently our environment is using SQL Server 2016 Standard Edition. Hopefully, we will be updating to SQL 2017 sometime in 2019. Thank you so much for the assistance, as well as a big thanks to @slightwv. I didn't even know about the APPLY keywords. I feel like I'm never going to get a chance to learn enough for my MCSA DBA Developer cert. LOL :-(.

I do have some questions regarding the sample output.

• In the response in which you wanted to know which SQL Server version, the results look to be very promising and so far identical to what I had in mind.

• In the response in which you provided a lot more sample data, I noticed that the following entries although do overlap, are displaying twice vs as a single claim entry. The example with STRING_AGG is the expected output, with some column additions but...it looks golden.

|7 |263698 | 99999 | 2018-05-19 08:00:00.000 | 2018-05-19 11:00:00.000 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |138283 |61 |12 |
|8 |263699 | 99999 | 2018-05-19 08:30:00.000 | 2018-05-19 12:00:00.000 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |138284 |61 |12 |

Select allOpen in new window

• How would I use parameters with the supplied query? The parameters I will be using are @StartTime as well as @EndTime.

>>"In the response in which you provided a lot more sample data, I noticed that the following entries although do overlap, are displaying twice vs as a single claim entry. "

That is data BEFORE any aggregation, it is intended to display the effect of the APPLY calculation in the columns ServiceStart  & ServiceEnd  and it is through those columns having equal values that  - eventually - those 2 lines will be combined (aggregated).

To be clear that result was NOT intended to be the "expected result"  - it was intended to display the apply subquery result so a reader could understand any subsequent aggregation based on those results. You could also leverage this approach to list out every row that belongs to a particular "service period".

Please note: I felt compelled to add extra data to ensure that a discontinuity existed between 2 service sessions within a day - without this I could not test my calculations correctly. When providing "sample data" you do need to consider what will shake-out any problems you are going to encounter.

>>"In the response in which you wanted to know which SQL Server version, the results look to be very promising and so far identical to what I had in mind."

That's good

>>"Currently our environment is using SQL Server 2016 Standard Edition. Hopefully, we will be updating to SQL 2017 sometime in 2019"

Darn. The syntax for string aggregation prior to SQL Server 2017 is v.ugly and probably slower

>>"How would I use parameters with the supplied query? The parameters I will be using are @StartTime as well as @EndTime."

Your intention is to produce a stored procedure, just like the original you supplied with the question. So, use them exactly as you do now:

ALTER PROCEDURE [dbo].[spr_PayrollReportTimeExpenseEntries]
      -- Add the parameters for the stored procedure here
      @StartDate DATETIME,
      @EndDate DATETIME

etc.

Questions

Could you please summarize how the 3 tables are meant to join together

In particular I am unsure how expenses relate, there appears to be only an employeeID , is there an assumption that the expense date relates to the period of service?

If there is more than one service period within a day - what happens with expenses?
If the expense data does not align to a service period - what happens?

(btw: these issues would not have occurred with the old single tbl_claims design, but now you do need to consider them, and have answers.)

@PortletPaul
I apologize for the huge delay in response. The Christmas break and New Year have been crazy here. I'm hoping that I've understood your questions correctly.

>>"In particular I am unsure how expenses relate, there appears to be only an employeeID , is there an assumption that the expense date relates to the period of service? "

>>>The 3 tables, expense, hours and claims are related by employee id, the ExpenseDate from the expense table and the WorkDate from the hours table only.

>>"If there is more than one service period within a day - what happens with expenses?"

>>>A claim entry is an entry that an employee enters in order to bill the state(s). Then, employees will enter any expenses for that day along with time entries. When the payroll report is ran, it should gather all claims, expenses and time entries for the given date range. A lot of time there's more than a single service provided by an employee for a single day. They will enter each service as a new claim entry, and then enter their expenses. So, an employee could have 5 different expense amounts for a single date.

>>"If the expense data does not align to a service period - what happens?"

>>>Would you please elaborate on this question for me please?

Trent, you have given us the challenge to locate and summarize "overlaps" which as I have shown can be done.

I refer to each of these summarized overlaps as a "service period"

I believe more than one service period can exist in  a day (an assumption; but you haven't said this cannot happen)
e.g.

+-----------+-------------------------+-------------------------+
|ProvidedBy |      ServiceStart       |       ServiceEnd        | 
+-----------+-------------------------+-------------------------+
|     99999 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |
|     99999 | 2018-05-19 14:00:00.000 | 2018-05-19 15:30:00.000 |

Select allOpen in new window

Now, if there are expenses recorded for 2018-05-19, WHICH of those service periods do we attribute to expense to?
or, HOW do we distribute the expenses on that day?

That's problem 1

Now, assume we do NOT have ANY service periods recorded for 2018-05-20, but we DO have expenses on that day.

What do you expect the query to do in this situation?

That's problem 2 (i.e. If the expense data does not align to a service period - what happens?)

In short:
I do not see how the expenses are tightly coupled to any row in Claims. It would have been better IMHO had a claim_id foreign key relationship existed. Then you could aggregate the expenses to the related "service period" and you would avoid the potential for expenses that are misaligned by date.

So, instead of trying to describe the relationships, can you please provide any query that joins the 3 tables together. I can then use this to incorporate the summation of service periods.

@PortletPaul,

Now that you put things into a different light, or maybe I'm thinking a little differently here. Since expenses and time entries do NOT directly relate to a claim, we shouldn't need to include expenses or time entries as they are 100% separate from anything else. I apologize if I have caused a complete 180 or even 360 here on construction.

The only thing I'm concerned about is combining claim entries that overlap. Expenses and time entries can be retrieved from a separate stored procedure. Does that make things a little easier to follow? Just to recap here, I would like ONLY claims for the stored procedure and I'll use another stored procedure for time and expenses.

So, the stored procedure should gather a list of claims for a given period and then combine the claims that overlap for that same period. Make sense?

Let me see if I can either bridge the gap with what I think is going on or get clarification that I'm still confused

Trent, what I got out of the question is you are looking for to payroll aspect at this time?

If you take the straight times for each claim Mary could get paid twice when shes overlaps time on two different claims.  If there is a time in the day when she isn't working on a claim, you want that entry listed separately.

So Mary picks up john at home at 8.  Drops him off at the mall at 8:30.  Swings by Mary's and takes her to the Nail Salon at 9.  Picks up John at the mall at 10 and takes him home.  Pick Mary up at the Salon and takes her home at 11.

Mary gets paid for 3 hours total across two claims.  If she goes back to get John at 1PM and takes him home at 2PM, that is a separate 1 hour line item.

Please confirm this is what you are after or correct if I'm still confused.

I for one am pleased to hear these are not combined now as I could not see how that was going to work.

I believe this may help:

;with cte as (
        select 
             c.ClaimID, c.ProvidedBy, c.StartTime, c.EndTime, oa.ServiceStart, oa.ServiceEnd, c.ClaimDetailID, c.ServiceID, c.Duration
        from claims c
        outer apply (
            select min(t2.StartTime) ServiceStart, max(t2.EndTime) ServiceEnd
            from claims t2
            where t2.StartTime <= c.EndTime 
            and (t2.EndTime IS NULL OR t2.EndTime >= c.StartTime)
            ) oa
        )
select 
     c.ProvidedBy, c.ServiceStart, c.ServiceEnd
     , max(ca1.ClaimIDs) ClaimIDs
     , max(ca2.ClaimDetailIDs) ClaimDetailIDs
     , max(ca3.ServiceIDs) ServiceIDs     
from cte c
CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + CAST(p.[ClaimID] AS varchar(40))
              FROM cte AS p
              WHERE p.ServiceStart = c.ServiceStart
              ORDER BY p.ClaimID
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (ClaimIDs)
CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + CAST(p.[ClaimDetailID] AS varchar(40))
              FROM cte AS p
              WHERE p.ServiceStart = c.ServiceStart
              ORDER BY p.ClaimDetailID
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca2 (ClaimDetailIDs)
CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + CAST(p.[ServiceID] AS varchar(40))
              FROM cte AS p
              WHERE p.ServiceStart = c.ServiceStart
              ORDER BY p.ClaimDetailID
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca3 (ServiceIDs)
group by 
     c.ProvidedBy, c.ServiceStart, c.ServiceEnd
order by 
     c.ProvidedBy, c.ServiceStart, c.ServiceEnd
;

Select allOpen in new window

which produces a result like this:

+----+------------+-------------------------+-------------------------+-------------------+-----------------+------------+
|    | ProvidedBy |      ServiceStart       |       ServiceEnd        |     ClaimIDs      | ClaimDetailIDs  | ServiceIDs |
+----+------------+-------------------------+-------------------------+-------------------+-----------------+------------+
|  1 |      99999 | 2018-05-01 00:00:00.000 | 2018-05-01 23:59:00.000 |            263707 |          138289 |         57 |
|  2 |      99999 | 2018-05-02 00:00:00.000 | 2018-05-02 23:59:00.000 |            263708 |          138290 |         57 |
|  3 |      99999 | 2018-05-03 00:00:00.000 | 2018-05-03 23:59:00.000 |            263709 |          138291 |         57 |
|  4 |      99999 | 2018-05-07 00:00:00.000 | 2018-05-07 23:59:00.000 |            263710 |          138292 |         57 |
|  5 |      99999 | 2018-05-08 00:00:00.000 | 2018-05-08 23:59:00.000 |            263711 |          138293 |         57 |
|  6 |      99999 | 2018-05-09 00:00:00.000 | 2018-05-09 23:59:00.000 |            263712 |          138294 |         57 |
|  7 |      99999 | 2018-05-19 08:00:00.000 | 2018-05-19 12:00:00.000 |    263698, 263699 |  138283, 138284 |     61, 61 |
|  8 |      99999 | 2018-05-19 14:00:00.000 | 2018-05-19 15:30:00.000 |  3363698, 4463699 |  138283, 138284 |     61, 61 |
|  9 |      99999 | 2018-05-26 00:00:00.000 | 2018-05-26 23:59:00.000 |            263700 |          138285 |         61 |
| 10 |      99999 | 2018-05-27 00:00:00.000 | 2018-05-27 23:59:00.000 |            263701 |          138286 |         61 |
+----+------------+-------------------------+-------------------------+-------------------+-----------------+------------+

Select allOpen in new window

available as an online demo
Note this query is much simpler once you get to SQL Server 2017 where string_agg() is available e.g.

select 
     c.ProvidedBy, oa.ServiceStart, oa.ServiceEnd
     , string_agg(cast(c.ClaimID as varchar),',') WITHIN GROUP (order by c.ClaimID) AS ClaimIDs
     , string_agg(cast(c.ClaimDetailID as varchar),',') WITHIN GROUP (order by c.ClaimID) AS ClaimDetailIDs
     , string_agg(cast(c.ServiceID as varchar),',') WITHIN GROUP (order by c.ClaimID) AS serviceids
from claims c
outer apply (
    select min(t2.StartTime) ServiceStart, max(t2.EndTime) ServiceEnd
    from claims t2
    where t2.StartTime <= c.EndTime 
    and (t2.EndTime IS NULL OR t2.EndTime >= c.StartTime)
    ) oa
group by 
     c.ProvidedBy, oa.ServiceStart, oa.ServiceEnd
order by 
     c.ProvidedBy, oa.ServiceStart, oa.ServiceEnd
;

Select allOpen in new window

Are you still after assistance on this?

@PortletPaul

Yes, I am. I apologize, I've been ill. I'll provide additional details in a separate response.

@PortletPaul,

So, the query seems to work, however,there needs to be some additional filtering. I tested this on my dev database and it went for over 3 minutes without any results. I stopped it and had 23 results, however those results included claims for other people. So, I think we need to filter by ProvidedBy. I did modify this query a bit, however, I'm getting NULL rows returned. I cannot exclude NULL regardless of what I do.

USE [SBM-DEV]
GO
DECLARE @StartTime DATETIME, @EndTime DATETIME
SET @StartTime = '2018-11-01'
SET	@EndTime = '2018-11-05'
;WITH cte
     AS (SELECT c.ClaimID, 
                c.ProvidedBy, 
                c.StartTime, 
                c.EndTime, 
                oa.ServiceStart, 
                oa.ServiceEnd, 
                c.ClaimDetailID, 
                c.ServiceID, 
                c.Duration
         FROM tbl_Claims c
              OUTER APPLY
         (
             SELECT MIN(t2.StartTime) ServiceStart, 
                    MAX(t2.EndTime) ServiceEnd
             FROM tbl_Claims t2
             WHERE c.ServiceID IS NOT NULL AND t2.StartTime <= c.EndTime
                   AND (t2.EndTime IS NULL
                        OR t2.EndTime >= c.StartTime)
			AND (t2.ServiceID > 0 and c.ServiceID > 0 )
			AND (t2.StartTime >= @StartTime AND t2.EndTime <= @EndTime)
			AND (t2.ProvidedBy = c.ProvidedBy)
         ) oa)
     SELECT c.ProvidedBy, 
            c.ServiceStart, 
            c.ServiceEnd, 
            MAX(ca1.ClaimIDs) ClaimIDs, 
            MAX(ca2.ClaimDetailIDs) ClaimDetailIDs, 
            MAX(ca3.ServiceIDs) ServiceIDs
     FROM cte c
          CROSS APPLY
     (
         SELECT STUFF(
         (
             SELECT ', ' + CAST(p.[ClaimID] AS VARCHAR(40))
             FROM cte AS p
             WHERE p.ServiceStart = c.ServiceStart AND (p.ProvidedBy = c.ProvidedBy)
					AND (p.ServiceID > 0 AND p.ServiceID IS NOT NULL)
             ORDER BY p.ClaimID FOR XML PATH('')
         ), 1, 1, '')
     ) ca1(ClaimIDs)
          CROSS APPLY
     (
         SELECT STUFF(
         (
             SELECT ', ' + CAST(p.[ClaimDetailID] AS VARCHAR(40))
             FROM cte AS p
             WHERE p.ServiceStart = c.ServiceStart AND (p.ProvidedBy = c.ProvidedBy)
			 AND (p.ServiceID > 0 AND p.ServiceID IS NOT NULL)
             ORDER BY p.ClaimDetailID FOR XML PATH('')
         ), 1, 1, '')
     ) ca2(ClaimDetailIDs)
          CROSS APPLY
     (
         SELECT STUFF(
         (
             SELECT ', ' + CAST(p.[ServiceID] AS VARCHAR(40))
             FROM cte AS p
             WHERE p.ServiceStart = c.ServiceStart AND (p.ProvidedBy = c.ProvidedBy)
			 AND (p.ServiceID > 0 AND p.ServiceID IS NOT NULL)
             ORDER BY p.ClaimDetailID FOR XML PATH('')
         ), 1, 1, '')
     ) ca3(ServiceIDs)
     GROUP BY c.ProvidedBy, 
              c.ServiceStart, 
              c.ServiceEnd
     ORDER BY c.ProvidedBy, 
              c.ServiceStart, 
              c.ServiceEnd;

Select allOpen in new window

Trent,

I hope you are fully recovered.

It isn't really feasible for me to comment on your query, or the NULLs, or the added filtering you suggest is needed. as I have no way of reproducing these issues. To reproduce them I would need sample data that DOES contain the problems (plus the expected result based on supplied sample data) .

Regarding performance perhaps you could run an execution plan