Thinking outside the box

/*******************************************************************************
  Prepare script with testcase
*******************************************************************************/

DECLARE     @XMLString XML,
            @ElementEqual VARCHAR(50),
            @ElementLike VARCHAR(50),
            @AttributeEqual VARCHAR(50),
            @AttributeLike VARCHAR(50)

SELECT      @XMLString = '
                  <Customers>
                        <Customer>
                              <FirstName>Kevin</FirstName>
                              <LastName>Goff</LastName>
                              <City type="aca">Camp Hill</City>
                        </Customer>
                        <Customer>
                              <FirstName>Steve</FirstName>
                              <LastName>Goff</LastName>
                              <City type="acb"> Philadelphia</City>
                        </Customer>
                  </Customers>',
      @ElementEqual = 'Camp Hill',
      @ElementLike = 'adel',
      @AttributeEqual = 'acb',
      @AttributeLike = 'c'

/*******************************************************************************
  Search for City-nodes
*******************************************************************************/

-- Get all City where element is equal to "Camp Hill"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City/text()[. = "Camp Hill"]') = 1

-- Get all City where element is equal to @ElementEqual
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City/text()[. = sql:variable("@ElementEqual")]') = 1

-- Get all City where element like "adel"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(. , "adel")]') = 1

-- Get all City where element like @ElementLike
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(. , sql:variable("@ElementLike"))]') = 1

/*******************************************************************************
  Search for Type-attribute in City-nodes
*******************************************************************************/

-- Get all City where attribute is equal to "acb"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City[@type = "acb"]') = 1

-- Get all City where attribute is equal to @AttributeEqual
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City[@type = sql:variable("@AttributeEqual")]') = 1

--Get all City where attribute like "c"
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(@type, "c")]') = 1

-- Get all City where attribute like @AttributeLike
SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName, 
            cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName, 
            cust.coldef.value('City[1]','VARCHAR(20)') AS City,
            cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ
FROM        @XMLString.nodes('/Customers/Customer') AS cust(coldef)
WHERE       cust.coldef.exist('City [contains(@type, sql:variable("@AttributeLike"))]') = 1

-- Prepare sample data
DECLARE       @Keywords TABLE
       (
              FaqID INT,
              Keyword VARCHAR(200)
       )

INSERT @Keywords
SELECT 1, 'help' UNION ALL
SELECT 1, 'resolve' UNION ALL
SELECT 1, 'issue' UNION ALL
SELECT 2, 'Red Herring'

DECLARE       @Faq TABLE
       (
              FaqID INT,
              Question VARCHAR(MAX),
              Answer VARCHAR(MAX)
       )

INSERT @Faq
SELECT 1, 'This is stupid question', 'This is a stupid answer'

DECLARE @Question AS VARCHAR(200)

-- Prepare user supplied parameter
SET @Question = 'How can we help resolve your issue'

-- Show the expected result
SELECT        f.Question,
              f.Answer
FROM          @Faq AS f
INNER JOIN    (
                     SELECT        FaqID         
                     FROM          @Keywords
                     GROUP BY      FaqID
                     HAVING        SUM(CASE WHEN @Question LIKE '%' + Keyword + '%' THEN 1 ELSE 0 END) = COUNT(*)
              ) AS d ON d.FaqID = f.FaqID

-- Prepare user supplied parameter
SET @Question = 'How can we help resolve your problem'

-- Show the expected result
SELECT        f.Question,
              f.Answer
FROM          @Faq AS f
INNER JOIN    (
                     SELECT        FaqID         
                     FROM          @Keywords
                     GROUP BY      FaqID
                     HAVING        SUM(CASE WHEN @Question LIKE '%' + Keyword + '%' THEN 1 ELSE 0 END) = COUNT(*)
              ) AS d ON d.FaqID = f.FaqID

 

-- Initialize the search parameter
DECLARE       @WantedValue INT  
SET    @WantedValue = 221
 
-- Stage the source data
DECLARE       @Data TABLE
       (
              RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
              MaxItems INT,
              CurrentItems INT DEFAULT 0,
              FaceValue INT,
              BestUnder INT DEFAULT 0,
              BestOver INT DEFAULT 1
       )
 
-- Aggregate the source data
INSERT        @Data
              (
                     MaxItems,
                     FaceValue
              )
SELECT        COUNT(*),
              Qty
FROM          (
                     SELECT 899 AS Qty UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 95 UNION ALL
                     SELECT 50 UNION ALL
                     SELECT 55 UNION ALL
                     SELECT 40 UNION ALL
                     SELECT 5 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 50 UNION ALL
                     SELECT 250 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 90 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 50 UNION ALL
                     SELECT 350 UNION ALL
                     SELECT 450 UNION ALL
                     SELECT 450 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 100 UNION ALL
                     SELECT 50 UNION ALL
                     SELECT 50 UNION ALL
                     SELECT 50 UNION ALL
                     SELECT 1 UNION ALL
                     SELECT 10 UNION ALL
                     SELECT 1
              ) AS d
GROUP BY      Qty
ORDER BY      Qty DESC
 
 
-- Declare some control variables
DECLARE       @CurrentSum INT,
       @BestUnder INT,
       @BestOver INT,
       @RecID INT
 
-- If exact single wanted sum, select that item!
IF EXISTS (SELECT * FROM @Data WHERE FaceValue = @WantedValue)
       BEGIN
              SELECT 0 AS SumType,
                     1 AS Items,
                     FaceValue
              FROM   @Data
              WHERE FaceValue = @WantedValue
 
              RETURN
       END
 
-- If productsum is less to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) < @WantedValue
       BEGIN
              SELECT -1 AS SumType,
                     MaxItems AS Items,
                     FaceValue
              FROM   @Data
 
              RETURN
       END
 
-- If productsum is equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) = @WantedValue
       BEGIN
              SELECT 0 AS SumType,
                     MaxItems AS Items,
                     FaceValue
              FROM   @Data
 
              RETURN
       END
 
-- Delete all unworkable FaceValues, keep one greater FaceValue because of oversum.
DELETE
FROM   @Data
WHERE FaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
 
-- Update MaxItems to a proper value
UPDATE @Data
SET    MaxItems =    CASE
                           WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
                           ELSE MaxItems
                     END
 
-- Update BestOver to a proper value
UPDATE @Data
SET    BestOver = MaxItems
 
-- Initialize the control mechanism
SELECT @RecID = MIN(RecID),
       @BestUnder = 0,
       @BestOver = SUM(BestOver * FaceValue)
FROM   @Data
 
-- Do the loop!
WHILE @RecID IS NOT NULL
       BEGIN
              -- Reset all "bits" not incremented
              UPDATE @Data
              SET    CurrentItems = 0
              WHERE RecID < @RecID
 
              -- Increment the current "bit"
              UPDATE @Data
              SET    CurrentItems = CurrentItems + 1
              WHERE RecID = @RecID
 
              -- Get the current sum
              SELECT @CurrentSum = SUM(CurrentItems * FaceValue)
              FROM   @Data
              WHERE CurrentItems > 0
 
              -- Stop here if the current sum is equal to the sum we want
              IF @CurrentSum = @WantedValue
                     BREAK
              ELSE
                     -- Update the current BestUnder if previous BestUnder is less
                     IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
                            BEGIN
                                  UPDATE @Data
                                  SET    BestUnder = CurrentItems
 
                                  SET    @BestUnder = @CurrentSum
                           END
                     ELSE
                           -- Update the current BestOver if previous BestOver is more
                           IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
                                  BEGIN
                                         UPDATE @Data
                                         SET    BestOver = CurrentItems
 
                                         SET    @BestOver = @CurrentSum
                                  END
 
              -- Find the next proper "bit" to increment
              SELECT @RecID = MIN(RecID)
              FROM   @Data
              WHERE CurrentItems < MaxItems
       END
 
-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
       IF @WantedValue - @BestUnder < @BestOver - @WantedValue
              -- If BestUnder is closer to the sum we want, choose that
              SELECT -1 AS SumType,
                     BestUnder AS Items,
                     FaceValue
              FROM   @Data
              WHERE BestUnder > 0
       ELSE
              -- If BestOver is closer to the sum we want, choose that
              SELECT 1 AS SumType,
                     BestOver AS Items,
                     FaceValue
              FROM   @Data
              WHERE BestOver > 0
ELSE
       -- We have an exact match
       SELECT 0 AS SumType,
              CurrentItems AS Items,
              FaceValue
       FROM   @Data
       WHERE CurrentItems > 0

This problem originated here
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y
and I post the solution here for two reasons.

1) The forum above does not support code tags
2) The common interest is high enough

-- Prepare sample data
DECLARE @Sample TABLE
        ( 
            HoleID CHAR(8), 
            mFrom SMALLMONEY, 
            mTo SMALLMONEY, 
            Result SMALLMONEY, 
            PRIMARY KEY CLUSTERED 
            ( 
                HoleID, 
                mFrom 
            ), 
            Seq INT 
        )

INSERT  @Sample 
        ( 
            HoleID, 
            mFrom, 
            mTo, 
            Result 
        )
SELECT  'TWDD0004',   1   ,   2   ,  0.86 UNION ALL
SELECT  'TWDD0004',   3   ,   4   ,  8.93 UNION ALL
SELECT  'TWDD0004',   4   ,   5   ,  2.78 UNION ALL
SELECT  'TWDD0004',   8   ,   9   ,  1.21 UNION ALL
SELECT  'TWDD0004',  10   ,  11   ,  2.36 UNION ALL
SELECT  'TWDD0004',  11   ,  12   ,  0.86 UNION ALL
SELECT  'TWDD0004', 103