如何在以下查询中使用提前查询

问题描述

你好，

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

推荐答案

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

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