Table Header with Dynamic Dates
-
Hello, I need to create a dynamic table that produces a Date Column Header based on Date parameters entered, is this possible? My table is as follows, followed by the desired results:
CREATE TABLE #t (CustID int, StaffID int, JobID int, Dt datetime, Units money)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '10/29/2022', 1)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '10/30/2022', 0)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '10/31/2022', 4)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '11/01/2022', 8)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '11/02/2022', 8)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '11/03/2022', 2)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '11/04/2022', 1)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '11/05/2022', 4)
INSERT INTO #t (CustID, StaffID, JobID, Dt, Units) VALUES (101, 555, 444, '11/06/2022', 0)
SELECT * FROM #t WHERE Dt BETWEEN '10/31/2022' AND '11/04/2022'
Thank you in advance for any help!
-
If you have a Calendar table (or a table valued function to return all the dates between the start date and end date), then you can outer join your data table to the calendar table and return that.
SELECT c.CalendarDate, SUM(Units)
FROM Calendar c LEFT JOIN #t ON c.CalendarDate = #t.dt
(Except I'd split the date and the time so that they're not in the same column...)
-
For dynamic SQL, try the following >
declare @sqlStart nvarchar(200)=
'Select *
FROM (Select custID, staffId, jobID, dt, sum(units) as units from #t group by custID, staffId, jobID, dt
)as t Pivot(Sum(units) for dt in (';
declare @sqldates nvarchar(200) = '';
Select @sqldates =@sqlDates + ','+ '['+convert(varchar(20), iv.dt, 101) +']'
from (select distinct dt from #t) as iv;
Select @sqldates=right(@sqldates,len(@sqldates)-1 );
Declare @sqlEnd nvarchar(20) = ')) as p';
declare @sqlStmt nvarchar(420)= concat(@sqlStart, @sqldates, @sqlEnd)
Exec sp_executeSQL @sqlstmt----------------------------------------------------
-
Test out the solution I provided with data that varies in the first few columns and adjust the pivot experssion as needed.
----------------------------------------------------
-
this works perfectly! Many thanks!!
- rjjh78 wrote:
this works perfectly! Many thanks!!
It works perfectly for the given data. Try deleting one of the dates and find out that the date doesn't appear in the result. If that's ok, the perfect it is! If not, then you'll need to use a Calendar table or an iTVF to generate the dates to do a an Outer Join to so all dates are included and the missing dates can be reported as NULL, 0, or some other meaningful representation to identify that there was no entry(ies) for that date in the source data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply