asked on

Need count of serial numbers

Hi

I have this data
Serial Number Service_Order
X000701        1511293
X000701        1511293
X000701        1510831
X000701        1510831
X000881 1622890

I need to know the count of service orders for a serial number.
In this case I would need
X000701        2
X000881 1

ASKER CERTIFIED SOLUTION

sbagireddi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Charles Baldo

ASKER

sbagireddi

I apologize I need a TSQL solution

sbagireddi

select count(SerialNumber) where Service_Order = '1511293'

You will need to pass the specific serial number to get the count though.

SOLUTION

Joel Coehoorn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Charles Baldo

ASKER

The problem is I do not know the service order number or specific serial numbers. I have a table of data and need to do the counts

Charles Baldo

ASKER

I ended up doing this
select count(*)
from
(
SELECT distinct
1 as v,
t1.service_order_number
FROM
service_order t1 INNER JOIN service_order_labor t2 ON t1.service_order_number = t2.service_order_number
WHERE
t2.line_type = 'L'
AND t1.serial_number = @sno
AND t1.complete_date_sid BETWEEN @startdate AND @endDate
) a

Thanks for your help I will distribute the points evenly

Charles Baldo

ASKER

Thanks