SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 HOW TO TURN 6 QUERIES INTO 1?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  21:34:02  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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 - 04/15/2007 :  21:35:41  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
THE WORDS WITH CUBE WERE A MISTAKE. THANX
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  21:41:12  Show Profile  Reply with Quote
What is the relationship between table TXNHIS and CODFIL ? is it one to one ?


KH

Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  21:43:44  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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.
Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  21:48:10  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/15/2007 :  21:57:50  Show Profile  Reply with Quote
Is your Caps Lock key stuck?


CODO ERGO SUM
Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:01:28  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
No its not. but thanks for your help
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  22:02:50  Show Profile  Reply with Quote

	
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



KH

Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:02:57  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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
Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:04:35  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
thank you khtan

I recieved this message.

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


Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  22:07:55  Show Profile  Reply with Quote
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



KH


Edited by - khtan on 04/15/2007 22:08:39
Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:14:30  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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'.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  22:16:55  Show Profile  Reply with Quote
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



KH

Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:19:34  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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

Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:22:42  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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.
Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:24:59  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  22:28:14  Show Profile  Reply with Quote

 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

The C is the derived table name, it is not referring to CODFIL. Try the last query that i posted


KH

Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  22:37:42  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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.










Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  22:45:19  Show Profile  Reply with Quote
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



KH

Go to Top of Page

CRYSTALM
Starting Member

16 Posts

Posted - 04/15/2007 :  23:00:59  Show Profile  Send CRYSTALM an AOL message  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/15/2007 :  23:05:33  Show Profile  Reply with Quote
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



KH

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000