) Area table has 4 polygons:East,West,North and South.Clients are traveling from one area to other as well as inside their area.i need to find
trip COUNTs based on their Pickup(PU) and Dropoff(DO) area.For example: ZONENAME EAST WEST NORTH SOUTH East 6699 0 0 0 West 0 4381 0 0 North 0 0 8454 0 South 0 0 0 2623 All trips PU East to DO East is 6699.. All trips PU West to Do West is 4381.But i also need counts from PU East to DO West...Another words i have to fill in '0's here is what i come up with so far: SELECT A.ZONENAME , SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0)) =
1 AND A.ID=1 THEN 1 ELSE 0 END ) AS EAST, SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND (A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND A.ID=2) THEN 1 ELSE 0 END ) AS NORTH, SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND (A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND A.ID=3) THEN 1 ELSE 0 END ) AS SOUTH , SUM(CASE WHEN A.GEOM.STIntersects(GEOMETRY::Point(T.PICKUP_ADDRESS_GEOCODE_X/ 1000000.0,T.PICKUP_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND (A.GEOM.STIntersects(GEOMETRY::Point(T.DROPOFF_ADDRESS_GEOCODE_X / 1000000.0,T.DROPOFF_ADDRESS_GEOCODE_Y/ 1000000.0, 0).STBuffer(0))
= 1 AND A.ID=4) THEN 1 ELSE 0 END ) AS WEST FROM TRIP T,AREA A GROUP BY A.ZONENAME