Counting Events, which influence a certain column

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

General SQL Server Forums

New to SQL Server Programming

Counting Events, which influence a certain column

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-12 : 10:09:04

Hi,

I have a table (which is a join of two tables where ID=ID), which has the following structure:

Name........Quarter........ID.........Price.........Type
PA..........A..............1..........11............b
PA..........A..............4..........12............b
PA..........A..............9..........12............s

What I am trying to do is, I would like to count the number of 'b'- and 'a'-types for each Quarter (A,B,C and D), whenever the price has changed, which happened in the above example in row 2 (ID 4). My code right now only prints out everthing, but I would have to count everything in Excel. Can you help me on this one?

This is how my code looks like:

select b.Type, b.ID, b.Name, a.Quarter, a.Price
from PriceDate1 a
cross join PricesBackup b
where b.ID = a.ID
order by b.Name, a.Quarter, b.ID

Tnx in advance

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-12 : 11:36:08

Basically you want to count how many DISTINCT Prices within a TYPE.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 11:39:58

[code]select b.Type, b.ID, b.Name, a.Quarter, a.Price,NULL AS Seq
INTO #Temp
from PriceDate1 a
inner join PricesBackup b
on b.ID = a.ID

DECLARE @Quarter CHAR(3),
@Type CHAR(1),
@Price money,
@seq INT

SELECT @Quarter = Quarter,
@Type = Type,
@Price=Price
@seq = 0
FROM #Temp
ORDER BY Quarter,
Type

UPDATE #Temp
SET @seq = seq = CASE
WHEN @Quarter = Quarter AND @Type =Type AND @Price <> Price THEN @seq + 1
ELSE 0
END,
@Quarter = Quarter,
@Type = Type,
@Price=Price
WHERE Type IN ('a','b')

SELECT *
FROM #Temp
WHERE Seq >0
order by Name, Quarter, ID[/code]

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-12 : 11:54:08

wow, tnx for the answer, I will try this asap! does this code also consider the name in the counting, because I need to find out how many types in each quarter for each name there are..

@habingl: distinct prices wouldn't do it, because we can stay at a certain price level for a long time, so that prices would repeat itself all the time..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 11:57:20

quote:
Originally posted by rocco2008

wow, tnx for the answer, I will try this asap! does this code also consider the name in the counting, because I need to find out how many types in each quarter for each name there are..

@habingl: distinct prices wouldn't do it, because we can stay at a certain price level for a long time, so that prices would repeat itself all the time..

nope. name was not considered. modify like below to include name


select b.Type, b.ID, b.Name, a.Quarter, a.Price,NULL AS Seq
INTO #Temp
from PriceDate1 a
inner join PricesBackup b
on b.ID = a.ID 

DECLARE	@Name varchar(50),
        @Quarter CHAR(3),
	@Type CHAR(1),
        @Price money,
	@seq INT

SELECT		@Name=Name,
                @Quarter = Quarter,
		@Type = Type,
                @Price=Price
		@seq = 0
FROM		#Temp
ORDER BY	Quarter,
		Type

UPDATE	#Temp
SET	@seq = seq =	CASE
				WHEN @Name=Name AND @Quarter = Quarter AND @Type =Type AND @Price <> Price THEN @seq + 1
				ELSE 0
			END,
        @Name=Name,
	@Quarter = Quarter,
	@Type = Type,
        @Price=Price
WHERE Type IN ('a','b')

SELECT *
FROM #Temp
WHERE Seq >0
order by Name, Quarter, ID

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-12 : 12:43:22

tnx for the correction, but I get this error message:

(54891 row(s) affected)

Server: Msg 8152, Level 16, State 2, Line 22
String or binary data would be truncated.
The statement has been terminated.

(0 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 12:48:29

i have just given some arbitrary lengths for varchar fields make sure you use same length as length of your field in table for variables.

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-12 : 12:50:21

okay, now I made it work, but it gives me the wrong results. it looks like it is counting how many times each price appears for each name in the different quarters and each type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 13:01:43

what about this?

SELECT  t.Name,t.Quarter,t.Type,COUNT(*) AS CountValue
FROM YourTable t
CROSS APPLY (SELECT TOP 1 Price
             FROM YourTable
             WHERE Name=t.Name
             AND Quarter=t.Quarter
             AND Type=t.Type
             AND Price <> t.Price
             AND ID >t.ID
             ORDER BY ID)t1
GROUP BY t.Name,t.Quarter,t.Type

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-12 : 15:41:00

tnx for the great help! I am not sure, but at first glance don't we have to join the tables somewhere?? The Problem is, that the 'Type'-Values are only in table 2 and the 'Price'-Values are only in table one. I have no rights to update the table and just write the information into the table I want... :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:07:38

then can you post some sample data from tables please?

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-13 : 06:26:03

sure, this is how it looks like:

ID Name Size Type Subtype Price
2211877 ABC 252 S T 44.22
2211920 ABC 74 B M 44.22
2211990 ABC 253 B T 44.24
2212058 ABC 500 B L 44.25
2212128 ABC 404 B L 44.26
2212166 ABC 115 B M 44.26
2212194 ABC 253 B T 44.26
2212229 ABC 500 B L 44.26
2212237 ABC 404 B L 44.26
2212270 ABC 582 S L 44.25
2212323 ABC 253 B M 44.25
2212453 ABC 253 B T 44.26

ID Name Size Type Subtype Quarter
2211877 ABC 252 S T A
2211920 ABC 74 B M A
2211990 ABC 253 B T A
2212058 ABC 500 B L A
2212128 ABC 404 B L A
2212166 ABC 115 B M A
2212194 ABC 253 B T A
2212229 ABC 500 B L A
2212237 ABC 404 B L A
2212270 ABC 582 S L A
2212323 ABC 253 B M A
2212453 ABC 253 B T A

tnx again!

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-14 : 05:41:14

any ideas yet?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources