problem .... income during month in sql server 2008

Question

Post reply

problem .... income during month in sql server 2008

amr.moussa.87

SSC Rookie

Points: 26
More actions
June 19, 2014 at 8:32 pm

#294716

i have tried many thing
i have table called visiting, it have two column (visiting date) and ( cost ) like that
visiting_date cost
20-6-2014 50
20-6-2014 50
21-7-2014 200
21-7-2014 200
i want to make view that can sum cost of each month individual so output will be
month income
6 100
7 400
than you for helping

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

SELECT DATEPART(MM,VISITINGDATE),SUM(COST) FROM yourtable

GROUP BY DATEPART(MM,VISITINGDATE)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

amr.moussa.87 (6/19/2014)
i have tried many thing
i have table called visiting, it have two column (visiting date) and ( cost ) like that
visiting_date cost
20-6-2014 50
20-6-2014 50
21-7-2014 200
21-7-2014 200
i want to make view that can sum cost of each month individual so output will be
month income
6 100
7 400
than you for helping

What do you want to display when the year changes over?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vijred Say Hey Kid Points: 681 More actions · Answer 3

I use to create unique MonthID based on Year and month combination.

select (datepart(yy,SalesDate)-2010)*12+datepart(MM,SalesDate) as MonthID, sum(total)

group by (datepart(yy,SalesDate)-2010)*12+datepart(MM,SalesDate)

-- Assuming 2010 as the minimum month

-Vijred (http://vijredblog.wordpress.com)

amr.moussa.87 SSC Rookie Points: 26 More actions · Answer 4

thank you guys your answer help me so much 🙂 i find the solution

SELECT SUM(cost) as income,

DATEPART(Month, visiting_date) as month,

DATEPART(Year, visiting_date) as year

FROM table1

GROUP BY DATEPART(Year, visiting_date), DATEPART(Month, visiting_date)

ORDER BY DATEPART(Year, visiting_date), DATEPART(Month, visiting_date)