I'm making sales statistic report to calculate scanned documents station wise using below Query:
SELECT RIGHT(CONVERT(varchar(11),DATEADD(MONTH,DATEDIFF(MONTH,0,InvoiceDate),0),13),LEN (CONVERT(varchar(11),DATEADD(MONTH,DATEDIFF(MONTH,0,InvoiceDate),0),13))-3) [Month], COUNT(GDNNo)TotalInvoices,(COUNT(InvoiceAttachment)+ COUNT(GDNAttachmentStatus))ScannedInvoices, (COUNT(GDNNo)-(COUNT(InvoiceAttachment)+COUNT(GDNAttachmentStatus)))[Remaining], round(cast((Count(InvoiceAttachment)+COUNT(GDNAttachmentStatus))as float)/COUNT(GDNNo)*100 ,3)[Completion] FROM SaleDocuments WHERE InvoiceDate between '2020/05/03' and '2020/06/23' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, InvoiceDate), 0), StationId order by [Month] desc
but i want if InvoiceAttachmentStatus is
count GDNAttachmentStatus. The method which I'm following counts duplicate data in case of InvoiceAttachmentStatus, GDNAttachmentStatus both values present.
How to count single value only if one column is