sql query requirement

  • As mentioned in the stored procedure, I did not understand the requirements for that part.

    This query should do it :

    select top 6 B.dte

    from

    (

      select id=1,dte = min(dte) from dbo.tst

     union all

      select id=2,dte = max(dte) from dbo.tst

     union all

      select id=14-month,A.dte from

      (

       select TOP 4 month = datepart(mm,dte),dte = max(dte)

       from dbo.tst

       group by datepart(mm,dte)

       having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)

       order by dte desc

      /**/) A

     union all

      select id=(100*(25-month))+(99-datepart(dd,dte)),A.dte from

      (

       select TOP 6 month = datepart(mm,dte),dte

       from dbo.tst

       where dte not in (   select dte = min(dte) from dbo.tst

          union all

           select dte = max(dte) from dbo.tst

          union all

           select dte from (

           select TOP 4 month = datepart(mm,dte),dte = max(dte)

           from dbo.tst

           group by datepart(mm,dte)

           having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)

           order by dte desc ) A

          )

       order by dte desc

      /**) A

    ) B

    order by id

     

    ----- and this is the new procedure :

     

    create procedure dbo.Shankar as

    begin

     set nocount on

     declare @result_table table (  dte datetime not null UNIQUE clustered/**/)

     declare @mindte datetime,

      @maxdte datetime,

      @curdte datetime,

      @count int

     -- Get min and max

      select @mindte = min(dte), @maxdte = max(dte), @curdte = getdate() from dbo.tst

     -- add min and max to result

     insert @result_table (dte) values (@mindte)

     insert @result_table (dte) values (@maxdte)

     -- get maximum 4 records, each time the maximum for a month

     -- starting from first month

     -- but do not take dates allready used

     

     insert @result_table (dte)

     select  TOP 4 A.dte from (

      select TOP 4 month = datepart(mm,dte),dte = max(dte)

      from dbo.tst

      group by datepart(mm,dte)

      having max(dte) not in ( select dte from @result_table)

      order by dte desc) A

      

     -- count how much records to add

     select @count = 6 - count(*) from @result_table

     -- if still records to add

     if @count > 0

     begin

      set rowcount @count

      insert @result_table (dte)

      select dte

      from dbo.tst

      where dte not in ( select dte from @result_table )

      order by dte desc

     

      set rowcount 0

     end

     select * from @result_table

    end

  • Let me put out my requirements clearly

    This is the data that i have in the table

    2004-01-01 00:00:00.000

    2004-01-02 00:00:00.000

    2004-01-03 00:00:00.000

    2004-02-07 00:00:00.000

    2004-02-14 00:00:00.000

    2004-03-06 00:00:00.000

    2004-03-19 00:00:00.000

    2004-03-24 00:00:00.000

    2004-03-28 00:00:00.000

    2004-03-29 00:00:00.000

    2004-03-31 00:00:00.000

    My output should be like this

     01 Jan 2004

     03 Jan 2004

     14 Feb 2004

     28 Mar 2004

     29 Mar 2004

     31 Mar 2004

    If i have data in the table like this

    01 Jan 2004

    02 Jan 2004                                                                                       01 Feb 2004

    07 Feb 2004

    04 Mar 2004

    06 Apr 2004

    19 Apr 2004

    04 May 2004

    28 May 2004

    03 Jun 2004

    05 Jul 2004

    Then my outout should be

    01 Jan 2004

    04 Mar 2004

    19 Apr 2004

    28 May 2004

    03 Jun 2004

    05 Jul 2004

    The whole idea is like this.

    I need to display 6 Records at a time

    First record is never a problem

    the question is the last 5 records.

    If i have data for more than 6 months, then i pick up the

    Max date for every month and add it to the First row and display the records

    This should be in descending. starting from the Latest month (5 records)

    If i dont have data for 6 months.

    I find out how many months are there, pick up the Max date for every month

    Check if this count is actually = 4.

    If its not, go to the current month and check how many records are there.

    If the number of records in the current month matches the required records, then populate all

    these records and show.

    If the number of records in the current month does not match the required records, then

    goto Month-1 and get all the records to make the count 5.

    Keep doing this till i get my count of records as 6

    This is my requirement

    Hope its clear now

     

     

     

  • the query I posted should do this, but will not work if the dates are not in the same year. This new query should do the job.

    query :

    select dte from

    (

    select top 6 B.dte

    from

    (

      select id='999999999',dte = min(dte) from dbo.tst

     union all

      select id='999999998',dte = max(dte) from dbo.tst

     union all

      select id='8' + convert(varchar(8),A.dte,112),A.dte from

      (

       select TOP 4 month = datepart(mm,dte),dte = max(dte)

       from dbo.tst

       group by datepart(mm,dte)

       having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)

       order by dte desc

      /**/) A

     union all

      select id='7' + convert(varchar(8),A.dte,112),A.dte from

      (

       select TOP 6 dte

       from dbo.tst

       where dte not in (   select dte = min(dte) from dbo.tst

          union all

           select dte = max(dte) from dbo.tst

          union all

           select dte from (

           select TOP 4 month = datepart(mm,dte),dte = max(dte)

           from dbo.tst

           group by datepart(mm,dte)

           having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)

           order by dte desc ) A

          )

       order by dte desc

      /**/) A

    ) B

    order by id desc

    ) C

    order by dte

  • hey ...

    looks like the query does not fit this criteria

    01 Jan 2004

    02 Jan 2004

    07 Jan 2004

    01 Feb 2004

    04 Feb 2004

    06 Feb 2004

    04 Mar 2004

    09 Mar 2004

    18 Mar 2004

    20 Mar 2004

    28 Apr 2004

    The output should be like this

    01 Jan 2004

    06 Feb 2004

    18 Mar 2004

    19 Mar 2004

    20 Mar 2004

    28 Apr 2004

    But i get the output as

    2004-01-01 00:00:00.000

    2004-01-07 00:00:00.000

    2004-02-06 00:00:00.000

    2004-03-18 00:00:00.000

    2004-03-20 00:00:00.000

    2004-04-28 00:00:00.000

  • SELECT CONVERT(char(12),dt,113)

    FROM

    (SELECT MIN(Dt) dt

     FROM Dates

     UNION ALL

     SELECT TOP 4 dt

     FROM

     (SELECT MAX(Dt) dt

      FROM Dates

      WHERE Dt <

      (SELECT MAX(Dt)

       FROM Dates)

      GROUP BY CONVERT(char(6),Dt,112)) m

     ORDER BY dt DESC

     UNION ALL

     SELECT MAX(Dt)

     FROM Dates) o

    ORDER BY dt



    --Jonathan

  • 19 Mar 2004 is not in the input ... I supposed only records in the input table should come up ?

  • oops... i'm sorry

    i wanted to say 09 March and instead i typed 19 March there...

    sorry the output should have it as 09 Mar 2004

  • Oops, you do not want the max(dte) fro the first month ? :

     

    select dte from

    (

    select top 6 B.dte

    from

    (

      select id='999999999',dte = min(dte) from dbo.tst

     union all

      select id='999999998',dte = max(dte) from dbo.tst

     union all

      select id='8' + convert(varchar(8),A.dte,112),A.dte from

      (

       select TOP 4 month = datepart(mm,dte),dte = max(dte)

       from dbo.tst

       where datepart(yy,dte)*1000 + datepart(mm,dte) != ( select datepart(yy,min(dte))*1000 + datepart(mm,min(dte)) from dbo.tst )

       group by datepart(mm,dte)

       having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)

       order by dte desc

     /**/) A

     union all

      select id='7' + convert(varchar(8),A.dte,112),A.dte from

      (

       select TOP 6 dte

       from dbo.tst

       where dte not in (   select dte = min(dte) from dbo.tst

          union all

           select dte = max(dte) from dbo.tst

          union all

           select dte from (

           select TOP 4 month = datepart(mm,dte),dte = max(dte)

           from dbo.tst

           where datepart(yy,dte)*1000 + datepart(mm,dte) != ( select datepart(yy,min(dte))*1000 + datepart(mm,min(dte)) from dbo.tst )

           group by datepart(mm,dte)

           having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)

           order by dte desc ) A

          )

       order by dte desc

      /**/) A

    ) B

    order by id desc

    ) C

    order by dte

  • Thanks a ton...

    I got my requirement fixed beautifully.

    Thanks a lot for all the people who took efforts to get my think done.

    Without u guys..it would have been really tough for me...

    But, i have learnt a lot of SQL from this assignment. \

    Hopefully, from next time i'll be able to work it out on my own

    THANKS A TON again...

    One last thing...

    Can u explain me the Logic on how u have done this .....I can be prepared for

    debugging from Tomm, even if there are any changes to the logic

     

    THANKS AGAIN

    Shankar

     

  • You can copy/paste the following lines. Whiles explaining the query, I changed it a little bit. This new "explained" (?) query should do it :

    /*

    Shankar query :

    I will try to explain how the query has been build.

    Step 1 : get the minimum and the maximum :

     select dte=min(dte) from dbo.tst

    union all

     select dte = max(dte) from dbo.tst

    Step 2 : select the max date for each month ( max 4 records ), starting from the last month.

       max 4 records : TOP 4

      starting from the last month : order by dte desc

      max by month : group by month => the month has to be in the select.

      As the dates are not always from the same year, a month will be defined as YYYYMM.

      The easeast way to get this, is to use convert(char(6),dte,112), or if you want to be sure

      not having warning messages about truncation, left(convert(char(8),dte,112),6)

      Also, the previous selected dates may not be used as a result => in the having clause.

      Also, we do not want results from the first month. => exclude the using the where clause.

      Should should read the BOL about the difference between where and having clause.

     select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)

     from dbo.tst

     where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )

     group by left(convert(char(8),dte,112),6)

     having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )

     order by dte desc

     as I have to do a union with the result of step 1, so I use the previous query as a 'derived table', and

     select only the dte from it

     select step2.dte from ( <<previous query>> ) step2

    Query up till now :

     select dte=min(dte) from dbo.tst

    union all

     select dte = max(dte) from dbo.tst

    union all

     select step2.dte from

     (

      select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)

      from dbo.tst

      where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )

      group by left(convert(char(8),dte,112),6)

      having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )

      order by dte desc

      ) step2

    step 3 : add dates starting from the last date to make sure we have at least 6 dates. Exclude allready used dates.

      The query for this would be like :

     select TOP 6 dte -- I never need more then 6 dates

     from db.tst

     where dte not in ( <<dates allready selected>> )

     order by dte desc

     and I have to place the query I allready have till now in the <<dates allready selected>> part.

     

     Now we still have to solve 1 problem.

     When using union, SQL server wil try to output each date only once. We con not use this, because we have a certain order

     to respect for the queries. I can use "union all" to tell SQL server the result should not be unique, but At that point I can

     not be sure for 100% that the results will allways be in the specific order. As only the first 6 dates should

     selected from the total query, I have to force an order. Step 1 should have priority 1, step 2 2 and step 3 3. In each step, the order by

     clause should be respected. => I wil add an id colomn to each subquery.

     for step 1, id can be a constant value of 1

     for step 2, the value should be > the value of step 1, and the biggest date should have the smallest values

     => PROBLEM. The easiest way is to reverse the order, and have step 1 an id > step 2 > step 3.

     so, as the order for step 3 and step 4 are based on a date value, the best way to have an id is to use the date in

     format YYYYMMDD. add the step number as first character ( but step 1 should be > step 2, so I start to count from 9 down to 7 ),

     and now I have I good orderby criteria. Step 1 and Step 2 get A constant value, as big as possible

     SO the result of all this :

      select id='999999999',dte = min(dte) from dbo.tst

     union all

      select id='999999998',dte = max(dte) from dbo.tst

     union all

      select id='8' + convert(varchar(8),step2.dte,112),step2.dte from

      (

       select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)

       from dbo.tst

       where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )

       group by left(convert(char(8),dte,112),6)

       having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )

       order by dte desc

       ) step2

     union all

      select id='7' + convert(varchar(8),step3.dte,112),step3.dte from

      (

       select TOP 6 dte

       from dbo.tst

       where dte not in (   select dte = min(dte) from dbo.tst

          union all

           select dte = max(dte) from dbo.tst

          union all

           select step2.dte from

           (

            select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)

            from dbo.tst

            where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )

            group by left(convert(char(8),dte,112),6)

            having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )

            order by dte desc

            ) step2

          )

       order by dte desc

       ) step3

    step 4 : from the above query, only get the first 6 records

     select TOP 6 Alldte.dte from ( <<above query>> ) Alldte order by Alldte.id desc

    step 5 : I want the finale result ordered by date :

     select Final.dte from ( <<above query>> ) Final order by Final.dte

    RESULT :

    */

    select Final.dte from

    (

     select TOP 6 Alldte.dte from

     (

       select id='999999999',dte = min(dte) from dbo.tst

      union all

       select id='999999998',dte = max(dte) from dbo.tst

      union all

       select id='8' + convert(varchar(8),step2.dte,112),step2.dte from

       (

        select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)

        from dbo.tst

        where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )

        group by left(convert(char(8),dte,112),6)

        having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )

        order by dte desc

        ) step2

      union all

       select id='7' + convert(varchar(8),step3.dte,112),step3.dte from

       (

        select TOP 6 dte

        from dbo.tst

        where dte not in (   select dte = min(dte) from dbo.tst

           union all

            select dte = max(dte) from dbo.tst

           union all

            select step2.dte from

            (

             select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)

             from dbo.tst

             where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )

             group by left(convert(char(8),dte,112),6)

             having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )

             order by dte desc

             ) step2

           )

        order by dte desc

        ) step3

      ) Alldte

     order by Alldte.id desc

    ) Final

    order by Final.dte

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply