query help needed, multi joins

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

SQL Server Development (2000)

query help needed, multi joins

Author

Topic

ould
Starting Member

6 Posts

Posted - 2008-07-31 : 13:18:42

Hi everyone,

I am a new member here and am looking for help on a Query I am trying to code.

Here's the general idea. I have a database which stores credit card transactions which come from different terminals, among many other things. Basically what I am trying to accomplish is to have a listing of the number of transactions per each terminal with $ total as well as showing me terminals that have no transactions. I have the first part working presently but am unable to get the query to bring up terminals that have no transactions.

There are 4 tables involved in my query:

1. CreditCardTransaction - lists credit transactions
2. DevTyp - Description of the Devices
3. Parking - Where the device is located
4. CreditCardMerchant(not shown in my current query as I couldn't get it to work, it threw off my numbers) - lists all terminals that are able to accept credit cards

Fields:

CreditCardTransaction - TerminalId, Transaction_Amout, TerminalType and ParkingId

DevTyp - Joined by TerminalId

Parking - Joined by ParkingId

CreditCardMerchant - Joined by TerminalId

What I have so far:

SELECT CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc As Parking_Lot, COUNT(*) As Transaction_Count, SUM (CreditCardTransaction.Transaction_Amount) as Total_Sales
FROM CreditCardTransaction
JOIN Parking ON CreditCardTransaction.ParkingId = Parking.Id
JOIN DevTyp ON CreditCardTransaction.TerminalType = DevTyp.DeviceId
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= '07/01/2008 00:00:00' AND
CreditCardTransaction.Upload_DateTime <= '07/30/2008 23:59:59'
GROUP by CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc
ORDER by Parking.Dsc ASC, DevTyp.DeviceDesc ASC, CreditCardTransaction.TerminalId ASC

This gives me output like:

4 Cashier station Lot 5V 2 10.0000
5 Cashier station Lot 5V 284 2372.0000

Which is what I am after as far as layout is concerned, but currently it doesn't show me the terminals which have no sales.

I tried to use this query but it threw off my numbers and still didn't show me the terminals with no sales:

SELECT CreditCardMerchant.TerminalId, DevTyp.DeviceDesc, Parking.Dsc As Parking_Lot, COUNT(*) As Transaction_Count, SUM (CreditCardTransaction.Transaction_Amount) as Total_Sales
FROM CreditCardTransaction
LEFT OUTER JOIN CreditCardMerchant ON CreditCardTransaction.TerminalId = CreditCardMerchant.TerminalId
JOIN Parking ON CreditCardTransaction.ParkingId = Parking.Id
JOIN DevTyp ON CreditCardTransaction.TerminalType = DevTyp.DeviceId
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= '07/01/2008 00:00:00' AND
CreditCardTransaction.Upload_DateTime <= '07/30/2008 23:59:59'
GROUP by CreditCardMerchant.TerminalId, DevTyp.DeviceDesc, Parking.Dsc
ORDER by Parking.Dsc ASC, DevTyp.DeviceDesc ASC, CreditCardMerchant.TerminalId ASC

It gave me the same output except the numbers were skewed and it still didn't give me the terminals with no transactions.

Also while I am at it how do I tell the query to give me the last week worth of data rather than editing the dates by hand everytime I run the script. So say I run it on Monday, I want the previous Monday through Sunday data.

Thanks for any insight, if you need more info please let me know...

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:28:59

Which is base table for terminals?
the last week worth of data can be obtained by replacing where condition as
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= DATEADD(d,DATEDIFF(d,0,GETDATE()-7),0) AND
CreditCardTransaction.Upload_DateTime <= DATEADD(d,DATEDIFF(d,0,GETDATE()-3),0)

ould
Starting Member

6 Posts

Posted - 2008-07-31 : 13:42:47

quote:
Originally posted by visakh16

Which is base table for terminals?
the last week worth of data can be obtained by replacing where condition as
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= DATEADD(d,DATEDIFF(d,0,GETDATE()-7),0) AND
CreditCardTransaction.Upload_DateTime <= DATEADD(d,DATEDIFF(d,0,GETDATE()-3),0)

Thanks for the reply I will try your date strings,

The "CreditCardMerchant" table lists all terminals which are able to except credit cards which is all I want returned in my query. I guess that would make it the base. "CreditCardTransaction" table just lists transactions one by one (TransactionDateTime, CC#, Expiry, Amount, terminal #, parking #, terminaltype, creditcardtype etc etc). Some terminals may have no transactions for the period listed(i.e. not being used, equipment failure etc.)

Hope that helps.

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:50:37

May be this.

SELECT CreditCardMerchant.TerminalId, 
temp.DeviceDesc, 
temp.Parking_Lot, 
temp.Transaction_Count,
temp.Total_Sales
FROM CreditCardMerchant
LEFT OUTER JOIN 
( 
SELECT CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc As Parking_Lot, COUNT(*) As Transaction_Count, SUM (CreditCardTransaction.Transaction_Amount) as Total_Sales
FROM CreditCardTransaction 
JOIN Parking ON CreditCardTransaction.ParkingId = Parking.Id 
JOIN DevTyp ON CreditCardTransaction.TerminalType = DevTyp.DeviceId
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= DATEADD(d,DATEDIFF(d,0,GETDATE()-7),0) AND
CreditCardTransaction.Upload_DateTime <=DATEADD(d,DATEDIFF(d,0,GETDATE()-3),0) 
GROUP by CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc)temp
ON temp.TerminalId = CreditCardMerchant.TerminalId
ORDER by temp.Parking_Lot ASC, temp.DeviceDesc ASC, CreditCardMerchant.TerminalId ASC

ould
Starting Member

6 Posts

Posted - 2008-07-31 : 16:23:06

Thanks a bunch!!

That seemed to work! I had to change the date strings slightly and add a DISTINCT to the first SELECT statement. CreditCardMerchant has a terminalid for each Credit Card Type(Visa, MC, AMEX etc) so I had reapeating data initially.

Now, is there a somewhat easy way to automatically run this every week email the results to a specified email address(es)?? Or even export to an excel file would be good. If not, no big deal I can run it manually.

Thanks again,

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 06:17:32

quote:
Originally posted by ould

Thanks a bunch!!

That seemed to work! I had to change the date strings slightly and add a DISTINCT to the first SELECT statement. CreditCardMerchant has a terminalid for each Credit Card Type(Visa, MC, AMEX etc) so I had reapeating data initially.

Now, is there a somewhat easy way to automatically run this every week email the results to a specified email address(es)?? Or even export to an excel file would be good. If not, no big deal I can run it manually.

Thanks again,

Kevin

yup. you can wrap this query in a stored procedure and add a sql agent job to call the procedure and schedule it to according to your convienience. The job will automatically run procedure and get the results. you could then use xp_sendmail procedure to send results as mail to email addresses (you should have to set up mail profiles for users).

ould
Starting Member

6 Posts

Posted - 2008-08-13 : 17:23:46

Actually after running this query a few more times I am seeing it isn't quite picking up all the terminals as I had hoped. From what I can tell if there is no transactions at all from a given terminal anywhere in the CreditCardTransaction table it won't return a NULL in my results but if there is transactions appearing from said terminal just not in my specified date range then it will return with a NULL. I want to see a NULL come up no matter what, as long as the terminal appears in the CreditCardMerchant table. I thought the "LEFT outer JOIN" would accomplish this but it doesn't appear to be working correctly. Any ideas on how I can accomplish this? Here is the query in it's current almost working state:

SELECT DISTINCT CreditCardMerchant.TerminalId, 
temp.DeviceDesc, 
temp.Parking_Lot, 
temp.Transaction_Count,
temp.Total_Sales
FROM CreditCardMerchant
LEFT OUTER JOIN 
( 
SELECT CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc As Parking_Lot, COUNT(*) As Transaction_Count, SUM (CreditCardTransaction.Transaction_Amount) as Total_Sales
FROM CreditCardTransaction 
JOIN Parking ON CreditCardTransaction.ParkingId = Parking.Id 
JOIN DevTyp ON CreditCardTransaction.TerminalType = DevTyp.DeviceId
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= DATEADD(d,DATEDIFF(d,0,GETDATE()-7),0) AND
CreditCardTransaction.Upload_DateTime <=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) 
GROUP by CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc)temp
ON temp.TerminalId = CreditCardMerchant.TerminalId
ORDER by temp.Parking_Lot ASC, CreditCardMerchant.TerminalId ASC, temp.DeviceDesc ASC

I had to use the DISTINCT as CreditCardMerchant has 3 records per terminal(one for each credit card type accepted).

Thanks again!!

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:45:23

Not sure about what you're telling. it will return a record if it exists in CreditCardMerchant no matter whether it didnt exist in others or even didnt have matching ones. cann you explain with some data problem you're facing?

ould
Starting Member

6 Posts

Posted - 2008-08-18 : 12:11:23

quote:
Originally posted by visakh16

Not sure about what you're telling. it will return a record if it exists in CreditCardMerchant no matter whether it didnt exist in others or even didnt have matching ones. cann you explain with some data problem you're facing?

That is what I am saying, it's not returing all records from CreditCardMerchant. It's only returning when there is transactions in CreditCardTransaction. For example I have a terminal #4 from a Lot #10 which appears in CreditCardMerchant but is not presently being used hence there are no transactions currently coming from it yet it doesn't appear in my result set. It's as if the Left Join is not functioning the way it is supposed to.

Here's some examples to help explain since I find it hard to explain over the net:

Query:


SELECT DISTINCT CreditCardMerchant.TerminalId, Parking.Dsc, DevTyp.DeviceDesc
FROM CreditCardMerchant
JOIN Parking ON CreditCardMerchant.ParkingId = Parking.Id
JOIN DevTyp ON CreditCardMerchant.TerminalType = DevTyp.DeviceId
ORDER by Parking.Dsc

Results:


TerminalId  Dsc                        DeviceDesc                     
----------- --------------------       --------------------------- 
4           Lot 5V                         Cashier station
5           Lot 5V                         Cashier station
14          Lot 6A/Ice Gardens             Cashier station
1           Lot PS-II                      Automated pay
1           Lot PS-II                      Cashier station
1           Lot PS-II                      Exit
2           Lot PS-II                      Automated pay
2           Lot PS-II                      Exit
5           PS-III                         Automated pay
6           PS-III                         Automated pay
7           PS-III                         Automated pay
8           PS-III                         Automated pay
18          PS-III                         Cashier station
31          PS-III                         Exit
32          PS-III                         Exit
33          PS-III                         Exit
34          PS-III                         Exit
3           York Lanes                     Automated pay
4           York Lanes                     Automated pay
11          York Lanes                     Cashier station
20          York Lanes                     Exit
21          York Lanes                     Exit

(22 row(s) affected)

See there is 22 distinct terminals.

However when I Run this query:


SELECT DISTINCT CreditCardMerchant.TerminalId, 
temp.DeviceDesc, 
temp.Parking_Lot, 
temp.Transaction_Count,
temp.Total_Sales
FROM CreditCardMerchant
LEFT OUTER JOIN 
( 
SELECT CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc As Parking_Lot, COUNT(*) As Transaction_Count, SUM (CreditCardTransaction.Transaction_Amount) as Total_Sales
FROM CreditCardTransaction 
JOIN Parking ON CreditCardTransaction.ParkingId = Parking.Id 
JOIN DevTyp ON CreditCardTransaction.TerminalType = DevTyp.DeviceId
WHERE CreditCardTransaction.Approved <> 0 AND
CreditCardTransaction.Upload_DateTime >= DATEADD(d,DATEDIFF(d,0,GETDATE()-7),0) AND
CreditCardTransaction.Upload_DateTime <=DATEADD(d,DATEDIFF(d,0,GETDATE()),0) 
GROUP by CreditCardTransaction.TerminalId, DevTyp.DeviceDesc, Parking.Dsc)temp
ON temp.TerminalId = CreditCardMerchant.TerminalId
ORDER by temp.Parking_Lot ASC, CreditCardMerchant.TerminalId ASC, temp.DeviceDesc ASC

here are the results:


TerminalId  DeviceDesc                     Parking_Lot                    Transaction_Count Total_Sales           
----------- ------------------------------ ------------------------------ ----------------- --------------------- 
5           Cashier station                Lot 5V                         54                446.0000
14          Cashier station                Lot 6A/Ice Gardens             1                 10.0000
1           Automated pay                  Lot PS-II                      200               1962.0000
1           Cashier station                Lot PS-II                      169               1741.0000
1           Exit                           Lot PS-II                      168               1288.0000
2           Automated pay                  Lot PS-II                      56                556.0000
2           Exit                           Lot PS-II                      142               1343.0000
5           Automated pay                  PS-III                         5                 46.0000
6           Automated pay                  PS-III                         279               2463.0000
7           Automated pay                  PS-III                         248               2625.0000
8           Automated pay                  PS-III                         7                 62.0000
18          Cashier station                PS-III                         5                 40.0000
31          Exit                           PS-III                         106               929.0000
32          Exit                           PS-III                         115               1082.0000
33          Exit                           PS-III                         51                372.0000
34          Exit                           PS-III                         208               1895.0000
3           Automated pay                  York Lanes                     397               3808.5000
4           Automated pay                  York Lanes                     248               2173.0000
11          Cashier station                York Lanes                     96                832.0000
20          Exit                           York Lanes                     99                961.0000
21          Exit                           York Lanes                     66                537.0000

(21 row(s) affected)

It only returns 21 terminals. Terminal #4 From 5V is missing(it has no transactions as it is out of service at the moment.)

Any Thoughts? I really appreciate you taking the time to look at this for me..

Thanks,

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 12:51:43

I cant understand how it will miss out records which exists in CreditCardMerchant when you use LEFT JOIN. it can only happen if you've some where caluse attached to your query. Are you sure you're using the same query or are you merging this with something else?

ould
Starting Member

6 Posts

Posted - 2008-08-18 : 13:37:11

I am using just what I posted and am currently only using it in Query analyzer, I copied it straight from there into my post as I ran it. I am at a loss too as to why it isn't working.

Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources