need a sql script to produce a result from 2 tables

Question

Hi guys, I have been struggling witha query that is proably something simple, but I can see the wood form the trees for it.

I have two tables

Table 1
ID

Name

Table 2
Table1ID
Date
Task

i need to get a grid result, that will give me the next 7 das results for each name, even if nothign is in the date or task result.

I want this result from the current date for the next 7 days on screen,
Producing somethign like this

name	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	06/01/2018	07/01/2018
bob1	task		task		task		task
bob2		task		task		task
bob3	task		task		task		task
bob4		task		task		task
bob5							task
bob6	task		task		task		task
bob7		task		task		task

A, how can I product a sql result and what is thr string to use?

Im stuck and I cant work it out.

And 2
how can I get a repeater result to show on screen.

I would like it to be a rolling screen and result tht will work on a day to day basis.

what query strignwould you use tog get a result like the above with empty strings next to the names and only show whats allocated?

Thanks in advance

Dave

Bryian Tan · Answer

it should. try it out.

David Green · Answer

hi, thank you Would that give me a result if nothing was on on that date for that user? Thanks dave

Bryian Tan · Answer

You might need to use dynamic PIVOT in SQL to accomplish this. Here is an example based on your post. CREATE TABLE #tempNext7Day ([ Date ] DATE ) CREATE TABLE #Table1 ( Id INT , [ Name ] VARCHAR (100)) CREATE TABLE #Table2 ( Table1ID INT , [ Date ] DATE , [Task] VARCHAR (100)) declare @counter int , @next7Date VARCHAR (100), @sql VARCHAR (1000) = '' --temp table INSERT INTO #Table1 SELECT 1, 'User 1' UNION SELECT 2, 'User 2' UNION SELECT 3, 'User 3' INSERT INTO #Table2 SELECT 1, '2018-02-04' , 'Task 1' UNION SELECT 1, '2018-02-05' , 'Task 2' UNION SELECT 1, '2018-02-06' , 'Task 3' UNION SELECT 1, '2018-02-07' , 'Task 4' UNION SELECT 1, '2018-02-08' , 'Task 5' UNION SELECT 1, '2018-02-09' , 'Task 6' UNION SELECT 1, '2018-02-10' , 'Task 7' UNION SELECT 1, '2018-02-11' , 'Task 8' UNION SELECT 1, '2018-02-12' , 'Task 9' UNION SELECT 1, '2018-02-13' , 'Task 10' UNION SELECT 2, '2018-02-04' , 'Task 21' UNION SELECT 2, '2018-02-05' , 'Task 22' UNION SELECT 2, '2018-02-06' , 'Task 23' UNION SELECT 2, '2018-02-07' , 'Task 24' UNION SELECT 2, '2018-02-08' , 'Task 25' UNION SELECT 2, '2018-02-09' , 'Task 26' UNION SELECT 2, '2018-02-10' , 'Task 27' UNION SELECT 2, '2018-02-11' , 'Task 28' UNION SELECT 2, '2018-02-12' , 'Task 29' UNION SELECT 2, '2018-02-13' , 'Task 30' --get next 7 days date SET @counter = 0 while @counter begin --print CONVERT(VARCHAR(10),DATEADD(dd, @counter, GETDATE()),101) INSERT INTO #tempNext7Day SELECT CONVERT ( VARCHAR (10),DATEADD(dd, @counter, GETDATE()),101) set @counter = @counter + 1 end --SELECT * FROM #tempNext7Day --set the date in comma separated string SELECT @next7Date = STUFF(( SELECT ',[' + CONVERT ( VARCHAR (10), [ Date ], 101) + ']' FROM #tempNext7Day FOR XML PATH( '' ) ), 1, 1, '' ) PRINT @next7Date SET @sql = @sql + ' ; WITH temp AS ( SELECT t1.Id, t1. Name , t2.[ Date ], t2.Task FROM #Table1 t1 JOIN #Table2 t2 ON t1.Id=t2.Table1ID JOIN #tempNext7Day t3 ON t2.[ Date ] = t3.[ Date ] ) SELECT Name , ' + @next7Date + ' FROM temp PIVOT ( MAX ([TASK]) FOR [ Date ] IN ( '+@next7Date+' ) ) AS P' PRINT @sql EXEC (@sql) --cleanup DROP TABLE #Table1 DROP TABLE #Table2 DROP TABLE #tempNext7Day Output if run on 2/4/18 Name 2/4/2018 2/5/2018 2/6/2018 2/7/2018 2/8/2018 2/9/2018 2/10/2018 User 1 Task 1 Task 2 Task 3 Task 4 Task 5 Task 6 Task 7 User 2 Task 21 Task 22 Task 23 Task 24 Task 25 Task 26 Task 27

Bryian Tan · Answer

We can try.

David Green · Answer

i I were to paste the table data here, could you adapt your sript to work as I am still struglging with this