Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Adding a variable, grouping & count to a Procedure

Author  Topic 

jaybee
Yak Posting Veteran

72 Posts
Posted - 2008-03-26 : 12:18:50
Hi again, I've got the rewrite below, but what I'd like to do is to add a variable that allows the client to enter a start date and finish date, and also to group the results by the categories below (Accessories/Womenswear) along with a count.

SELECT i.SKU , iqs.StatusDate, ic.CategoryID, Category, CASE

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

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%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-03-26 : 12:56:42
[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '20080301',
@EndDate = '20080401'

SELECT CategoryGroup,
SUM(Items)
FROM (
SELECT CASE
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,
CASE
WHEN iqs.StatusDate >= @StartDate AND iqs.StatusDate < @EndDate THEN 1
ELSE 0
END AS Items
FROM InventoryQueryStatus AS iqs
INNER JOIN InventoryStatus AS [is] ON [is].StatusID = iqs.StatusID
INNER JOIN Inventory AS i ON i.InventoryID = iqs.InventoryID
INNER JOIN InventoryCategory AS ic ON ic.CategoryID = i.CategoryID
WHERE iqs.StatusID = 31000
AND Category NOT LIKE 'Force%'
) AS d
GROUP BY CategoryGroup
ORDER BY CategoryGroup[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-03-26 : 12:57:34
[code]SELECT t.CategoryGroup,COUNT(*) FROM
(SELECT i.SKU , iqs.StatusDate, ic.CategoryID, Category, CASE

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

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 BETWEEN @StartDate AND @EndDate)t
GROUP BY CategoryGroup
[/code]

where @StartDate and @EndDate are your date parameters
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2008-03-26 : 13:31:09
Hi guys, that indeed gets me closer, but what I want is in ADDITION to what I'm getting below, not instead of!!

Here's some sample data, sorry it's a bit squashed!!

SKU StatusDate CategoryID Category CategoryGroup
MAUAB225446 16/01/2008 50 Female Nightwear Brands WomensOutsideBrand
MAUAB225446 16/01/2008 50 Female Nightwear Brands WomensOutsideBrand
MWFAA225471 03/12/2007 6 Female Footwear OB WomensOwnBrand
MWFAA225471 03/12/2007 6 Female Footwear OB WomensOwnBrand

MAUAB225446 16/01/2008 50 Female Nightwear Brands WomensOutsideBrand
MAUAB225446 16/01/2008 50 Female Nightwear Brands WomensOutsideBrand
MWFAA225471 03/12/2007 6 Female Footwear OB WomensOwnBrand
MWFAA225471 03/12/2007 6 Female Footwear OB WomensOwnBrand
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-03-26 : 13:53:52
[code]SELECT t1.SKU , t1.StatusDate, t1.CategoryID, t1.Category,t2.CategoryGroup
FROM (SELECT i.SKU , iqs.StatusDate, ic.CategoryID, Category, CASE

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

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 BETWEEN @StartDate AND @EndDate
)t1
INNER JOIN
(
SELECT t.Category,t.CategoryGroup,COUNT(*) FROM
(SELECT i.SKU , iqs.StatusDate, ic.CategoryID, Category, CASE

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

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 BETWEEN @StartDate AND @EndDate)t
GROUP BY Category,CategoryGroup)t2
ON t2.Category=t1.Category[/code]
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2008-03-27 : 05:17:46
Looks like a great piece of code, but getting this error message:

Msg 8155, Level 16, State 2, Line 4
No column was specified for column 3 of 't2'.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-03-27 : 05:22:25
You should know how to fix this!

SELECT t.Category,t.CategoryGroup,COUNT(*) FROM

SELECT t.Category,t.CategoryGroup,COUNT(*) AS MyNameHere FROM



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site