Query Help Insert same data for multiple dates

Question

Post reply

Query Help Insert same data for multiple dates

SQL Server

Old Hand

Points: 377

March 23, 2020 at 4:38 pm

Go to Answer

#3736348

Need Query help to repeat data for future dates based on MAX date in another table.

create table #tblTest
(ename varchar(10),
 Amount float,
 eDate date)

insert into #tblTest values ('ABC',615.00,'03/23/2020')
insert into #tblTest values ('ABC',540.00,'03/23/2020')
insert into #tblTest values ('ABC',207.00,'03/23/2020')
insert into #tblTest values ('PQR',449.00,'03/23/2020')
insert into #tblTest values ('PQR',1065.00,'03/23/2020')
insert into #tblTest values ('PQR',-2435.00,'03/23/2020')


select distinct ename,eDate,sum(Amount) from #tblTest
group by ename,eDate

I would like to repeat the out based on the MAX Date in other table. If the MAX Date is 03/27 then the output will be :

Capture

Thanks!

This topic was modified 4 years, 8 months ago by SQL Server.

Phil Parkin

SSC Guru

Points: 246965

March 23, 2020 at 5:13 pm

Answer

#3736364

Here's a native version, in case you'd prefer that.

DECLARE @MinDate DATE =
        (
            SELECT MIN(tt.eDate) FROM #tblTest tt
        );
DECLARE @MaxDate DATE = '20200327';

DROP TABLE IF EXISTS #Date;

CREATE TABLE #Date
(
    ThisDate DATE NOT NULL PRIMARY KEY CLUSTERED
);

WITH E1 (N)
AS (SELECT 1
    FROM
    (
        VALUES
            (1)
           ,(1)
           ,(1)
           ,(1)
           ,(1)
           ,(1)
           ,(1)
           ,(1)
           ,(1)
           ,(1)
    ) dt (n) )
    ,E2 (N)
AS (SELECT 1
    FROM E1 a
        ,E1 b) --10E+2 or 100 rows
    ,cteTally (N)
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM E2)
INSERT #Date
(
    ThisDate
)
SELECT DATEADD(DAY, cteTally.N - 1, @MinDate)
FROM cteTally
WHERE cteTally.N <= DATEDIFF(DAY, @MinDate, @MaxDate) + 1;

SELECT DISTINCT
       t.ename
      ,d.ThisDate
      ,SUM(t.Amount)
FROM #tblTest t
    JOIN #Date d
        ON t.eDate <= d.ThisDate
GROUP BY t.ename
        ,d.ThisDate;

(Thanks to Sean Lange for the in-line date tally table code, which I copied almost verbatim.)

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

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

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

Steve Collins SSCrazy Points: 2159 More actions · Answer 1

drop table if exists #tblTest;
go
create table #tblTest
(ename varchar(10),
 Amount float,
 eDate date);
 go

insert #tblTest values
('ABC',615.00,'03/23/2020'),
('ABC',540.00,'03/23/2020'),
('ABC',207.00,'03/23/2020'),
('PQR',449.00,'03/23/2020'),
('PQR',1065.00,'03/23/2020'),
('PQR',-2435.00,'03/23/2020');

declare
  @max_dt           datetime='2020-03-27';

with
base_cte(ename,eDate,amount) as (
    select ename, eDate, sum(Amount)
    from #tblTest
    group by ename,eDate)
select
  bc.ename, cast(d.[value] as date) eDate, bc.amount
from
  base_cte bc
 cross apply
  dbo.daterange(bc.eDate, dateadd(dd, datediff(dd, bc.eDate, @max_dt), bc.eDate), 'dd', 1) d
order by 
  d.[value] asc;

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

Steve Collins SSCrazy Points: 2159 More actions · Answer 2

This uses the daterange function from this article: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

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