counting row changes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

counting row changes

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-31 : 15:22:36

I have a table that looks something like this

Date value1
5/1 1
5/1 1
5/1 1
5/1 2
5/1 2
5/2 2
5/2 2
5/3 1
5/3 1
I want to have as a result:
5/1 1 because it went from 1 to 2
5/2 0 because it stayed at 2
5/3 1 because it went from 2 to 1

can anyone help? how do i do somehting like this

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-31 : 15:30:29

if in a day it as more changes for example if Value1 goes in a single day from 1 to 2 to 3 then it changed twice and therefore it should be something like
5/1 2

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 15:50:06

What if it goes from 1 to 2 to 1 in the same day?

Corey

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-31 : 16:01:45

yes its 2 changes

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-31 : 16:08:22

Order of Data in a Database has no meaning...you'd need a column to indicate what the last value would be...

How about



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(OrderEntry int IDENTITY(1,1), [Date] datetime, Value1 int)
GO

INSERT INTO myTable99([Date],Value1)
SELECT '5/1/2004', 1 UNION ALL
SELECT '5/1/2004', 1 UNION ALL
SELECT '5/1/2004', 1 UNION ALL
SELECT '5/1/2004', 2 UNION ALL
SELECT '5/1/2004', 2 UNION ALL
SELECT '5/2/2004', 2 UNION ALL
SELECT '5/2/2004', 2 UNION ALL
SELECT '5/3/2004', 1 UNION ALL
SELECT '5/3/2004', 1
GO

--Find the Number of Changes in 1 Day

SELECT [Date], COUNT(DISTINCT Value1)-1 AS CHANGES 
INTO myChanges99
FROM myTable99
GROUP BY [Date]

-- Find The Last Value for a Day

SELECT [Date], Value1
 INTO myLastValue99 
  FROM myTable99 o
 WHERE OrderEntry = ( SELECT MAX(OrderEntry) 
			FROM myTable99 i
		       WHERE i.[date] = o.[date] 
		    GROUP BY [Date])

-- Now if the Last Value for a Day Matches the last Value for the previous Day, Subtract 1?  I'm guessing now...
-- And if it's not the same, add 1?

SELECT l.Date, CHANGES + ChangeValue AS FinalChanges 
  FROM myChanges99 l 
  JOIN (SELECT l.[Date], ISNULL(r.Value1 - l.Value1,0) AS ChangeValue 
	  FROM myLastValue99 l LEFT JOIN myLastValue99 r
	    ON l.[Date] = DATEADD(d,1,r.[date])) AS r
    ON l.[date] = r.[Date]
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myChanges99
DROP TABLE myLastValue99
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-31 : 16:09:15

your table needs a primary key to be able to return reliable results.

otherwise, there is no logical difference between:

5/1 1
5/1 1
5/1 1
5/1 2
5/1 2

and

5/1 1
5/1 2
5/1 1
5/1 2
5/1 1

which would return 2 different results. Is there an additional column in your table that serves as (or contributes to) a PK?

If so, you can use my "runs and streaks" technique on this -- it should work fairly well (thought it is inefficient with very large sets of data). check out the articles at this site for more info.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-31 : 16:12:49

Hey Jeff...where you bean?

Yeah I forgot about that article....

http://www.sqlteam.com/item.asp?ItemID=12654

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-31 : 16:16:10

i've been coming and going -- sorry i missed the thread about naming conventions !

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-08-31 : 16:51:13

Triggers would probably be the easiest to implement.

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-31 : 16:53:05

sorry the date has a timestamp too and its sorted by date

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-31 : 16:53:29

how would i implement it with a Trigger

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 09:03:13

Well that's good...just group by CONVERT(varchar,yourDateCol,101) and do the MAX based on that date..

But you do still have other problems, like going back and forth between the same value.

Jeff's article is good..but the TRIGGER idea is better....

in other words, for every INSERT, check the previous values and save the stats that you want to a log...

Is this homework?

What practical purpose does this information support?

Brett

8-)

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-01 : 13:30:41

i am only doing a select, basicly, i want a report with the changes, basicly a change represents ineficiency in the work process, so the more changes we have the more that explains our efficiency percent meaning if you go from setup 1 to setup 2 its a change if you go back again to setup 1 that is another change. every setup change takes time , time that its not used by production.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 15:30:57

....bout to be hung for this...also you should be able to tweak the logic so the final select doesn't need a group by...

See, I told you I don't know SQL


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([Date] datetime, Value1 int)
GO

INSERT INTO myTable99([Date],Value1)
SELECT '5/1/2004 1:00', 1 UNION ALL
SELECT '5/1/2004 2:00', 1 UNION ALL
SELECT '5/1/2004 3:00', 1 UNION ALL
SELECT '5/1/2004 4:00', 2 UNION ALL
SELECT '5/1/2004 5:00', 2 UNION ALL
SELECT '5/1/2004 6:00', 1 UNION ALL
SELECT '5/1/2004 7:00', 2 UNION ALL
SELECT '5/2/2004 1:00', 2 UNION ALL
SELECT '5/2/2004 2:00', 2 UNION ALL
SELECT '5/3/2004 1:00', 1 UNION ALL
SELECT '5/3/2004 2:00', 1
GO

DECLARE @Date datetime, @Value1 int, @Date_Old datetime, @Value1_Old int
DECLARE @Changes table ([Date] datetime, Value1 int)

DECLARE myCursor99 CURSOR FOR
SELECT [Date], Value1 FROM myTable99 ORDER BY [date]

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @Date, @Value1

WHILE @@FETCH_STATUS = 0
  BEGIN

	IF @Date <> ISNULL(@Date_Old,'') AND @Value1 = ISNULL(@Value1_Old,@Value1)
	  BEGIN
		INSERT INTO @Changes([Date], Value1)
		SELECT CONVERT(varchar(10),@Date,101), 0
		SELECT @Date_Old = @Date, @Value1_Old = @Value1
	  END

	IF @Date <> ISNULL(@Date_Old,'') AND @Value1 <> ISNULL(@Value1_Old,@Value1)
	  BEGIN
		INSERT INTO @Changes([Date], Value1)
		SELECT CONVERT(varchar(10),@Date,101), 1
		SELECT @Date_Old = @Date, @Value1_Old = @Value1
	  END

	IF @Value1 <> @Value1_Old
	  BEGIN
		UPDATE @Changes SET Value1 = Value1 + 1 
		WHERE CONVERT(varchar(10),Date,101) = CONVERT(varchar(10),@Date,101)
		SELECT @Value1_Old = @Value1
	  END
	FETCH NEXT FROM myCursor99 INTO @Date, @Value1
  END
CLOSE myCursor99
DEALLOCATE myCursor99

SELECT [Date], SUM(Value1) FROM @Changes GROUP BY [Date]
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-01 : 16:02:36

damm son you good :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-01 : 16:16:22

Brett... you should hang... unnecessary cursors... BAH!


Declare @myTable table ([Date] datetime, Value1 int)

INSERT INTO @myTable([Date],Value1)
SELECT '5/1/2004 1:00', 1 UNION ALL
SELECT '5/1/2004 2:00', 1 UNION ALL
SELECT '5/1/2004 3:00', 1 UNION ALL
SELECT '5/1/2004 4:00', 2 UNION ALL
SELECT '5/1/2004 5:00', 2 UNION ALL
SELECT '5/1/2004 6:00', 1 UNION ALL
SELECT '5/1/2004 7:00', 2 UNION ALL
SELECT '5/2/2004 1:00', 2 UNION ALL
SELECT '5/2/2004 2:00', 2 UNION ALL
SELECT '5/3/2004 1:00', 1 UNION ALL
SELECT '5/3/2004 2:00', 1


Select 
	date = convert(datetime,convert(nvarchar,Date,101)),
	CngCnt = sum(Changed)
From
	(
	Select 
		Date, 
		value1, 
		changed = case when (select top 1 value1 from @myTable where date < A.date Order By Date Desc)<> value1 then 1 else 0 end
	From @myTable as A
	) as Z
Group By convert(datetime,convert(nvarchar,Date,101))

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 16:21:08

And there you go....Thanks Corey...

I will now go home and repent with a bottle of tequila

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-01 : 16:23:37

no need to repent... but don't let us keep you from your ritas

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources