Geek asks and answers

Is this the best way to write SQL for this report?

We have data orders, and we need to generate a report Weekly - Custom date Range of 7 Daysusing SQL. The report will display weekly countorders. The user will select End Date, and we need to create 12 DateRange units based on this date. For example, the user selects 1/24/2013we need 12 points / units as:

Point12 = End Date - 7 Days
Point11 = End Date - 14 Days
Point10 = End Date - 21 Days
Point9  = End Date - 28 Days
.
.
Point1  = End Date - X Days

Our solution :

We plan to create a temporary table with 12 rows. Each row will have such data (we will calculate the start and end dates for each point):

Point   StartDate           EndDate      TotalOrders
Point12  2013-01-24          2013-01-30   
Point11  2013-01-17          2013-01-23

After that we will receive countorders for each line.

Is this a good solution to this problem or can it be optimized?

EDIT:

Weekly DateRange will be Custom date Range of 7 Days.

enter image description here

+5
2

, SQLServer, , .

, 12 CTE , ( / ) . , .

+1

?

SELECT
 WEEKOFYEAR(`date`) AS point,
 SUM(orders) AS order
FROM `tablename`
WHERE `date` BETWEEN `startdate` AND `enddate`
GROUP BY point
-1

More articles:


All Articles