Home / Questions / 530755
Accepted
cosito
Asked: 2022-06-29 11:05:46 +0800 CST 2022-06-29 11:05:46 +0800 CST

add data for the current week

  • 772

I have a table of products:

id  | descripcion

1      latas

and a service table:

id |  id_producto |  cantidad |       fecha

1       1              2        2022-04-27 11:41:17.327
2       1              5        2022-04-26 11:45:23.327
3       1              1        2022-04-27 11:48:17.327
4       1              2        2022-04-18 11:41:17.327

I need to calculate the quantity sold for the current week, that is, my week starts on Monday 25and my week close is the following Monday.

Since you can only see one sale on 18, but that is from last week, you would only need to add the sales of the current week until the week ends.

I have this query, it's not complete, but I don't know how else to do it:

SELECT sum(cantidad) FROM servicios where id_producto = '1' and datepart >= 1 then datepart <= 1

What I have tried is to add the quantities that go from this Monday to the next of a specific product, from the day 25to the day 2of May, it would have to give me 8 as a result, since there is only one quantity from yesterday and 2from the day today, and if more are done tomorrow that should also be added to the total.

For the closing and beginning of the following week it will have to be different depending on the quantities of that current week

sql

1 Answers

  1. Best Answer

    I understand that the main problem in your case is how to determine the "current week", certainly the current date is obtained with a GETDATE()now, to know from today, which is the date of Monday and which is Sunday, you have to do some calculations , taking into account the @@DATEFIRSTone with which the motor is configured. I'm going to copy this logic which works great and is server configuration agnostic:

    DECLARE @Hoy DATE

SET @Hoy = GETDATE()

SELECT  @Hoy                                                                Hoy,
        DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @Hoy)) % 7, @Hoy)   Lunes,
        DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @Hoy)) % 7, @Hoy)   Domingo

    Now it's trivial to implement it in your query:

    SELECT sum(cantidad) 
       FROM servicios 
       where id_producto = '1' 
             and fecha >= DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @Hoy)) % 7, @Hoy) 
             and fecha <= DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @Hoy)) % 7, @Hoy)
    • 1