Hi,
I need to count the record from the cte query,
I did this way,
with cte as (
select distinct cust.Name as CompanyName,ROW_NUMBER() OVER(order by (InvoiceDate) Desc) as RowNumber,DATEDIFF(DAY ,inv.InvoicedueDate ,getdate()) DaysTillDate ,
inv.InvoiceId,dbo.fn_GetOrderFromInv(inv.InvoiceId) as OrderNumber ,inv.InvoiceDate, (inv.Prefix + '' + Convert(varchar,inv.InvoiceNo)) as InvoiceNo ,Del.DeliveryNo,Hcomp.Name as HandlingCompany,
CONVERT(decimal(18,3), inv.InvoiceValue) as Base_Invoiceamount,CONVERT(decimal(18,2), inv.Currency_InvoiceValue ) as Cust_Invoiceamount,inv.InvoiceDueDate,
Isnull(((inv.InvoiceValue) - (select SUM(Amount) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),inv.InvoiceValue) as Base_Outstanding,
Convert(decimal(12,2), Isnull(((inv.Currency_InvoiceValue) - (select (SUM(Currency_Amount) ) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),(inv.Currency_InvoiceValue))) as Cust_Outstanding,
inv.InvoiceTypeId,
dbo.fn_GetinvoiceCNType(inv.InvoiceTypeId,493 ,inv.InvoiceId,inv.InvoiceValue) Note
from genInvoices inv
left join genInvoiceOrders invord on invord.InvoiceId =inv.InvoiceId
left join genOrders ord on ord.OrderId =invord.OrderId
left join genCompanies cust on ord.CompanyId = cust.CompanyId
left join genDelivery Del on Del.DeliveryId = inv.DeliveryId
left join genCompanies Hcomp on Hcomp.CompanyID = inv.HandCompanyId
where ( inv.StatusId=307 ) and ( inv.InvoiceTypeId = 3 or inv.InvoiceTypeId = 1 ) and ((isnull(inv.IsInvoiceDeliver,0)=1 and isnull(inv.IsEinvoice,0)=1) or (isnull(inv.IsDeliverbyPost,0)=1) or (isnull(inv.IsEinvoice,0)=0 and isnull(inv.IsDeliverbyPost,0)=0) ) and inv.CustCompanyId = 45084 and 1=1 and 1=1)
, cte2 as (select count(*) as cnt from cte)
select RowNumber,cnt, CompanyName,DaysTillDate,InvoiceId,OrderNumber, dbo.fn_globaldateformat(InvoiceDate,493) as InvoiceDate,InvoiceDate as InvoiceDatesort,InvoiceNo,DeliveryNo,
HandlingCompany,Base_Invoiceamount,Cust_Invoiceamount,dbo.fn_globaldateformat(InvoiceDueDate,493) as InvoiceDueDate,InvoiceDueDate as InvoiceDueDatesort,
Base_Outstanding,Cust_Outstanding,InvoiceTypeId,Note from cte,cte2 where RowNumber between 1 and 20
this query takes 700ms to execute,
but when I remove another cte(i.e. removing count()) than it takes only 250ms to exeute,
after removing Count the query is,
with cte as (
select distinct cust.Name as CompanyName,ROW_NUMBER() OVER(order by (InvoiceDate) Desc) as RowNumber,DATEDIFF(DAY ,inv.InvoicedueDate ,getdate()) DaysTillDate ,
inv.InvoiceId,dbo.fn_GetOrderFromInv(inv.InvoiceId) as OrderNumber ,inv.InvoiceDate, (inv.Prefix + '' + Convert(varchar,inv.InvoiceNo)) as InvoiceNo ,Del.DeliveryNo,Hcomp.Name as HandlingCompany,
CONVERT(decimal(18,3), inv.InvoiceValue) as Base_Invoiceamount,CONVERT(decimal(18,2), inv.Currency_InvoiceValue ) as Cust_Invoiceamount,inv.InvoiceDueDate,
Isnull(((inv.InvoiceValue) - (select SUM(Amount) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),inv.InvoiceValue) as Base_Outstanding,
Convert(decimal(12,2), Isnull(((inv.Currency_InvoiceValue) - (select (SUM(Currency_Amount) ) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),(inv.Currency_InvoiceValue))) as Cust_Outstanding,
inv.InvoiceTypeId,
dbo.fn_GetinvoiceCNType(inv.InvoiceTypeId,493 ,inv.InvoiceId,inv.InvoiceValue) Note
from genInvoices inv
left join genInvoiceOrders invord on invord.InvoiceId =inv.InvoiceId
left join genOrders ord on ord.OrderId =invord.OrderId
left join genCompanies cust on ord.CompanyId = cust.CompanyId
left join genDelivery Del on Del.DeliveryId = inv.DeliveryId
left join genCompanies Hcomp on Hcomp.CompanyID = inv.HandCompanyId
where ( inv.StatusId=307 ) and ( inv.InvoiceTypeId = 3 or inv.InvoiceTypeId = 1 ) and ((isnull(inv.IsInvoiceDeliver,0)=1 and isnull(inv.IsEinvoice,0)=1) or (isnull(inv.IsDeliverbyPost,0)=1) or (isnull(inv.IsEinvoice,0)=0 and isnull(inv.IsDeliverbyPost,0)=0) ) and inv.CustCompanyId = 45084 and 1=1 and 1=1)
select RowNumber, CompanyName,DaysTillDate,InvoiceId,OrderNumber, dbo.fn_globaldateformat(InvoiceDate,493) as InvoiceDate,InvoiceDate as InvoiceDatesort,InvoiceNo,DeliveryNo,
HandlingCompany,Base_Invoiceamount,Cust_Invoiceamount,dbo.fn_globaldateformat(InvoiceDueDate,493) as InvoiceDueDate,InvoiceDueDate as InvoiceDueDatesort,
Base_Outstanding,Cust_Outstanding,InvoiceTypeId,Note from cte where RowNumber between 1 and 20
My only question is that how can I count record from cte query?
Thanks.