查询以获得第一和第二个差异列的最后一个值 [英] Query to get difference of first & last values of column

问题描述

大家好，

我正在获取以下查询的结果集

Hi All,

I am getting result set for the below query

SELECT CAST(SUM(CASE WHEN  S.TAXABLEAMT <=2000 THEN (S.INVOICEQTY) ELSE NULL END) AS DECIMAL(30,2)) AS QTY ,
YEAR(S.invoicedate) YEAR1,Month(S.invoicedate) MNTH  
FROM SALESDATA S
where  month(S.invoicedate) BETWEEN 1AND 4  and year(S.invoicedate) BETWEEN  2009 AND 2010  
GROUP BY YEAR(S.invoicedate),Month(S.invoicedate)
ORDER BY YEAR(S.invoicedate),Month(S.invoicedate)

如
数量 每月/每年
250 01/2010
238 02/2010
450 03/2010
238 04/2010
150 05/2010
238 05/2010
650 06/2010
238 07/2010
250 08/2010
238 09/2010
250 10/2010
238 11/2010
250 12/2010
238 01/2009
250 01/2009
238 02/2009
450 03/2009
238 04/2009

现在我想要结果集中第一列第一个值(即250)和第一列最后一个值(即238)的数量

作为单独的列(即仅单个值).

有可能吗?(在查询中使用单独的表或在同一表中)

问候，
NSJ

-
原始标题-如何获取存储过程结果集中的第一列第一个值和第一列最后一个值之间的区别?

as
QTY MONTH/YEAR
250 01/2010
238 02/2010
450 03/2010
238 04/2010
150 05/2010
238 05/2010
650 06/2010
238 07/2010
250 08/2010
238 09/2010
250 10/2010
238 11/2010
250 12/2010
238 01/2009
250 01/2009
238 02/2009
450 03/2009
238 04/2009

Now I want difference between QTY of First column First value(i.e.250) and First column Last value(i.e.238 ) in result set

as Separate column.(i.e. only single value ).

Is it possible?(with Separate table or within same table in the query)

Regards,
NSJ

--
Original title - How to get the difference between First column First value and First column Last value in result set of a stored procedure?

推荐答案

试试这个

try this

SELECT CAST(SUM(CASE WHEN  S.TAXABLEAMT <=2000 THEN (S.INVOICEQTY) ELSE NULL END) AS DECIMAL(30,2)) AS QTY ,
YEAR(S.invoicedate) YEAR1,Month(S.invoicedate) MNTH,
(
	(SELECT top 1 CAST(SUM(CASE WHEN  S1.TAXABLEAMT <=2000 THEN (S1.INVOICEQTY) ELSE NULL END) AS DECIMAL(30,2)) AS QTY
	FROM SALESDATA S1
	where  month(S1.invoicedate) BETWEEN 1AND 4  and year(S1.invoicedate) BETWEEN  2009 AND 2010  
	ORDER BY YEAR(S1.invoicedate),Month(S1.invoicedate))
	-
	(SELECT top 1 CAST(SUM(CASE WHEN  S2.TAXABLEAMT <=2000 THEN (S2.INVOICEQTY) ELSE NULL END) AS DECIMAL(30,2)) AS QTY
	FROM SALESDATA S2
	where  month(S2.invoicedate) BETWEEN 1AND 4  and year(S2.invoicedate) BETWEEN  2009 AND 2010  
	ORDER BY YEAR(S2.invoicedate) DESC,Month(S2.invoicedate) DESC)
) [Diffrence]
FROM SALESDATA S
where  month(S.invoicedate) BETWEEN 1AND 4  and year(S.invoicedate) BETWEEN  2009 AND 2010  
GROUP BY YEAR(S.invoicedate),Month(S.invoicedate)
ORDER BY YEAR(S.invoicedate),Month(S.invoicedate)

这篇关于查询以获得第一和第二个差异列的最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！