CODEWITHSUNDEEP
sqldatabase
Brian lee
Brian lee

Reputation: 356

SQL:How to select column that last 3 digit are multiply of 4?

My sql schema is like this : sqlfiddle

I want to Show the TransactionID, DoctorID, PatientID, and TotalMedicine (obtained from amount of MedicineID on each transaction) where the last 3 digits numbers of PatientID are multiples of 4. Sort the data in ascending by DoctorID , then count the TotalMedicine the largest, smallest and average on the Doctor ID.

I have tried this 

SELECT th.TransactionID,
       th.DoctorID,
       th.PatientID,
       COUNT(td.MedicineID) AS TotalMedicine
FROM   TransactionHeader th
       JOIN TransactionDetail td
         ON th.TransactionID = td.TransactionID
WHERE  CAST(RIGHT(th.PatientID, 3) AS INT) % 4 = 0
GROUP  BY th.TransactionID,
          th.DoctorID,
          th.PatientID
ORDER  BY th.DoctorID ASC
COMPUTE max(COUNT(td.MedicineID)), 
        min(COUNT(td.MedicineID)), 
        avg(COUNT(td.MedicineID)) BY th.DoctorID

but it didnot select the last 3 digit of the PatientID that are multiplies of 4..

Upvotes: 0

Views: 472

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Add Where Cast(Substring(PatientId, Len(PatientId) - 2, 3) as Integer) % 4 = 0

Select TransactionID, DoctorID, PatientID, 
    Sum() TotalMedicine 
From TransactionHeader th
   JOIN TransactionDetail td
     ON th.TransactionID = td.TransactionID
Where Cast(Substring(PatientId, Len(PatientId) - 2, 3) as Integer) % 4 = 0
GROUP BY th.TransactionID,
      th.DoctorID,th.PatientID

Upvotes: 0

DougM
DougM

Reputation: 2888

Assuming that you can't change your schema to give tableHeader a varchar data type, or a char(5), you need to account for the spaces on the end of the column.

RTRIM will what you want.

SELECT th.TransactionID,
       th.DoctorID,
       th.PatientID,
       COUNT(td.MedicineID) AS TotalMedicine
FROM   TransactionHeader th
       JOIN TransactionDetail td
         ON th.TransactionID = td.TransactionID
WHERE  CAST(RIGHT(RTRIM(th.PatientID), 3) AS INT) % 4 = 0
GROUP  BY th.TransactionID,
          th.DoctorID,
          th.PatientID
ORDER  BY th.DoctorID ASC

Upvotes: 2

Related Questions