March 25, 2008 at 11:18 am
Query:
SELECT CategoryID, Category, CASE substring(category, 1, 4)
when 'fema' then 'female' WHEN 'Male' THEN 'Male' WHEN 'Gift' THEN 'Female' END AS CategoryGroup
FROM dbo.InventoryCategory
WHERE (Category NOT LIKE 'Force%')
Sample Data:
CategoryID Category CategoryGroup
1 Female Jewellery OB Female
2 Female Jewellery Brands Female
3 Female Underwear Brands Female
However, I need to rewrite the code so it returns as follows,
CategoryID Category CategoryGroup
1 Female Jewellery OB Female Accessories Own Brand
2 Female Jewellery Brands Female Accessories Outside Brand
3 Female Underwear Brand Womenswear Outside Brand
24 Male Accs OB Male Accessories Outside Brand
Thanks all,
Jaybee.
March 26, 2008 at 1:14 am
Where does that information you want in the CategoryGroup come from?
Please read the following on how to post problems for the fastest answers.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2008 at 2:42 am
SELECTCategoryID,
Category,
CASE
WHEN Category LIKE 'Fema%Jewel%OB' THEN 'Female Accessories Own Brand'
WHEN Category LIKE 'Fema%Jewel%Brand%' THEN 'Female Accessories Outside Brand'
WHEN Category LIKE 'Fema%Und%wear%Brand%' THEN 'Womenswear Outside Brand'
WHEN Category LIKE 'Male%Accs%OB' THEN 'Male Accessories Outside Brand'
END AS CategoryGroup
FROMdbo.InventoryCategory
WHERECategory NOT LIKE 'Force%'
N 56°04'39.16"
E 12°55'05.25"
March 26, 2008 at 3:46 am
Hi Peso, that code worked beautifully!
This will be part of a subquery/join, I hope you can take a look when I've clarified requirement.
Jaybee
March 26, 2008 at 10:08 am
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 f (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
JOINInventoryStatus [is]
ON[is].StatusID = iqs.StatusID
JOINInventory i
ONi.InventoryID = iqs.InventoryID
JOINInventoryCategory ic
ONic.CategoryID = i.CategoryID
WHEREiqs.StatusID = 31000
and Category NOT LIKE 'Force%'
March 26, 2008 at 11:26 pm
Is the category group stored anywhere? If not, have you considered creating a table for the cateory group and adding a foreign key to the InventoryCategory table?
It will make queries like this much simpler.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply