0

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

enter image description here

but i want if InvoiceAttachmentStatus is null then 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 null?

  • SQL Server 2008R2 – Osama Rizwan Jun 24 at 11:02
  • @MsNets I just need Concept of how to achieve results. – Osama Rizwan Jun 24 at 11:03
0

You can use a CASE statement to determine if either column or both columns are NOT NULL. Based on the CASE condition, you set a pseudo column to 1 or 0 (1 for any of the columns being NOT NULL or 0 for all columns beings NULL). You can then group by your required columns and aggregate the pseudo column to count the number of rows where at least one of the columns is NOT NULL.

You can see a mock example of this here.

SELECT StationId,
  SUM(Value)
FROM
(
SELECT StationId,
  CASE
    WHEN Col1 IS NOT NULL OR Col2 IS NOT NULL THEN 1
    ELSE 0
  END AS Value
FROM Records
) g
GROUP BY StationId
| improve this answer | |
  • why we are summing values ? can't we use count()? – Osama Rizwan Jun 25 at 5:36
  • @Osama, replace 0 with null (or remove the else clause since it is the default), you can then count instead of sum – Lennart Jun 25 at 6:44
0

You can use CASE to match:

SELECT COUNT(CASE WHEN Col1 IS NULL AND Col2 IS NULL THEN 1 END) as CASE1
     , COUNT(CASE WHEN Col1 IS NULL AND Col2 IS NOT NULL THEN 1 END) as CASE2
     , COUNT(CASE WHEN Col1 IS NOT NULL AND Col2 IS NULL THEN 1 END) as CASE3
     , COUNT(CASE WHEN Col1 IS NOT NULL AND Col2 IS NOT NULL THEN 1 END) as CASE4
FROM ...

As long as your CASE expressions are mutually exclusive, rows are counted just once. If the predicate in CASE evaluates to FALSE, NULL is returned, i.e. is not counted.

| improve this answer | |
0

The solution that i had tried yesterdays was

Select COUNT(GDNNo)TotalInvoices,count(Count_Scanned)ScannedInvoices, --Count(InvoiceAttachment)+
(COUNT(GDNNo)-count(Count_Scanned))[Remaining],
round(cast(count(Count_Scanned)as float)/COUNT(GDNNo)*100 ,3)[Completion]
from
( 
  select InvoiceDate,InvoiceAttachment,InvoiceAttachmentStatus,StationId,SaleOrderAttachment,GDNNo,GDNAttachmentStatus,
    case when InvoiceAttachment is not null or SaleOrderAttachment is not null then 1 
    when InvoiceAttachment is not null and SaleOrderAttachment is not null then 1 
    END AS Count_Scanned
    from SaleDocuments 
) sub_query
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.