help pleaseeeee - 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

New to SQL Server Programming

help pleaseeeee

Author

Topic

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-11 : 21:43:58

i want to find the customers with at least once transaction which contained more than the half item_categories

the tables i have are:

transactions(transID,transCustomer,transStore,transDateTime)
transactionItems(transID,itemID,quantity,ivalue)
items(itemID,itemGroupID)
item_groups(groupID,group_categoryID)
item_categories(categoryID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 22:49:09

[code]SELECT t.*
FROM transactions t
INNER JOIN (SELECT ti.transID,COUNT(ig.group_categoryID) AS CatCount
FROM transactionItems ti
INNER JOIN items i
ON i.itemID=ti.itemID
INNER JOIN item_groups ig
ON ig.groupID=i.itemgroupID
GROUP BY ti.transID)tmp
ON tmp.transID=t.transID
WHERE tmp.CatCount>(SELECT COUNT(*) FROM item_categories)/2.0[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

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

SELECT
t.transID,t.transCustomer ,t.transStore ,t.transDateTime
FROM transactions11 t
CROSS APPLY
(SELECT
ti.transID,COUNT(ig1.group_categoryID) AS ccount
FROM
transactionItems11 ti
INNER JOIN
items11 i ON i.itemID=ti.itemID
INNER JOIN
item_groups11 ig1 ON ig1.groupID=i.itemgroupID
GROUP BY
ti.transID)tm
WHERE
tm.transID=t.transID
AND tm.ccount >= (SELECT COUNT(*) FROM item_categories11)/2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 23:21:43

please note that APPLY operator will work only in sql 2005 and above with compatibility level 90 and above

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-12 : 13:06:45

i have sql 2005

the first answer gives me 961 rows and the second one 1544 rows

adding a distinct to the codes and dividing with 7 because i had no answers.........

SELECT t.transCustomer,tmp.Category_Count
FROM transactions t
INNER JOIN (SELECT ti.transID,COUNT ( distinct ig.group_categoryID) AS Category_Count
FROM transactionItems ti
INNER JOIN items i
ON i.itemID=ti.itemID
INNER JOIN item_groups ig
ON ig.group_ID=i.itemgroupID
GROUP BY ti.transID)tmp
ON tmp.transID=t.transID
WHERE tmp.Category_Count>(SELECT COUNT(*) FROM item_categories)/7
order by t.transCustomer desc

SELECT t.transCustomer ,tm.ccount
FROM transactions t
CROSS APPLY
(SELECT ti.transID,COUNT( distinct ig1.group_categoryID) AS ccount
FROM transactionItems ti
INNER JOIN items i ON i.itemID=ti.itemID
INNER JOIN item_groups ig1 ON ig1.group_ID=i.itemgroupID
GROUP BY ti.transID)tm
WHERE tm.transID=t.transID
AND tm.ccount >= (SELECT COUNT(*) FROM item_categories)/7
order by t.transCustomer desc

i get as answer 2913 at the first staatement and 40083 at the second

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-12 : 16:43:23

quote:

Originally posted by visakh16

SELECT t.*
FROM transactions t
INNER JOIN (SELECT ti.transID,COUNT(ig.group_categoryID) AS CatCount
            FROM transactionItems ti
            INNER JOIN items i
            ON i.itemID=ti.itemID
            INNER JOIN item_groups ig
            ON ig.groupID=i.itemgroupID
            GROUP BY ti.transID)tmp
ON tmp.transID=t.transID
WHERE tmp.CatCount>(SELECT COUNT(*) FROM item_categories)/2.0

is there any way to collect the number of times every customer matches the criteria???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 23:40:44

quote:

Originally posted by constantinos1987

quote:

Originally posted by visakh16

SELECT t.*
FROM transactions t
INNER JOIN (SELECT ti.transID,COUNT(ig.group_categoryID) AS CatCount
            FROM transactionItems ti
            INNER JOIN items i
            ON i.itemID=ti.itemID
            INNER JOIN item_groups ig
            ON ig.groupID=i.itemgroupID
            GROUP BY ti.transID)tmp
ON tmp.transID=t.transID
WHERE tmp.CatCount>(SELECT COUNT(*) FROM item_categories)/2.0

is there any way to collect the number of times every customer matches the criteria???

SELECT t.transCustomer,COUNT(transID) AS transactions
FROM transactions t
INNER JOIN (SELECT ti.transID,COUNT(ig.group_categoryID) AS CatCount
            FROM transactionItems ti
            INNER JOIN items i
            ON i.itemID=ti.itemID
            INNER JOIN item_groups ig
            ON ig.groupID=i.itemgroupID
            GROUP BY ti.transID)tmp
ON tmp.transID=t.transID
WHERE tmp.CatCount>(SELECT COUNT(*) FROM item_categories)/2.0
GROUP BY t.transCustomer

number of times they had a transaction which contain greater than half category of items? if yes,use above

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-13 : 12:24:36

thank you guys!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 12:33:09

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources