SQL Syntax needed for weeks of year

I have a table with a structure like this:
DOCNUMBR,DOCDATE,DOCAMOUNT

I need to create a report like this:

Week #            2015 Amount       2014 Amount
1                        5,000                      4,750
2                       7,500                       6,000
through week 52

What is the proper and most efficient way to structure a query to produce these results?
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
How do YOU define a week?

e.g. Does it start on Monday? or Sunday? (or some other day?)
0
PortletPaulfreelancerCommented:
You could do it using  DATEPART(week,...)

select
      datepart(week,docdate) [Week #]                
    , sum(case when year(docdate) = 2015 then DOCAMOUNT end) [2015 Amount]
    , sum(case when year(docdate) = 2014 then DOCAMOUNT end) [2014 Amount]
from table1
group by
      datepart(week,docdate)
;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
That is a good question. What is happening here is that donations come in each week and then a deposit is made on Monday.  So for this example, Monday would be the first day of the week. We are just trying to compare donations for each week of the year but within each week the day would not matter. So the first week of the year would be the week that would end with the first Monday.
0
PortletPaulfreelancerCommented:
I suggest you read the datepart documentation (use link supplied earlier) there is a a lot of information regarding "week" and the impact of DATEFIRST.
Choose the option(s) that suits you best.
1
rwheeler23Author Commented:
Excellent documentation. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.