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
 General SQL Server Forums
 New to SQL Server Programming
 count transaction by month
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

dayna
Starting Member

Philippines
14 Posts

Posted - 12/03/2012 :  20:34:08  Show Profile  Reply with Quote
I am trying to get a count of transactions from a table,all i want is view only the month and the year that the transaction correspond in.. but this code show all the months and year of every transaction.

here is the code:

SELECT TOP 100 PERCENT MONTH(dttTransactionDate) AS TRANSMONTH, YEAR(dttTransactionDate) AS TRANSYEAR, COUNT(*) AS NoOfTransactionMonthly,
COUNT(DISTINCT dttTransactionSymbol) AS TRANSACTIONSYMBOL, dttBranchCode, dttProductCode, dttSequence, dttCheckDigit,
datDepositorName AS DEPOSITORNAME, datDepositorAddress AS DEPOSITORADDRESS, dttErrorCorrect AS ErrorCorrect
FROM dbo.DEPOSITTRANSACTION
GROUP BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttBranchCode, dttProductCode, dttSequence, dttCheckDigit, datDepositorName, datDepositorAddress,
dttErrorCorrect
HAVING (YEAR(dttTransactionDate) <> 2011)
ORDER BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttSequence


help me pls... im using sql server 2000

dcometa

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 12/03/2012 :  22:55:31  Show Profile  Reply with Quote
quote:
the month and the year that the transaction correspond in

what do you mean by that ?


KH
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/03/2012 :  23:30:24  Show Profile  Reply with Quote

SELECT TOP 100 PERCENT MONTH(dttTransactionDate) AS TRANSMONTH, YEAR(dttTransactionDate) AS TRANSYEAR, COUNT(*) AS NoOfTransactionMonthly, 
COUNT(DISTINCT dttTransactionSymbol) AS TRANSACTIONSYMBOL , dttBranchCode, dttProductCode, dttSequence, dttCheckDigit, 
datDepositorName AS DEPOSITORNAME, datDepositorAddress AS DEPOSITORADDRESS, dttErrorCorrect AS ErrorCorrect
FROM dbo.DEPOSITTRANSACTION
GROUP BY MONTH(dttTransactionDate), YEAR(dttTransactionDate) , dttBranchCode, dttProductCode, dttSequence, dttCheckDigit, datDepositorName, datDepositorAddress, 
dttErrorCorrect
HAVING (YEAR(dttTransactionDate) <> 2011)
ORDER BY MONTH(dttTransactionDate), YEAR(dttTransactionDate)


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2012 :  01:09:33  Show Profile  Reply with Quote
why do you need top 100 percent here? is this used inside some subquery?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/04/2012 :  20:35:55  Show Profile  Reply with Quote
i used the top 100 percent in order to view all accounts together with transactions for each depositor. the script i posted while ago is running already, but my problem is that it shows all the months and the year of every depositor transactions. i want only to view the the latest month and year of every transaction of each depositors.

again the script look like this:

SELECT TOP 100 PERCENT MONTH(dttTransactionDate) AS TRANSMONTH, YEAR(dttTransactionDate) AS TRANSYEAR, COUNT(*) AS NoOfTransactionMonthly,
COUNT(DISTINCT dttTransactionSymbol) AS TRANSACTIONSYMBOL, dttBranchCode, dttProductCode, dttSequence

FROM dbo.DEPOSITTRANSACTION
GROUP BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttBranchCode, dttProductCode, dttSequence
HAVING (YEAR(dttTransactionDate) <> 2011)
ORDER BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttSequence


the result:

TransMonth TransYear NoOfTransaction TransSymbol dttBranchCode dttProductcode dttsequence
11 2011 6 cd 10005 001 000032
12 2011 3 cd 10005 001 000032
1 2012 4 cd 10005 001 000032
2 2012 6 cw 10005 001 000032
3 2012 5 cd 10005 001 000032
4 2012 6 cd 10005 001 000032
5 2012 8 cw 10005 001 000032
6 2012 9 cw 10005 001 000032
7 2012 3 cd 10005 001 000032
8 2012 1 cd 10005 001 000032
9 2012 9 cd 10005 001 000032
10 2012 2 cw 10005 001 000032
11 2012 4 cd 10005 001 000032
12 2012 2 cd 10005 001 000032


for this result i want only to view the month and the year where in the depositor last transaction..

example result above i want only:

TransMonth TransYear NoOfTransaction TransSymbol dttBranchCode dttProductcode dttsequence

12 2012 2 cd 10005 001 000032

pls help me asap..
im using sql server 2000.
sorry for my wrong english.tnx for the help..







dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/05/2012 :  00:57:26  Show Profile  Reply with Quote
SELECT TOP 1..........
FROM.......
ORDER BY YEAR(dttTransactionDate) DESC, MONTH(dttTransactionDate) DESC, dttSequence



--
Chandu
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/05/2012 :  03:46:00  Show Profile  Reply with Quote
I am trying to get a count of transactions from a table, but I can't seem to get sql to get me to show all of the months instead of only the months and the year that the transactions occured in.

Here is the query:

SELECT MONTH(dttTransactionDate) AS TRANSMONTH, YEAR(dttTransactionDate) AS TRANSYEAR, COUNT(*) AS NoOfTransactionMonthly,
COUNT(DISTINCT dttTransactionSymbol) AS TRANSACTIONSYMBOL, dttBranchCode, dttProductCode, dttSequence
FROM dbo.DEPOSITTRANSACTION
GROUP BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttBranchCode, dttProductCode, dttSequence
HAVING (YEAR(dttTransactionDate) <> 2011)
ORDER BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttSequence

The query returns the following result set:

| Transmonth | Transyear | Transnumber || Transsymbol | dttbranchcode | dttproductcode |dttsequence
|-----------|------------|----------| |----------- |------------ |---------- |
| 12 | 2011 | 5 | | 2 | 1 | 001 | 00001
| 1 | 2012 | 1 | | 1 | 1 | 001 | 00002
| 2 | 2012 | 2 | | 2 | 1 | 001 | 00001
| 2 | 2012 | 3 | | 2 | 1 | 001 | 00002
| 3 | 2012 | 2 | | 2 | 1 | 001 | 00002

and so forth.... but the result im trying to look for is not that one..

here is i want to show :
from the result above, in dttsequence column (00001) the latest transaction there is feb. 2012 and the old transaction is dec. 2011.. sequence (00002) the latest transaction there is march 2012 and the old transaction is jan. 2012..

here is the result that i want to show on my table:

| Transmonth | Transyear | Transnumber || Transsymbol | dttbranchcode | dttproductcode |dttsequence

| 2 | 2012 | 2 | 2 | 1 | 001 | 00001
| 3 | 2012 | 2 | 2 | 1 | 001 | 00002


tnx for helping again...




dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/05/2012 :  05:16:57  Show Profile  Reply with Quote
hi dayna,

I am not getting exact solution for you. Can you post only actual table data and the expected output?

NoOfTransactionMonthly --> Total number of transactions per month regardless of year ( or with respect to year also ? )
TRANSACTIONSYMBOL --> u r counting different trans symbols here
and one more column is RecentTransactionDate for each dttsequence.. right??

If you post actual data and expected result, we can easily provide the solution

--
Chandu
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/05/2012 :  20:26:59  Show Profile  Reply with Quote
here is the complete table..

@TABLE: FROM DMMDEPOSITTRANSACTION
| DTTBRANCHCODE | DTTPRODUCTCODE| DTTSEQUENCE || DTTCHECKDIGIT | DTTTRANSACTIONDATE |
| 10005 | 001 | 00001 |01 |12/23/2011 |
| 10005 | 001 | 00001 |01 |12/24/2011 |
| 10005 | 001 | 00001 |01 |12/26/2011 |
| 10005 | 001 | 00001 |01 |12/27/2011 |
| 10005 | 001 | 00001 |01 |12/28/2011 |
| 10005 | 001 | 00002 |02 |01/3/2012 |
| 10005 | 001 | 00001 |01 |02/6/2012 |
| 10005 | 001 | 00001 |01 |02/7/2012 |
| 10005 | 001 | 00002 |02 |02/8/2012 |
| 10005 | 001 | 00002 |02 |02/9/2012 |
| 10005 | 001 | 00002 |02 |02/10/2012 |
| 10005 | 001 | 00002 |02 |03/12/2012 |
| 10005 | 001 | 00002 |02 |03/13/2012 |

| DTTTRANSACTIONSYMBOL|
| CD |
| CW |
| CD |
| CD |
| CW |
| CW |
| CD |
| CD |
| CD |
| CW |
| CD |
| CD |
| CD |


HERE IS MY VIEW TABLE (IM CALLING THE DTTTRANSACTION TABLE):

SELECT MONTH(dttTransactionDate) AS TRANSMONTH, YEAR(dttTransactionDate) AS TRANSYEAR, COUNT(*) AS NoOfTransactionMonthly, COUNT(DISTINCT dttTransactionSymbol) AS TRANSACTIONSYMBOL, dttBranchCode, dttProductCode, dttSequence, dttCheckDigit
FROM dbo.dmmDepositTransaction
GROUP BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttBranchCode, dttProductCode, dttSequence, dttCheckDigit
HAVING (YEAR(dttTransactionDate) <> 2011)
ORDER BY MONTH(dttTransactionDate), YEAR(dttTransactionDate), dttSequence

The query returns the following result set:

| Transmonth | Transyear | Transnumber || Transsymbol | dttbranchcode | dttproductcode |dttsequence|dttcheckdigit
|-----------|------------|----------| |----------- |------------ |---------- |
| 12 | 2011 | 5 | | 2 | 1 | 001 | 00001|01
| 1 | 2012 | 1 | | 1 | 1 | 001 | 00002|02
| 2 | 2012 | 2 | | 2 | 1 | 001 | 00001|01
| 2 | 2012 | 3 | | 2 | 1 | 001 | 00002|02
| 3 | 2012 | 2 | | 2 | 1 | 001 | 00002|02

and so forth.... but the result im trying to look for is not that one..

here is i want to show :
from the result above, in dttsequence column (00001) the latest transaction there is feb. 2012 and the old transaction is dec. 2011.. sequence (00002) the latest transaction there is march 2012 and the old transaction is jan. 2012..

here is the result that i want to show on my views table:

| Transmonth | Transyear | Transnumber || Transsymbol | dttbranchcode | dttproductcode |dttsequence|dttcheckdigit

| 2 | 2012 | 2 | 2 | 1 | 001 | 00001|01
| 3 | 2012 | 2 | 2 | 1 | 001 | 00002|02


NoOfTransactionMonthly ---> total number of transaction per month with the corresponding recent year

transactionsymbol --> yes im counting differenct trans symbols(cd and cw)for each dttsequence.

note: counting of nooftransactionmonthly and transactionsymbol is every dttsequence.

tnx...








dcometa
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/06/2012 :  00:58:11  Show Profile  Reply with Quote

SELECT YEAR(DTTTRANSACTIONDATE) AS TransYear,
MONTH(DTTTRANSACTIONDATE) AS TransMonth,
COUNT(DTTTRANSACTIONDATE) AS Transnumber,
COUNT(DISTINCT DTTTRANSACTIONSYMBOL) AS Transymbol,
COUNT(DTTBRANCHCODE) AS dttbranchcode,
DTTPRODUCTCODE
FROM
(
SELECT *,MAX(DTTTRANSACTIONDATE) OVER (PARTITION BY DTTSEQUENCE ) AS MaxDate
FROM dbo.dmmDepositTransaction
)t
WHERE DTTTRANSACTIONDATE > = DATEADD(mm,DATEDIFF(mm,0,MaxDate),0)
AND DTTTRANSACTIONDATE < DATEADD(mm,DATEDIFF(mm,0,MaxDate)+1,0)
GROUP BY YEAR(DTTTRANSACTIONDATE),MONTH(DTTTRANSACTIONDATE),DTTPRODUCTCODE



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/06/2012 :  01:14:02  Show Profile  Reply with Quote
Alternate is:

DECLARE @DMMDEPOSITTRANSACTION TABLE(DTTBRANCHCODE int, DTTPRODUCTCODE varchar(10),  DTTSEQUENCE varchar(10), DTTCHECKDIGIT varchar(10), DTTTRANSACTIONDATE date,  DTTTRANSACTIONSYMBOL varchar(10))
INSERT INTO @DMMDEPOSITTRANSACTION 
SELECT '10005', '001', '00001', '01', '12/23/2011', 'CD' union all
SELECT '10005', '001', '00001', '01', '12/24/2011', 'CW' union all
SELECT '10005', '001', '00001', '01', '12/26/2011', 'CD' union all
SELECT '10005', '001', '00001', '01', '12/27/2011', 'CD' union all
SELECT '10005', '001', '00001', '01', '12/28/2011', 'CW' union all
SELECT '10005', '001', '00002', '02', '01/3/2012', 'CW' union all
SELECT '10005', '001', '00001', '01', '02/6/2012', 'CD' union all
SELECT '10005', '001', '00001', '01', '02/7/2012', 'CD' union all
SELECT '10005', '001', '00002', '02', '02/8/2012', 'CD' union all
SELECT '10005', '001', '00002', '02', '02/9/2012', 'CW' union all
SELECT '10005', '001', '00002', '02', '02/10/2012', 'CD' union all
SELECT '10005', '001', '00002', '02', '03/12/2012', 'CD' union all
SELECT '10005', '001', '00002', '02', '03/13/2012', 'CD' 

;WITH CTE 
AS
(
	SELECT  DTTSEQUENCE, MAX(DTTTRANSACTIONDATE) RecentTransDate, COUNT( distinct DTTTRANSACTIONSYMBOL) noOfTransSymls
	FROM @DMMDEPOSITTRANSACTION
	GROUP BY DTTSEQUENCE
)
SELECT distinct MONTH(RecentTransDate) TranMonth, YEAR(RecentTransDate) TransYear,
COUNT(d.DTTTRANSACTIONDATE ) OVER(Partition by c.DTTSEQUENCE) noOfTrans,
noOfTransSymls, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, c.DTTSEQUENCE, d.DTTCHECKDIGIT 
FROM @DMMDEPOSITTRANSACTION d 
JOIN CTE c 
ON CONVERT( VARCHAR(7), d.DTTTRANSACTIONDATE, 102) = CONVERT( VARCHAR(7), c.RecentTransDate, 102) AND c.DTTSEQUENCE = d.DTTSEQUENCE




--
Chandu

Edited by - bandi on 12/06/2012 01:26:42
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/06/2012 :  03:31:35  Show Profile  Reply with Quote
is that query applicable with sql server 2000? because I've tried to run the query it will come up an error.

thank you for the help..


---im using ms sql server 2000---

dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/06/2012 :  04:22:02  Show Profile  Reply with Quote

-- In mssql 2000
SELECT MONTH(RecentTransDate) TranMonth, YEAR(RecentTransDate) TransYear, noOfTransSymls, noOfTrans, DTTPRODUCTCODE, DTTBRANCHCODE, DTTSEQUENCE, DTTCHECKDIGIT
FROM ( SELECT distinct noOfTransSymls, COUNT(*) noOfTrans, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, c.DTTSEQUENCE, d.DTTCHECKDIGIT, RecentTransDate 
		FROM @DMMDEPOSITTRANSACTION d 
		JOIN (SELECT  DTTSEQUENCE, MAX(DTTTRANSACTIONDATE) RecentTransDate,COUNT( distinct DTTTRANSACTIONSYMBOL) noOfTransSymls
				FROM @DMMDEPOSITTRANSACTION
				GROUP BY DTTSEQUENCE
			  ) c  ON CONVERT( VARCHAR(7), d.DTTTRANSACTIONDATE, 102) = CONVERT( VARCHAR(7), c.RecentTransDate, 102) 
			    AND c.DTTSEQUENCE = d.DTTSEQUENCE
		GROUP BY c.DTTSEQUENCE, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, d.DTTCHECKDIGIT, RecentTransDate, noOfTransSymls
	 )t


--
Chandu
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/09/2012 :  20:48:42  Show Profile  Reply with Quote
thank you for the script chandu it works perpectly..

---another question related to the topic above--
Is it possible that in dmmDepositTransaction Table can i automatically count the transaction by month instead of adding another table in views which is the table t (basis is the query above)?
if that's the case how can i add it into my script? because base on the query above i have encounter an error if i will call it into my vb6 code, the data is not accumulate counting, the difference has lack of 2 transaction every month. maybe because im using views table even though the views table t is exactly counting the transaction but if i will call it to my vb code then run the program itself it lack of 2 transactions.

here is the table dmmdeposittransaction and how will i add the transaction count by month:

@TABLE: FROM DMMDEPOSITTRANSACTION
| DTTBRANCHCODE | DTTPRODUCTCODE| DTTSEQUENCE || DTTCHECKDIGIT | DTTTRANSACTIONDATE |
| 10005 | 001 | 00001 |01 |12/23/2011 |
| 10005 | 001 | 00001 |01 |12/24/2011 |
| 10005 | 001 | 00001 |01 |12/26/2011 |
| 10005 | 001 | 00001 |01 |12/27/2011 |
| 10005 | 001 | 00001 |01 |12/28/2011 |
| 10005 | 001 | 00002 |02 |01/3/2012 |
| 10005 | 001 | 00001 |01 |02/6/2012 |
| 10005 | 001 | 00001 |01 |02/7/2012 |
| 10005 | 001 | 00002 |02 |02/8/2012 |
| 10005 | 001 | 00002 |02 |02/9/2012 |
| 10005 | 001 | 00002 |02 |02/10/2012 |
| 10005 | 001 | 00002 |02 |03/12/2012 |
| 10005 | 001 | 00002 |02 |03/13/2012 |

| DTTTRANSACTIONSYMBOL|
| CD |
| CW |
| CD |
| CD |
| CW |
| CW |
| CD |
| CD |
| CD |
| CW |
| CD |
| CD |
| CD |

pls help me..i really need it..pls.pls......



dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/10/2012 :  00:00:53  Show Profile  Reply with Quote
Just create this view..... What is the problem with t query?

CREATE VIEW myView 
As
SELECT MONTH(RecentTransDate) TranMonth, YEAR(RecentTransDate) TransYear, noOfTransSymls, noOfTrans, DTTPRODUCTCODE, DTTBRANCHCODE, DTTSEQUENCE, DTTCHECKDIGIT
FROM ( SELECT distinct noOfTransSymls, COUNT(*) noOfTrans, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, c.DTTSEQUENCE, d.DTTCHECKDIGIT, RecentTransDate 
		FROM DMMDEPOSITTRANSACTION d 
		JOIN (SELECT  DTTSEQUENCE, MAX(DTTTRANSACTIONDATE) RecentTransDate,COUNT( distinct DTTTRANSACTIONSYMBOL) noOfTransSymls
				FROM DMMDEPOSITTRANSACTION
				GROUP BY DTTSEQUENCE
			  ) c  ON CONVERT( VARCHAR(7), d.DTTTRANSACTIONDATE, 102) = CONVERT( VARCHAR(7), c.RecentTransDate, 102) 
			    AND c.DTTSEQUENCE = d.DTTSEQUENCE
		GROUP BY c.DTTSEQUENCE, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, d.DTTCHECKDIGIT, RecentTransDate, noOfTransSymls
	 )t

then see results like this:
SELECT * FROM myView


--
Chandu
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/10/2012 :  00:48:38  Show Profile  Reply with Quote
i created already the view and it works perfectly. i have no problem with that..

but my question is that, is it possible to count the transaction without creating view.because i have already a table which is dmmdepositetransaction from that table itself i will count transaction monthly.
from the above sample it will come up that i have two tables, im calling the dmmdeposittransaction to my view table and name it as table t.



dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/10/2012 :  01:29:30  Show Profile  Reply with Quote
>> is it possible to count the transaction without creating view?

Yes no need of creating view.

U can use my query directly
SELECT MONTH(RecentTransDate) TranMonth, YEAR(RecentTransDate) TransYear, noOfTransSymls, noOfTrans, DTTPRODUCTCODE, DTTBRANCHCODE, DTTSEQUENCE, DTTCHECKDIGIT
FROM ( SELECT distinct noOfTransSymls, COUNT(*) noOfTrans, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, c.DTTSEQUENCE, d.DTTCHECKDIGIT, RecentTransDate 
		FROM DMMDEPOSITTRANSACTION d 
		JOIN (SELECT  DTTSEQUENCE, MAX(DTTTRANSACTIONDATE) RecentTransDate,COUNT( distinct DTTTRANSACTIONSYMBOL) noOfTransSymls
				FROM DMMDEPOSITTRANSACTION
				GROUP BY DTTSEQUENCE
			  ) c  ON CONVERT( VARCHAR(7), d.DTTTRANSACTIONDATE, 102) = CONVERT( VARCHAR(7), c.RecentTransDate, 102) 
			    AND c.DTTSEQUENCE = d.DTTSEQUENCE
		GROUP BY c.DTTSEQUENCE, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, d.DTTCHECKDIGIT, RecentTransDate, noOfTransSymls
	 )t1




--
Chandu
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/10/2012 :  02:21:48  Show Profile  Reply with Quote
i tried that query but their is no output.another thing, is it important to put t1? correct me if wrong is the t1 is the name of the table? what is the purpose of that?sorry for so much question, but im lost already..,

dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/10/2012 :  02:39:59  Show Profile  Reply with Quote
quote:
Originally posted by dayna

i tried that query but their is no output.another thing, is it important to put t1? correct me if wrong is the t1 is the name of the table? what is the purpose of that?sorry for so much question, but im lost already..,
dcometa

t1 is alias name for that intermediate result set. You can give any name there...

SELECT MONTH(RecentTransDate) TranMonth, YEAR(RecentTransDate) TransYear, noOfTransSymls, noOfTrans, DTTPRODUCTCODE, DTTBRANCHCODE, DTTSEQUENCE, DTTCHECKDIGIT
FROM ( SELECT distinct noOfTransSymls, COUNT(*) noOfTrans, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, c.DTTSEQUENCE, d.DTTCHECKDIGIT, RecentTransDate 
		FROM DMMDEPOSITTRANSACTION d 
		JOIN (SELECT  DTTSEQUENCE, MAX(DTTTRANSACTIONDATE) RecentTransDate,COUNT( distinct DTTTRANSACTIONSYMBOL) noOfTransSymls
				FROM DMMDEPOSITTRANSACTION
				GROUP BY DTTSEQUENCE
			  ) c  ON CONVERT( VARCHAR(7), d.DTTTRANSACTIONDATE, 102) = CONVERT( VARCHAR(7), c.RecentTransDate, 102) 
			    AND c.DTTSEQUENCE = d.DTTSEQUENCE
		GROUP BY c.DTTSEQUENCE, d.DTTBRANCHCODE, d.DTTPRODUCTCODE, d.DTTCHECKDIGIT, RecentTransDate, noOfTransSymls
	 )t1

That red marked part is your table name..

--
Chandu
Go to Top of Page

dayna
Starting Member

Philippines
14 Posts

Posted - 12/10/2012 :  02:47:39  Show Profile  Reply with Quote
ok i got it, but i tried that query but no result..supposed to be transmonth, transyear,nooftranssymbls, and nooftrans appeared on the table dmmdeposittransaction but it seems no additional name in the table dmmdeposittransaction. how can i do it to appeared that on my table?

dcometa
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/10/2012 :  03:31:57  Show Profile  Reply with Quote
These are also the columns( DTTPRODUCTCODE, DTTBRANCHCODE, DTTSEQUENCE, DTTCHECKDIGIT) of your table.... right?


--
Chandu
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.23 seconds. Powered By: Snitz Forums 2000