SQL dynamic output tables-CodePudding

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)