September 24, 2009 at 11:23 am
I have been running into an issue with simplifying my code to produce a better result. First off, I am pulling data on terminals with their different types of activity. Right now I pull each type of transaction separately using 7 SELECT queries. Then I must manually align each type of transaction up with its associated terminal id number. So I get the following.
Query 1
Result
Terminal ID-----Total Trans
A1----------------------1
A2----------------------5
A3----------------------10
=====================
Query 2
Result
Terminal ID--------SC
A1----------------------2
A2----------------------1
A3----------------------6
What I would like to do is have this.
Terminal ID-----Total Trans-------SC
A1----------------------1----------------2
A2----------------------5----------------1
A3----------------------10---------------6
My problem is that I am using specific WHERE clauses that pertains to each individual SELECT query. If I was to create it as one big SELECT query, my results would not be correct as the WHERE clause would incorrectly include or skip pertinent data for the different types of Data that I am pulling.
The one constant is [Terminal ID]
Here is my code
-- Total Transactions
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT ([Terminal ID]) AS [Total Trans]
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
-- Surcharges
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT (CAST (tbl_Export_Record10_Layout.[Fee Amount] AS NUMERIC (6))) AS Surcharge
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND dbo.tbl_Export_Record10_Layout.Reject = ''
AND tbl_Export_Record10_Layout.[Fee Amount] <> '0000000'
AND tbl_Export_Record10_Layout.[Fee Mark] <> ''
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
-- Withdrawals
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT (CAST (tbl_Export_Record10_Layout.[Credit Amount] AS NUMERIC (10))) AS Withdrawal
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND dbo.tbl_Export_Record10_Layout.Reject = ''
AND type in ('SW','SWM','DW','DWM','CW','CWM','FW','FWM','OW','OWM')
OR tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND tbl_Export_Record10_Layout.[Credit Amount] <> '00000000000'
AND tbl_Export_Record10_Layout.[Credit Mark] = ''
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
-- Deposits
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT (CAST (tbl_Export_Record10_Layout.[Debit Amount] AS NUMERIC (10))) AS Deposits
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND dbo.tbl_Export_Record10_Layout.Reject = ''
AND type in ('SD','DD','CDD','OD')
OR tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND tbl_Export_Record10_Layout.[Debit Amount] <> '00000000000'
AND tbl_Export_Record10_Layout.[Debit Mark] = ''
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
-- Balance Inquiries
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT ([Terminal ID]) AS [# of Bal Inq]
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND dbo.tbl_Export_Record10_Layout.Reject = ''
AND type in ('SBI','DBI','CBI','FBI','OBI')
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
-- Transfers
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT ([Terminal ID]) AS [# of XFer]
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND dbo.tbl_Export_Record10_Layout.Reject = ''
AND type IN ('STS','STD','STO','DTS','DTD','DTO','CTS','CTD','CTO','OTS','OTD','OTO')
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
-- Denials
USE TerminalActivity;
GO
SELECT DISTINCT [Terminal ID],
COUNT ([Terminal ID]) AS Denials
FROM dbo.tbl_Export_Record10_Layout
WHERE tbl_Export_Record10_Layout.DTProcessingDate BETWEEN '09/20/2009' AND '09/23/2009'
AND dbo.tbl_Export_Record10_Layout.Reject <> ''
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
GO
My other problem is that a few of the queries do not return values for every terminal ID, lets use Deposits for instance. Not every terminal does deposits so if a terminal ID does not have a deposit, I want it to return a 0. currently it ignores the terminal ID altogether and instead of showing the same number of results as total trans, it only has half of the terminals, or only the ones that completed a deposit.
I know this is a long post but I really appreciate any help that is provided.
Thank you very much for your time.
Bryan
September 24, 2009 at 12:07 pm
You could use a series of CTEs to help you. Each one could contain a query that you've written and then join them. This is a good CTE intro: Common Table Expressions in SQL Server 2005[/url]. you want to be careful in that if you can combine queries, you do want to.
You can self join tables, so if you need to use them together.
Select count(a.tran), count(b.tran)
from sales a
innner join sales b
on a.pk = b.ok
where a.trandate > '1/1/2009'
and b.trandate > '1.1.1999'
They can have separate WHERE statements.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2009 at 1:29 pm
This is a situation I have been wondering about lately, too. There has to be a better way. Here's a simplified example of what I've been doing, including the final example using 2 CTE definitions. Your link showed me how to stack the CTEs. Thanks!
CREATE TABLE GL (account CHAR(3), accType VARCHAR(10), amount MONEY)
GO
INSERT INTO GL VALUES ('100', 'Budget', 5000)
INSERT INTO GL VALUES ('100', 'Expense', 2000)
INSERT INTO GL VALUES ('200', 'Budget', 7000)
INSERT INTO GL VALUES ('200', 'Expense', 1000)
INSERT INTO GL VALUES ('200', 'Expense', 3000)
INSERT INTO GL VALUES ('300', 'Budget', 9000)
GO
/* Desired results:
Account Budget Expense
100 5000.00 2000.00
200 7000.00 4000.00
300 9000.00 0.00
*/
-- Joining the table to itself does not produce correct results.
SELECT a.account AS 'Account', SUM(a.amount) AS 'Budget', SUM(b.amount) AS 'Expense'
FROM GL a FULL OUTER JOIN GL b ON a.account = b.account
WHERE a.accType = 'Budget' AND b.accType = 'Expense'
GROUP BY a.account
ORDER BY a.account
/* Actual _incorrect_ results:
Account Budget Expense
100 5000.00 2000.00
200 14000.00 4000.00
*/
-- Using a CTE with UNION seems overly complicated, but it does produce correct results.
;
WITH sumAcc (account, budget, expense)
AS (
SELECT account, amount, 0
FROM GL
WHERE accType = 'Budget'
UNION
SELECT account, 0, amount
FROM GL
WHERE accType = 'Expense'
)
SELECT account AS 'Account', SUM(budget) AS 'Budget', SUM(expense) AS 'Expense'
FROM sumAcc
GROUP BY account
ORDER BY account
-- Using more than 1 CTE is a bit more elegant, and it does produce correct results.
;
WITH sumBud (account, budget)
AS (
SELECT account, SUM(amount)
FROM GL
WHERE accType = 'Budget'
GROUP BY account
),
sumExp (account, expense)
AS (
SELECT account, SUM(amount)
FROM GL
WHERE accType = 'Expense'
GROUP BY account
)
SELECT sumbud.account AS 'Account', SUM(ISNULL(sumBud.budget,0)) AS 'Budget', SUM(ISNULL(sumExp.expense,0)) AS 'Expense'
FROM sumBud LEFT OUTER JOIN sumExp ON sumBud.account = sumExp.account
GROUP BY sumbud.account
ORDER BY sumbud.account
So it seems that for each separate accType, I would need a separate CTE. Isn't there a better way? I like the idea of joining the table to itself, but I can't seem to get it working.
September 25, 2009 at 1:39 pm
I was thinking the multiple CTEs. The idea being to abstract out those subqueries and make them easier to code without having to create a view. I think it works great, and it's easy to follow the logic.
It's possible that you could write it in a single statement, but I think it's very complicated.
I suspect using the full outer join is what's giving you strange results. What I was suggesting is that you qualify the join for each item you need to get back as a separate table and item in the WHERE clause. Some could be outer joins, but I'd think you'd want left or right joins, not full.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2009 at 2:22 pm
Maybe I'm missing what you're saying about the join type and where clause, but it doesn't seem to matter what type of join I use, the where clause will always exclude the row for the 300 account because it does not have b.accType = 'Expense'. Let me demonstrate:
-- Joining the table to itself does not produce correct results. Here are the records before the grouping occurs.
SELECT a.account AS 'Account', a.accType AS 'a.accType', b.accType AS 'b.accType', a.amount AS 'Budget', b.amount AS 'Expense'
FROM GL a LEFT OUTER JOIN GL b ON a.account = b.account
ORDER BY a.account
/* Results:
Account a.accType b.accType Budget Expense
100 Budget Budget 5000.00 5000.00
100 Budget Expense 5000.00 2000.00
100 Expense Budget 2000.00 5000.00
100 Expense Expense 2000.00 2000.00
200 Budget Budget 7000.00 7000.00
200 Budget Expense 7000.00 1000.00
200 Budget Expense 7000.00 3000.00
200 Expense Budget 1000.00 7000.00
200 Expense Expense 1000.00 1000.00
200 Expense Expense 1000.00 3000.00
200 Expense Budget 3000.00 7000.00
200 Expense Expense 3000.00 1000.00
200 Expense Expense 3000.00 3000.00
300 Budget Budget 9000.00 9000.00
*/
Maybe CTEs are the way to go, but it bugs me that I can't figure out how to use a self join.
September 25, 2009 at 3:24 pm
;With budgets (account, total)
As (Select account
,sum(amount)
From dbo.Gl
Where accType = 'Budget'
Group By account
)
, expenses (account, total)
As (Select account
,sum(amount)
From dbo.Gl
Where accType = 'Expense'
Group By account
)
Select *
From budgets b
Left Join expenses e On e.account = b.account;
I don't think there really is any way to do this with a self-join because of the nature of the table. Because each row records either a budget item or an expense, and they are not related one to one (you can have 0, one or more budget items and 0, one or more expenses) you cannot get correct results because you will end up counting items twice (or not counting, depending upon how it is joined).
So, we need to total up each type first - then join the totals. This can be done using CTE's (as above), or derived tables or views.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2009 at 3:54 pm
Thank you all for your help. I guess this is a good example of why it's nice to work with normalized tables whenever possible. When it's not possible, I have this query in my toolbox. 🙂
September 28, 2009 at 9:38 am
Thank you for your posts and helpful information. From my reading on CTE's, I believe I will not be able to use a CTE as we a running this DB on SQL 2000. I was thinking about this all weekend and have become increasingly frustrated. I have all the data I need, I just cant put it into one query. So close by yet so far away from a single pull to retrieve all of the data in one shot.
Thanks again for all your help. I will continue to look for an solution as there has to be one somewhere.
Bryan
September 28, 2009 at 9:44 am
Ah, you posted in the SQL 2005 forum.
If you're on SQL 2000, your best bet is to create a few views to make the joins easier. I'd recommend that.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 28, 2009 at 10:15 am
You can convert the CTE's into derived tables - are as Steve mentioned, create Views for each one. For example:
SELECT {column list from derived tables}
FROM (SELECT {list of columns you need}
FROM tables
WHERE {criteria}
GROUP BY {if needed} ) AS der1
JOIN (SELECT {list of columns you need}
FROM tables
WHERE {criteria}
GROUP BY {if needed} ) AS der2 ON der2.KeyColumn = der1.KeyColumn
WHERE {additional criteria here, if needed}
The key is to make sure you are joining each derived table to an appropriate column. In your case, you would join on the [Terminal ID]. BTW - you do not need to include a DISTINCT if you are using GROUP BY. And also, you will have to remove the ORDER BY because that won't be allowed (you could put the ORDER BY on the outer query if really needed).
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 29, 2009 at 3:49 am
I suspect using CASE would work better:
DECLARE @StartDate datetime
,@EndDate datetime
SELECT @StartDate = '20090920'
,@EndDate = '20090923'
SELECT [Terminal ID]
,COUNT([Terminal ID]) AS TotalTrans
,COUNT(CASE WHEN Reject = '' AND [Fee Amount] <> '0000000' AND [Fee Mark] <> '' THEN 1 END) AS Surcharge
,COUNT
(
CASE
WHEN
-- check this logic
(
Reject = ''
AND type IN ('SW','SWM','DW','DWM','CW','CWM','FW','FWM','OW','OWM')
)
OR
(
[Credit Amount] <> '00000000000'
AND [Credit Mark] = ''
)
THEN 1
END
) AS Withdrawal
,COUNT
(
CASE
WHEN
-- check this logic
(
Reject = ''
AND type IN ('SD','DD','CDD','OD')
)
OR
(
[Debit Amount] <> '00000000000'
AND [Debit Mark] = ''
)
THEN 1
END
) AS Deposits
,COUNT(CASE WHEN Reject = '' AND type IN ('SBI','DBI','CBI','FBI','OBI') THEN 1 END) AS [# of Bal Inq]
,COUNT(CASE WHEN Reject = ''
AND type IN ('STS','STD','STO','DTS','DTD','DTO','CTS','CTD','CTO','OTS','OTD','OTO')
THEN 1 END) AS [# of XFer]
,COUNT(CASE WHEN Reject <> '' THEN 1 END) AS Denials
FROM dbo.tbl_Export_Record10_Layout
WHERE DTProcessingDate BETWEEN @StartDate AND @EndDate
GROUP BY [Terminal ID]
ORDER BY [Terminal ID]
September 29, 2009 at 6:33 am
Firstly, Thank you all for you help in solving this problem. I have some great idea's that I can use CTE's for on my other 2005 SQL DB's.
Ken,
The code that you have re-organized has worked perfectly. I can't believe I never thought of using a CASE statement. Major kudos for all of your time and effort!
Thank you again!
Bryan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply