Query Rewrite

Hi all,

Here's the code I'd like to update, and below it a set of sample data:

Declare @StartDate DateTime
Declare @EndDate DateTime
Set @StartDate = '20-mar-2008'
Set @EndDate = '25-mar-2008'

SELECT

COUNT (iqs.childid) as Cnt
,CASE

--Search Categories and Create Initial Groupings--
WHEN Category LIKE '%Jewel%' THEN 'Accessories'
WHEN Category LIKE '%Beauty%' THEN 'Accessories'
WHEN Category LIKE '%Accs%' THEN 'Accessories'
WHEN Category LIKE '%Gift%' THEN 'Accessories'
WHEN Category LIKE '%Grooming%' THEN 'Accessories'
WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand'
WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand'
WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand'
WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand'
WHEN Category LIKE '%Female%' THEN 'Womenswear'
WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand'
WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand'
WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand'
WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand'
WHEN Category LIKE '%Male%' THEN 'MensOwnBrand'
END AS CategoryGroup
,CONVERT(VARCHAR(10), iqs.StatusDate, 103) AS StatusDate

FROM InventoryQueryStatus iqs
JOIN InventoryStatus [is]
ON [is].StatusID = iqs.StatusID
JOIN Inventory i
ON i.InventoryID = iqs.InventoryID
JOIN InventoryCategory ic
ON ic.CategoryID = i.CategoryID
WHERE iqs.StatusID = 31000
and Category NOT LIKE 'Force%'
--AND iqs.StatusDate >=GETDATE()-1
AND iqs.StatusDate BETWEEN @StartDate AND @EndDate
GROUP BY
CASE

--Search Categories and Create Initial Groupings--
WHEN Category LIKE '%Jewel%' THEN 'Accessories'
WHEN Category LIKE '%Beauty%' THEN 'Accessories'
WHEN Category LIKE '%Accs%' THEN 'Accessories'
WHEN Category LIKE '%Gift%' THEN 'Accessories'
WHEN Category LIKE '%Grooming%' THEN 'Accessories'
WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand'
WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand'
WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand'
WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand'
WHEN Category LIKE '%Female%' THEN 'Womenswear'
WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand'
WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand'
WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand'
WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand'
WHEN Category LIKE '%Male%' THEN 'MensOwnBrand'
END
,CONVERT(VARCHAR(10), iqs.StatusDate, 103)

Order By StatusDate


Sample Data:

164WomensOutsideBrand 20/03/2008
5MensOutsideBrand 20/03/2008
78WomensOwnBrand 20/03/2008
92WomensPremiumOutsideBrand 20/03/2008
1Accessories 20/03/2008

However, I'd like to enable a total for the day (340)

Thanks,

JB