Platform
Learn
Community
Support
Partner Center
Sign up
or
Log in
logout
Account Settings
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
What's New
Community
Home
Forums
Forge
Ideas
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Sign up
or
Log in
Home
Forums
Forge
Ideas
Members
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2-minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
Day and week
Community
›
Forums
›
Technology & Integration
Day and week
New Post
New Post
Nuno Fernandes
Posted on 2008-11-13
Nuno Fernandes
Rank: #1109
Posted on 2008-11-13
Q
: My manager has asked me if I could make some reports with order information per day and per week. Can you give me an example how you can get totals by date and by week? (In the order table I have order-date and total-price) Is it also possible to have a range between 2 dates?
A
: Have you tried an advanced query like:
-- total orders per day
SELECT {ORDER}.Date, sum( {ORDER}.TotalPrice )
FROM {ORDER} WHERE
{ORDER}.Date >= @DateSince
AND {ORDER}.Date <= @DateUntil
GROUP BY {ORDER}.Date
ORDER BY {ORDER}.Date
-- total orders per week
SELECT datepart( week, {ORDER}.Date ), sum( {ORDER}.TotalPrice )
FROM {ORDER} WHERE
{ORDER}.Date >= @DateSince
AND {ORDER}.Date <= @DateUntil
GROUP BY datepart( week, {ORDER}.Date )
ORDER BY datepart( week, {ORDER}.Date )
In reports it usually also comes in handy the use of WITH ROLLUP modifier associated to the GROUP BY. Something like
GROUP BY {ORDER}.Date WITH ROLLUP
It provides an extra row with the total of the grouped rows. Try it!
Quick Reply
attachment
Choose File
No file chosen
Submit
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...