1 2 3 Previous Next 31 Replies Latest reply on Feb 3, 2016 1:23 PM by Fahri Akar

    Creating Date Range Relationships

    mgostovich

      History: I use a very large database that I created to manage an inpatient treatment center. I have a one client to many admissions relationship as the primary core. Every month I have to report to billing the clients who received services within that month. For years I have been using a calculation on both sides of a relationship to create a set of date ranges. On the admissions side, it takes the admit date and a field that is either the discharge date if one is present, or the admit date + 60 days if not. On the reporting side, I input the start and end dates for the report and it uses those.

       

      Problem: The database crashed a few months ago and I had to go back to a much earlier version and rebuild some of the functionality that I had built into it. Since then, the relationship doesn't work correctly. instead of just the people that received services within those date ranges, I get everyone since I started reentering data in October 2015.

       

      Has anyone out there worked with this and have a good solution to make this work? I am open to starting over from scratch if I can make it work, I am just frustrated every month with having to "MacGyver" it every month just to run the report which also means that only I can do it.

       

      Michael Gostovich

        • 1. Re: Creating Date Range Relationships
          mikebeargie

          It sounds like it's based on creation date, rather than service/appointment date. I would start by auditing your relationships and making sure that they aren't based on creation date.

          • 2. Re: Creating Date Range Relationships
            mgostovich

            So here is what the actual calculation looks like on the admissions side of the relationship. "Start1" = the day they entered treatment and "End1" = day they discharged or if they are still in treatment, Start1 + 60 days...

             

            Substitute(Middle(" 0 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^1)/10^0) + 1) * 2  - 1; (Int(end1/10^0) - Int(start1/10^0) + 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 6)) & Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^2)/10^1) + 1) * 2  - 1; (Int(end1/10^1) - Int(start1/10^1) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 5)) & Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^3)/10^2) + 1) * 2  - 1; (Int(end1/10^2) - Int(start1/10^2) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 4)) & Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^4)/10^3) + 1) * 2  - 1; (Int(end1/10^3) - Int(start1/10^3) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 3)) & Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^5)/10^4) + 1) * 2  - 1; (Int(end1/10^4) - Int(start1/10^4) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 2)) & Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^6)/10^5) + 1) * 2  - 1; (Int(end1/10^5) - Int(start1/10^5) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 1)) & Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(start1; 10^7)/10^6) + 1) * 2  - 1; (Int(end1/10^6) - Int(start1/10^6) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(start1); 7); 0)) & Substitute(Middle(" 9 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^1)/10^0)) * 2 + 1; (Int(end1/10^0) - Int(start1/10^0) + 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 6)) & Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^2)/10^1)) * 2 + 1; (Int(end1/10^1) - Int(start1/10^1) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 5)) & Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^3)/10^2)) * 2 + 1; (Int(end1/10^2) - Int(start1/10^2) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 4)) & Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^4)/10^3)) * 2 + 1; (Int(end1/10^3) - Int(start1/10^3) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 3)) & Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^5)/10^4)) * 2 + 1; (Int(end1/10^4) - Int(start1/10^4) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 2)) & Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^6)/10^5)) * 2 + 1; (Int(end1/10^5) - Int(start1/10^5) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 1)) & Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(end1; 10^7)/10^6)) * 2 + 1; (Int(end1/10^6) - Int(start1/10^6) - 1) * 2); " ";

            "¶" &  Left(Right("000000" & GetAsNumber(end1); 7); 0))&

            "¶X" &Right("000000" & GetAsNumber(start1); 7) &

            "¶X" & Left(Right("000000" & GetAsNumber(start1); 7); 6) &

            "¶X" & Left(Right("000000" & GetAsNumber(start1); 7); 5) &

            "¶X" & Left(Right("000000" & GetAsNumber(start1); 7); 4) &

            "¶X" & Left(Right("000000" & GetAsNumber(start1); 7); 3) &

            "¶X" & Left(Right("000000" & GetAsNumber(start1); 7); 2) &

            "¶X" & Left(Right("000000" & GetAsNumber(start1); 7); 1);

            • 3. Re: Creating Date Range Relationships
              mrwatson-gbs

              OMG! That's either amazing or mad! I need to get to a computer to see what this mother does!

              • 4. Re: Creating Date Range Relationships
                mgostovich

                It will generate a list of numbers that you can then use to create a relationship

                • 5. Re: Creating Date Range Relationships
                  siplus

                  are dates stored as date, or as something else ? (Text, number..)

                  • 6. Re: Creating Date Range Relationships
                    siplus

                    Every month I have to report to billing the clients who received services within that month.

                     

                     

                    ExecuteSQL("SELÈCT FKPatientID FROM Services WHERE ServiceDate BETWEEN ? AND ?";"";""; Date($mth; 1; $yr); Date($mth+1; 0; $yr)) ?

                    • 7. Re: Creating Date Range Relationships
                      mgostovich

                      They are stored as text

                      • 8. Re: Creating Date Range Relationships
                        siplus

                        First step then: get real dates.

                         

                        You have a separator for each date, let's say it's "/", so a US-formatted date will look like 5/14/16 or 5/14/2016, right ?

                         

                        then listdate = substitute(yourdate;"/";¶) will give

                         

                        5

                        14

                        16

                         

                        And

                         

                        Let ([

                        yr1 = GetValue(listdate;3);

                        yr2 = GetAsNumber(Choose(length(yr1) = 4; "20" & yr1; yr1))];

                         

                        Date(GetValue(listdate);1; GetValue(listdate;2); yr2)

                        )

                         

                        will transform it into a real date.

                        • 9. Re: Creating Date Range Relationships
                          mgostovich

                          siplus,

                          admittedly, I am not very strong when it comes to SQL. I have to generate more than just a list of names. the report in essence looks like this...

                           

                          Client A          Unique Id #          Admit Date          Discharge Date          # of days to bill          Counselor


                          The report is actually pulled into 7 different portals based on funding type and then exported out to Excel for the accounting department and there is a summary page for the exec team and Board summarizing utilization for the month.

                          • 10. Re: Creating Date Range Relationships
                            mgostovich

                            Sorry I misunderstood you. It starts out as a real date and then the calc converts it to a number and then it is stored as a text list that looks like this when you have a start date of 1/4/2016 and an end date of 3/4/2016.

                             

                            0735967

                            0735968

                            0735969

                            073597

                            073598

                            073599

                            0736027

                            0736026

                            0736025

                            0736024

                            0736023

                            0736022

                            0736021

                            0736020

                            073601

                            073600

                            X0735967

                            X073596

                            X07359

                            X0735

                            X073

                            X07

                            X0

                            • 11. Re: Creating Date Range Relationships
                              mrwatson-gbs

                              Wow ... and what do the numbers represent?

                               

                              Why is there so many numbers for a simple 3 day period?

                               

                              How does/is it supposed to work?

                               

                              Sent from my iPhone

                              • 12. Re: Creating Date Range Relationships
                                mrwatson-gbs

                                Ah I guess they are US style dates 2 month period .... But I still don't understand the logic here.

                                 

                                Sent from my iPhone

                                • 13. Re: Creating Date Range Relationships
                                  siplus

                                  this looks like a search/match key.

                                  • 14. Re: Creating Date Range Relationships
                                    mgostovich

                                    More or less yes. The reporting table creates a list similar to the one above based on the Report "Start" and "End" dates and then the resulting matched records show up in a portal

                                    1 2 3 Previous Next