How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 nullI think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:

>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference i
s
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null

How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 nullI think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:
>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference is
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null|||Thank you so very much. It is really helpful!!
"Roy Harvey" wrote:
> I think this will do what you describe. The one difference is that
> the value for week 1 follows the rule specified "if no data for
> previous week, the difference is null", while the data shown does not
> reflect that. A CASE expression could be used to enforce that week 1
> would get zero, but I chose not to complicate the query that much.
> SELECT d, week, weight,
> (SELECT A.weight - B.weight
> FROM Whatever as B
> WHERE A.d = B.d
> AND A.week = B.week + 1)
> FROM Whatever as A
> Roy Harvey
> Beacon Falls, CT
> On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
> <luvgreen@.discussions.microsoft.com> wrote:
> >Hi, Experts:
> >
> >I have a question about getting running difference. The difference is
> >compared with previous week, if no data for previous week, the difference is
> >null. Any suggestions? Thanks much!
> >
> >id week weight diff
> >1 1 100 0
> >1 2 102 -2
> >1 3 99 3
> >2 1 103 0
> >2 3 102 null
>

How to get running difference with SQL?

Hi, Experts:
I have a question about getting running difference. The difference is
compared with previous week, if no data for previous week, the difference is
null. Any suggestions? Thanks much!
id week weight diff
1 1 100 0
1 2 102 -2
1 3 99 3
2 1 103 0
2 3 102 null
I think this will do what you describe. The one difference is that
the value for week 1 follows the rule specified "if no data for
previous week, the difference is null", while the data shown does not
reflect that. A CASE expression could be used to enforce that week 1
would get zero, but I chose not to complicate the query that much.
SELECT d, week, weight,
(SELECT A.weight - B.weight
FROM Whatever as B
WHERE A.d = B.d
AND A.week = B.week + 1)
FROM Whatever as A
Roy Harvey
Beacon Falls, CT
On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
<luvgreen@.discussions.microsoft.com> wrote:

>Hi, Experts:
>I have a question about getting running difference. The difference is
>compared with previous week, if no data for previous week, the difference is
>null. Any suggestions? Thanks much!
>id week weight diff
>1 1 100 0
>1 2 102 -2
>1 3 99 3
>2 1 103 0
>2 3 102 null
|||Thank you so very much. It is really helpful!!
"Roy Harvey" wrote:

> I think this will do what you describe. The one difference is that
> the value for week 1 follows the rule specified "if no data for
> previous week, the difference is null", while the data shown does not
> reflect that. A CASE expression could be used to enforce that week 1
> would get zero, but I chose not to complicate the query that much.
> SELECT d, week, weight,
> (SELECT A.weight - B.weight
> FROM Whatever as B
> WHERE A.d = B.d
> AND A.week = B.week + 1)
> FROM Whatever as A
> Roy Harvey
> Beacon Falls, CT
> On Mon, 18 Jun 2007 08:20:00 -0700, luvgreen
> <luvgreen@.discussions.microsoft.com> wrote:
>

How to get Recordset count

I'm currently using SQL 2005 but in previous versions I remember that within Query Analyzer there was a recordset count that displayed in the lower right hand corner. Currently within 2005 I can't find a way to do this without doing it manually which I can't do now since the stored procedure I'm testing has almost 100 recordsets. Any help would be greatly appricated.

Jerry:

That feature is still available; check and see if you are displaying the "status bar" (I think). In the meantime I'll do some more checking about that feature.

Kent

|||

In SSMS, it should be visible in the status bar of the Results window.

Welcome as a new user to SQL Server Jerry. Hopefully, you will be able to get the help you need from these forums, and as your skills progress, please don't forget to drop in occasionally and help others. (You will ALWAYS know something someone else can benefit from...)

It's not a 'recordset' -the output from a SQL query is referred to as a 'resultset'.

|||

My mistake on the 'recordset' comment. I know it's a resultset.

I've been using SQL for a bit now but the result set count still doesn't show up. I have all the bars visible. I see "Ln", "Col", "Ch" & I see the number of rows returned and server i'm running against under the resultset window.

|||

I think that perhaps you are looking to get the message that "n row(s) affected" that may follow an activity. (It sounds like perhaps you are seeing the "n rows" in the status bar.)

To get the "n row(s) affected" message, use this command:

SET NOCOUNT OFF

To stop getting the "n row(s) affected" message, use this command:

SET NOCOUNT ON

|||I thought in previous versions of SQL it actually said how man result sets were coming back. Not the number of rows but the number of result sets. I need 97 result sets to come back with a series of stored procedures that are being executed. I don't want to go through and count every result set.|||

I'm not aware of any facility within T-SQL that reports the number of resultsets returned. One query = one resultset.

I think that may be available with some of the ADO objects, specifically the datareader object.

I supose you could add a counter variable in the procedure, and check each query for @.@.ROWCOUNT, incremented as desired, and then return a statement using that variable.

|||

I don't remember one either.

You could possibly return the data to the grid and then (assuming no errors or print statements) you could get a count from the

(1 row(s) affected)

messages in the messages pane..

How to Get previous record thru sql query

How to Get previous record thru sql query

For the example

my table:

1 usera item1 1.00 01/02/07
2 usera item1 2.00 02/02/07
3 userc item2 3.00 03/02/07

--
how to use the query to make them join became like this (get/join with the next record)

1 usera item1 1.00 01/02/07 item1 2.00 02/02/07
3 userc item2 3.00 03/02/07 null null null

>.<
need help ... thanks alot

You can try something like :

select m1.*, m2.Item, m2.Qty, m2.Date from mytable m1 LEFT JOIN mytable m2 ON m1.user=m2.user WHERE m1.date != m2.Date

Hope this helps,

Vivek

|||

it will work only if dates are different change it to (assuming that your first column is Identity ID)

select m1.*, m2.Item, m2.Qty, m2.Date from mytable m1 LEFT JOIN mytable m2 ON m1.user=m2.user WHERE m1.ID != m2.ID

to get data in all cases, but this will return only two sets of data per customer what if you have more records per customer?

And it will also repeat entries for your test data you will have records for users reported two times in different order 1 with 2 and 2 with 1

so you have to modify this query a little

select m1.*, m2.Item, m2.Qty, m2.Date
from mytable m1
LEFT JOIN mytable m2
ON m1.user=m2.user
andm1.ID != m2.ID
WHEREm1.ID < m2.ID

but it will work only if you have no more than 2 records per user

try and let me know if it works fro you or not.

Thanks

|||

Thanks alot who has replied me ^^

Yes.. i hav try on the way thatJpazgier provided, but it's too bad i m having more than 2 records in same user and the order is not just only at the top and bottom for example it may

1 usera item1 1.00 01/02/07
2 usera item1 2.00 02/02/07
3 userc item2 3.00 03/02/07
4 usera item1 1.50 04/02/07

and after i have join it will become

1 usera item1 1.00 01/02/07 item1 2.00 02/02/07
2 usera item1 2.00 02/0207 item1 1.50 04/02/07
3 userc item2 3.00 03/02/07 null null null
4 usera item1 1.50 04/02/07 null null null

thank u very much for ans my question ^^

|||

Another query for getting two consecutive rows (the row which you want along with the previous row)

select * from TableName Where DateField='02/03/07' Or DateField in(Select Top 1 DateField from TableName where DateField < ''02/03/07' Order by DateField DESC)

Thanks

Sorry Webmaster: (I am feeling bad that The forum @. ASP.Net was not allowed me to enter my Indian Standard Time (GMT + 5:30))