locked
need a query RRS feed

  • Question

  • my table is

    TB1:

    idno                date_s                          date_e                             

    125                 2005-01-01                  2005-01-30           

    126                 2010-02-01                  2010-02-30            

    126                 2010-02-01                  2010-02-07            

    126                 2010-07-01                  2010-07-30            

    126                 2012-05-05                  2012-05-05            

    127                 2012-08-05                  2012-08-6              

    127                 2011-01-01                  2011-01-19            

    -------

    for each IDno: if date_s  is repeated in multiple rows, Only one of them should be counted

    for example:    idno=126  and date_s=2010-02-01    is repeated in two rows >>Therefore, only one is considered.

    OUTPUT:

    idno                 count(idno)

    125                  1

    126                  3

    127                  2


    • Edited by Ashkan209 Saturday, June 7, 2014 5:57 AM
    Saturday, June 7, 2014 5:54 AM

Answers

  • Rows of continuous changes

    for example = 10000 rows

    isnt this enough?

    SELECT idno,COUNT(DISTINCT date_s)
    FROM TB1
    GROUP BY idno


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Naomi N Sunday, June 8, 2014 5:52 PM
    • Marked as answer by Elvis Long Monday, June 16, 2014 9:54 AM
    Saturday, June 7, 2014 9:58 AM

All replies

  • declare @table table
    ( 
    
    idno           int,     date_s             date,             date_e      date )
    insert @table
    values (125     ,            '2005-01-01'     ,             '2005-01-30'    ),(
    126      ,           '2010-02-01'     ,            '2010-02-28'    ), (        
    126       ,          '2010-02-01'       ,           '2010-02-07'      ),(       
    126        ,         '2010-07-01'        ,          '2010-07-30'        ),(     
    126         ,        '2012-05-05'        ,         '2012-05-05'          ), (  
    127          ,       '2012-08-05'          ,        '2012-08-6'             ),(  
    127           ,     '2011-01-01'           ,       '2011-01-19'  )
    -------------------
    select a.idno , ( select count(distinct b.date_s) from @table as b where a.idno = b.idno  ) as cnt
    from @table as a
    group by idno 


    T-SQL e-book by TechNet Wiki Community
    My Blog
    My Articles

    Saturday, June 7, 2014 7:00 AM
  • The table above is presented as an example. In fact, this table has many rows.

    There is no possibility to Insert the above methodfor all rows

    Saturday, June 7, 2014 8:17 AM
  • Rows of continuous changes

    for example = 10000 rows

    Saturday, June 7, 2014 9:31 AM
  • Rows of continuous changes

    for example = 10000 rows

    isnt this enough?

    SELECT idno,COUNT(DISTINCT date_s)
    FROM TB1
    GROUP BY idno


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Naomi N Sunday, June 8, 2014 5:52 PM
    • Marked as answer by Elvis Long Monday, June 16, 2014 9:54 AM
    Saturday, June 7, 2014 9:58 AM
  • Rows of continuous changes

    for example = 10000 rows

    Well, 10K is not so much to store it as a separate column. It's better to add an index on column Idno that includes column date_s. 

    T-SQL e-book by TechNet Wiki Community
    My Blog
    My Articles


    Sunday, June 8, 2014 4:21 AM
  • You may try the below:

    Assuming the below scenario:

    1. Will never have two months overlap within date_s and date_e

    2. Can have any day of a month as date_s

    Select idno,count(distinct(cast(DATEPART(month,date_s) as varchar(2)) +''+Cast(DATEPART(YEAR,date_s) as varchar(4))))
    From @table
    Group by  idno
    

    Sunday, June 8, 2014 4:58 AM