Home > Software design >  SQL dynamic output tables
SQL dynamic output tables

Time:10-17

right now I have 2 tables. The first one has all the sales done by sales persons throughout each week, this table updates every week and only displays the sales of the current week (erases the data and starts over next week), the second table is a record of all sales done ever.

SALES FOR THIS WEEK OCTOBER(9 TO 15)(updates every week)

iD sales_representative iD_sale date_sale
1 21 51 Oct 11
2 22 52 OCT 10
3 23 53 Oct 14
4 24 54 OCT 13

TOTAL SALES IN RECORD SALES FOR THIS WEEK OCTOBER(9 TO 15)(updates every week)

iD sales_representative date_sale total
51 21 Oct 11 $500
52 22 OCT 10 $100
53 23 Oct 14 $600
54 19 OCT 13 $450
55 19 Jan 20 $150
56 32 OCT 8 $800
57 23 Sep 10 $570
58 19 March 13 $300

What I'm trying to build are more 5 queries (tables),

  • First query (table), all the sales persons that made their first sale ever in in the current week
iD sales_representative iD_sale date_sale
1 21 51 Oct 11
2 22 52 OCT 10
  • Second query (table), all the sales persons that made their SECOND sale ever in the current week
iD sales_representative iD_sale date_sale Last_sale
1 23 53 Oct 14 Sep 10
2
  • Third query (table), all the sales persons that made their THIRD sale ever in the current week
iD sales_representative iD_sale date_sale Last_sale
1 19 54 Oct 13 March 13
2

And so on and so on until the 5th table. I've been trying this for more than a week. If I can provide more information or answer anything I couldn't explain, please let me know.

*if they made their first sale ever on the current week and then another sale in the same week it will show on the second table with the last_sale date of the first one

CodePudding user response:

Label a week by the date of the Sunday at its start and then map sales to weeks.

At the same time number each representative's sales to allow you to select the _n_th.

CREATE FUNCTION StartOfWeek(@d datetime) RETUNS date
AS
    RETURN (SELECT DATEADD(WEEK, DATEDIFF(WEEK, '2022-01-02', '2022-10-12'), '2022-01-02'))
END

DECLARE @n int
SELECT @n = 3

SELECT *
FROM (
    SELECT iD, sales_representative, iD_sale, date_sale,
        dbo.StartOfWeek(date_sale) AS w,
        ROW_NUMBER() OVER (PARTITION BY sales_representative ORDER BY iD AS SaleNumber
) AS s
WHERE SaleNumber = @n
    AND w = dbo.StartOfWeek(CURRENT_TIMESTAMP)
  • Related