Need Help With a Query - 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

General SQL Server Forums

Database Design and Application Architecture

Need Help With a Query

Author

Topic

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 09:05:29

Hi everyone. First, thanks again for such a great site!

Ok. I have this query that should be simple, but has been driving me nuts for about 5 hours. Time for some help I think. :)

Here is my sample table structure and sample data:

UID PID MID VN R
--- --- --- --- ---
1 1 3 abcd 0
2 1 5 jght 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 1 3 iigj 1
4 1 5 fjfj 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5 2 3 fjfe 0
6 2 5 oogg 0
7 2 9 ffdd 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8 2 3 qjww 1
9 2 5 ooid 1
10 2 9 opwm 0

Each row is unique by combo of PID, MID, and VN. I have UID in there as a identity just for ease of joining to other tables. PID is a purchase id. There are two merchants (3 and 5) on the first purchase id = 1 and there are two purchases. There are 3 merchants and 2 purchases on purchase id = 2. To aid in seeing the data, i separated the purchases by the ~~~ row.

What I need to do is get a count of the number of purchases that have at least 1 merchant redeemed value (R) that is equal to 1. If there are two where R=1 (for example, 1-3-iigj-1 and 1-5-fjfj-1) that should be counted just once.

The end result of the query should be "3" based on the data above. This is because the first purchase has 1 merchant redeemed, the second has 2, the third has none, and the forth has 2.

I was trying group by's with having clauses and distinct count, but none of it was working. I am close, but nothing seems to be getting the right result! Any help would be MUCH appreciated. Thanks so much everyone!

Dennis

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 09:19:17

I think you supplied bad data... Let me know if my assumption is incorrect.


Declare @t table (
	UID int,
	PID int,
	MID int,
	VN varchar(10),
	R bit
)
Insert Into @t Select 1, 1, 3, 'abcd', 0
Insert Into @t Select 2, 1, 5, 'jght', 1
Insert Into @t Select 3, 2, 3, 'iigj', 1
Insert Into @t Select 4, 2, 5, 'fjfj', 1
Insert Into @t Select 5, 3, 3, 'fjfe', 0
Insert Into @t Select 6, 3, 5, 'oogg', 0
Insert Into @t Select 7, 3, 9, 'ffdd', 0
Insert Into @t Select 8, 4, 3, 'qjww', 1
Insert Into @t Select 9, 4, 5, 'ooid', 1
Insert Into @t Select 10, 4, 9, 'opwm', 0

Select COUNT(distinct pId) From @t Where R = 1

Corey

I Has Returned!!

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 09:52:23

Hi Corey. The data is actually correct. To answer your question though, i need to give more info I think.

I have a purchases table that contains 1 purchaseid per purchase - this is how i relate my table data below to other parts of my application. In that table, there is a quantity field. For purchaseid = 1 in my data, the quantity would be 2 in the purchases table, hence why you see 2 sets of pid = 1 and mid = 3 and 5.

Within the same purchase in my data shown, the VN # is the unique part. So, for example, the first 4 rows consist of 1 purchase that has quantity of "2" and has 2 merchants with one voucher for each merchant/purchase combo for each quantity bought.

Please let me know if you need more explanation, I know it is kind of confusing. thx!

Dennis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 09:54:24

how do you identify the individual purchases. in your sample data, there are multiple purchases with same id then how do you uniquely define them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 10:09:10

I think this is a bit janky though... you don't have a clean way of separating the purchases.


Declare @t table (
	UID int,
	PID int,
	MID int,
	VN varchar(10),
	R bit
)
Insert Into @t Select 1, 1, 3, 'abcd', 0
Insert Into @t Select 2, 1, 5, 'jght', 1
Insert Into @t Select 3, 1, 3, 'iigj', 1
Insert Into @t Select 4, 1, 5, 'fjfj', 1
Insert Into @t Select 5, 2, 3, 'fjfe', 0
Insert Into @t Select 6, 2, 5, 'oogg', 0
Insert Into @t Select 7, 2, 9, 'ffdd', 0
Insert Into @t Select 8, 2, 3, 'qjww', 1
Insert Into @t Select 9, 2, 5, 'ooid', 1
Insert Into @t Select 10, 2, 9, 'opwm', 0


;with a as (Select *, PN = ROW_NUMBER()Over(Partition By PID, MID Order By UID) From @t)
, b as (Select *, APN = Dense_Rank()Over(Order By PID, PN) From a)
Select COUNT(distinct APN) From b Where R = 1

Corey

I Has Returned!!

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 11:02:52

Corey, below is real test data from my system. Basically, I am trying to "collapse" the merchantid to be "if one or more have a redeemedlocation that is not null, that counts as "1". The purchase data is also supplied so you can see how it relates.

I see what you mean about not really having a way to specify if a record is part of one specific purchase. You can SEE it from the way the data is laid out, but hard to get at in code. See this the new data helps. I could not get this query to work (returns 4, not 3):

with a as (Select *, PN = ROW_NUMBER()Over(Partition By PurchaseID, MerchantID Order By VoucherID) From Vouchers)
, b as (Select *, APN = Dense_Rank()Over(Order By PurchaseID, PN) From a)
Select COUNT(distinct APN) From b Where RedeemedAtLocationID IS NOT NULL AND PurchaseID IN (SELECT PurchaseID FROM Purchases WHERE DealID = 3)

What I COULD do is add in another field to vouchers that basically is just a helper field to do what we need to do. Call it "QuantityID". That would just be a 1, 2, 3 etc... that just groups the purchases together to show the merchants that are part of that purchase. Would that help??

Please let me know thoughts, thanks again buddy!

Dennis

----------------------------

Declare @vouch table (
VoucherID int,
PurchaseID int,
MerchantID int,
VoucherNumber varchar(12),
RedeemedAtLocationID INT
)

Insert Into @vouch Select 5,1,3,'8951027261',4
Insert Into @vouch Select 6,1,3,'2368511042',4
Insert Into @vouch Select 7,1,5,'7674706111',9
Insert Into @vouch Select 8,1,5,'1686929692',NULL
Insert Into @vouch Select 9,2,4,'6197520511',2
Insert Into @vouch Select 10,3,3,'85108073531',NULL
Insert Into @vouch Select 11,3,5,'67114008721',NULL
Insert Into @vouch Select 12,3,3,'90129692192',NULL
Insert Into @vouch Select 13,3,5,'84131677692',NULL
Insert Into @vouch Select 14,3,3,'76146595273',NULL
Insert Into @vouch Select 15,3,5,'11159521093',NULL
Insert Into @vouch Select 18,8,3,'5482642571',NULL
Insert Into @vouch Select 19,8,3,'4481948132',NULL
Insert Into @vouch Select 20,8,3,'9284508763',NULL
Insert Into @vouch Select 21,8,5,'9983723251',NULL
Insert Into @vouch Select 22,8,5,'7084468732',9
Insert Into @vouch Select 23,8,5,'6988208563',NULL
Insert Into @vouch Select 24,10,4,'98101431351',NULL
Insert Into @vouch Select 25,10,4,'44106393582',NULL
Insert Into @vouch Select 34,12,3,'10128208131',4
Insert Into @vouch Select 35,12,3,'66123160352',NULL
Insert Into @vouch Select 36,12,3,'60122386543',NULL
Insert Into @vouch Select 37,12,3,'33121501034',NULL
Insert Into @vouch Select 38,12,5,'92121135401',NULL
Insert Into @vouch Select 39,12,5,'60129320682',NULL
Insert Into @vouch Select 40,12,5,'77125213813',NULL
Insert Into @vouch Select 41,12,5,'70127861434',NULL
Insert Into @vouch Select 42,13,4,'10137197021',NULL
Insert Into @vouch Select 43,13,4,'137504692',2
Insert Into @vouch Select 44,13,4,'60137830093',NULL

Declare @purch table (
PurchaseID int,
DealID int
)

Insert Into @purch Select 1,3
Insert Into @purch Select 2,9
Insert Into @purch Select 3,3
Insert Into @purch Select 8,3
Insert Into @purch Select 10,5
Insert Into @purch Select 12,3
Insert Into @purch Select 13,5

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 11:05:34

Sorry, wrong query. Forgot to update table names:

with a as (Select *, PN = ROW_NUMBER()Over(Partition By PurchaseID, MerchantID Order By VoucherID) From @vouch)
, b as (Select *, APN = Dense_Rank()Over(Order By PurchaseID, PN) From a)
Select COUNT(distinct APN) From b Where RedeemedAtLocationID IS NOT NULL AND PurchaseID IN (SELECT PurchaseID FROM @purch WHERE DealID = 3)

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 11:07:48

visakh16:

You are right, i think that is the problem. This table was not originally designed to do what I am trying to do, so I might need to add a field to help group each part of the purchase.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 11:23:38

I think my query still works fine, try running this:


;with a as (Select *, PN = ROW_NUMBER()Over(Partition By PurchaseID, MerchantID Order By VoucherID) From @vouch Where PurchaseID IN (SELECT PurchaseID FROM @purch WHERE DealID = 3)) 
, b as (Select *, APN = Dense_Rank()Over(Order By PurchaseID, PN) From a)
--Select COUNT(distinct APN) From b Where RedeemedAtLocationID IS NOT NULL
Select * From b --Where RedeemedAtLocationID IS NOT NULL

The results indicate that from this set of data, there are 4 cases that match your criteria

P.S.

quote:

What I COULD do is add in another field to vouchers that basically is just a helper field to do what we need to do. Call it "QuantityID". That would just be a 1, 2, 3 etc... that just groups the purchases together to show the merchants that are part of that purchase. Would that help??

That is exactly what the first CTE does...

;with a as (Select *, PN = ROW_NUMBER()Over(Partition By PurchaseID, MerchantID Order By VoucherID) From @vouch Where PurchaseID IN (SELECT PurchaseID FROM @purch WHERE DealID = 3))

Corey

I Has Returned!!

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 12:03:34

Ah, yes. You are right. I was still thinking about my smaller dataset from earlier. There is another problem though. You cannot tell whether a merchant is part of a purchase or not from this data. For example, look at purchase id 10 and 12. You cannot tell if that correlates to purchase id 11 or 13. The table needs another field. Let's use this data now to get the new query. THANKS!

Dennis
------------

Declare @vouch table (
VoucherID int,
PurchaseID int,
QuantityID smallint,
MerchantID int,
VoucherNumber varchar(12),
RedeemedAtLocationID INT
)

Insert Into @vouch Select 5,1,1,3,'8951027261',4
Insert Into @vouch Select 6,1,2,3,'2368511042',4
Insert Into @vouch Select 7,1,1,5,'7674706111',9
Insert Into @vouch Select 8,1,2,5,'1686929692',NULL
Insert Into @vouch Select 9,2,1,4,'6197520511',2
Insert Into @vouch Select 10,3,1,3,'85108073531',NULL
Insert Into @vouch Select 11,3,1,5,'67114008721',NULL
Insert Into @vouch Select 12,3,2,3,'90129692192',NULL
Insert Into @vouch Select 13,3,2,5,'84131677692',NULL
Insert Into @vouch Select 14,3,3,3,'76146595273',NULL
Insert Into @vouch Select 15,3,3,5,'11159521093',NULL
Insert Into @vouch Select 18,8,1,3,'5482642571',NULL
Insert Into @vouch Select 19,8,2,3,'4481948132',NULL
Insert Into @vouch Select 20,8,3,3,'9284508763',NULL
Insert Into @vouch Select 21,8,1,5,'9983723251',NULL
Insert Into @vouch Select 22,8,2,5,'7084468732',9
Insert Into @vouch Select 23,8,3,5,'6988208563',NULL
Insert Into @vouch Select 24,10,1,4,'98101431351',NULL
Insert Into @vouch Select 25,10,2,4,'44106393582',NULL
Insert Into @vouch Select 34,12,1,3,'10128208131',4
Insert Into @vouch Select 35,12,2,3,'66123160352',NULL
Insert Into @vouch Select 36,12,3,3,'60122386543',NULL
Insert Into @vouch Select 37,12,4,3,'33121501034',NULL
Insert Into @vouch Select 38,12,1,5,'92121135401',NULL
Insert Into @vouch Select 39,12,2,5,'60129320682',NULL
Insert Into @vouch Select 40,12,3,5,'77125213813',NULL
Insert Into @vouch Select 41,12,4,5,'70127861434',NULL
Insert Into @vouch Select 42,13,1,4,'10137197021',NULL
Insert Into @vouch Select 43,13,2,4,'137504692',2
Insert Into @vouch Select 44,13,3,4,'60137830093',NULL

Declare @purch table (
PurchaseID int,
DealID int,
QuantityID smallint
)

Insert Into @purch Select 1,3,2
Insert Into @purch Select 2,9,1
Insert Into @purch Select 3,3,3
Insert Into @purch Select 8,3,3
Insert Into @purch Select 10,5,2
Insert Into @purch Select 12,3,4
Insert Into @purch Select 13,5,3

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 12:22:40

So taking out the first cte, I end up with this:


;with a as (Select *, APN = Dense_Rank()Over(Order By PurchaseID, QuantityId) From @vouch)
Select COUNT(distinct APN) From a Where RedeemedAtLocationID IS NOT NULL AND PurchaseID IN (SELECT PurchaseID FROM @purch WHERE DealID = 3)

Corey

I Has Returned!!

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 12:39:31

yes, looks like this works! Thanks so much. Two questions:

1) Can you explain that query? It is using things I have not seem before and maybe the concept can help me in other applications.

2) I am trying to use the above as a subquery to pull in to my result set in another area of my application. This does not work, I need a work around using your method:

Declare @deal table (
DealID int,
CityID int,
StartDateTime datetime,
DealImage1 varchar(25)
)

Insert Into @deal Select 3,1,null,'/test/hi.jpg'

Declare @cities table (
CityID int,
CItyName varchar(25)
)

Insert Into @cities Select 1,'chicago'

SELECT D.StartDateTime, D.DealImage1, C.CityName,
(WITH a AS (SELECT *, APN = Dense_Rank()OVER(ORDER BY PurchaseID, QuantityId) FROM Vouchers)
SELECT COUNT(DISTINCT APN) FROM a WHERE RedeemedAtLocationID IS NOT NULL AND PurchaseID IN (SELECT PurchaseID FROM Purchases WHERE DealID = 3)) AS TotalRedeemed
FROM @deal D INNER JOIN cities C ON C.CityID = D.CityID
WHERE D.DeleteFlag = 0
ORDER BY D.StartDateTime

Thanks again for the help. You are a master!

Dennis

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 12:50:52


;WITH a AS (SELECT *, APN = Dense_Rank()OVER(ORDER BY PurchaseID, QuantityId) FROM Vouchers)

SELECT D.StartDateTime, D.DealImage1, C.CityName, 
(SELECT COUNT(DISTINCT APN) FROM a WHERE RedeemedAtLocationID IS NOT NULL AND PurchaseID IN (SELECT PurchaseID FROM Purchases WHERE DealID = 3)) AS TotalRedeemed
FROM @deal D INNER JOIN cities C ON C.CityID = D.CityID
WHERE D.DeleteFlag = 0 
ORDER BY D.StartDateTime

The part in blue is a CTE or Common Table Expression (kinda like a subquery: http://msdn.microsoft.com/en-us/library/ms190766.aspx)
I'm using the CTE to uniquely identify each <purchaseId,quantityId> pair so that we can use Distinct in the count().

Dense_Rank() is an aggregate function that provides a rank for each row based on the order by conditions. It differs from Rank() in that if 2 (or more) rows 'tie' based on the order by conditions, they will share the same rank (like Rank()), but no rank numbers will be skipped (unlike Rank()).

Corey

I Has Returned!!

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-17 : 12:58:09

Amazing. Thanks so much! You learn something new every day! Have a great one.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources