Filtering Query help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Filtering Query help

Author

Topic

Tava
Starting Member

3 Posts

Posted - 2010-09-24 : 01:27:52

Hi All,

I'm just new to this forum, so not sure if i'm in the right place to ask or sorry if this has been asked before.

Currently i'm writing a simple query (what i thought was anyway to retrieve transaction history. Our database is based about transactions and items per transaction and im only trying to pull out certain information based on say promotions, below is an example of the Columns in a table for a database called "testdb".

[testdb].[dbo].[transactions]

ReceiptNo | Product | Qty | Price | Logged

1 | KingPackage | 1 | 500 | 2010-09-21
1 | King Bed | 1 | N/A | 2010-09-21
1 | Bedsides | 2 | N/A | 2010-09-21
-----------------------------------------
2 | KingPackage | 1 | 500 | 2010-09-21
2 | King Bed | 1 | N/A | 2010-09-21
2 | Bedsides | 2 | N/A | 2010-09-21
2 | GiftCard | 1 | 100 | 2010-09-21

I'm trying to build a query to see how many KingPackage's are sold but rule is it must contain GiftCard as part of the filter otherwise it is not the right promotion and i have no idea what the receipt numbers are. So in this case the output of information would be only for Receipt #2 and needs to be displayed as below:

Output

ReceiptNo | Product | Qty | Price
2 | KingPackage | 1 | 500
2 | King Bed | 1 | N/A
2 | Bedsides | 2 | N/A
2 | GiftCard | 1 | 100

I've already written a Query to try to get this result & completed the necessary script

SELECT t.ReceiptNo,
t.Product,
t.Qty,
t.Price
t.Logged

FROM [testdb].[dbo].[transactions] as t

WHERE (t.Logged > '2010-09-21 00:01' AND t.Logged < '2010-09-21 23:59')

But i dont know how to pull through all the information as below, if i filter it to product = KingPackage can just get 1 line but for all receipts that have sold this.

ReceiptNo | Product | Qty | Price
2 | KingPackage | 1 | 500
2 | King Bed | 1 | N/A
2 | Bedsides | 2 | N/A
2 | GiftCard | 1 | 100

Any help would be greatly appreciated.

Tava

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-24 : 04:21:40

SELECT * FROM transactions
WHERE t.ReceiptNo IN
(
SELECT t.ReceiptNo,
FROM [testdb].[dbo].[transactions] as t
WHERE (t.Logged > '2010-09-21 00:01' AND t.Logged < '2010-09-21 23:59')
AND t.Product = 'KingPackage'
AND EXISTS(SELECT 1 FROM transactions WHERE Product ='GiftCard' AND ReceiptNo = t.ReceiptNo)
)

Tava
Starting Member

3 Posts

Posted - 2010-09-26 : 19:06:46

quote:
Originally posted by rohitvishwakarma

SELECT * FROM transactions
WHERE t.ReceiptNo IN
(
SELECT t.ReceiptNo,
FROM [testdb].[dbo].[transactions] as t
WHERE (t.Logged > '2010-09-21 00:01' AND t.Logged < '2010-09-21 23:59')
AND t.Product = 'KingPackage'
AND EXISTS(SELECT 1 FROM transactions WHERE Product ='GiftCard' AND ReceiptNo = t.ReceiptNo)
)

Thanks for your help rohitvishwakarma,

I'm still struggling to get this working i tried using your statement just in a new query but that failed but modified it slightly to get it to work but still not sure how to incorporate it in my code. I think when i started this thread i thought it would be a simple statement but missed out key areas of information to help.

I've modified the code but not sure where to put in your select statement you provided, Here is sample of code which is more accurate:

SELECT *
FROM [testdb].[dbo].[transsummary] AS ts

INNER JOIN [testdb].[dbo].[transdetail] AS td ON

ts.storeid = td.storeid AND
ts.transnumber = td.transnumber

WHERE (ts.Logged > '2010-09-21 00:01' AND ts.Logged < '2010-09-21 23:59')

ORDER BY ts.storeid

the "Transsummary" table holds the original receipt numbers, logged dates and cost but "Transdetails" holds all items, receipt numbers as well, which is why i've done a join.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources