作者:xiaoliangtang | 来源:互联网 | 2023-05-17 14:15
I have two types of rows in a table:
表中有两种类型的行:
There is the standard data, which is tied to a taskId and is loaded into the database during a process, the first two rows below are this type of data. The second type of data is not loaded in the process, it is inserted as part of a form. YOu can see the difference because the first type of data has a taskId and NonScrumStoryId is NULL. In the second case, NonScrumStoryId column is populated and TaskId is NULL.
有一个标准数据,它绑定到一个taskId并在处理过程中加载到数据库中,下面的前两行是这种类型的数据。第二种类型的数据不会被加载到流程中,而是作为表单的一部分插入。您可以看到差异,因为第一类数据有一个taskId,而非scrumstoryid为空。在第二种情况下,非scrumstoryid列是填充的,而TaskId是NULL。
I have a query to get data from this table:
我有一个查询来从表中获取数据:
DECLARE @startDateParam DATETIME
,@endDateParam DATETIME
,@productId VARCHAR(100)
,@search VARCHAR(100)
,@userParam VARCHAR(100)
,@orgTeamPK VARCHAR(100)
SET @startDateParam = '2013-11-25 00:00:00'
SET @endDateParam = '2013-12-01 23:59:59'
SET @productId = '%'
SET @search = '%%'
SET @userParam = '%'
SET @orgTeamPK = '%'
SELECT '3' AS RowType
,DTH.EnteredBy AS Person
,COALESCE(PDT.[Name], APP.AppName) AS Project
,(
CASE WHEN (
STY.KanBanProductId IS NOT NULL
AND STY.SprintId IS NULL
) THEN 'KanBan' WHEN (
STY.KanBanProductId IS NULL
AND STY.SprintId IS NOT NULL
) THEN 'Sprint' ELSE SCY.Catagory END
) AS ProjectType
,COALESCE(STY.[Number], NSS.IncidentNumber) AS StoryNumber
,COALESCE(STY.Title, NSS.[Description]) AS StoryTitle
,CONVERT(VARCHAR(20), STY.Effort) AS Effort
,COALESCE(TSK.[Name], '') AS Task
,CONVERT(VARCHAR(20), TSK.OriginalEstimateHours) AS OriginalEstimateHours
,SCY.Catagory AS Category
,NSS.IncidentNumber AS IncidentNumber
,APP.AppName AS ApplicationName
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 2 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS MondayHours
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 3 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS TuesdayHours
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 4 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS WednesdayHours
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 5 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS ThursdayHours
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 6 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS FridayHours
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 7 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS SaturdayHours
,CAST(SUM(CASE WHEN DATEPART(dw, DTH.ActivityDate) = 1 THEN DTH.[Hours] ELSE 0 END) AS VARCHAR(20)) AS SundayHours
,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
,CAST(SUM(CASE WHEN DTH.Hours > 0 THEN DTH.[UserDifference] END) AS VARCHAR(20)) AS DifferentUsers
,CAST(SUM(CASE WHEN DTH.Hours > 0 THEN DTH.DoubleBookedFlag END) AS VARCHAR(20)) AS DoubleBookedFlag
,DTH.PointPerson AS PointPerson
FROM DailyTaskHours DTH
LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
LEFT JOIN NonScrumStory NSS ON DTH.NOnScrumStoryId= NSS.PK_NonScrumStory
LEFT JOIN SupportCatagory SCY ON NSS.CatagoryId = SCY.PK_SupportCatagory
LEFT JOIN [Application] APP ON NSS.ApplicatiOnId= APP.PK_Application
LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
LEFT JOIN [User] USR ON DTH.EnteredBy = USR.DisplayName
WHERE DTH.EnteredBy LIKE @userParam
AND ActivityDate >= @startDateParam
AND ActivityDate <= @endDateParam
AND PDT.PK_Product LIKE @productId
AND (
(
@orgTeamPK = '%'
AND (
USR.[OrganizationalTeamId] LIKE @orgTeamPK
OR USR.[OrganizationalTeamId] IS NULL
)
)
OR (
@orgTeamPK <> '%'
AND (USR.[OrganizationalTeamId] LIKE @orgTeamPK)
)
AND (
(
STY.Number LIKE @search
OR STY.Number IS NULL
)
OR (
STY.Title LIKE @search
OR STY.Number IS NULL
)
OR (
TSK.NAME LIKE @search
OR STY.Number IS NULL
)
)
)
GROUP BY DTH.EnteredBy
,PDT.[Name]
,SPT.[Name]
,SPT.[Description]
,STY.[Number]
,STY.Title
,TSK.[Name]
,SCY.Catagory
,NSS.IncidentNumber
,APP.AppName
,STY.KanBanProductId
,STY.SprintId
,NSS.[Description]
,TSK.OriginalEstimateHours
,STY.Effort
,DTH.PointPerson
HAVING SUM(DTH.[Hours]) > 0
My problem is that this query is only returning rows from the table that are from the process, it is not returning NonScrumStory columns and I need it to.
我的问题是,这个查询只返回来自流程的表中的行,它不返回非scrumstory列,我需要它。
Edit: It appears part of the WHERE clause is causing the issue.
编辑:它会出现WHERE子句的一部分,导致问题。
Ater investigation it probably has to do with this line:
之后的调查可能与这条线有关:
AND PDT.PK_Product LIKE @productId
How can I remedy this?
我该如何补救?
1 个解决方案
4
You're joining Task
using
你加入任务使用
LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
then joining Story
using
然后加入故事使用
LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
then joining Product
using
然后加入产品使用
LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
(Note the Task -> Story -> Product reliance)
(注意任务->故事->产品依赖)
Those joins are all fine, but you're filtering on Product
using
这些连接都很好,但是您正在对产品使用进行过滤
AND PDT.PK_Product LIKE @productId
This will, no matter what, match rows that have a product like the parameter which in turn limits the results to rows that have stories, which limits to rows that have tasks.
无论如何,这将匹配具有类似参数的行,该参数将结果限制为具有事例的行,将结果限制为具有任务的行。
You can check if @productId
is null or equal to the product. This will allow you to return all rows if the parameter is not specified, or return rows filtered to the product.
您可以检查@productId是否为null或等于产品。如果没有指定参数,您可以返回所有的行,或者返回过滤到产品的行。
...
AND ActivityDate <= @endDateParam
AND (@productId IS NULL OR PDT.PK_Product LIKE @productId)
AND (
...
If you need to always filter on product, then you'll probably have to use a UNION
and select all rows with tasks in one, then all rows with NonScrumStory
in the other.
如果您需要始终对产品进行过滤,那么您可能必须使用一个UNION,并在其中一个中选择所有的行,然后在其他行中使用非scrumstory。