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 2008 Forums
 Transact-SQL (2008)
 Query generate monthly transaction		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phyxe
Starting Member

Philippines
13 Posts
Posted - 05/23/2012 :  23:42:18  Show Profile  Reply with Quote
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)

Singapore
16746 Posts
Posted - 05/24/2012 :  00:49:41  Show Profile  Reply with Quote 

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)


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

KH
Time is always against us
Edited by - khtan on 05/24/2012 00:50:51
Go to Top of Page

mani_12345
Starting Member

India
35 Posts
Posted - 05/24/2012 :  01:12:53  Show Profile  Reply with Quote

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
Go to Top of Page

phyxe
Starting Member

Philippines
13 Posts
Posted - 05/24/2012 :  02:26:36  Show Profile  Reply with Quote
thank you so much khtan! the query worked perfectly :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.08 seconds. Powered By: Snitz Forums 2000