November 7, 2017 at 2:07 am
I have two tables one table have lists of tasks and the second table have list of spares which are linked to the tasks.
CREATE TABLE #Tasks
(
TaskId INT NOT NULL
, TaskCode VARCHAR(20)
,DurationInMinutes DECIMAL
)
CREATE TABLE #TaskSpares
(
ID INT NOT NULL
,ParentId INT NOT NULL
,StockDescription VARCHAR(50)
,Quantity INT
)
INSERT INTO #Tasks (TaskId,TaskCode, DurationInMinutes) VALUES (71918,'TBA8-020220',5 )
INSERT INTO #Tasks (TaskId,TaskCode, DurationInMinutes) VALUES (72086,'TBA8-040020',10 )
INSERT INTO #Tasks (TaskId,TaskCode, DurationInMinutes) VALUES (72428,'TBA8-060020',5 )
INSERT INTO #Tasks (TaskId,TaskCode, DurationInMinutes) VALUES (72446,'TBA8-060080',5 )
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5786,71918,'Bearing',2)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5787,71918,'Flange bushing',6)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5819,72086,'Filter',3)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5820,72086,'Toothed belt',1)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5872,72428,'Roller',3)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5873,72428,'Teflon hose',2)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5874,72428,'Bushing',5)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5876,72446,'Spring',10)
INSERT INTO #TaskSpares (ID, ParentId, StockDescription,Quantity) VALUES (5877,72446,'Gasket',1)
SELECT t.TaskId
,t.TaskCode
,t.DurationInMinutes
,ts.ParentId
,ts.StockDescription
,ts.Quantity
FROM #Tasks AS t
JOIN #TaskSpares AS ts ON ts.ParentId = t.TaskId;
my issue here is i'm creating a ssrs report from the script above where when the user select Tasks the report gives me the list of tasks with sum of DurationInMinutes and if the user select Stock the report gives me StockDescription with sum of Quantity
The only problem is when the Task is selected the report should give me the distinct task with the duration.
e.g TaskCode = 'TBA8-020220' has a DurationInMinutes of 5. but the report gives me 10 because there's 2 stockItems linked to it.
how can I modify my query to select the distinct on task code.
November 7, 2017 at 2:26 am
Will task code TBA8-020220 always have a task of 5. If fact, is the duration the same across a task? If so, also set your data to group on Duration In Minutes, as well as your Task Code.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 7, 2017 at 2:32 am
If you try to summarise for each TaskCode, then you'll lose some detail - in this case, the individual values of StockDescription. Please will you show us what the whole result set should look like?
John
November 7, 2017 at 7:11 am
the duration varies for each tasks hence I can't group it together
November 7, 2017 at 7:38 am
Nomvula - Tuesday, November 7, 2017 7:11 AMthe duration varies for each tasks hence I can't group it together
So, if task TBA8-020220 sometimes takes 10 minutes, and sometimes takes 5 what would you expect to be shown in your column? An average? The Minimum?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply