question

360nomore avatar image
360nomore asked

Managing SQL Server 2005 query to handle 13 month rolling calendar

I have a table that has a field called STMNDATE, what I am looking to do is create a query that returns the sum of three other fields (BILLED, PAID, DEFAULTPRICE) by MM-YYYY date format for a rolling 13 month span, each month its own data. Secondly, same question and I am using a UNION query for this now, check9ing for best practice, but There are two dates, statement date ([STMNDATE]) and Journal Date ([JOURNALSENTERED]), for my billed and paid date, I need to look between two statement dates, for my journal date I have to look between statement date and journal end date (represents the date of deposit). Like I said, am using a UNION now, but not sure if best practice. I was most of the way there, using a function, but for political reasons, I cannot use a function on this client server and now I have donkeyed my query to the point I need assistance in writing this. Can you help? QUESTION 1 - Code SELECT stmndate as MM-YYYY ,isnull(sum(billed,0) as FeesBilled ,isnull(sum(paid),0)as FeesPaid ,isnull(sum(defaultprice),0) as PLFees FROM Not needed for this question, my joins are good WHERE ????
sql server 2005
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just comment here, this should probably be moved out of the 'Site Discussion' category back to the main thread so that others can see it.
0 Likes 0 ·

1 Answer

·
JohnM avatar image
JohnM answered
If I understand your first question correctly, you shouldn't need a function. I'm not sure if this homework or not, but look at the GROUP BY function to get your rolling 13 months. Group by the month & year from STMNDATE. The second portion of your question should be able to be handled by using either BETWEEN or '' in conjunction with STMNDATE to do a the date comparison. If you are still stuck, post the entire query that you have thus far and we'll try to help! Hope that helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.