Query to get difference of first & last values of column

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hi All,

I am getting result set for the below query

SQL

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

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

Posted 27-Jan-11 21:29pm

SRIRAM 2

Updated 27-Jan-11 21:57pm

Indivara

v3

Add a Solution

Comments

Sunasara Imdadhusen 28-Jan-11 3:42am

Added code formatting.

Indivara 28-Jan-11 3:58am

While you're at it, try to shorten horribly long titles as well...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gaurav_patel · Accepted Answer · 2011-01-28T00:47:00

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)