how to create monthly reports from daily based reports using sql server

Question

i have a table contains columns cost,date,customer-id,products bought i can create daily reports but now i want to create monthly reports as sum of cost. i have a grid view to show it please explain me clearly am a beginner to this .net what should be done in grid view and what should be the sql query i am using grid view manually generated columns daily basis are customer-id,date,cost,products bought

for monthly reports

i am using grid-view columns cutomerid,month,year,cost

what should be the sql query

Please don't just ask us to solve the problem for you. Show us how _you_ tried to solve the problem yourself, then show us _exactly_ what the result was, and tell us why you feel it didn't work. See "[What Have You Tried?](http://whathaveyoutried.com/)" for an excellent article that you _really need to read_. — John Saunders, Jan 26 '14 at 03:51

wruckie · Accepted Answer · 2014-01-26T04:02:10.397

0

Your issue is not .net, but SQL. Write the query first, displaying it in your app will phase 2. Do you have reporting services?

Select sum(cost), [Month], [Year], customer-id from 
( 
   Select cost, Month(date) as [Month], Year(date) as [Year] , customer-id
   from customerbooking where date >= '2-1-2014' and date <= '2-28-2014'
) as a
Group by [Month], [Year], customer-id

edited Jan 26 '14 at 04:02

answered Jan 26 '14 at 03:36

wruckie

1,717
1
23
34

i dont know how to use report services can't we do this through sql server it may not be the easiest way please can you explain what should be sql query – Tajkumar Jan 26 '14 at 03:41
report builder is easier than this. What is your table name? – wruckie Jan 26 '14 at 03:43
-1: so far, this is a set of comments, not the answer to the question. – John Saunders Jan 26 '14 at 03:52
1

I'm removing the downvote, but, in the future, please don't start the answer until you have enough information to answer with. – John Saunders Jan 26 '14 at 03:59
And, BTW, take a close look at that query, and I'm sure you'll find some fairly obvious improvements. – John Saunders Jan 26 '14 at 04:00
i got it right but now i want to display month name instead of month number how to do that – Tajkumar Jan 26 '14 at 12:14
http://stackoverflow.com/questions/185520/convert-month-number-to-month-name-function-in-sql – wruckie Jan 26 '14 at 13:20

how to create monthly reports from daily based reports using sql server

1 Answers1