ColdCoffee (5/4/2010)
May be, something like this?
WITH CTE(ID_NUMBER ) as
(
select distinct O.ID_NUMBER ID_NUMBER from Table as O
WHERE (O.FIRST_DATE BETWEEN '20090101' AND '20091231')
)
select ID_NUMBER , count(ID_NUMBER) R_Count from CTE
GROUP BY ID_NUMBER
I used the previous query for one person like:
WITH CTE(ID_NUMBER ) as
(
select distinct O.ORDER_NUMBER ID_NUMBER from TSORDR as O
WHERE (O.SUBMITTED_DATE BETWEEN '20090101' AND '20091231') and (O.NIN_CODE='990685711')
)
select ID_NUMBER , count(ID_NUMBER) R_Count from CTE
GROUP BY ID_NUMBER
But is does not give the Count for all records in one record, it gives me each record or ID_NUMBER Count, and in this case the result was the following (17 record):
ID_NUMBERR_Count
016931421
017158281
017182761
017233871
017261311
017432801
017499361
017514751
018043581
018185501
018344441
018469651
018690431
018825311
019103981
019378681
019750941
and If I put two persons in the query like:
WITH CTE(ID_NUMBER ) as
(
select distinct O.ORDER_NUMBER ID_NUMBER from TSORDR as O
WHERE (O.SUBMITTED_DATE BETWEEN '20090101' AND '20091231') and (O.NIN_CODE='990685711' OR O.NIN_CODE='993435940')
)
select ID_NUMBER , count(ID_NUMBER) R_Count from CTE
GROUP BY ID_NUMBER
then the result will be (32 record) -> 17 record for person 1, and 15 for person 2:
ID_NUMBERR_Count
016895791
016918541
016931421
017025411
017158281
017170151
017182761
017199361
017199601
017199641
017233871
017261311
017271901
017432801
017499361
017514751
018043581
018185501
018267041
018267061
018344441
018469651
018648761
018648791
018690431
018715471
018825311
019103981
019378681
019750941
019814151
019863711
I want the previous result to be returned in two records for the two persons like:
ID_NUMBERR_Count
1 17
2 15