how to fetch number of records based on changing column value

Question

I am having a LEAVE table as following

EMPID       LEAVEDT       APPDT      LEAVETYPE
------      -------       -------    ---------
E1          10-3-13       5-3-13      CL
E1          11-3-13       5-3-13      CL
E1          12-3-13       5-3-13      CL
E1          13-3-13       5-3-13      SL
E1          14-3-13       5-3-13      SL
E1          15-3-13       5-3-13      PL
E1          16-3-13       5-3-13      CL
E1          17-3-13       5-3-13      CL
E1          18-3-13       5-3-13      PL
E1          19-3-13       5-3-13      PL
E1          20-3-13       5-3-13      CL

Now I want to fetch the record in which i want to show how many times and for how many days employee E1 has taken each type of leave. For the above example my query result should be :---

EMPID      FROM      TO         LEAVETYPE
-----      -----    ------      ---------
E1         10-3-13  12-3-13     CL
E1         13-3-13  14-3-13     SL
E1         15-3-13  15-3-13     PL
E1         16-3-13  17-3-13     CL
E1         18-3-13  19-3-13     PL
E1         20-3-13  20-3-13     CL

please help me with suitable sql query. Please note: empid and leavedt are primary keys of my table.

score 0 · Answer 1 · answered May 19 '13 at 06:21

With small modification to your table structure as below.

     create table leaves (empid varchar(10) , 
                 leavefromdt date,
                 leavetodate date,
                 appdate date,
                 leavetype varchar(2));

Sample data

      insert into leaves values('E1', Cast('3-10-2013' as datetime),
        Cast('3-12-2013' as datetime), Cast('3-5-2013' as datetime),'CL');

      insert into leaves values('E1',Cast('3-13-2013'as datetime),
        Cast('3-14-2013'as datetime),Cast('5-3-2013'as datetime),'SL');

      insert into leaves values('E1',Cast('3-15-2013'as datetime),
        Cast('3-15-2013'as datetime),Cast('5-3-2013'as datetime),'PL');

      insert into leaves values('E1',Cast('3-16-2013'as datetime),
         Cast('3-17-2013'as datetime),Cast('5-3-2013'as datetime),'CL');

Query

     select * , DATEDIFF(DAY, leavefromdt,leavetodate )+1 days from leaves
      order by empid,leavetype

Demo SQLFiddle

how to fetch number of records based on changing column value

1 Answers1