I ask for an algorithm to be compared by monthly sales, such as June 9, output from June 1st to 9th, May 1 to 9, April 1st to 9 ... Sales comparison, but sales in the database is the amount of sales, that is, non-all-week sales, to divide 5 (indicating that the Sunday Sunday) is multiplied by days. . . Similar input June 30th, etc.
Table structure: Salesid Quantity SaleSdate ... .... 22 50 2005-6-5 23 15 2005-6-12 24 22 2005-6-19 25 555 2005-6-26 26 321 2005-7 -3 ... .... Salesdate is Sunday, to sum (quanity) is required to sell sales at the beginning of the month, that is, the same period of time in the same period of time, only one month
In addition, if it is input to June 30, you should add QUANTITY / 5 * 3 on July 3 to Quantity / 5 * 4 and 12, No. 26 QUANTITY, June 3. Sales Note: On the 3rd, on the 2nd, June 1, Wednesday, Wednesday, June 27, 28, 29, 30 for Monday to Thursday
-------------------------------------------------- ------- SELECT MONTH 01 to ' MONTH CAST (DAY) As Varchar), SUM (Quantity) AS Quantity from (SELECT Left (Convert (VARCHAR (10), SaleSdate, 120), 8 AS Month, Right (VARCHAR (10), SaleSdate, 120), 2) As Day, from (Select Dateadd (Day, -6, Salesdate) AS SaleSdate, Quantity / 5 As Quantity From Table Union All Select Dateadd ( Day, -5, salesdate) AS Salesdate, Quantity / 5 As Quantity From Table Union All Select Datedd (Day, -4, SaleSdate) AS SaleSdate, Quantity / 5 As Quantity from Table Union All Select Dateadd (Day, -3, Salesdate As SaleSdate, Quantity / 5 As Quantity from Table Union All Select DateAdd (Day, -2, Salesdate) AS Salesdate, Quantity / 5 As Quantity FROM T1) T2 WHERE CAST (DAY As Int) <= day (parameter) Group by month ----------------------------------------------- -----------
