CODEWITHSUNDEEP
sqlsql-server
Vinit Sharma
Vinit Sharma

Reputation: 46

How to get unique record with Invoice Number in SQL

I have a table like this; now I need distinct invoicenumber with comma-separated testname with all columns. I try this query but I get an error.

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

SELECT DISTINCT
    PTD.InvoiceNumber, PL.PatientName, PTD.TestDate, PTD.Discount, PTD.Commission,
    (SELECT SUM(TestAmount) AS TestAmount 
     FROM tb_Patienttestdetails
     WHERE PTD.InvoiceNumber = InvoiceNumber) AS TotalAmount,  
    (TotalAmount - (PTD.Commission + PTD.Discount)) AS RealAmount,
    PTD.Remark, PL.PatientId,    
    (SELECT DISTINCT
         t1.InvoiceNumber,
         STUFF((SELECT DISTINCT '' + t2.Testname 
                FROM tb_PatientTestDetails t2  
                WHERE t1.InvoiceNumber = t2.InvoiceNumber
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '') TestName
     FROM
         tb_PatientTestDetails t1)   
FROM 
    tb_Patienttestdetails PTD 
INNER JOIN
    tb_PatientList PL ON PTD.PatientId = PL.Id

enter image description here

Upvotes: 0

Views: 408

Answers (2)

Vinit Sharma
Vinit Sharma

Reputation: 46

Problem Solved By This Code

SELECT PTD.InvoiceNumber, PL.PatientName, PTD.TestDate, PTD.Discount, PTD.Commission,
       SUM(PTD.TestAmount) AS TestAmount,
       SUM(PTD.TestAmount - (PTD.Commission + PTD.Discount)) AS RealAmount,
       PTD.Remark, PL.PatientId,   
      STUFF((SELECT distinct '' + t2.Testname from tb_PatientTestDetails t2  where PTD.InvoiceNumber = t2.InvoiceNumber FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'') TestName  FROM tb_PatientList PL JOIN
    tb_Patienttestdetails PTD 
    ON PTD.PatientId = PL.Id GROUP BY PTD.InvoiceNumber, PL.PatientName, PTD.TestDate, PTD.Discount, PTD.Commission, PTD.Remark, PL.PatientId;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

The answer seems pretty obvious. This subquery is returning two columns:

(SELECT DISTINCT
     t1.InvoiceNumber,
     STUFF((SELECT DISTINCT '' + t2.Testname 
            FROM tb_PatientTestDetails t2  
            WHERE t1.InvoiceNumber = t2.InvoiceNumber
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '') TestName
 FROM
     tb_PatientTestDetails t1)

Presumably, you intend something like this:

SELECT PTD.InvoiceNumber, PL.PatientName, PTD.TestDate, PTD.Discount, PTD.Commission,
       SUM(PTD.TestAmount) AS TestAmount,
       SUM(PTD.TestAmount - (PTD.Commission + PTD.Discount)) AS RealAmount,
       PTD.Remark, PL.PatientId,    
       STUFF((SELECT DISTINCT '' + t2.Testname 
              FROM tb_PatientTestDetails ptd2 
              WHERE ptd.InvoiceNumber = ptd2.InvoiceNumber
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, ''
             )
            ) as tests 
FROM tb_PatientList PL JOIN
    tb_Patienttestdetails PTD 
    ON PTD.PatientId = PL.Id
GROUP BY PTD.InvoiceNumber, PL.PatientName, PTD.TestDate, PTD.Discount, PTD.Commission, PTD.Remark, PL.PatientId;

Upvotes: 1

Related Questions