HOW TO TURN 6 QUERIES INTO 1? - 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 2005 Forums

Transact-SQL (2005)

HOW TO TURN 6 QUERIES INTO 1?

Author

Topic

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 21:34:02

I literally pick up the book sql for mere mortals friday night.

I have these 6 queries I would like to turn into one result data set.

here are my queries:

Print '-----------------------------------------------------'
Print '2006 DAILY TRANSACTION IMPORTS'
Print 'Provided by: Crystal McAdams'
Print '-----------------------------------------------------'

SELECT TXN_DAT AS 'TRANSACTION DATE', SUM (TXN_AMT) AS 'TOTAL DAILY AMOUNT'
FROM TXNHIS
WHERE TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
GROUP BY TXN_DAT
ORDER BY TXN_DAT ASC
GO

SELECT TXN_DAT AS 'TRANSACTION DATE', SUM (TXNHIS.TXN_AMT) AS 'DAILY INCOMING AMOUNT'
FROM TXNHIS
INNER JOIN CODFIL ON TXNHIS.TXN_COD = CODFIL.COD_COD
WHERE CODFIL.COD_TYP = 'I ' AND TXN_DAT BETWEEN '01/01/2006' AND '12/13/2006'
GROUP BY TXN_DAT
ORDER BY TXN_DAT ASC
GO

SELECT TXN_DAT AS 'TRANSACTION DATE', SUM( TXNHIS.TXN_AMT) AS 'DAILY OUTGOING AMOUNT'
FROM TXNHIS
INNER JOIN CODFIL ON TXNHIS.TXN_COD = CODFIL.COD_COD
WHERE CODFIL.COD_TYP = 'O' AND TXN_DAT BETWEEN '01/01/2006' AND '12/13/2006'
GROUP BY TXN_DAT
ORDER BY TXN_DAT ASC
GO

SELECT TXNHIS.TXN_DAT AS 'DATE', COUNT (TXN_DAT) AS 'SUM OF DAILY TRANSACTIONS'
FROM TXNHIS
WHERE TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
GROUP BY TXN_DAT
ORDER BY TXN_DAT ASC
GO

SELECT TXNHIS.TXN_DAT AS 'DATE', COUNT (TXN_DAT) AS 'SUM OF DAILY INCOMING TRANSACTIONS'
FROM TXNHIS
INNER JOIN CODFIL ON TXNHIS.TXN_COD = CODFIL.COD_COD
WHERE CODFIL.COD_TYP = 'I' AND TXN_DAT BETWEEN '01/01/2006' AND '12/13/2006'
GROUP BY TXN_DAT
ORDER BY TXN_DAT ASC
GO

SELECT TXNHIS.TXN_DAT AS 'DATE', COUNT (TXN_DAT) AS 'SUM OF DAILY OUTGOING TRANSACTIONS'
FROM TXNHIS
INNER JOIN CODFIL ON TXNHIS.TXN_COD = CODFIL.COD_COD
WHERE CODFIL.COD_TYP = 'O' AND TXN_DAT BETWEEN '01/01/2006' AND '12/13/2006'
GROUP BY TXN_DAT
ORDER BY TXN_DAT ASC
GO

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

I am trying to add the other 4 queries as subqueries in the select statement but am failing.

here is what i did so far (but the third column gives me the same aggregate total for all of the rows.)

SELECT TXN_DAT AS 'TRANSACTION DATE', SUM (TXN_AMT) AS 'TOTAL DAILY AMOUNT', (SELECT TXNHIS.TXN_AMT FROM TXNHIS
INNER JOIN CODFIL ON TXNHIS.TXN_COD = CODFIL.COD_COD WHERE CODFIL.COD_TYP = 'I') AS 'DAILY INCOMING AMOUNT'
FROM TXNHIS
WHERE TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
GROUP BY TXN_DAT, TXN_DAT
WITH CUBE
ORDER BY TXN_DAT ASC
GO

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

thanx

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 21:35:41

THE WORDS WITH CUBE WERE A MISTAKE. THANX

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 21:41:12

What is the relationship between table TXNHIS and CODFIL ? is it one to one ?

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 21:43:44

TXNHIS IS A TABLE OF BANK ACCOUNT TRANSACTIONS. EACH ROW IN THE TXNHIS HAS A TXN_COD (TRANSACTION CODE) AND THAT CODE IS THE MANY AND THE CODFIL IS THE ONE.

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 21:48:10

I WANT TO HAVE ALL TOTAL NUMBER OF TRANSACTIONS AND SUMS FOR EACH DAY
LIKE

DATE
TOTAL DAILY MONEY MOVED
TOTAL AMOUNT OF DAILY CREDITS
TOTAL AMOUNT OF DAILY DEBITS
TOTAL NUMBER OF DAILY CREDIT TRANSATIONS
TOTAL NUMBER OF DAILY DEBIT TRANSACTIONS

AND THE ONLY WAY TO SEGREGATE DEBITS FROM CREDITS IS THEIR CODFIL.COD_TYP
EITHER THE COD_TYP IS I FOR INCOING OR O FOR OUTGOING .

THANK U SOOOOOOOOOOOOOOOOOOOO MUCH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-15 : 21:57:50

Is your Caps Lock key stuck?

CODO ERGO SUM

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:01:28

No its not. but thanks for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 22:02:50

[code]

SELECT T.TXN_DAT,
T.[TOTAL DAILY AMOUNT],
T.[SUM OF DAILY TRANSACTIONS],
C.[DAILY INCOMING AMOUNT],
C.[DAILY OUTGOING AMOUNT],
C.[SUM OF DAILY INCOMING TRANSACTIONS],
C.[SUM OF DAILY OUTGOING TRANSACTIONS]
FROM (
SELECT T.TXN_DAT,
SUM (T.TXN_AMT) AS [TOTAL DAILY AMOUNT],
COUNT (TXN_DAT) AS [SUM OF DAILY TRANSACTIONS]
FROM TXNHIS AS T
GROUP BY T.TXN_DAT
) T
INNER JOIN
(
SELECT T.TXN_DAT,
SUM(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_AMT ELSE 0 END) AS [DAILY INCOMING AMOUNT],
SUM(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_AMT ELSE 0 END) AS [DAILY OUTGOING AMOUNT],
COUNT(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_DAT END) AS [SUM OF DAILY INCOMING TRANSACTIONS]
COUNT(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_DAT END) AS [SUM OF DAILY OUTGOING TRANSACTIONS]
FROM TXNHIS AS T INNER JOIN CODFIL AS C
ON T.TXN_COD = C.COD_COD
GROUP BY T.TXN_DAT
) C
ON T.TXN_COD = C.COD_COD
WHERE T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC
GO
[/code]

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:02:57

i guess i could always run all the scripts tomorrow as i have created them. but i was really trying to get them all into one dataset

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:04:35

thank you khtan

I recieved this message.

Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near 'COUNT'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 22:07:55

add comma after [SUM OF DAILY INCOMING TRANSACTIONS]


SELECT	T.TXN_DAT, 
	T.[TOTAL DAILY AMOUNT],
	T.[SUM OF DAILY TRANSACTIONS],
	C.[DAILY INCOMING AMOUNT],
	C.[DAILY OUTGOING AMOUNT],
	C.[SUM OF DAILY INCOMING TRANSACTIONS],
	C.[SUM OF DAILY OUTGOING TRANSACTIONS]
FROM 	(
		SELECT	T.TXN_DAT,
			SUM (T.TXN_AMT) AS [TOTAL DAILY AMOUNT],
			COUNT (TXN_DAT) AS [SUM OF DAILY TRANSACTIONS]
		FROM	TXNHIS AS T
		GROUP BY T.TXN_DAT
	) T
INNER JOIN
	(
		SELECT	T.TXN_DAT, 
			SUM(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_AMT ELSE 0 END) AS [DAILY INCOMING AMOUNT],
			SUM(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_AMT ELSE 0 END) AS [DAILY OUTGOING AMOUNT],
			COUNT(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_DAT END) 	 AS [SUM OF DAILY INCOMING TRANSACTIONS],
			COUNT(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_DAT END) 	 AS [SUM OF DAILY OUTGOING TRANSACTIONS]
		FROM	TXNHIS AS T INNER JOIN CODFIL AS C
			ON	T.TXN_COD	= C.COD_COD
		GROUP BY T.TXN_DAT
	) C
	ON	T.TXN_COD	= C.COD_COD
WHERE 	T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:14:30

KH

I know my columns exist in my table. But, now I get the following error message:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'TXN_COD'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'COD_COD'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 22:16:55

sorry... cut & paste error


SELECT	T.TXN_DAT, 
	T.[TOTAL DAILY AMOUNT],
	T.[SUM OF DAILY TRANSACTIONS],
	C.[DAILY INCOMING AMOUNT],
	C.[DAILY OUTGOING AMOUNT],
	C.[SUM OF DAILY INCOMING TRANSACTIONS],
	C.[SUM OF DAILY OUTGOING TRANSACTIONS]
FROM 	(
		SELECT	T.TXN_DAT,
			SUM (T.TXN_AMT) AS [TOTAL DAILY AMOUNT],
			COUNT (TXN_DAT) AS [SUM OF DAILY TRANSACTIONS]
		FROM	TXNHIS AS T
		GROUP BY T.TXN_DAT
	) T
INNER JOIN
	(
		SELECT	T.TXN_DAT, 
			SUM(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_AMT ELSE 0 END) AS [DAILY INCOMING AMOUNT],
			SUM(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_AMT ELSE 0 END) AS [DAILY OUTGOING AMOUNT],
			COUNT(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_DAT END) 	 AS [SUM OF DAILY INCOMING TRANSACTIONS],
			COUNT(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_DAT END) 	 AS [SUM OF DAILY OUTGOING TRANSACTIONS]
		FROM	TXNHIS AS T INNER JOIN CODFIL AS C
			ON	T.TXN_COD	= C.COD_COD
		GROUP BY T.TXN_DAT
	) C
	ON	T.TXN_DAT	= C.TXN_DAT
WHERE 	T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:19:34

i was about to say
ON T.TXN_COD = C.COD_COD
you named table.table 2 times.

I just saw your reply. let me plug it in.

thanks

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:22:42

TXNHIS Table has a field for transaction date
txnhis.txn_dat

CODFIL - the transaction code list (primary key for transaction codes)
this table does not have a column for dates.

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:24:59

KH

I changed this in the query

ON T.TXN_COD = C.COD_COD
GROUP BY T.TXN_DAT
) C
ON T.TXN_COD = C.COD_COD
WHERE T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC

but I am receiving this message:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'TXN_COD'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'COD_COD'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 22:28:14

[code]
ON T.TXN_COD = C.COD_COD
GROUP BY T.TXN_DAT
) C
ON T.TXN_COD = C.COD_COD
WHERE T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC
[/code]
The C is the derived table name, it is not referring to CODFIL. Try the last query that i posted

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 22:37:42

KH
Thank you soooooo much. If you ever need help with designing graphics. Let me know. Thats what I do. I decided to try learning sql. Is there any book you would recommend? What do u call the method u used to help me? So i can look it up? (embedded statements? sub query?)

once again thank you so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 22:45:19

quote:
designing graphics

may be you can contribute to this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81829

quote:
Is there any book you would recommend?

There are lots of good book listed here http://www.sqlteam.com/store.asp.

quote:
What do u call the method u used to help me?

That is call derived table.
This is a derived table named T, out of a query. Similarly for the other one C.


        (
		SELECT	T.TXN_DAT,
			SUM (T.TXN_AMT) AS [TOTAL DAILY AMOUNT],
			COUNT (TXN_DAT) AS [SUM OF DAILY TRANSACTIONS]
		FROM	TXNHIS AS T
		GROUP BY T.TXN_DAT
	) T

CRYSTALM
Starting Member

16 Posts

Posted - 2007-04-15 : 23:00:59

I was playing with the code you made. I tried making 3 inner joins. I have to keep working at it.

SELECT T.TXN_DAT,
T.[TOTAL DAILY AMOUNT],
T.[SUM OF DAILY TRANSACTIONS],
C.[DAILY INCOMING AMOUNT],
C.[DAILY OUTGOING AMOUNT],
C.[SUM OF DAILY INCOMING TRANSACTIONS],
C.[SUM OF DAILY OUTGOING TRANSACTIONS],
A.[DAILY INCOMING AMOUNT FOR BUSINESS ACCOUNTS],
A.[DAILY OUTGOING AMOUNT FOR BUSINESS ACCOUNTS],
A.[SUM OF DAILY INCOMING TRANSACTIONS FOR BUSINESS ACCOUNTS],
A.[SUM OF DAILY OUTGOING TRANSACTIONS FOR BUSINESS ACCOUNTS]
FROM ((
SELECT T.TXN_DAT,
SUM (T.TXN_AMT) AS [TOTAL DAILY AMOUNT],
COUNT (TXN_DAT) AS [SUM OF DAILY TRANSACTIONS]
FROM TXNHIS AS T
GROUP BY T.TXN_DAT
) T
INNER JOIN
(
SELECT T.TXN_DAT,
SUM(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_AMT ELSE 0 END) AS [DAILY INCOMING AMOUNT],
SUM(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_AMT ELSE 0 END) AS [DAILY OUTGOING AMOUNT],
COUNT(CASE WHEN C.COD_TYP = 'I' THEN T.TXN_DAT END) AS [SUM OF DAILY INCOMING TRANSACTIONS],
COUNT(CASE WHEN C.COD_TYP = 'O' THEN T.TXN_DAT END) AS [SUM OF DAILY OUTGOING TRANSACTIONS]
FROM TXNHIS AS T INNER JOIN CODFIL AS C
ON T.TXN_COD = C.COD_COD
GROUP BY T.TXN_DAT
) C)

INNER JOIN
(
SELECT T.TXN_DAT,
SUM(CASE WHEN C.COD_TYP = 'I' AND A.ACC_TYP = 'BUS' THEN T.TXN_AMT ELSE 0 END) AS [DAILY INCOMING AMOUNT FOR BUSINESS ACCOUNTS],
SUM(CASE WHEN C.COD_TYP = 'O' AND A.ACC_TYP = 'BUS' THEN T.TXN_AMT ELSE 0 END) AS [DAILY OUTGOING AMOUNT FOR BUSINESS ACCOUNTS],
COUNT(CASE WHEN C.COD_TYP = 'I' AND A.ACC_TYP = 'BUS' THEN T.TXN_DAT END) AS [SUM OF DAILY INCOMING TRANSACTIONS FOR BUSIENSS ACCOUNTS],
COUNT(CASE WHEN C.COD_TYP = 'O' AND A.ACC_TYP = 'BUS' THEN T.TXN_DAT END) AS [SUM OF DAILY OUTGOING TRANSACTIONS FOR BUSINESS ACCOUNTS]
FROM TXNHIS AS T INNER JOIN ACCFIL AS A
ON T.TXN_ACC = A.ACC_NUM
GROUP BY T.TXN_DAT
) A

ON T.TXN_DAT = A.TXN_DAT
WHERE T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 23:05:33

you can just add the INNER JOIN directly to the query. You don't have to create another derived table


SELECT	. . . 
FROM 	(
                <existing 1st query here>
	) T
INNER JOIN
	(
                <existing 2nd query here> 
	) C
	ON	T.TXN_DAT	= C.TXN_DAT
INNER JOIN
        (
                 <your new query here>
        ) A
        ON      T.TXN_DAT       = A.TXN_DAT
WHERE 	T.TXN_DAT BETWEEN '01/01/2006' AND '12/31/2006'
ORDER BY T.TXN_DAT ASC

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources