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 recoverability

    We 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

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • 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