Table Header with Dynamic Dates

Question

Post reply

Table Header with Dynamic Dates

rjjh78

SSCrazy

Points: 2784

October 31, 2022 at 3:11 pm

Go to Answer

#4106848

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!

MMartin1

One Orange Chip

Points: 27778

November 1, 2022 at 12:07 am

Answer

#4107040

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

----------------------------------------------------

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

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

pietlinden SSC Guru Points: 63434 More actions · Answer 1

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...)

MMartin1 One Orange Chip Points: 27778 More actions · Answer 2

Test out the solution I provided with data that varies in the first few columns and adjust the pivot experssion as needed.

----------------------------------------------------

rjjh78 SSCrazy Points: 2784 More actions · Answer 3

rjjh78

SSCrazy

Points: 2784

November 1, 2022 at 6:40 pm

#4107448

this works perfectly! Many thanks!!

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

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)