Delete Rows

Question

Post reply

Delete Rows

craig.bryant24

Valued Member

Points: 51
More actions
August 11, 2010 at 8:01 am

#387549

Hi,
I am creating a table through a stored procedure, in the table I am trying to get a distinct count. Basically I have a list of drivers and whether they go in state or out of state. These drivers can cover both in state deliveries and out of deliveries. But if they go out of state I want to count them exclusively as an out of state driver, but in my table the driver is coming in as both an in state driver and an out of state driver...see below for example. So down below, I would not want it to pull in the records 7, INTRASTATE and L, INTRASTATE Is there a way to solve this through the stored procedure?
Driver in state or out
7 INTRASTATE
7 INTERSTATE
A INTRASTATE
D INTRASTATE
H INTRASTATE
L INTRASTATE
L INTERSTATE
M INTRASTATE
A INTERSTATE
C INTERSTATE

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

So confusing the requirement is, bryant!

Can u please elaborate ?

As a side note, include the status column along with the driver ID in the Group BY dint solve your request?

We need more info about your table structure, your sample data, constriants if any in a readily consumable format..click teh link below to put your question in a neat format

CLICK HERE FOR FORUM POSTING ETIQUETTES - BY JEFF MODEN[/url]

drew.allen SSC Guru Points: 77014 More actions · Answer 2

If you group on the driver ID, you can find the Min() of the status. You can also use a PIVOT if the group by won't work for some reason.

For a more complete answer, provide the table and sample data scripts for testing.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

This would be one method to eliminate from the count those drivers who had INTERSTATE and INTRASTATE routes

CREATE TABLE #T(Did CHAR(1),T VARCHAR(10))

INSERT INTO #T

SELECT '7','INTRASTATE' UNION ALL

SELECT '7','INTERSTATE' UNION ALL

SELECT 'A','INTRASTATE' UNION ALL

SELECT 'D','INTRASTATE' UNION ALL

SELECT 'H','INTRASTATE' UNION ALL

SELECT 'L','INTRASTATE' UNION ALL

SELECT 'L','INTERSTATE' UNION ALL

SELECT 'M','INTRASTATE' UNION ALL

SELECT 'A','INTERSTATE' UNION ALL

SELECT 'C','INTERSTATE'

;with numbered as(SELECT rowno=row_number() over

(partition by Did order by T ASC),Did,T from #T)

--SELECT * FROM numbered for debugging only

SELECT COUNT(Did) FROM numbered WHERE Rowno = 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]