如何在以下查询中使用提前查询 [英] How can I use brought forward query in this following query

问题描述

你好，

i希望通过我的查询带来当前总日期

请告诉我一些修改。



我尝试过：

hello,
i wanna bring the total till current date through my this query
please tell me some amendments.

What I have tried:

create procedure sp_broughtForward
(
  @gl_acid int,
  @userid int,
  @dateFrom datetime,
  @dateTo datetime
  )
  as 
  begin

SELECT 	ROW_NUMBER() OVER (Order by gLedeger.PK_id) as sno,gLedeger.gl_date as date,gLedeger.gl_narration  as Narration ,
	case when gLedeger.gl_drcr > 0 then abs(gLedeger.gl_drcr) else 0.00 end as debit,
	case when gLedeger.GL_DRCR	 < 0 then abs(gLedeger.gl_drcr) else 0.00 end as credit,
	SUM(gLedeger.gl_drcr) OVER(ORDER BY gledeger.pk_ID ROWS UNBOUNDED PRECEDING) AS    Balance  
	FROM gLedeger   LEFT JOIN master ON 
    gLedeger.gl_acid = master.PK_ID left join registration r on master.userId= @userid
    WHERE gLedeger.gl_acid = @gl_acid
	and gl_date between Convert(Date, @dateFrom ,103) and Convert(Date,@dateTo,103)
	ORDER BY gLedeger.gl_date
end

推荐答案

您的问题非常简短且难以完全理解情况，但是您似乎想从查询中检索数据加上结果中有一个总数或小计。



最简单的方法是创建两个返回q结果的查询uery和union所有第二个查询都包含你想要的小计。请注意工会中返回的字段所有查询必须是相同的订单和数据类型。



例如

Your questions very brief and difficult to fully understand the situation, however it appears you want to retrieve the data from your query plus have a total or subtotal in the result.

The easiest way to do this is to create two queries that return the result of your query and union all a second query with the subtotal you want. Please note the fields returned in a union all queries must be the same order and data types.

eg

Create procedure sp_broadForward
(
  @gl_acid int,
  @userid int,
  @dateFrom datetime,
  @dateTo datetime
  )
  as 
  begin
 
SELECT 	ROW_NUMBER() OVER (Order by gLedeger.PK_id) as sno,gLedeger.gl_date as date,gLedeger.gl_narration  as Narration ,
	case when gLedeger.gl_drcr > 0 then abs(gLedeger.gl_drcr) else 0.00 end as debit,
	case when gLedeger.GL_DRCR	 < 0 then abs(gLedeger.gl_drcr) else 0.00 end as credit,
	SUM(gLedeger.gl_drcr) OVER(ORDER BY gledeger.pk_ID ROWS UNBOUNDED PRECEDING) AS    Balance  
	FROM gLedeger   LEFT JOIN master ON 
    gLedeger.gl_acid = master.PK_ID left join registration r on master.userId= @userid
    WHERE gLedeger.gl_acid = @gl_acid
	and gl_date between Convert(Date, @dateFrom ,103) and Convert(Date,@dateTo,103)   -- etc (data)

Union all

select Field1s1, Fields2 --etc (Query to return the total or subtotal you want)

End

这篇关于如何在以下查询中使用提前查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！