need help to simplify this script
-
Hi All
Please help me in simplify this code and making it configurable.
Presently this script gives result of customers who purchased only one product like Soap \Pencil\Pen etc
i need this code as configurable
1. if i give 2 then this piece of code should give customers who has taken more than 2 products like Pen & Pencil combination can be of any thing.
2. if i give 3 then this piece of code should give customers who has taken more than 3 products like Pen & Pencil & Soap combination can be of any thing.
CREATE TABLE #Findings
(
CName VARCHAR(100),
Soaps INT,
Paste INT,
Choclates INT,
Biscuit INT,
Pencil INT,
Pen INT
)
INSERT INTO #Findings
VALUES
('Customer1',1,0,0,0,0,0),
('Customer2',1,1,1,0,0,0),
('Customer3',1,0,0,0,1,0),
('Customer4',1,0,0,0,0,0),
('Customer5',1,0,1,0,1,0),
('Customer6',1,0,1,1,0,0),
('Customer7',0,0,0,0,0,1),
('Customer8',0,0,0,0,1,0),
('Customer9',0,1,0,0,0,0)
select * from #Findings
WHERE (
(Soaps=1 AND Paste=0 AND Choclates=0 AND Biscuit=0 AND Pencil=0 AND Pen=0)
OR (Soaps=0 AND Paste=1 AND Choclates=0 AND Biscuit=0 AND Pencil=0 AND Pen=0)
OR (Soaps=0 AND Paste=0 AND Choclates=1 AND Biscuit=0 AND Pencil=0 AND Pen=0)
OR (Soaps=0 AND Paste=0 AND Choclates=0 AND Biscuit=1 AND Pencil=0 AND Pen=0)
OR (Soaps=0 AND Paste=0 AND Choclates=0 AND Biscuit=0 AND Pencil=1 AND Pen=0)
OR (Soaps=0 AND Paste=0 AND Choclates=0 AND Biscuit=0 AND Pencil=0 AND Pen=1)
)
drop table #Findings
-
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Great article Gail, I certainly learnt a lot reading it
Hope this helps,
Rock from VbCity -
In the data you provide the value are always 1 or 0. i.e. I guess if they buy 2 soaps, the value is nonetheless 1?
If that is so, then you can just add
WHERE soaps+pencils+biscuits...etc....=@myparam (or >=@myparam)
Did I miss something!?
-
NO for representing values i have used 1 it may contain more than one
-
Still not clear then if you buy 2 pencils does that count as 2 towards your total, or just one?
Depending on the answer, either the suggestion I provided or something similar but casting as bits
WHERE cast(soaps as bit)+cast(pencils as bit)+cast(biscuits as bit)...etc....=@myparam (or >=@myparam)
Still missing something?
-
CREATE TABLE #Findings
(
CName VARCHAR(100),
Soaps INT,
Paste INT,
Choclates INT,
Biscuit INT,
Pencil INT,
Pen INT
)
INSERT INTO #Findings
VALUES
('Customer1',1,0,0,0,0,0),
('Customer2',1,1,1,0,0,0),
('Customer3',1,0,0,0,1,0),
('Customer4',1,0,0,0,0,0),
('Customer5',1,0,1,0,1,0),
('Customer6',1,0,1,1,0,0),
('Customer7',0,0,0,0,0,1),
('Customer8',0,0,0,0,1,0),
('Customer9',0,1,0,0,0,0)
DECLARE @NbrProducts int = 1;
SELECT*
FROM#Findings
WHERECASE WHEN Soaps > 0 THEN 1 ELSE 0 END +
CASE WHEN Paste > 0 THEN 1 ELSE 0 END +
CASE WHEN Choclates > 0 THEN 1 ELSE 0 END +
CASE WHEN Biscuit > 0 THEN 1 ELSE 0 END +
CASE WHEN Pencil > 0 THEN 1 ELSE 0 END +
CASE WHEN Pen > 0 THEN 1 ELSE 0 END >= @NbrProducts
DROP TABLE #Findings
-
If your columns behave as bit columns instead of behaving as int, there's no need for the case, you can simply add the columns.
-
Artoo22 (6/5/2012)
Luis Cazares (6/5/2012)
If your columns behave as bit columns instead of behaving as int, there's no need for the case, you can simply add the columns.you cannot add bits
Sure you can, but not in this manner. However, they're not bits; they're only being used as bits would be, but they're ints.
-
Thanks you very much Artoo.
-
ok
-
Just to show that I'm keeping current on SQL 2008 stuff, and probably for no other reason, I thought I'd post a less verbose alternative.
SELECT Employee, NEW_LOB, NEW_DEPARTMENT, NEW_COST_CENTRE, NEW_MANAGER, NEW_JOB_CODE, NEW_CYCLE
FROM #Findings
WHERE @NbrProducts >
(SELECT COUNT(Products)
FROM (VALUES (NEW_LOB), (NEW_DEPARTMENT), (NEW_COST_CENTRE)
,(NEW_MANAGER), (NEW_JOB_CODE), (NEW_CYCLE)) t(Products))
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (6/5/2012)
Just to show that I'm keeping current on SQL 2008 stuff, and probably for no other reason, I thought I'd post a less verbose alternative.SELECT Employee, NEW_LOB, NEW_DEPARTMENT, NEW_COST_CENTRE, NEW_MANAGER, NEW_JOB_CODE, NEW_CYCLE
FROM #Findings
WHERE @NbrProducts >
(SELECT COUNT(Products)
FROM (VALUES (NEW_LOB), (NEW_DEPARTMENT), (NEW_COST_CENTRE)
,(NEW_MANAGER), (NEW_JOB_CODE), (NEW_CYCLE)) t(Products))
I like it 🙂
-
David McKinney (6/6/2012)
dwain.c (6/5/2012)
Just to show that I'm keeping current on SQL 2008 stuff, and probably for no other reason, I thought I'd post a less verbose alternative.SELECT Employee, NEW_LOB, NEW_DEPARTMENT, NEW_COST_CENTRE, NEW_MANAGER, NEW_JOB_CODE, NEW_CYCLE
FROM #Findings
WHERE @NbrProducts >
(SELECT COUNT(Products)
FROM (VALUES (NEW_LOB), (NEW_DEPARTMENT), (NEW_COST_CENTRE)
,(NEW_MANAGER), (NEW_JOB_CODE), (NEW_CYCLE)) t(Products))
I like it 🙂
Thanks! It was fun too. I don't often get a chance to use the new 2008 features (most of my work is in 2005) so when I saw an opportunity I jumped on it. Fair game I think as this was posted in the 2008 forum.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply