Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I query order by date.

Like this:

enter image description here

And the same for week, month, year...?

share|improve this question
    
What is your table structure like? You need to make it very clear what your data looks like, and what your desired results are. right now it's not clear at all. You can edit your question to add that info in. –  JNK Feb 22 '12 at 13:07
    
i have just updated the questions. I just need a statistics report –  vNext Feb 22 '12 at 13:10
    
Well you still don't show what your actual data looks like, so I'll have to guess. –  JNK Feb 22 '12 at 13:21
1  
-1 as this looks like a basic homework questions that a simple google search will answer (or a basic knowledge of sql) –  Andrew Bickerton Feb 22 '12 at 13:21
    
I need a report to show many orders in database last 30 days. Ex: 02/22/2012 -> 132 orders, 02/21/2012 -> 81 orders... etc. Just show date, total orders of that day. That's all –  vNext Feb 22 '12 at 13:26

1 Answer 1

up vote 5 down vote accepted

Without sample data I need to guess, but you want aggregations.

For Days:

SELECT CAST(OrderDate as DATE), as 'Date'
       COUNT(*) as 'Orders', 
       SUM(OrderPaidAmt) as 'TotalAmpunt'
FROM OrderTable
GROUP BY CAST(OrderDate as DATE)

For weeks or months, you need to do aggregation on DATEPART output:

SELECT DATEPART(WEEK, OrderDate) as 'Week',
       COUNT(*) as 'Orders', 
       SUM(OrderPaidAmt) as 'TotalAmpunt'
FROM OrderTable
WHERE YEAR(Orderdate) = YEAR(GETDATE())
GROUP BY CAST(OrderDate as DATE)

I added the WHERE clause in the second one to only show from this year. If you want to see multiple years you need to GROUP BY the YEAR() value of your orderdate as well.

share|improve this answer
    
For days, how can I get last 30 days? not all days! –  vNext Feb 22 '12 at 13:44
    
Add a WHERE filter - WHERE OrderDate > DATEADD(day, -30, GETDATE()) –  JNK Feb 22 '12 at 13:45
3  
Also you're welcome. You realize people who answer your question are just people on the internet who aren't getting paid or anything, right? –  JNK Feb 22 '12 at 13:50
    
Yes, thanks you for supporting me :) –  vNext Feb 22 '12 at 15:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.