count dates of a week sql server

Question

count dates of a week sql server

Navigation

#1 by (0 votes)

-1

good day what I want is to ask a question that you tell me last week has example 15 applications starting from Monday to Friday and next or down example 10 requests of the week ancestor so on but all in one consultation. I hope you can help me, I also hope to be specific in what I want. Thank you! this my code, dateadd is for you to take today's date and go back to last week

select  count (t1.ID_SolicitudContrato) as conteo,
        t1.fecha_Actualizacion,
        t1.alcanceContrato,
         t2.ID_SolicitudContrato,
        t2.fecha_Actualizacion,
        t2.alcanceContrato  
        from CON_SolicitudContrato as t1
        inner join  CON_SolicitudContrato as t2 on t1.fecha_Actualizacion =t2.fecha_Actualizacion
        where t1.fecha_Actualizacion<DATEADD(wk,DATEDIFF(wk,7,GETDATE()),5)
        and  t1.fecha_Actualizacion >DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)
        and t2.fecha_Actualizacion<DATEADD(wk,DATEDIFF(wk,14,GETDATE()),5)
        and  t2.fecha_Actualizacion >DATEADD(wk,DATEDIFF(wk,14,GETDATE()),0)
        group by t1.ID_SolicitudContrato  ,t1.fecha_Actualizacion,t1.alcanceContrato,t2.ID_SolicitudContrato  ,t2.fecha_Actualizacion,t2.alcanceContrato
        order by t1.ID_SolicitudContrato desc

sql

asked by jose cas 05.12.2018 в 15:52

source

1 answer

I can not import model in route typescript How to fill the radio button with javascript with data from the BD? [closed]

score 0 · Answer 1

As a first point we know that the year has 52 weeks

link

as a second point, the function that we will use to know in what number of the week the date is found is DATEPART

link

Knowing these data and what I understood in your question is that you want to know the number of requests entered per week.

here is an example of how it would be:

DECLARE @TABLE AS TABLE(
Id int identity(1,1),
solicitud int,
fecha datetime
)

declare @i as int = 1;

while (@i <= 31 )
begin
    insert into @TABLE (solicitud,fecha) values (@i,dateadd(day,@i,getdate()))
    set @i = @i + 1
end


--select  DATEPART(week,fecha),* from @TABLE
select  DATEPART(week,fecha) 'Numero de semana' ,count(id) 'cantidad de solicitud ',year(fecha) year from @TABLE group by DATEPART(week,fecha),year(fecha) order by year(fecha) asc