difference btween sales orders by month

Question

0.00/5 (No votes)

See more:

Pid Sales Month1
1 10 Jan
2 30 Feb
3 60 Mar
4 50 Apr

based on Month1 How to Display sales values diffrence

Like
OutPut

Pid Sales Month1 Differe
1 10 Jan 0
2 30 Feb -20
3 60 Mar -30
4 50 Apr 10

Posted 16-Mar-14 20:54pm

Member 10675465

Add a Solution

2 solutions

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)

Peter Leow · Answer 1 · 2014-03-16T22:40:00

Solution 2

Try this:

SQL

select t1.pid, t1.sales, t1.month1,
isnull((select t2.sales from table1 t2 where t2.pid = t1.pid - 1) - t1.sales, 0) as differe
from table1 t1

Posted 16-Mar-14 22:40pm

Peter Leow

Updated 16-Mar-14 22:42pm

v2

Comments

Member 10675465 17-Mar-14 6:12am

thanks its working

Peter Leow 17-Mar-14 6:41am

You are welcome.

Jörgen Andersson · Answer 2 · 2014-03-16T21:24:00

Solution 1

Depends on your database and version thereof.
If it's new enough you can use the analytical function Lag

SQL

Select  Pid,
        Sales,
        Month1,
        LAG(sales, 1, 0) OVER (PARTITION BY Pid ORDER BY Month1) - sales as difference
FROM    MyTable

For other solutions you need to state your Database and model as it differs a bit between them

<update>
You can also use Row_Number:

SQL

With ordered as (
    Select  Pid,
            Sales,
            Month1,
            Row_Number() OVER (PARTITION BY Pid ORDER BY Month1) rn
    FROM    MyTable
    )
Select  O1.Pid,
        O1.Sales,
        O1.Month1,
        O2.Sales - O1.sales as Difference
FROM    Ordered O1 left outer Join Ordered O2 on O1.rn = O2.rn - 1

</update>

Posted 16-Mar-14 21:24pm

Jörgen Andersson

Updated 16-Mar-14 23:01pm

v5

Comments

Member 10675465 17-Mar-14 3:35am

Its Not working error i am getting Msg 195, Level 15, State 10, Line 4
'LAG' is not a recognized built-in function name.

My Database is sql2005

Jörgen Andersson 17-Mar-14 3:59am

Lag doesn't exist in SQLServer 2005, but Row_Number does. Updated solution.

Member 10675465 17-Mar-14 4:08am

notworking i am getting error Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'OVER'.

Jörgen Andersson 17-Mar-14 4:24am

fixed typo

Member 10675465 17-Mar-14 4:28am

No luck below error i am getting Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Jan' to data type int.

Jörgen Andersson 17-Mar-14 5:02am

I'm having bad luck when I'm thinking today, updated solution again. :-)