Untitled

use [PROD-US-MO-BJC-6-20-2012]

with lastTaskTime as
(
        select top 1 PrintedTime from GenericPrintDriverTasksQueue
        order by PrintedTime desc
),

[1weekago] as
(
        select LogicalUnitID,
                COUNT(GenericPrintDriverTasksQueue.LogicalUnitID)
                as [1 week ago] from GenericPrintDriverTasksQueue
        where PrintedTime between DATEADD
        (
                d, -7 , (select * from lastTaskTime)
        )
        and (select * from lastTaskTime)
        group by logicalunitID
),

[2weeksago] as
(
        select LogicalUnitID,
                COUNT(GenericPrintDriverTasksQueue.LogicalUnitID)
                as [2 weeks ago] from GenericPrintDriverTasksQueue
        where PrintedTime between DATEADD
        (
                d, -14 , (select * from lastTaskTime)
        )
        and DATEADD
        (
            d, -8 , (select * from lastTaskTime)
        )
        group by logicalunitID
),

[3weeksago] as
(
        select LogicalUnitID,
                COUNT(GenericPrintDriverTasksQueue.LogicalUnitID)
                as [3 weeks ago] from GenericPrintDriverTasksQueue
        where PrintedTime between DATEADD
        (
                d, -21 , (select * from lastTaskTime)
        )
        and DATEADD
        (
            d, -15 , (select * from lastTaskTime)
        )
        group by logicalunitID
),

[4weeksago] as
(
        select LogicalUnitID,
                COUNT(GenericPrintDriverTasksQueue.LogicalUnitID)
                as [4 weeks ago] from GenericPrintDriverTasksQueue
        where PrintedTime between DATEADD
        (
                d, -28 , (select * from lastTaskTime)
        )
        and DATEADD
        (
            d, -22 , (select * from lastTaskTime)
        )
        group by logicalunitID
),

[5weeksago] as
(
        select LogicalUnitID,
                COUNT(GenericPrintDriverTasksQueue.LogicalUnitID)
                as [5 weeks ago] from GenericPrintDriverTasksQueue
        where PrintedTime between DATEADD
        (
                d, -35 , (select * from lastTaskTime)
        )
        and DATEADD
        (
            d, -29 , (select * from lastTaskTime)
        )
        group by logicalunitID
)

select logicalunits.Name,
        [1weekago].[1 week ago], [2weeksago].[2 weeks ago], [3weeksago].[3 weeks ago],
        [4weeksago].[4 weeks ago], [5weeksago].[5 weeks ago]
from [1weekago]
inner join [2weeksago]
        on [1weekago].logicalunitID = [2weeksago].LogicalUnitID
inner join [3weeksago]
        on [1weekago].logicalunitID = [3weeksago].LogicalUnitID
inner join [4weeksago]
        on [1weekago].logicalunitID = [4weeksago].LogicalUnitID
inner join [5weeksago]
        on [1weekago].logicalunitID = [5weeksago].LogicalUnitID
inner join logicalunits
        on [1weekago].LogicalUnitID = logicalunits.Logicalunitid