Quick help with a select query 1

[zeeshanmbutt]

Quick help with a select query


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....

 

[AlexCuse]

You need to join the table to query that contains the count where (SHPrice <> PSPrice), in order to get both counts.

Try something like this:

select a.saledate, a.pricezonename, a.ProductID
, count(a.productID) as TotalCnt
, b.PIDCNT as CountWherePricesDiff
from #Tempp a
left join
(
select productID, count(*) as PIDCNT from #Tempp
where SHPrice <> PSPrice
group by productID
) b
on a.ProductID = b.ProductID
group by a.saledate, a.pricezonename, a.ProductID, b.PIDCNT

Hope that it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[zeeshanmbutt]

AlexCuse,

Thanks for your feedback. BUT how come the count for 'CountWherePricesDiff' is greater than 'TotalCnt'. It cannot be greater than 'TotalCnt'

Can you please check?.

Thanks a million....

 

[AlexCuse]

Ah yes, that. SOrry, here is an updated query. Needed to group by and join on saledate as well.

select a.saledate, a.pricezonename, a.ProductID
, count(a.productID) as TotalCnt
, isnull(b.PIDCNT,0) as CountWherePricesDiff
from #Tempp a
left join
(
select productID, sum(1) as PIDCNT from #Tempp
where SHPrice <> PSPrice
group by productID
) b
on a.ProductID = b.ProductID
group by a.saledate, a.pricezonename, a.ProductID, b.PIDCNT

Hope this one helps

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Ok, I swear that something is wrong with my ctrl+v buttons! HERE is teh real query

select a.saledate, a.pricezonename, a.ProductID
, count(a.productID) as TotalCnt
, isnull(b.PIDCNT,0) as CountWherePricesDiff
from #Tempp a
left join
(
select productID, saledate, sum(1) as PIDCNT from #Tempp
where SHPrice <> PSPrice
group by productID, saledate
) b
on a.ProductID = b.ProductID
and a.saledate = b.saledate
group by a.saledate, a.pricezonename, a.ProductID, b.PIDCNT

Ignorance of certain subjects is a great part of wisdom

 

[zeeshanmbutt]

AlexCuse,

Thanks again. BUT shouldn't we also join on 'pricezonename' as well?

Can you confirm & provide the correct query.

Thanks a million...

 

[AlexCuse]

Why not?

Look at what's in red, this what is new

select a.saledate, a.pricezonename, a.ProductID
, count(a.productID) as TotalCnt
, isnull(b.PIDCNT,0) as CountWherePricesDiff
from #Tempp a
left join
(
select productID, saledate[COLOR=red], pricezonename[/color]
, sum(1) as PIDCNT from #Tempp
where SHPrice <> PSPrice
group by productID, saledate
) b
on a.ProductID = b.ProductID
and a.saledate = b.saledate
[COLOR=red]and a.pricezonename = b.pricezonename[/color]
group by a.saledate, a.pricezonename, a.ProductID, b.PIDCNT

Hope it helps

Alex

Ignorance of certain subjects is a great part of wisdom

 

[zeeshanmbutt]

Thanks again. It looks good.

What if I dont want to see at per product level, rather I want see the diff price per zone, and per date only.
That is what if I need only the following in my reslut set.

saledate,pricezonename,TotalCnt,CountWherePricesDiff

Any help?

 

[AlexCuse]

zeeshanmbutt - I added pricezonename to the Group By in the subquery (sorry I missed that, you will want to add it to the query you are using as well).

In response to your question, you would just need to delete any references to ProductID, whether they are in select, group by or join portion of the query. You will also need to be sure to delete the necessary comma's.

I will highlight in red what needs to be deleted, so you can delete it yourself to see how it works for future reference:

select a.saledate, a.pricezonename[COLOR=red], a.ProductID[/color]
, count(a.productID) as TotalCnt
, isnull(b.PIDCNT,0) as CountWherePricesDiff
from #Tempp a
left join
(
select [COLOR=red]productID, [/color]saledate, pricezonename
, sum(1) as PIDCNT from #Tempp
where SHPrice <> PSPrice
group by [COLOR=red]productID,[/color] saledate, pricezonename
) b
on [COLOR=red]a.ProductID = b.ProductID
and [/color]a.saledate = b.saledate
and a.pricezonename = b.pricezonename
group by a.saledate, a.pricezonename[COLOR=red], a.ProductID[/color], b.PIDCNT

HOpe this helps,

ALex



Ignorance of certain subjects is a great part of wisdom

 

[zeeshanmbutt]

Thanks ...

You are great