SQL Count 2

[IAMINFO]

Hello everyone, I cannot figure out what I am doing wrong here. My goal is to show all cells in a housing unit where 3 or more inmates are housed. When I run the sql statement below I get cells with 2 inmates and cells with 3 inmate. I only want to see cells with 3 inmates in them, please help if you are able thank you for taking time.

SELECT OFFENDER, FACILITY, BLOCK, CELL, BED, MOVEIN, MOVEOUT, STAY
FROM pps_housingchanges AS H
WHERE (MOVEIN < '7/2/2009 00:00') AND (MOVEOUT > '7/2/2009 00:00' OR
MOVEOUT IS NULL) AND (FACILITY = 'HOC') AND (BLOCK = 'D2BLK') AND
((SELECT COUNT(OFFENDER) AS Expr1
FROM pps_housingchanges
WHERE (MOVEIN < '7/2/2009 00:00') AND (MOVEOUT > '7/2/2009 00:00' OR
MOVEOUT IS NULL)) > 2)
ORDER BY BLOCK, CELL, BED

 

[gmmastros]

I think there is probably a more efficient way to do this, but... let's see if this works first.

SELECT OFFENDER, FACILITY, BLOCK, CELL, BED, MOVEIN, MOVEOUT, STAY
FROM   pps_housingchanges AS H
WHERE  (MOVEIN < '7/2/2009 00:00') 
		AND (MOVEOUT > '7/2/2009 00:00' OR MOVEOUT IS NULL) 
		AND (FACILITY = 'HOC') 
		AND (BLOCK = 'D2BLK') 
		AND ((SELECT COUNT(OFFENDER) AS Expr1
              FROM   pps_housingchanges
              WHERE  (MOVEIN < '7/2/2009 00:00') 
					 AND (MOVEOUT > '7/2/2009 00:00' OR MOVEOUT IS NULL)
                     AND pps_housingchanges.Facility = H.Facility
                     AND pps_housingchanges.Block = H.Block
                     And pps_housingchanges.Cell = H.Cell) > 2)
ORDER BY BLOCK, CELL, BED

If that returns the correct results, and it's slow, let me know. I can probably help you make it faster. First make it work, then make it fast (I always say).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

select * from (SELECT OFFENDER, FACILITY, BLOCK, CELL, BED, MOVEIN, MOVEOUT, STAY, COUNT(*) OVER (Partition by Facility, Block, Cell) as CntOffenders
FROM pps_housingchanges AS H
WHERE (MOVEIN < '7/2/2009 00:00') AND (MOVEOUT > '7/2/2009 00:00' OR
MOVEOUT IS NULL) AND (FACILITY = 'HOC') AND (BLOCK = 'D2BLK')
ORDER BY BLOCK, CELL, BED) Off where CntOffenders >2

The above is SQL Server 2005 and up solution.

 

[IAMINFO]

gmmastros thank you very much,that worked you were right it was slow but I am thankful, if you could please show me how to speed it up. Thanks again

 

[IAMINFO]

markros thank you for taking the time to try and help me,I really appreciate it, this was the error message I received

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Thanks again

 

[gmmastros]

I think this will return the same results, but will probably execute faster.

SELECT OFFENDER, FACILITY, BLOCK, CELL, BED, MOVEIN, MOVEOUT, STAY
From   pps_housingchanges
       Inner Join (
         Select FACILITY, BLOCK, CELL
         From   pps_housingchanges
         Where  (MOVEIN < '7/2/2009 00:00') 
                AND (MOVEOUT > '7/2/2009 00:00' OR MOVEOUT IS NULL) 
                AND (FACILITY = 'HOC') 
                AND (BLOCK = 'D2BLK')
         Having Count(*) > 2
         ) As AliasName
         On pps_housingchanges.FACILITY = AliasName.Facility
         And pps_housingchanges.Block = AliasName.Block
         And pps_housingchanges.Cell = AliasName.CELL
 Where  (pps_housingchanges.MOVEIN < '7/2/2009 00:00') 
         AND (pps_housingchanges.MOVEOUT > '7/2/2009 00:00' OR pps_housingchanges.MOVEOUT IS NULL) 
         AND (pps_housingchanges.FACILITY = 'HOC') 
         AND (pps_housingchanges.BLOCK = 'D2BLK')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Oops, sorry - move the order by at the very end - I didn't notice the order by.

select * from (SELECT OFFENDER, FACILITY, BLOCK, CELL, BED, MOVEIN, MOVEOUT, STAY, COUNT(*) OVER (Partition by Facility, Block, Cell) as CntOffenders
FROM pps_housingchanges AS H
WHERE (MOVEIN < '7/2/2009 00:00') AND (MOVEOUT > '7/2/2009 00:00' OR
MOVEOUT IS NULL) AND (FACILITY = 'HOC') AND (BLOCK = 'D2BLK')
) Off where CntOffenders >2 ORDER BY BLOCK, CELL, BED