Fetch data in MS SQL 2008

Question

I have three tables which are like:

table1
 id,
 created_Date

table2
 id
 district_ID
 status_ID

table3
 district_ID
 district_Name

Now i need the records in following format

Srno  District_name     <10 days        >10 and <20 days       >20 days

1     xxx               12               15                    20
2     yyy               8                0                     2

count days as per current date

for example: if the created date is 10-08-2013 and current date is 13-08-2013 the date difference will be 3

So what should my query be? Any suggestions will be appreciated.

Thank you

table1

id      created_Date
1       2013-07-12 13:32:10.957
2       2013-07-12 13:32:10.957
3       2013-08-01 10:00:10.957
4       2013-08-10 13:32:10.957
5       2013-08-10 14:32:10.957


table2

id      district_ID   status_id
1       1             3
2       2             3
3       2             7
4       3             4
5       4             3

table1

district_ID    district_Name
1              xxx
2              yyy
3              zzz
4              aaa
5              bbb

Answer 1

;with cte as (
    select t3.district_Name, datediff(day, t1.created_Date, getdate()) as diff
    from table1 as t1 as t1
        inner join table2 as t2 on t2.id = t1.id
        inner join table3 as t3 on t3.district_id = t2.district_id
)
select
    district_Name,
    sum(case when diff < 10 then 1 else 0 end) as [<10 days],
    sum(case when diff >= 10 and diff < 20 then 1 else 0 end) as [>=10 and < 20 days],
    sum(case when diff >= 20 then 1 else 0 end) as [>= 20 days]
from cte
group by district_Name

Answer 2

I would have a look at using DATEDIFF and CASE.

DATEDIFF (Transact-SQL)

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

Something like

SELECT  District_name,
        SUM(
                CASE 
                    WHEN DATEDIFF(day,created_Date, getdate()) < 10
                        THEN 1
                    ELSE 0
                END
            ) [<10 days],
        SUM(
                CASE 
                    WHEN DATEDIFF(day,created_Date, getdate()) >= 10 AND DATEDIFF(day,created_Date, getdate()) < 20
                        THEN 1
                    ELSE 0
                END
            ) [>10 and <20 days],
        SUM(
                CASE 
                    WHEN DATEDIFF(day,created_Date, getdate()) >= 20
                        THEN 1
                    ELSE 0
                END
            ) [>20 days]
FROM    Your_Tables_Here
GROUP BY    District_name