Query generate monthly transaction

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 2008 Forums

Transact-SQL (2008)

Query generate monthly transaction

Author

Topic

phyxe
Starting Member

13 Posts

Posted - 2012-05-23 : 23:42:18

Hi there,
I just want to have someone look at my query on generating monthly reports from my database. I have this txnlog table where it captures every transaction with reference to server time. so i build this query to generate a monthly report

select  COUNT (*) as [TOTAL Transaction for the day],
txndate as [Date]
from TxnLog 
where user_name ='user'
and txnDate between '2012-05-01' and '2012-05-30 11:59:59 PM'
groub by txndate

but with this code i get this table

quote:
TOTAL Captured for the day date
-------------------------- -----------------------
1 2012-05-07 08:10:52.447
1 2012-05-07 08:15:31.250
1 2012-05-07 09:08:32.477
1 2012-05-07 09:15:48.850
1 2012-05-07 11:08:56.387
1 2012-05-07 11:18:12.997
1 2012-05-07 11:22:14.887
1 2012-05-07 11:31:00.993
1 2012-05-07 11:37:09.013
1 2012-05-07 11:43:49.517
1 2012-05-07 11:46:54.090

but i what i need as a result would be like this

quote:
TOTAL Captured for the day date
-------------------------- -----------------------
18 2012-05-07
21 2012-05-08
61 2012-05-09

is there a possible way to do this?

thank you in advance for any help :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-24 : 00:49:41

[code]
select COUNT (*) as [TOTAL Transaction for the day],
dateadd(day, datediff(day, 0, txndate), 0) as [Date]
from TxnLog
where user_name ='user'
and txnDate >= '2012-05-01'
and txnDate < '2012-06-01'
groub by dateadd(day, datediff(day, 0, txndate), 0)
[/code]

EDIT :
Monthly ? shouldn't it be up to 31st of May ?

KH
[spoiler]Time is always against us[/spoiler]

mani_12345
Starting Member

35 Posts

Posted - 2012-05-24 : 01:12:53

i guesss it's wrkng fine ...

select count(*) 'Count ',date_user from prbm
where user_name ='user' and date_user between '2012-05-24' and '2012-05-29'
group by date_user

phyxe
Starting Member

13 Posts

Posted - 2012-05-24 : 02:26:36

thank you so much khtan! the query worked perfectly :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources