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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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