| Author |
 Topic  |
|
CRYSTALM
Starting Member
16 Posts |
 Posted - 04/15/2007 : 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 - 04/15/2007 : 21:35:41
|
| THE WORDS WITH CUBE WERE A MISTAKE. THANX |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/15/2007 : 21:41:12
|
What is the relationship between table TXNHIS and CODFIL ? is it one to one ?
KH
|
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 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 - 04/15/2007 : 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)
USA
6997 Posts |
Posted - 04/15/2007 : 21:57:50
|
Is your Caps Lock key stuck?
CODO ERGO SUM |
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 22:01:28
|
| No its not. but thanks for your help |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/15/2007 : 22:02:50
|
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
|
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 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 - 04/15/2007 : 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)
Singapore
16746 Posts |
Posted - 04/15/2007 : 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
KH
|
Edited by - khtan on 04/15/2007 22:08:39 |
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 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)
Singapore
16746 Posts |
Posted - 04/15/2007 : 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
KH
|
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 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 - 04/15/2007 : 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 - 04/15/2007 : 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)
Singapore
16746 Posts |
Posted - 04/15/2007 : 22:28:14
|
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
|
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 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)
Singapore
16746 Posts |
Posted - 04/15/2007 : 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
KH
|
|
|
|
CRYSTALM
Starting Member
16 Posts |
Posted - 04/15/2007 : 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)
Singapore
16746 Posts |
Posted - 04/15/2007 : 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
KH
|
|
|
|
Topic |
|
HOW TO TURN 6 QUERIES INTO 1?
