multiple queries

Question

multiple queries

Old Hand

Points: 359

November 19, 2021 at 9:39 am

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)

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

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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

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 haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

rafamaniac Old Hand Points: 359 More actions · Answer 2

rafamaniac

Old Hand

Points: 359

November 19, 2021 at 1:41 pm

#3954851

that is it thank you a lot. Best Regards

ScottPletcher SSC Guru Points: 100946 More actions · Answer 3

;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) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Collins SSCrazy Points: 2068 More actions · Answer 4

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