Date & Sum - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date & Sum

Author

Topic

drgoebel
Starting Member

3 Posts

Posted - 2007-05-03 : 09:00:31

Hi @ all,

i would like to create a view on a table.
there are records like

Date Amount
01/04/07 10000
03/04/07 3500

and so on.

now i want to create a view, that gives me just:

April 07 13500

and so on.

I would like to use that for an import to excel.

Any ideas?

THANKS,

drgoebel

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 09:11:39

haven0t tested this but try something like this:
select datename(mm, Date) + ' ' + case(year(date) as char(4)) as formatedDate, sum(Amount) as Amnt
from MyTable
group by convert(char(4), date, 112)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 09:13:26

SELECT SUBSTRING(CONVERT(VARCHAR, Date, 106), 4, 6) AS Month, SUM(Amount)
FROM Table1
GROUP BY SUBSTRING(CONVERT(VARCHAR, Date, 106), 4, 6)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 09:29:38

That won't work, Spirit, I guess.

-- Prepare sample data
declare @t table
(
	[Date] datetime,
	Amount int
)

insert @t
select '20070401', 10000 union all
select '20070403', 3500

-- expected output
select datename(mm, [Date]) + ' ' + cast(year([date]) as char(4)) as formatedDate, sum(Amount) as Amnt
from @t
group by datename(mm, [Date]) + ' ' + cast(year([date]) as char(4))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 09:30:12

no strings, no formatting. use data with proper datatypes. simply round your date to the first day of each month, format as necessary in Excel:


select 
  [Month], sum(value)
from
(
  select dateadd(mm, datediff(mm,0, yourDate),0) as [Month], value
  from yourtable
) x
group by 
  [Month]

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 09:52:33

harsh, thanx.

jeff... but this will put all aprils from all years together, won't it?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 09:55:18

No, the DATEADD/DATEDIFF combination reset all dates to same month but the 1st day.

Peter Larsson
Helsingborg, Sweden

drgoebel
Starting Member

3 Posts

Posted - 2007-05-03 : 10:49:32

quote:
Originally posted by jsmith8858

no strings, no formatting. use data with proper datatypes. simply round your date to the first day of each month, format as necessary in Excel:

select
[Month], sum(value)
from
(
select dateadd(mm, datediff(mm,0, yourDate),0) as [Month], value
from yourtable
) x
group by
[Month]

- Jeff
http://weblogs.sqlteam.com/JeffS

OK, thanks a lot, that is my normal SQL query for a view.

SELECT [Posting Date], Quantity
FROM dbo.[databsasename$Job Ledger Entry]
WHERE ([Resource Group No_] = 'INGENIEURE')

ist that then correct:


select 
  [Month], sum(quantity)
from
(
  select dateadd(mm, datediff(mm,0, posting date),0) as [Month], value
  from dbo.[databsasename$Job Ledger Entry]
) x
group by 
  [Month]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 14:49:37

you need to change value to quantity in the SQL statement. And don't ask us, try it yourself! Also, you'll have syntax errors since you have column names with spaces but not delimited by [ ].

The key is to read and understand what we are showing you, be sure it makes sense, ask questions if it doesn't, and then apply it to your situation. The skill of applying general techniques to your specific situation is a critical, key skill to have for a software developer.

- Jeff
http://weblogs.sqlteam.com/JeffS

drgoebel
Starting Member

3 Posts

Posted - 2007-05-04 : 04:39:06

great, thanks a lot, it works!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 05:50:02

quote:
Originally posted by spirit1

harsh, thanx.

jeff... but this will put all aprils from all years together, won't it?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Not until you use Month(datecol)=4

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources