Month wise total - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Month wise total

Author

Topic

pentahari
Starting Member

26 Posts

Posted - 2008-08-22 : 03:30:03

i have stored the purchase date and time in cdate field and purchase amount in puramount field.

My problem:
How to display the month wise purchase amount total.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 03:34:38

[code]group by dateadd(month, datediff(month, 0, [purchase_date]), 0)[/code]

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

pentahari
Starting Member

26 Posts

Posted - 2008-08-22 : 03:41:13

quote:
Originally posted by khtan

group by dateadd(month, datediff(month, 0, [purchase_date]), 0)

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

Sorry, I am very new to SQL. Please give any sample query. I will understand.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 03:43:14

[code]
select dateadd(month, datediff(month, 0, [purchase_date]), 0) as [Purchase Date]
sum(puramount) as [Purchase Amount]
from yourtable
group by dateadd(month, datediff(month, 0, [purchase_date]), 0)
[/code]

Change those in bold to you actual table & column name

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

pentahari
Starting Member

26 Posts

Posted - 2008-08-22 : 04:25:03

quote:
Originally posted by khtan

select dateadd(month, datediff(month, 0, [purchase_date]), 0) as [Purchase Date]
sum(puramount) as [Purchase Amount]
from yourtable
group by dateadd(month, datediff(month, 0, [purchase_date]), 0)

Change those in bold to you actual table & column name
KH
[spoiler]Time is always against us[/spoiler]

Thanks for your reply.
Your query works fine.

one another question.

select * from purchaseTable where purdate='8/22/2008'
This query list out the purchase transaction of today. but how to list out the Purchase Transaction of month.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 04:27:50

select * from purchaseTable where purdate>'08/01/2008' and purdate<'09/01/2008'

pentahari
Starting Member

26 Posts

Posted - 2008-08-22 : 04:34:24

quote:
Originally posted by visakh16

select * from purchaseTable where purdate>'08/01/2008' and purdate<'09/01/2008'

Thanks for your good reply.
Your query works fine. This is answer to my question.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 04:36:34

SELECT 	* 
FROM 	purchaseTable 
WHERE 	purdate	>= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND	purdate	<  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources