SQL query to select a percentage of the total

WITH cte 
     AS (SELECT storeid, 
                numemployees, 
                ( numemployees * 100 ) / SUM(numemployees) OVER (PARTITION BY 1) 
                AS 
                percentofstores 
         FROM   stores) 
SELECT * 
FROM   cte 
WHERE  percentofstores >= 30 
ORDER BY numemployees desc

      

        
        
        
      

    

Working demo

An alternative that doesn't use SUM / OVER

SELECT s.storeid, s.numemployees 
FROM   (SELECT SUM(numemployees) AS [tots] 
        FROM   stores) AS t, 
       stores s 
WHERE  CAST(numemployees AS DECIMAL(15, 5)) / tots >= .3 
ORDER BY s.numemployees desc

      

        
        
        
      

    

Working demo

Note that in the second version I chose not to multiply by 100 before dividing. This requires casting to decimal, otherwise it will be implicitly converted to int, which will not result in records being returned

Also I don't quite understand what you want, but you can add TOP 1

to both queries and this will limit the results to only those with the most stores with over 30%

UPDATE

Based on your comments, this sounds to paraphrase Kevin

You need lines starting with a store with the majority of employees and working until you have at least 30%

It's tricky because it requires a percentage of completion and a cart packing problem, but it works. Note. I have included two other test cases (where the percentage is exactly equal and is slightly more than the top two combinations)

Working demo

DECLARE @percent DECIMAL (20, 16) 

SET @percent = 0.3
--Other test values
--SET @percent = 0.6992547128452433
--SET @percent = 0.6992547128452434 

;WITH sums 
     AS (SELECT DISTINCT s.storeid, 
                         s.numemployees, 
                         s.numemployees + Coalesce(SUM(s2.numemployees) OVER ( 
                                                   PARTITION 
                                                   BY 
                                                   s.numemployees), 0) 
                         runningsum 
         FROM   stores s 
                LEFT JOIN stores s2 
                  ON s.numemployees < s2.numemployees), 
     percents 
     AS (SELECT storeid, 
                numemployees, 
                runningsum, 
                CAST(runningsum AS DECIMAL(15, 5)) / tots.total 
                running_percent, 
                Row_number() OVER (ORDER BY runningsum, storeid ) rn 
         FROM   sums, 
                (SELECT SUM(numemployees) total 
                 FROM   stores) AS tots) 
SELECT p.storeID,
       p.numemployees,
       p.running_percent,
       p.running_percent,
       p.rn 
FROM   percents p 
       CROSS JOIN (SELECT MAX(rn) rn 
                  FROM   percents 
                  WHERE  running_percent = @percent) exactpercent 

       LEFT JOIN (SELECT MAX(rn) rn 
                   FROM   percents 
                   WHERE  running_percent <= @percent) underpercent 
         ON p.rn <= underpercent.rn 
             OR ( exactpercent.rn IS NULL 
                  AND p.rn <= underpercent.rn + 1 ) 
WHERE 
      underpercent.rn is not null or p.rn = 1