need a sql script to produce a result from 2 tables

David Green

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan8y ago
We can try.
0
David Green8y ago
i I were to paste the table data here, could you adapt your sript to work as I am still struglging with this
0
Bryian Tan8y ago
it should. try it out.
+1
David Green8y ago
hi, thank you
Would that give me a result if nothing was on on that date for that user?
Thanks
dave
+1

Bryian Tan8y ago

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