PIVOT SQL Server Assistance

Question

Given the following table structure:

CrimeID | No_Of_Crimes  |   CrimeDate    | Violence | Robbery | ASB

1            1             22/02/2011         Y          Y      N

2            3             18/02/2011         Y          N      N

3            3             23/02/2011         N          N      Y

4            2             16/02/2011         N          N      Y

5            1             17/02/2011         N          N      Y

Is there a chance of producing a result set that looks like this with T-SQL?

Category   |  This Week |  Last Week
Violence          1           3
Robbery           1           0
ASB               3           1

Where last week shuld be a data less than '20/02/2011' and this week should be greater than or equal to '20/02/2011'

I'm not looking for someone to code this out for me, though a code snippet would be handy :), just some advice on whether this is possible, and how i should go about it with SQL Server.

For info, i'm currently performing all this aggregation using LINQ on the web server, but this requires 19MB being sent over the network every time this request is made. (The table has lots of categories, and > 150,000 rows). I want to make the DB do all the work and only send a small amount of data over the network

Many thanks

Answer 1

Late to the party, but a solution with an optimal query plan:

Sample data

create table crimes(
    CrimeID int, No_Of_Crimes int, CrimeDate datetime,
    Violence char(1), Robbery char(1), ASB char(1));
insert crimes
select 1,1,'20110221','Y','Y','N' union all
select 2,3,'20110218','Y','N','N' union all
select 3,3,'20110223','N','N','Y' union all
select 4,2,'20110216','N','N','Y' union all
select 5,1,'20110217','N','N','Y';

Make more data - about 10240 rows in total in addition to the 5 above, each 5 being 2 weeks prior to the previous 5. Also create an index that will help on crimedate.

insert crimes
select crimeId+number*5, no_of_Crimes, DATEADD(wk,-number*2,crimedate),
violence, robbery, asb
from crimes, master..spt_values
where type='P'

create index ix_crimedate on crimes(crimedate)

From here on, check output of each to see where this is going. Check also the execution plan.

Standard Unpivot to break the categories.

select CrimeID, No_Of_Crimes, CrimeDate, Category, YesNo
from crimes
unpivot (YesNo for Category in (Violence,Robbery,ASB)) upv
where YesNo='Y'

Notes:

1. The filter on YesNo is actually applied AFTER unpivoting. You can comment it out to see.

Unpivot again, but this time select data only for last week and this week.

select CrimeID, No_Of_Crimes, Category,
    Week = sign(datediff(d,CrimeDate,w.firstDayThisWeek)+0.1)
from crimes
unpivot (YesNo for Category in (Violence,Robbery,ASB)) upv
cross join (select DATEADD(wk, DateDiff(wk, 0, getdate()), 0)) w(firstDayThisWeek)
where YesNo='Y'
  and CrimeDate >= w.firstDayThisWeek -7
  and CrimeDate <  w.firstDayThisWeek +7

Notes:

1. (select DATEADD(wk, DateDiff(wk, 0, getdate()), 0)) w(firstDayThisWeek) makes a single-column table where the column contains the pivotal date for this query, being the first day of the current week (using DATEFIRST setting)
2. The filter on CrimeDate is actually applied on the BASE TABLE prior to unpivoting. Check plan
3. Sign() just breaks the data into 3 buckets (-1/0/+1). Adding +0.1 ensures that there are only two buckets -1 and +1.

The final query, pivoting by this/last week

select Category, isnull([1],0) ThisWeek, isnull([-1],0) LastWeek
from
(
    select Category, No_Of_Crimes,
        Week = sign(datediff(d,w.firstDayThisWeek,CrimeDate)+0.1)
    from crimes
    unpivot (YesNo for Category in (Violence,Robbery,ASB)) upv
    cross join (select DATEADD(wk, DateDiff(wk, 0, getdate()), -1)) w(firstDayThisWeek)
    where YesNo='Y'
      and CrimeDate >= w.firstDayThisWeek -7
      and CrimeDate <  w.firstDayThisWeek +7
) p
pivot (sum(No_Of_Crimes) for Week in ([-1],[1])) pv
order by Category Desc

Output

Category  ThisWeek    LastWeek
--------- ----------- -----------
Violence  1           3
Robbery   1           0
ASB       3           3

Answer 2

EDIT removed incorrect sql for clarity EDIT Forget the above try the below

select * 
from (
select wk, crime, SUM(number) number
from (
    select case when datepart(week, crimedate) = datepart(week, GETDATE()) then 'This Week'
            when datepart(week, crimedate) = datepart(week, GETDATE())-1 then 'Last Week'
            else 'OLDER' end as wk, 
            crimedate, 
            case when violence ='Y' then no_of_crimes else 0 end as violence, 
            case when robbery ='Y' then no_of_crimes else 0 end as robbery, 
            case when asb ='Y' then no_of_crimes else 0 end as asb 
    from crimetable) as src
UNPIVOT
    (number for crime in 
    (violence, robbery, asb)) as pivtab
group by wk, crime
) z
PIVOT
( sum(number)
for wk in ([This Week], [Last Week])
) as pivtab

Answer 3

I would try this:

declare @FirstDayOfThisWeek date = '20110220';
select cat.category, 
       ThisWeek = sum(case when cat.CrimeDate >= @FirstDayOfThisWeek
                     then crt.No_of_crimes else 0 end), 
       LastWeek = sum(case when cat.CrimeDate >= @FirstDayOfThisWeek
                     then 0 else crt.No_of_crimes end)
from crimetable crt
cross apply (values 
    ('Violence', crt.Violence),
    ('Robbery', crt.Robbery),
    ('ASB', crt.ASB))
    cat (category, incategory)
where cat.incategory = 'Y'
  and crt.CrimeDate >= @FirstDayOfThisWeek-7
group by cat.category;