10 Replies Latest reply on Aug 23, 2019 2:39 PM by user9294681

    solution to the given requirement below

    user9294681

      Hi All;

       

      Can you please help me with the following problem. I have included the associated sample data below

       

      create table district_info

      (

        outlet_id number

        ,loc varchar2(100)

        ,district varchar2(100)

       

      );

       

      insert into district_info values ( 1, 'New York', 'A');

      insert into district_info values (2, 'Boston', 'A');

      insert into district_info values (3, 'Dallas', 'A');

      insert into district_info values (4, 'Chicago', 'B');

      insert into district_info values (5, 'Los Angeles', 'B');

      insert into district_info values (6, 'Miami', 'C');

      insert into district_info values (7, 'Atlanta', 'C');

      insert into district_info values (8, 'Charleston', 'D');

      commit;

       

      create table price_range

      (

          pid number

        ,district varchar2(100)

        ,price number

        ,start_date date

        ,end_date date

      );

       

      insert into price_range values (11111, 'A', 10.00, to_date('2019-01-01', 'YYYY-DD-MM'), to_date('2019-03-01', 'YYYY-DD-MM'));

      insert into price_range values (11111, 'Main', 12.00, to_date('2019-01-01', 'YYYY-DD-MM'), to_date('2019-06-01', 'YYYY-DD-MM'));

      commit;

       

       

       

      create table outlet_id_change_price

      (

      pid number

      ,outlet_id number

      ,price number

      ,start_date date

      ,end_date date

       

       

      );

       

      insert into outlet_id_change_price values (11111, 2, 7.00, to_date('2019-01-01', 'YYYY-DD-MM'),  to_date('2019-02-01', 'YYYY-DD-MM') );

      commit;

       

      create table calendar_info as

      select  to_date('01-01-2019', 'MM-DD-YYYY') + (level - 1) as ind_dates from dual

      connect by level <= to_date('01-10-2019', 'MM-DD-YYYY') - to_date('01-01-2019', 'MM-DD-YYYY')

       

      the expected output is gotten as follows

       

      if an outlet_id in a particular district has a price then use that price and its associated start_date and end_date. if its associated start_date and end_date period ends before the district price end_date then use the associated district price for the remaining dates

      if the district price ends before the main price  end_date then use the associated main price for the remaining dates.

      For all remaining outlet_id that doesn't have any district price or outlet_id_change_price then just use the Main price instead

       

      expected output

       

      pid     outlet_id   price   start_date(YYYY-DD-MM)  end_date(YYYY-DD-MM)
      
      
      11111     1         10.00   2019-01-01              2019-01-01
      11111     1         10.00   2019-02-01              2019-02-01
      11111     1         10.00   2019-03-01              2019-03-01 
      
      
      11111     2         7.00    2019-01-01              2019-01-01 
      11111     2         7.00    2019-02-01              2019-02-01
      11111     2         10.00   2019-03-01              2019-03-01
      
      11111     1         12.00   2019-04-01              2019-04-01
      11111     1         12.00   2019-05-01              2019-05-01
      11111     1         12.00   2019-06-01              2019-06-01
      
      11111     2         12.00   2019-04-01              2019-04-01
      11111     2         12.00   2019-05-01              2019-05-01
      11111     2         12.00   2019-06-01              2019-06-01
      11111     3         12.00   2019-01-01              2019-01-01
      11111     3         12.00   2019-02-01              2019-02-01 
      11111     3         12.00   2019-03-01              2019-03-01
      11111     3         12.00   2019-04-01              2019-04-01
      11111     3         12.00   2019-05-01              2019-05-01
      11111     3         12.00   2019-06-01              2019-06-01
      
      11111     4         12.00   2019-01-01              2019-01-01
      11111     4         12.00   2019-02-01              2019-02-01 
      11111     4         12.00   2019-03-01              2019-03-01
      11111     4         12.00   2019-04-01              2019-04-01
      11111     4         12.00   2019-05-01              2019-05-01
      11111     4         12.00   2019-06-01              2019-06-01
      
      11111     5         12.00   2019-01-01              2019-01-01
      11111     5         12.00   2019-02-01              2019-02-01
      11111     5         12.00   2019-03-01              2019-03-01
      11111     5         12.00   2019-04-01              2019-04-01
      11111     5         12.00   2019-05-01              2019-05-01
      11111     5         12.00   2019-06-01              2019-06-01
      
      
      11111     6         12.00   2019-01-01              2019-01-01
      11111     6         12.00   2019-02-01              2019-02-01 
      11111     6         12.00   2019-03-01              2019-03-01
      11111     6         12.00   2019-04-01              2019-04-01
      11111     6         12.00   2019-05-01              2019-05-01
      11111     6         12.00   2019-06-01              2019-06-01
      
      11111     7         12.00   2019-01-01              2019-01-01
      11111     7         12.00   2019-02-01              2019-02-01 
      11111     7         12.00   2019-03-01              2019-03-01
      11111     7         12.00   2019-04-01              2019-04-01
      11111     7         12.00   2019-05-01              2019-05-01
      11111     7         12.00   2019-06-01              2019-06-01
      
      
      
      11111     8         12.00   2019-01-01              2019-01-01
      11111    8         12.00   2019-02-01              2019-02-01 
      11111     8         12.00   2019-03-01              2019-03-01
      11111     8         12.00   2019-04-01              2019-04-01
      11111     8        12.00   2019-05-01              2019-05-01
      11111     8        12.00   2019-06-01              2019-06-01
      

       

      Thanks in advance. I am using oracle 10.2.0.1

        • 1. Re: solution to the given requirement below
          jaramill

          Please CHANGE the title of your thread as it is meaningless and indicates nothing about SQL and/or PL/SQL.

          Per the forum guidelines link --> Re: 2. How do I ask a question on the forums?  #2

           

          2) Thread Subject line

          Give your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP".  This forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue.  By marking your question as urgent you are actually less likely to get a good response, as members will choose to ignore such questions or respond unfavorably.

           

          That's good you posted the DDL and DML statements.

          Now with that said, what query have YOU written that you need help with?  Is this a homework assignment?  We'll help but not do ALL the work for you.

          EDIT: I see the DB version listed

          • 2. Re: solution to the given requirement below
            Frank Kulash

            Hi,

            user9294681 wrote:

             

            Hi All;

             

            Can you please help me with the following problem. I have included the associated sample data below

             

            create table district_info

            (

            outlet_id number

            ,loc varchar2(100)

            ,district varchar2(100)

             

            );

             

            insert into district_info values ( 1, 'New York', 'A');

            insert into district_info values (2, 'Boston', 'A');

            insert into district_info values (3, 'Dallas', 'A');

            insert into district_info values (4, 'Chicago', 'B');

            insert into district_info values (5, 'Los Angeles', 'B');

            insert into district_info values (6, 'Miami', 'C');

            insert into district_info values (7, 'Atlanta', 'C');

            insert into district_info values (8, 'Charleston', 'D');

            commit;

             

            create table price_range

            (

            pid number

            ,district varchar2(100)

            ,price number

            ,start_date date

            ,end_date date

            );

             

            insert into price_range values (11111, 'A', 10.00, to_date('2019-01-01', 'YYYY-DD-MM'), to_date('2019-03-01', 'YYYY-DD-MM'));

            insert into price_range values (11111, 'Main', 12.00, to_date('2019-01-01', 'YYYY-DD-MM'), to_date('2019-06-01', 'YYYY-DD-MM'));

            commit;

             

             

             

            create table outlet_id_change_price

            (

            pid number

            ,outlet_id number

            ,price number

            ,start_date date

            ,end_date date

             

             

            );

             

            insert into outlet_id_change_price values (11111, 2, 7.00, to_date('2019-01-01', 'YYYY-DD-MM'), to_date('2019-02-01', 'YYYY-DD-MM') );

            commit;

             

            create table calendar_info as

            select to_date('01-01-2019', 'MM-DD-YYYY') + (level - 1) as ind_dates from dual

            connect by level < to_date('01-07-2019', 'MM-DD-YYYY') - to_date('01-01-2019', 'MM-DD-YYYY')

             

            the expected output is gotten as follows

             

            if an outlet_id in a particular district has a price then use that price and its associated start_date and end_date. if its associated start_date and end_date period ends before the district price end_date then use the associated district price for the remaining dates

            if the district price ends before the main price end_date then use the associated main price for the remaining dates.

             

            expected output

             

             

            1. pidoutlet_idpricestart_date(YYYY-DD-MM)end_date(YYYY-DD-MM)
            2. 11111110.002019-01-012019-01-01
            3. 11111110.002019-02-012019-02-01
            4. 11111110.002019-03-012019-03-01
            5. 1111127.002019-01-012019-01-01
            6. 1111127.002019-02-012019-02-01
            7. 11111210.002019-03-012019-03-01
            8. 11111112.002019-04-012019-04-01
            9. 11111112.002019-05-012019-05-01
            10. 11111112.002019-06-012019-06-01
            11. 11111212.002019-04-012019-04-01
            12. 11111212.002019-05-012019-05-01
            13. 11111212.002019-06-012019-06-01
            14. 11111312.002019-01-012019-06-01
            15. 11111412.002019-01-012019-06-01
            16. 11111512.002019-01-012019-06-01
            17. 11111612.002019-01-012019-06-01
            18. 11111712.002019-01-012019-06-01
            19. 11111812.002019-01-012019-06-01

             

             

            Thanks in advance. I am using oracle 10.2.0.1

            Does pid play any role in this problem?  You didn't say a word about it in your original message.

             

            Why do you want multiple output rows (all with start_date = end_date) for outlet_ids 1 and 2, but only a single output row (all with start_date < end_date) for the other outlet_ids?

             

            Do you need the output sorted the way you posted it?  If so, explain how it is ordered.

             

            The desired output includes 2019-06-01, but that date doesn't exist in calendar info.  Was that a mistake?  If so, post the correct table and output.

             

            If you posted your best attempt, as Jaramill suggested, it might help answer questions like these.

            • 3. Re: solution to the given requirement below
              user9294681

              thanks for the reply, it is not an homework assignment

               

              Does pid play any role in this problem?  You didn't say a word about it in your original message.

               

              Yes it does, the pid can be used to link price_range to the outlet_id_change_price, it is also a partno in the original dataset i am working with and we have other partno included in the set with different pricing such as partno 22222. this was just a simplified example  with only one partno   11111   

               

              Why do you want multiple output rows (all with start_date = end_date) for outlet_ids 1 and 2, but only a single output row (all with start_date < end_date) for the other outlet_ids?

               

              The reason we have multiple output rows for outlets_ids 1 and 2 is due to the pricing aspect. As you can see in the price_range table, we have district A which has a associated price of 10.00 for the period '2019-01-01'  - '2019-03-01' and outlets_ids  1 belongs to that district. once the price of 10.00 expires after 2019-03-01, it should use the main's price for the remaining period available in the main. hence that is why we have the first 3 -rows having the following prices

               

              11111    1        10.00  2019-01-01              2019-01-01

              11111    1        10.00  2019-02-01              2019-02-01

              11111    1        10.00  2019-03-01              2019-03-01

               

              and then the next rows having the main price for the remaining period available in the Main district

               

              11111    1        12.00  2019-04-01              2019-04-01

              11111    1        12.00  2019-05-01              2019-05-01

              11111    1        12.00  2019-06-01              2019-06-01

               

              Note, outletids 1, didn't have a outlet_id_change_price and likewise other outlet_ids unlike outletid2 2,

               

              so for outletid 2 we first check to see if there is an outlet_id_change_price, if it exist we grab the associated price which is 7.00 and it associated period and then display it, hence the following output below

               

              11111    2        7.00    2019-01-01              2019-01-01

              11111    2        7.00    2019-02-01              2019-02-01

               

              For the remaining dates in the Main district, we then check to see if there is a district price and if there is we use it for the remaining dates, hence the output as well below

               

              11111    2        10.00  2019-03-01              2019-03-01

               

              but since the district price dates doesn't fully cover the main price dates we have to use the main price for the remaining dates, hence the output below

               

              11111    2        12.00  2019-04-01              2019-04-01

                11111    2        12.00  2019-05-01              2019-05-01

              11111    2        12.00  2019-06-01              2019-06-01

               

              Do you need the output sorted the way you posted it?  If so, explain how it is ordered.

              no, the sorting doesn't matter.

               

              The desired output includes 2019-06-01, but that date doesn't exist in calendar info.  Was that a mistake?  If so, post the correct table and output.

              Yes it was a mistake, i have updated it

               

              If you posted your best attempt, as Jaramill suggested, it might help answer questions like these.

              I am currently working on my best attempt, i will soon post the work i have done so far

              • 4. Re: solution to the given requirement below
                jaramill

                All of these things you replied to Frank with, should have been in the ORIGINAL post.  Always put as MUCH information in the beginning to help us out.

                • 5. Re: solution to the given requirement below
                  Frank Kulash

                  Hi,

                  user9294681 wrote:

                  ...

                  Why do you want multiple output rows (all with start_date = end_date) for outlet_ids 1 and 2, but only a single output row (all with start_date < end_date) for the other outlet_ids?

                   

                  The reason we have multiple output rows for outlets_ids 1 and 2 is due to the pricing aspect. As you can see in the price_range table, we have district A which has a associated price of 10.00 for the period '2019-01-01' - '2019-03-01' and outlets_ids 1 belongs to that district.

                  ...

                  Sorry, I still don't understand.

                  Are you saying that, when a district is found in the price_range table, then all output rows for that outlet will have start_date=end_date, and when a district is not found on the price_range table, you want to group as many consecutive days (having the same outlet_id and price) together?

                  If so, why don't you want separate rows for each day for outlet_id=3?  It has the same district as outlet_ids 1 ad 2, so it matches the price_range table just as well as 1 and 2 do.

                  • 6. Re: solution to the given requirement below
                    user9294681

                    Sorry, I still don't understand.

                    Are you saying that, when a district is found in the price_range table, then all output rows for that outlet will have start_date=end_date, and when a district is not found on the price_range table, you want to group as many consecutive days (having the same outlet_id and price) together?

                    If so, why don't you want separate rows for each day for outlet_id=3?  It has the same district as outlet_ids 1 ad 2, so it matches the price_range table just as well as 1 and 2 do.

                     

                     

                    Outlet_id=3 should have separate rows as stated. Please see update. when I cut and pasted, it unfortunately somehow didn't paste everything I just noticed that. my apologises

                    • 7. Re: solution to the given requirement below
                      Frank Kulash

                      Hi,

                      user9294681 wrote:

                       

                      Sorry, I still don't understand.

                      Are you saying that, when a district is found in the price_range table, then all output rows for that outlet will have start_date=end_date, and when a district is not found on the price_range table, you want to group as many consecutive days (having the same outlet_id and price) together?

                      If so, why don't you want separate rows for each day for outlet_id=3? It has the same district as outlet_ids 1 ad 2, so it matches the price_range table just as well as 1 and 2 do.

                       

                       

                      Outlet_id=3 should have separate rows as stated. Please see update. when I cut and pasted, it unfortunately somehow didn't paste everything I just noticed that. my apologises

                      Please don't change your messages after you post them, especially after other people have replied.  It makes the thread hard to follow and easy to miss what you're saying.  Post corrections or additions in a new reply.  The only kind of change you should make to an existing thread is to add something like "EDIT: Output below is wrong.  See reply #6 for correct output."

                       

                      Are you now saying that you want one row of output for every distinct combination of outlet and date. and that start_date will always be the same as end_date?  If so, why do you need two separate columns?

                      • 8. Re: solution to the given requirement below
                        Frank Kulash

                        Hi,

                         

                        Here's one way to get the results you posted from the sample dat you posted:

                        WITH    district_prices    AS

                        (

                            SELECT  d.outlet_id

                            ,       p.*

                            ,       CASE

                                        WHEN  p.district = 'Main'

                                        THEN  'C'

                                        ELSE  'B'

                                    END  AS match_class

                            FROM             district_info  d

                            LEFT OUTER JOIN  price_range    p  ON  p.district IN (d.district, 'Main')

                        )

                        ,    all_prices    AS

                        (

                            SELECT  *

                            FROM    district_prices

                        UNION ALL

                            SELECT  outlet_id, pid, NULL, price, start_date, end_date, 'A'

                            FROM    outlet_id_change_price

                            WHERE   (outlet_id, pid) IN (

                                                            SELECT  outlet_id, pid

                                                            FROM    district_prices

                                                            WHERE   district  <> 'Main'

                                                        )

                        )

                        ,    ranked_matches    AS

                        (

                            SELECT  a.pid, a.outlet_id, a.price

                            ,       c.ind_dates

                            ,       DENSE_RANK () OVER ( PARTITION BY  a.pid, a.outlet_id, c.ind_dates

                                                         ORDER BY      a.match_class

                                                       )  AS rnk

                            FROM    all_prices     a

                            JOIN    calendar_info  c  ON  c.ind_dates  BETWEEN  a.start_date

                                                                       AND      a.end_date

                        )

                        SELECT    pid, outlet_id, price

                        ,         ind_dates   AS start_date

                        ,         ind_dates   AS end_date

                        FROM      ranked_matches

                        WHERE     rnk  = 1

                        ORDER BY  outlet_id

                        ,         ind_dates

                        ;

                        Output:

                               PID OUTLET_ID   PRICE START_DATE   END_DATE

                        -------- --------- ------- ------------ ----------

                           11111         1   10.00 2019-01-01   2019-01-01

                           11111         1   10.00 2019-02-01   2019-02-01

                           11111         1   10.00 2019-03-01   2019-03-01

                           11111         1   12.00 2019-04-01   2019-04-01

                           11111         1   12.00 2019-05-01   2019-05-01

                           11111         1   12.00 2019-06-01   2019-06-01

                           11111         2    7.00 2019-01-01   2019-01-01

                           11111         2    7.00 2019-02-01   2019-02-01

                           11111         2   10.00 2019-03-01   2019-03-01

                           11111         2   12.00 2019-04-01   2019-04-01

                           11111         2   12.00 2019-05-01   2019-05-01

                           11111         2   12.00 2019-06-01   2019-06-01

                           11111         3   10.00 2019-01-01   2019-01-01

                           11111         3   10.00 2019-02-01   2019-02-01

                           11111         3   10.00 2019-03-01   2019-03-01

                           11111         3   12.00 2019-04-01   2019-04-01

                           11111         3   12.00 2019-05-01   2019-05-01

                           11111         3   12.00 2019-06-01   2019-06-01

                           11111         4   12.00 2019-01-01   2019-01-01

                           11111         4   12.00 2019-02-01   2019-02-01

                           11111         4   12.00 2019-03-01   2019-03-01

                           11111         4   12.00 2019-04-01   2019-04-01

                           11111         4   12.00 2019-05-01   2019-05-01

                           11111         4   12.00 2019-06-01   2019-06-01

                           11111         5   12.00 2019-01-01   2019-01-01

                           11111         5   12.00 2019-02-01   2019-02-01

                           11111         5   12.00 2019-03-01   2019-03-01

                           11111         5   12.00 2019-04-01   2019-04-01

                           11111         5   12.00 2019-05-01   2019-05-01

                           11111         5   12.00 2019-06-01   2019-06-01

                           11111         6   12.00 2019-01-01   2019-01-01

                           11111         6   12.00 2019-02-01   2019-02-01

                           11111         6   12.00 2019-03-01   2019-03-01

                           11111         6   12.00 2019-04-01   2019-04-01

                           11111         6   12.00 2019-05-01   2019-05-01

                           11111         6   12.00 2019-06-01   2019-06-01

                           11111         7   12.00 2019-01-01   2019-01-01

                           11111         7   12.00 2019-02-01   2019-02-01

                           11111         7   12.00 2019-03-01   2019-03-01

                           11111         7   12.00 2019-04-01   2019-04-01

                           11111         7   12.00 2019-05-01   2019-05-01

                           11111         7   12.00 2019-06-01   2019-06-01

                           11111         8   12.00 2019-01-01   2019-01-01

                           11111         8   12.00 2019-02-01   2019-02-01

                           11111         8   12.00 2019-03-01   2019-03-01

                           11111         8   12.00 2019-04-01   2019-04-01

                           11111         8   12.00 2019-05-01   2019-05-01

                           11111         8   12.00 2019-06-01   2019-06-01

                        Depending on your exact requirements and your data, you may need some small changes.

                         

                        Here's how it works:

                        There are three ways to find a price for a given outlet

                        1. From the outlet_id-change_price table.  (Let's call these "Class A" prices.)
                        2. From the price_range table, where district <> 'Main' ("Class B")
                        3. From the price_range table, where district = 'Main' ("Class C")

                        When you can get prices for the same date using two or more different ways, you want to use the way that comes first in the list above: "Class A" matches are preferred, "Class C" matches are the worst.

                        In the query above, the sub-query called all_prices classifies each possible price as 'A', 'B' or 'C', as described above.  Then the sub-query called ranked_matches assigns numbers (1, 2, 3) to the various prices for each date, depending on what classes are available on that date.  Finally, the main query displays only the best available price, that is, the one ranked 1.

                        • 9. Re: solution to the given requirement below
                          mathguy

                          There are still a few unclear things in your question (even after your edits).

                           

                          Since you are using a calendar table, and apparently in the output you want one row per triple (PID, OUTLET_ID, IND_DATES), it makes no sense to have a start date and an end date in the output. In my code below, I generate an output with a single date column (still named IND_DATES - I see no reason to change the name).

                           

                          In your calendar table you have dates from 1 January to 9 January (not to 10 January as you perhaps thought), but in the output you only show data through 6 January. Perhaps that's a simple mistake on your part. Or perhaps you only want to show the combinations that do have a price, according to one of the rules. In my code below I assumed that's the case (you only want to show rows where there is a price).

                           

                          You are obviously missing a table from your inputs - showing the "products", with PID, presumably, as primary key. For my testing, I created it like so:

                           

                          create table products (pid number);

                          insert into products values(11111);

                          commit;

                           

                          Also, you have a default 'Main' district, tucked in with the other districts in a single table. That makes no sense; since you must look for the default price at least in some cases, that price should be in a separate table, which would be much smaller (resulting in much faster code). I wrote my solution in the simplest possible way - based on YOUR data model, flawed as it is in this respect; there are ways to make the code faster, without changing the data model, but that seems silly to me. Better fix the data model.

                           

                          With all that said, here's one way to do what you need. I wrote the solution so it would work with more than one product (but you will have to test it to make sure I wrote it correctly, since the test data only had one PID).

                           

                          select p.pid, d.outlet_id, c.ind_dates, coalesce(o.price, r.price, m.price) as price

                          from   products p cross join district_info d cross join calendar_info c

                                 left outer join outlet_id_change_price o

                                      on p.pid = o.pid and d.outlet_id = o.outlet_id and c.ind_dates between o.start_date and o.end_date

                                 left outer join price_range r

                                      on p.pid = r.pid and d.district  = r.district  and c.ind_dates between r.start_date and r.end_date

                                 left outer join price_range m

                                      on p.pid = m.pid and m.district  = 'Main'      and c.ind_dates between m.start_date and m.end_date

                          where  o.price is not null or r.price is not null or m.price is not null

                          order  by pid, outlet_id, ind_dates

                          ;

                           

                                 PID  OUTLET_ID IND_DATES       PRICE

                          ---------- ---------- ---------- ----------

                               11111          1 2019-01-01         10

                               11111          1 2019-02-01         10

                               11111          1 2019-03-01         10

                               11111          1 2019-04-01         12

                               11111          1 2019-05-01         12

                               11111          1 2019-06-01         12

                               11111          2 2019-01-01          7

                               11111          2 2019-02-01          7

                               11111          2 2019-03-01         10

                               11111          2 2019-04-01         12

                               11111          2 2019-05-01         12

                               11111          2 2019-06-01         12

                               11111          3 2019-01-01         10

                               11111          3 2019-02-01         10

                               11111          3 2019-03-01         10

                               11111          3 2019-04-01         12

                               11111          3 2019-05-01         12

                               11111          3 2019-06-01         12

                               11111          4 2019-01-01         12

                               11111          4 2019-02-01         12

                               11111          4 2019-03-01         12

                               11111          4 2019-04-01         12

                               11111          4 2019-05-01         12

                               11111          4 2019-06-01         12

                               11111          5 2019-01-01         12

                               11111          5 2019-02-01         12

                               11111          5 2019-03-01         12

                               11111          5 2019-04-01         12

                               11111          5 2019-05-01         12

                               11111          5 2019-06-01         12

                               11111          6 2019-01-01         12

                               11111          6 2019-02-01         12

                               11111          6 2019-03-01         12

                               11111          6 2019-04-01         12

                               11111          6 2019-05-01         12

                               11111          6 2019-06-01         12

                               11111          7 2019-01-01         12

                               11111          7 2019-02-01         12

                               11111          7 2019-03-01         12

                               11111          7 2019-04-01         12

                               11111          7 2019-05-01         12

                               11111          7 2019-06-01         12

                               11111          8 2019-01-01         12

                               11111          8 2019-02-01         12

                               11111          8 2019-03-01         12

                               11111          8 2019-04-01         12

                               11111          8 2019-05-01         12

                               11111          8 2019-06-01         12

                           

                          1 person found this helpful
                          • 10. Re: solution to the given requirement below
                            user9294681

                            Thanks for the edit suggestion I will keep that in mind. The need for the start_date and end_date even though they are the same was to show it as a 24 hours duration

                             

                            Thanks also for the posted solutions Frank

                             

                            Thanks Mathguy for the posted solutions

                             

                            I will test the solutions with more sample data. Thanks

                            • 11. Re: solution to the given requirement below
                              user9294681
                              In your calendar table you have dates from 1 January to 9 January (not to 10 January as you perhaps thought), but in the output you only show data through 6 January. Perhaps that's a simple mistake on your part.

                               

                              I generated more dates in the calendar just as left over. Kindly note this is just sample data set, in the real data set we will have a calendar table that contains dates till 2090

                               

                               

                              Also, you have a default 'Main' district, tucked in with the other districts in a single table. That makes no sense; since you must look for the default price at least in some cases, that price should be in a separate table, which would be much smaller (resulting in much faster code). I wrote my solution in the simplest possible way - based on YOUR data model, flawed as it is in this respect; there are ways to make the code faster, without changing the data model, but that seems silly to me. Better fix the data model.

                               

                              Yes I agree the data model is definitely flawed. Unfortunately, it is a third-party data model which we do not have control over and hence cannot be changed.