Question: i have one table contain 2 fields named as date,val
datas are

```
date        val
2014-08-01   A
2014-08-02   B
2014-08-03   A
2014-08-04   A
2014-08-05   B
2014-08-06   B
2014-08-07   A
2014-08-08   A
2014-08-09   B
2014-08-10   A
2014-08-11   A

```

i want a table the output like this

```
MIN        MAX        A B
2014-08-01  2014-08-05 3 2
2014-08-06  2014-08-11 4 2

```

The result will be calculated based on number of days, like for 5 days. in this question I selected 5 days interval.From this table i want to find the count of value 'A' and the count of value 'B' in the selected interval. 
Can any one find a solution for this. Any help will be appreciated.

Answer: 
i think this will help you :

```
    DECLARE @MinDate DATETIME
DECLARE @MaxDate DATETIME
DECLARE @StartDate DATETIME
DECLARE @DayCount INT
DECLARE @A_Count INT
DECLARE @B_Count INT

SET @DayCount = 5
SELECT  TOP 1 @MinDate=[date] FROM table_1 ORDER BY date
SELECT  TOP 1 @MaxDate=[date] FROM table_1 ORDER BY date DESC

CREATE TABLE #temp_table ( 
    _Min DATETIME,
    _Max DATETIME,
    _A INT,
    _B INT
)

SET @StartDate=@MinDate
WHILE @StartDate < @MaxDate
BEGIN
    SELECT @A_Count = COUNT(*) FROM table_1 WHERE date BETWEEN @StartDate AND DATEADD(dd,@DayCount-1,@StartDate) AND val = 'A'
    SELECT @B_Count = COUNT(*) FROM table_1 WHERE date BETWEEN @StartDate AND DATEADD(dd,@DayCount-1,@StartDate) AND val = 'B'
    INSERT INTO #temp_table VALUES (@StartDate,DATEADD(dd,@DayCount-1,@StartDate),@A_Count,@B_Count)
    SET @StartDate = DATEADD(dd,@DayCount,@StartDate)
END
SELECT * FROM #temp_table
DROP TABLE #temp_table

```

and SqlFiddle demo: [click here](http://sqlfiddle.com/#!3/09676f/6/0)
You can execute a query like this: 

```
SELECT MAX(date), MIN(date), val, COUNT(val) AS occurence
FROM table 
WHERE date>minDate AND date<maxDate
GROUP BY val

```

where **minDate** and **maxDate** are your varables.

Result will be read in this way: Value "val" apper "occurence" time between minDate and maxDate