C#-sql

[talikardi]

I am trying to get a report and I know how to do it but I dont like the way of doing it. I have two tables: Customers(id,name etc..), Transactions (transId,customerId,transTotal, transDate etc..) in mssql

what I need is transaction total per month for each customer.

what I am trying to do is write a sql statement for each month and I have two parameters for sql select. startDate, endDate. It is easy to determine startDate (01/month/year), however i couldnt find an easy way of finding last day of the month. and I dont want to do it manually for each month. I need to loop it and there is also fucking february!!

is there any other way of doing this? need a little help, thx !

[noobie]

if you are using Date object, you might get endDate by subtracting one day from next month.

however, what you are trying to achieve is too much simpler with something like this:

select customerId, datepart(month,transDate), sum(transTotal) from Customers (nolock) c, Transactions (nolock) t where c.id=t.customerId and transDate >= ('01/01/'+convert(varchar,datepart(year,getdate()))) group by customerID, datepart(month,transDate)

the last AND statement might be unnecessary for your use and data..