Quick help with a select query !!!

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 2005 Forums

Transact-SQL (2005)

Quick help with a select query !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-20 : 13:17:31

Hi All,

I have a table called #Tempp with the following fields & types.

saledate(datetime),pricezonename(nvarchar),storeid(nvarchar),productid(nvarchar),SHPrice(decimal),PSPrice(decimal)

This #Tempp have the following type of records.
saledate,pricezonename,storeid,productid,SHPrice,PSPrice
2007-02-04,SouthSide_City,4138,1-01200000157-000,1.89,1.89
2007-02-04,SouthSide_City,4126,1-01200000157-000,1.75,1.89
2007-02-04,SouthSide_City,4110,1-01200000157-000,1.75,1.89
2007-02-04,SouthSide_City,4157,1-01200000157-000,1.75,1.89

As you can see the #Tempp table tells what is the SHPrice & PSPrice per product; per date; per zone; and per outlet.

I want to display the records per date,per zone, where the SHPrice is different than the PSPrice.

That is, using the above data I want to display the following fields & records (as in above example).

saledate,pricezonename,count(productid) ProductCount, CountWherePricesAreDiff
2007-02-04,SouthSide_City,4,3

Here we can note the following,
1)ProductCount is 4 since there are '4 sets of SHPrice & PSPrice' per date per zone for that productid '1-01200000157-000'.

2)I want to create a field 'CountWherePricesAreDiff' which in this example would be 3, since there are 3 places where the 'SHPrice & PSPrice' are differnt for that productid '1-01200000157-000' per zone per date.

How can I Create this 'CountWherePricesAreDiff' field?

Can somepne please provide me the TSQL script for that?

Please let me know.

Thanks a million....

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-20 : 13:25:30

Is it me? Or is this as clear as mud?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-20 : 13:37:12

X002548,

What do you mean, sorry I am not following you clear. Is the question not clear? Please let me know.

Can anyone help on this quickly?

Thanks a million in advance

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-20 : 14:02:09

Can anyone help PLEASE?

Thanks....

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-20 : 14:55:37

Read the int link in my sig...I'm not sure what you really want to do

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-20 : 17:46:25

This will return the results in your example, but it has issues if all the prices are the same Or maybe it was the data I used?).. FYI..

-- Prepare data
DECLARE @Temp TABLE 
(
	saledate datetime, 
	pricezonename nvarchar(50),
	storeid nvarchar(50),
	productid nvarchar(50),
	SHPrice decimal(10,2),
	PSPrice decimal(10,2)
)
INSERT @Temp
SELECT '2007-02-04', 'SouthSide_City', '4138', '1-01200000157-000' ,1.89,1.89 UNION ALL
SELECT '2007-02-04', 'SouthSide_City', '4126', '1-01200000157-000',1.75,1.89 UNION ALL
SELECT '2007-02-04', 'SouthSide_City', '4110', '1-01200000157-000',1.75,1.89 UNION ALL
SELECT '2007-02-04', 'SouthSide_City', '4157', '1-01200000157-000',1.75,1.89 

-- Run query
SELECT 
	t.saledate,
	t.pricezonename,
	COUNT(t.productid) AS ProductCount,
	COUNT(t.productid) - COALESCE(
	(
		SELECT 
			COUNT(a.productid)
		FROM
			@Temp a
		INNER JOIN
			@Temp b
			ON a.saledate = b.saledate
			AND a.pricezonename = b.pricezonename
			AND	a.productid = b.productid
		WHERE
			a.SHPrice = b.PSPrice
			AND a.PSPrice = b.SHPrice
			AND t.saledate = a.saledate
			AND t.pricezonename = a.pricezonename
			AND t.productid = a.productid
	), 0) AS CountWherePricesAreDiff
FROM
	@Temp t
GROUP BY
	t.saledate,
	t.pricezonename,
	t.productid

Cheers,

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 02:05:46

Well, what happens when using this test data? Mud?

-- Prepare data
DECLARE @Temp TABLE 
(
	saledate datetime, 
	pricezonename nvarchar(50),
	storeid nvarchar(50),
	productid nvarchar(50),
	SHPrice decimal(10,2),
	PSPrice decimal(10,2)
)
INSERT @Temp
SELECT '2007-02-04', 'SouthSide_City', '4138', '1-01200000157-000' ,1.89, 1.89 UNION ALL
SELECT '2007-02-04', 'SouthSide_City', '4126', '1-01200000157-000', 1.75, 1.89 UNION ALL
SELECT '2007-02-04', 'SouthSide_City', '4110', '1-01200000157-000', 1.77, 1.89 UNION ALL
SELECT '2007-02-04', 'SouthSide_City', '4157', '1-01200000157-000', 1.75, 1.89

Peter Larsson
Helsingborg, Sweden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-21 : 17:48:10

quote:
Originally posted by Peso

Well, what happens when using this test data? Mud?

It returns:

saledate                pricezonename  ProductCount CountWherePricesAreDiff
----------------------- -------------- ------------ -----------------------
2007-02-04 00:00:00.000 SouthSide_City 4            3

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources