sql server - transacciones - Cómo cambiar la consulta para proporcionar las últimas 15 semanas de datos en lugar de los últimos 15 días del servidor SQL

restaurar base de datos sql server 2012 a 2008

Question

La siguiente consulta proporciona el tiempo de reproducción de los usuarios de la base de datos diariamente durante los últimos 15 días. Agrega 0 si no se juega ningún juego. Ahora quiero obtener los datos del tiempo de juego semanalmente y 0 si no se juega ningún juego en toda la semana. Entonces quiero que la consulta brinde las últimas 15 semanas de datos.

Aquí está la consulta diaria.

CREATE PROCEDURE [dbo].[spGetPlayingTimeOfthepeoplesPerDay] @email NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MinDate DATE
        ,@MaxDate DATE
        ,@LastXDays INT

    SELECT @LastXDays = - 15

    SELECT @MaxDate = peoples.l_update
    FROM peoples
    WHERE peoples.email = @email

    DECLARE @test TABLE (
        quantity VARCHAR(100)
        ,DATE DATE
        ,TimePerDay DECIMAL(5, 2)
        );

    WITH CTE
    AS (
        SELECT peoples.email
            ,peoples.l_update
            ,act.quantity
            ,act.starttime
            ,act.endtime
            ,act.duration AS [Totaltime]
        FROM peoples
        INNER JOIN MPeoples ON peoples.Id = MPeoples.parent_id
        INNER JOIN slines ON MPeoples.id = slines.movesuser_id
        INNER JOIN seg ON slines.id = seg.sline_id
        INNER JOIN act ON seg.id = act.seg_id
        WHERE act.quantity = ''playing''
            AND (peoples.email = @email)
        GROUP BY peoples.email
            ,act.quantity
            ,act.duration
            ,act.starttime
            ,act.endtime
            ,peoples.l_update
        )
    INSERT INTO @test (
        quantity
        ,DATE
        ,TimePerDay
        )
    SELECT quantity
        ,Cast(starttime AS DATE) AS DATE
        ,SUM(datediff(second, starttime, endtime)) / 60.0 AS TimePerDay
    FROM cte WITH (NOLOCK)
    WHERE starttime >= dateadd(day, @LastXDays, l_update)
    GROUP BY quantity
        ,cast(starttime AS DATE)

    SELECT @MaxDate = @MaxDate
        ,@MinDate = dateadd(day, (@LastXDays + 1), @MaxDate);

    WITH AllDates
    AS (
        SELECT @MinDate AS xDate

        UNION ALL

        SELECT Dateadd(Day, 1, xDate)
        FROM AllDates AS ad
        WHERE ad.xDate < @MaxDate
        )
    SELECT ''playing'' AS quantity
        ,ad.xDate
        ,Isnull(t.TimePerDay, 0) AS TimePerDay
    FROM AllDates AS ad WITH (NOLOCK)
    LEFT JOIN @test AS t ON ad.xDate = t.DATE
END

Answer 1

Cambie el DATEADD de un día a la semana. Por lo tanto, dos cambios:

dateadd(week, @LastXDays, l_update)

y

dateadd(week, (@LastXDays + 1), @MaxDate)

En este caso, también cambiaría el nombre de la variable @LastXWeeks a @LastXWeeks .

CREATE PROCEDURE [dbo].[spGetPlayingTimeOfthepeoplesPerDay] @email NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MinDate DATE
        ,@MaxDate DATE
        ,@LastXDays INT

    SELECT @LastXWeeks = - 15

    SELECT @MaxDate = peoples.l_update
    FROM peoples
    WHERE peoples.email = @email

    DECLARE @test TABLE (
        quantity VARCHAR(100)
        ,DATE DATE
        ,TimePerDay DECIMAL(5, 2)
        );

    WITH CTE
    AS (
        SELECT peoples.email
            ,peoples.l_update
            ,act.quantity
            ,act.starttime
            ,act.endtime
            ,act.duration AS [Totaltime]
        FROM peoples
        INNER JOIN MPeoples ON peoples.Id = MPeoples.parent_id
        INNER JOIN slines ON MPeoples.id = slines.movesuser_id
        INNER JOIN seg ON slines.id = seg.sline_id
        INNER JOIN act ON seg.id = act.seg_id
        WHERE act.quantity = ''playing''
            AND (peoples.email = @email)
        GROUP BY peoples.email
            ,act.quantity
            ,act.duration
            ,act.starttime
            ,act.endtime
            ,peoples.l_update
        )
    INSERT INTO @test (
        quantity
        ,DATE
        ,TimePerDay
        )
    SELECT quantity
        ,Cast(starttime AS DATE) AS DATE
        ,SUM(datediff(second, starttime, endtime)) / 60.0 AS TimePerDay
    FROM cte WITH (NOLOCK)
    WHERE starttime >= dateadd(week, @LastXWeeks, l_update)
    GROUP BY quantity
        ,cast(starttime AS DATE)

    SELECT @MaxDate = @MaxDate
        ,@MinDate = dateadd(week, (@LastXWeeks + 1), @MaxDate);

    WITH AllDates
    AS (
        SELECT @MinDate AS xDate

        UNION ALL

        SELECT Dateadd(Day, 7, xDate)
        FROM AllDates AS ad
        WHERE ad.xDate < @MaxDate
        )
    SELECT ''playing'' AS quantity
        ,ad.xDate
        ,Isnull(t.TimePerDay, 0) AS TimePerDay
    FROM AllDates AS ad WITH (NOLOCK)
    LEFT JOIN @test AS t ON ad.xDate = t.DATE
END

Además, un consejo: no use sugerencias de consulta ( NOLOCK ) si no comprende su uso. En este caso, usar NOLOCK puede tener efectos desastrosos en sus resultados.

Aquí hay algunos artículos que debe leer antes de decidir si va a seguir usando NOLOCK o no.

Comprender la sugerencia de SQL Server NOLOCK

Los malos hábitos: poner NOLOCK en todas partes