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)
November 19, 2021 at 12:51 pm
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).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 19, 2021 at 1:41 pm
that is it thank you a lot. Best Regards
November 19, 2021 at 3:18 pm
;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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 19, 2021 at 3:43 pm
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