在SQL Server 2012中汇总 [英] Rollup in SQL server 2012

问题描述

亲爱的朋友们，



我是SQL Server 2012的新手..昨天我已经将SQL Server 2008数据库恢复到SQL Server 2012 ...一切正常。 ..由于计算功能在SQLL服务器2012中停止，某些报告无法正常工作..



我在这里复制我的小程序请帮我改进它通过ROLLUP ...

Dear Friends,

I am new to SQL server 2012..yesterday I have restored SQL server 2008 database onto SQL server 2012...everything working fine...however some of reporting does not work due to Compute function discontinues in SQLL server 2012..

I am copying my small procedure here please help me to re wright it through ROLLUP...

ALTER PROCEDURE [dbo].[getReportDailyFundPosition]
  @FromDate varchar(15)=null
 ,@Todate varchar(15)=null

AS
BEGIN
set dateformat dmy
declare  @sumExpense numeric(20,2)
declare  @sumRceive numeric(20,2)
declare  @different numeric(20,2)

Declare @Expense Table(ReleaseDate datetime,RowNo int,ExpenseDetails varchar(500) ,ExpenseAmount numeric(10,2),ExpenseDate varchar(15))
Declare @Receive Table(PaymentExpectedDate datetime,RowNo int,Customer varchar(60) ,Amount numeric(18,2))

Insert into @Expense SELECT ReleaseDate,Row_Number() over(Partition by ReleaseDate order by cast(ReleaseDate as datetime) desc ) ,ExpenseDetails ,ExpenseAmount,ExpenseDate FROM ExpenseDetails
WHERE
    (@FromDate is null or (@FromDate is not null and ReleaseDate >=cast(@FromDate as datetime)))
and (@Todate is null or (@Todate is not null and ReleaseDate <=cast(@Todate as datetime)))
and Status not in ('Rejected','Paid')

Insert into @Receive SELECT cast(ISNull(PID.PaymentExpectedDate,dateadd(dd,30,convert(datetime,PID.InvoiceDate))) as datetime) as PaymentExpectedDate
,Row_Number() over(Partition by cast(ISNull(PID.PaymentExpectedDate,dateadd(dd,30,convert(datetime,PID.InvoiceDate))) as datetime) order by cast(ISNull(PID.PaymentExpectedDate,dateadd(dd,30,convert(datetime,PID.InvoiceDate))) as datetime) desc )
,Customer
,(isnull((select sum(ChequeAmount) + sum(TDSAmount) from PaymentInvoiceBankDetails
         where  InvoiceID=PID.InvoiceID and PaymentDeposited = 'No') ,0)
  + isnull((select distinct PendingAmount from PaymentInvoiceBankDetails
         where  InvoiceID=PID.InvoiceID ),PID.TotalBillAmount)) as Amount

 From PurchaseOrder PO
     INNER JOIN PaymentInvoiceDetails  PID on PID.POID = PO.POID
     INNER JOIN CustomerMaster  C on PO.CustomerName = C.CustomerID
     where
  (isnull((select sum(ChequeAmount) + sum(TDSAmount) from PaymentInvoiceBankDetails
         where  InvoiceID=PID.InvoiceID and PaymentDeposited = 'No') ,0)
  + isnull((select distinct PendingAmount from PaymentInvoiceBankDetails
         where  InvoiceID=PID.InvoiceID ),PID.TotalBillAmount)) >0

     and  GeneratedInvoiceNo is not null
     and (@FromDate is null or (@FromDate is not null and PaymentExpectedDate >=cast(@FromDate as datetime)))
     and (@Todate is null or (@Todate is not null and PaymentExpectedDate <=cast(@Todate as datetime)))


select Date1,ExpenseDetails,ExpenseAmount,ExpenseDate,Customer,Amount
,case when cast (Date1 as datetime ) > cast (ExpenseDate as datetime ) then 'Red' else '' end as RedFlag
,isnull(Amount,0)-isnull(ExpenseAmount,0) as diff
from
(
 select REPLACE(Convert(varchar,isnull(E.ReleaseDate,PaymentExpectedDate),6), ' ', '-') as Date1,ExpenseDetails,ExpenseAmount,REPLACE(convert(varchar,cast (ExpenseDate as datetime ),6), ' ', '-') as ExpenseDate,Customer,Amount
  from @Expense E left join @Receive R on E.ReleaseDate=R.PaymentExpectedDate and E.rowno=R.rowno
 Union
 select REPLACE(Convert(varchar,isnull(E.ReleaseDate,PaymentExpectedDate),6), ' ', '-') as Date1,ExpenseDetails,ExpenseAmount,REPLACE(convert(varchar,cast (ExpenseDate as datetime ),6), ' ', '-') as ExpenseDate,Customer,Amount
  from @Expense E right join @Receive R on E.ReleaseDate=PaymentExpectedDate and E.rowno=R.rowno
) As T
order by cast(Date1 as datetime) desc

Compute sum(ExpenseAmount),sum(Amount),sum(isnull(Amount,0)-isnull(ExpenseAmount,0))

推荐答案

请先阅读我的评论。



看看这里：

使用ROLLUP聚合 [ ^ ]

使用ROLLUP汇总数据 [ ^ ]

CUBE，ROLLUP，COMPUTE，COMPUTE BY，GROUPING SETS [ ^ ]

SQL SERVER - 汇总条款简介 [ ^ ]

Please, read my comment first.

Have a look here:
Aggregation WITH ROLLUP[^]
Summarizing Data Using ROLLUP[^]
CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS[^]
SQL SERVER – Introduction to Rollup Clause[^]

这篇关于在SQL Server 2012中汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！