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

Question

Post reply

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

sriramnidumolu92

SSC Journeyman

Points: 84
More actions
January 28, 2011 at 1:28 am

#244374

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)
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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11420 More actions · Answer 1

wrong post

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Garadin One Orange Chip Points: 29613 More actions · Answer 2

As Mike said, you'll get better, faster results posting usable sample data in the fashion he supplies below. That said, I had a few minutes to kill, so here's one method.

;WITH

MM AS (

SELECT MIN(invoicedate) MinDate, MAX(invoicedate) MaxDate

FROM SalesData S

WHERE month(S.invoicedate) BETWEEN 1AND 4 and year(S.invoicedate) BETWEEN 2009 AND 2010),

OQ AS (

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)),

MinVal AS (

SELECT Qty FROM OQ INNER JOIN MM ON OQ.MNTH = MONTH(MM.MinDate) AND OQ.YEAR1 = YEAR(MM.MinDate)),

MaxVal AS (

SELECT Qty FROM OQ INNER JOIN MM ON OQ.MNTH = MONTH(MM.MaxDate) AND OQ.YEAR1 = YEAR(MM.MaxDate))

SELECT OQ.*, (SELECT Qty FROM MaxVal) - (SELECT Qty FROM MinVal) Diff

FROM OQ

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]