multiple queries

  • Hello. Is there anyway i can simplify the writing of this queries where the only thing that changes is the date on the three tables and the "datepart" on second table and third table?

    --table 1--

    Create table Report_Es1 (ola1 int)
    declare @h int
    set @h = (select Sum(case when [DATA] = CAST(GETDATE() AS DATE) then [card] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE() AS DATE) then [Moche] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE() AS DATE) then [track] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE() AS DATE) then [trio1] else 0 end)from [sales_HH])
    insert into Report_Es1 values (@h)

    --tabela 2--

    Create table Report_Es2 (ola2 int)
    declare @b int
    set @b = (select sum ( case when [DATA] = CAST(GETDATE()-1 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora) then [card] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE()-1 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora) then [Moche] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE()-1 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora)then [track] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE()-1 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora)then [trio1] else 0 end)
    Sum(case when [DATA] = CAST(GETDATE()-1 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora)then [dmr3] else 0 end)from [sales_HH])
    insert into Report_Es2 values (@b)

    --table 3--

    Create table Report_Es3 (ola3 int)
    declare @c int
    set @c = (select sum ( case when [DATA] = CAST(GETDATE()-7 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora) then [card] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE()-7 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora) then [Moche] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE()-7 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora)then [track] else 0 end) +
    Sum(case when [DATA] = CAST(GETDATE()-7 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora)then [trio1] else 0 end)
    Sum(case when [DATA] = CAST(GETDATE()-7 AS DATE) and DATEPART(HOUR, CURRENT_TIMESTAMP) > DATEPART(HOUR, hora)then [dmr3] else 0 end)from [sales_HH])
    insert into Report_Es3 values (@c)
  • Something like this?

    DECLARE @Today DATE = GETDATE();
    DECLARE @Yesterday DATE = DATEADD(DAY, -1, @Today);
    DECLARE @TodayLess7 DATE = DATEADD(DAY, -7, @Today);
    DECLARE @Hr TINYINT = DATEPART(HOUR, GETDATE());

    SELECT @Yesterday
    ,@Hr
    ,@TodayLess7;

    SELECT SUM(card + Moche + track + trio1)
    FROM sales_HH
    WHERE DATA = @Today;

    SELECT SUM(card + Moche + track + trio1 + dmr3)
    FROM sales_HH
    WHERE DATA = @Yesterday
    AND DATEPART(HOUR, CURRENT_TIMESTAMP) > @Hr;

    SELECT SUM(card + Moche + track + trio1 + dmr3)
    FROM sales_HH
    WHERE DATA = @TodayLess7
    AND DATEPART(HOUR, CURRENT_TIMESTAMP) > @Hr;

    If any of (card, Moche, track, trio1, dmr3) can be NULL, additional code is required, eg, replace card with ISNULL(card,0).

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • that is it thank you a lot. Best Regards

  • ;WITH cte_dates AS (
    SELECT CAST(GETDATE() AS date) AS today,
    DATEPART(HOUR, GETDATE()) AS today_hour,
    CAST(GETDATE() - 1 AS date) AS yesterday,
    CAST(GETDATE() - 7 AS date) AS lastweek
    )
    INSERT INTO Report_Es1 ( ola1 )
    SELECT datesums.*
    FROM (
    SELECT
    SUM(CASE WHEN [DATA] = today
    THEN [card] + [Moche] + [track] + [triol] ELSE 0 END) AS today_sum,
    SUM(CASE WHEN [DATA] = yesterday AND today_hour > DATEPART(HOUR, hora)
    THEN [card] + [Moche] + [track] + [triol] + [dmr3] ELSE 0 END) AS yesterday_sum,
    SUM(CASE WHEN [DATA] = lastweek AND today_hour > DATEPART(HOUR, hora)
    THEN [card] + [Moche] + [track] + [triol] + [dmr3] ELSE 0 END) AS lastweek_sum
    FROM [sales_HH]
    CROSS JOIN cte_dates
    WHERE [DATA] >= lastweek AND [DATA] <= today
    ) AS derived1
    CROSS APPLY ( VALUES(today_sum), (yesterday_sum), (lastweek_sum) ) AS datesums(datesum)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • drop table if exists Report_Es;
    go
    create table Report_Es(
    ola1 int,
    ola2 int,
    ola3 int);

    insert into Report_Es(ola1, ola2, ola3)
    select sum(case when DATA = cast(getdate() as date)
    then card+Moche+track+trio1 else 0 end),
    sum(case when DATA = cast(getdate() -1 as date)
    and datepart(hour, current_timestamp) > datepart(hour, hora)
    then card+Moche+track+trio1+dmr3 else 0 end),
    sum(case when DATA = cast(getdate() -7 as date)
    and datepart(hour, current_timestamp) > datepart(hour, hora)
    then card+Moche+track+trio1+dmr3 else 0 end)
    from sales_HH;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply