Sql-server – how to retrive data from InvoiceNo

aggregatesql server

select  Month(IH.InvoiceDate) as Month, 
    COUNT(ID.InvoiceNo) as Count,
    COUNT(ID.InvoiceNo) as DirectMobile,
    COUNT(ID.InvoiceNo) as Preselling,
    COUNT(ID.PerformaInvoiceNo) as DirectWeb
    from InvoiceDetail ID
INNER JOIN InvoiceHeader IH ON ID.InvoiceNo = IH.InvoiceNo
WHERE IH.IsBackend = 1
GROUP BY Month(IH.InvoiceDate)

I want to ask how to retrieve data from InvoiceNo into two columns and how to give the where clause to complete these conditions.

Count is full count of every type of invoices and i want to get the count of DirectMobile , DirectWeb and Preselling . For get the count of those 3 these are the coditions.

DirectMobile – IsBackend=0

DirectWeb – IsBackend=1 and PerformaInvoiceNo= '' or 0

Preselling – IsBackend = 1 and PerformaInvoiceNo = a varchar value.

Best Answer

Something like this (untested) might be what you're after:

SELECT Month(IH.InvoiceDate) as Month, 
    COUNT(*) as Count,
    SUM(CASE WHEN IH.IsBackend = 0 THEN 1 ELSE 0 END) as DirectMobile,
    SUM(CASE WHEN IH.IsBackend = 1 AND PerformaInvoiceNo <> '' AND PerformaInvoiceNo <> '0' THEN 1 ELSE 0 END) as Preselling,
    SUM(CASE WHEN IH.IsBackend = 1 AND (ID.PerformaInvoiceNo = '' OR ID.PerformaInvoiceNo = '0') THEN 1 ELSE 0 END) as DirectWeb
FROM InvoiceDetail ID
INNER JOIN InvoiceHeader IH USING (InvoiceNo)
GROUP BY Month(IH.InvoiceDate);

This emulates the effect of the filter clause that is available in Postgres. See https://modern-sql.com/feature/filter It's optional in the SQL:2003 standard.

Related Solutions

Sql-server – Vendors with Most Invoices in State

I think the inner query is right.

The MAX without a matching GROUP BY in the outer query will give a single value from the whole dataset. In your example the dataset would be the inner query. This is not what you want, so you have to add group by state.

Now you have the problem of vendor name. It can't be in the select list unless it is also in the GROUP BY, but that will give the wrong result. It is necessary to do this in two steps. In the outer query just deal with states:

Select state, max (..) as MaxByState
From (inner query)
Group by state

Then join this back to the inner query on the MAX() value:

Select
  ...
from (
    Select state, max (..) as MaxByState
    From (inner query)
    Group by state
) as mbs
inner join (
  Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

In other words, here are the most numerous invoices in each state. Which vendors have these many invoices?

If two vendors have the same number of invoices in a state there will be two rows output.

Since the inner query is repeated verbatim it would be better presented as a non-recursive CTE.

SQL Server 2012 has a number of windowing functions built in. The most useful one here is RANK (). It does just what you think it should do.

Select 
  RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

This eliminates all of the second-order dataset alignment on derived aggregate values required above. It is much easier to read and understand, and hence to maintain.

Sql-server – Create a view showing total minutes per customer per month and change from previous month

Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month.

USE Masscomm_XT; 

WITH C0 AS (
  SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
  FROM dbo.reptview_LDUsageTrend
)

SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (Minutes) AS MinuteTotal
FROM C0
GROUP BY C0.CustomerName, C0.PostingYear, C0.PostingMonth
;

Best Answer

Related Solutions

Sql-server – Vendors with Most Invoices in State

Sql-server – Create a view showing total minutes per customer per month and change from previous month

Related Question