SQL SCRIPT
-
Hi All, I have tables called CALL and Operator. Select Distinct(Mobile),MIN(DATE_OF_CALL),NAME_OPERATOR from Call inner Join Operator on Call.CustomerId=Operator.CustomerID where Datename(month,DATE_OF_CALL)='APRIL' Group by Mobile What i want is to get the first date of entry for each Mobiles in the month of april and the operator who attended them. However i Have to group by Name_operator to run the above script.But i dont want them to be grouped by OPerator.I want the first entry of a mobile in the database together with the name of the operator who attended them. How do i go about it Please? Thank you so much.
-
Hi All, I have tables called CALL and Operator. Select Distinct(Mobile),MIN(DATE_OF_CALL),NAME_OPERATOR from Call inner Join Operator on Call.CustomerId=Operator.CustomerID where Datename(month,DATE_OF_CALL)='APRIL' Group by Mobile What i want is to get the first date of entry for each Mobiles in the month of april and the operator who attended them. However i Have to group by Name_operator to run the above script.But i dont want them to be grouped by OPerator.I want the first entry of a mobile in the database together with the name of the operator who attended them. How do i go about it Please? Thank you so much.
Presumably you are using SQL 2005+ as you database (I know it may be an unfounded assumption) Look into Row_Number and partition. These will allow you to select the records for month# with a rownumber partitioned over mobileNo ordered by date of call having a rownumber = 1
Never underestimate the power of human stupidity RAH
-
Presumably you are using SQL 2005+ as you database (I know it may be an unfounded assumption) Look into Row_Number and partition. These will allow you to select the records for month# with a rownumber partitioned over mobileNo ordered by date of call having a rownumber = 1
Never underestimate the power of human stupidity RAH
Than you very much.Its most appreciated.I am Using SQL 2005. Could you please give me an example please? What i want is the first entry of a mobile number for a certain month.I have the operator name in a diffrent table called Operaor. For instance a mobile number 12 has called mike on 2009-04-01 and andy on 2009-04-02. I want only to get the first entry that is Mobile:12 DateofCall :2009-04-01 : operatorName: Mike. That is the first entry for this number on the month of april. How could i do that on the above script? Please if you can give me an example. Thank you onece again
-
Presumably you are using SQL 2005+ as you database (I know it may be an unfounded assumption) Look into Row_Number and partition. These will allow you to select the records for month# with a rownumber partitioned over mobileNo ordered by date of call having a rownumber = 1
Never underestimate the power of human stupidity RAH
-
HI I got an error with this one ? Can any one advice? Select distinct(Mobile), ROW_NUMBER() OVER(PARTITION BY mobile ORDER BY dateofcall DESC ) AS 'RowNumber' from Call having RowNumber=1 Error :Invalid Column RowNumber
-
Hi All, I have tables called CALL and Operator. Select Distinct(Mobile),MIN(DATE_OF_CALL),NAME_OPERATOR from Call inner Join Operator on Call.CustomerId=Operator.CustomerID where Datename(month,DATE_OF_CALL)='APRIL' Group by Mobile What i want is to get the first date of entry for each Mobiles in the month of april and the operator who attended them. However i Have to group by Name_operator to run the above script.But i dont want them to be grouped by OPerator.I want the first entry of a mobile in the database together with the name of the operator who attended them. How do i go about it Please? Thank you so much.
I'd do something like this:
SELECT [Call].*
,Operator.Fullname
FROM
(SELECT [Caller],FirstCall=MIN(CallTime) FROM [Call] WHERE DATEPART(MM,CallTime)=4 GROUP BY [Caller]) T
INNER JOIN [Call]
ON T.[Caller]=[Call].[Caller]
AND T.FirstCall=[Call].CallTime
INNER JOIN [Operator]
ON [Call].Operator=Operator.IDJust be sure to have indices on Caller and CallTime.