1 2 Previous Next 19 Replies Latest reply: Feb 13, 2008 2:27 PM by Rob van Wijk RSS

    SQL Query for a Date Range.

    620410
      I need to fetch the count for a date range greater than or equal to Monday of the week and less than or equal to sunday of the same week for the past 20 weeks.

      for Ex: since 01/21/2008 is a monday and 01/27/2008 is a sunday, i want the count of a particular field in that range for the week as well as for the previous 20 weeks till 09/22/2007 (Monday) and 09/28/2007(Sunday).

      If an SQL statement can be built on the above mentioned condition it would be helpful to me.

      Thanks.
      Chandrashekar
        • 1. Re: SQL Query for a Date Range.
          RadhakrishnaSarma
          As I understand, you just want to eliminate Sundays and get the data for the current as well as past 20 weeks. Is it not a simple count(*) with a WHERE to_char(date, 'DAY') not equal to 'SUNDAY'?


          Cheers
          Sarma.
          • 2. Re: SQL Query for a Date Range.
            620410
            Sundays are also included. It is from Monday to Sunday. How do i loop through and get back the past 20 weeks data.

            please let me know.

            Thanks.
            Chandrashekar
            • 3. Re: SQL Query for a Date Range.
              RadhakrishnaSarma
              please let me know.
              Only when you can give me your table structure and sample data with expected results.

              You use [pre] and [/pre] tags to preserve any formatiing for query or sample data and expected results.

              Cheers
              Sarma.
              • 4. Re: SQL Query for a Date Range.
                620410
                I need to fetch the data for total defects for the week falling from Monday to Sunday of the week and the previous 20 weeks.

                The fields are DEFECT_ID, FIXED_DATE from DEFECTS Table.

                The FIXED_DATE is the date for which i need to fetch the data from Monday to Sunday of the week and the previous 20 Weeks which will give me the Total defects falling for the particular week.

                Thanks.
                Chandrashekar
                • 5. Re: SQL Query for a Date Range.
                  566473
                  Try like this:
                  select nullif(level - 1,0) as num_week_ago,
                         start_date - (level - 1) * 7 + 0 as f1,
                         start_date - (level - 1) * 7 + 1 as f2,
                         start_date - (level - 1) * 7 + 2 as f3,
                         start_date - (level - 1) * 7 + 3 as f4,
                         start_date - (level - 1) * 7 + 4 as f5,
                         start_date - (level - 1) * 7 + 5 as f6,
                         start_date - (level - 1) * 7 + 6 as f7
                    from (select to_date('01/21/2008','mm/dd/yyyy') as start_date, 20 as num_week from dual) v
                  connect by level <= num_week
                  Or like this:
                  select start_date - level + 1 as f1
                    from (select to_date('01/21/2008','mm/dd/yyyy') as start_date, 20 as num_week from dual) v
                  connect by level <= num_week * 7
                  • 6. Re: SQL Query for a Date Range.
                    RadhakrishnaSarma
                    Okay. Let me ask you.

                    You want to have data like this?
                    week ending 26th Jan  - 20 defects
                    week ending 19th Jan  - 12 defects
                    week ending 12th Jan  - 10 defects
                    week ending 05th Jan  - 22 defects
                    Then you have something called week number.
                    to_char(fixed_date, 'YYYYWW')
                    You can group by on this and can get the sum(defect_id).


                    Cheers
                    Sarma.
                    • 7. Re: SQL Query for a Date Range.
                      620410
                      Thanks Sergey,

                      I am new to Oracle so i am not very clear .

                      What is f1, f2 and also what does level mean?

                      I need to get the total defects for the week for the last 20 weeks

                      First Condition:

                      STATUS Field value must be "Open" and for that, the FIXED_DATE
                      must be >= the Monday of the week AND <= the Sunday of the week being counted.

                      Second Condition:

                      STATUS Field value must be "Closed" and for that, the FIXED_DATE
                      must be >= the Monday of the week AND <= the Sunday of the week being counted.

                      I need to get the defects count for Open and Closed status for both conditions.

                      If you can build an SQL for me it would be really helpful.
                      • 8. Re: SQL Query for a Date Range.
                        620410
                        Thanks Sarma,

                        I tried. i need to know how to build an SQL for previous 20 weeks. Should it be in a loop?

                        Please provide me an SQL structure for the same.
                        • 9. Re: SQL Query for a Date Range.
                          Rob van Wijk
                          > Please provide me an SQL structure for the same.

                          Please provide a small test case: a create table statements, a few insert statements and input with expected output.

                          Regards,
                          Rob.
                          • 10. Re: SQL Query for a Date Range.
                            519688
                            select trunc(date_col,'IW'), count(*)
                            from my_table
                            where date_col >= trunc(date_col,'IW') - (19*7)
                            group by trunc(date_col,'IW')


                            trunc(date,'IW') gives you the monday of that week (weeks running monday to sunday)

                            so, group by trunc(date,'IW') will group Jan 21, 2008 - Jan 27, 2008 together

                            to get a 2 week range, subtract 19*7 days (7 days per week, 19 weeks, since the week of Jan 21 is one week so you really only need 19 more weeks).

                            btw, 9/22/07 was a Saturday, not a monday.
                            • 11. Re: SQL Query for a Date Range.
                              620410
                              shoblock,

                              I am getting error as "Invalid Number" when i execute the SQL statement. Please let me know what needs to be done.
                              • 12. Re: SQL Query for a Date Range.
                                APC
                                I am getting error as "Invalid Number" when i execute the SQL statement.
                                That seems a bit unlikely, if it really involves only columns of a date datatype.
                                Please let me know what needs to be done.
                                Please follow Rob's advice and post a create table statement and some sample data so we can re-create the problem.

                                Cheers, APC

                                Blog : http://radiofreetooting.blogspot.com/
                                • 13. Re: SQL Query for a Date Range.
                                  620410
                                  I have given the Create and Insert Statements

                                  CREATE TABLE DEFECTS (DEFECT_ID CHAR(10), FIXED_DATE DATE, STATUS CHAR(20))

                                  INSERT INTO DEFECTS (DEFECT_ID, FIXED_DATE, STATUS) VALUES ('001', '2000-01-01', 'OPEN')

                                  What i need is the total defects for the last 20 weeks from Monday to Sunday of every week based on the Fixed Date and Status.

                                  First Condition:

                                  STATUS Field value must be "OPEN" and for that, the FIXED_DATE
                                  must be >= the Monday of the week AND <= the Sunday of the week being counted.

                                  Second Condition:

                                  STATUS Field value must be "CLOSED" and for that, the FIXED_DATE
                                  must be >= the Monday of the week AND <= the Sunday of the week being counted.

                                  I need to get the defects count for Open and Closed status for both conditions.
                                  • 14. Re: SQL Query for a Date Range.
                                    Rob van Wijk
                                    So how does your expected output look like in this case?
                                    1 2 Previous Next