I have a query that gets the date of when a "Defect" Was opened and if it was closed it grabs that date as well.
This date returns like this:
What I want is it to display like this:
The Start count is the number of times that date appears in the start column and the same logic for the end_count. Which could return null or 0 it doesn't matter.
The total column is just Start - End. This helps me create a running total on the client side. I am not currently against doing a running total on the query side, I just know how to do it on the server side given a total.
Any ideas on how to convert my current date to display like that?
- Matt
"If I must boast, I will boast of the things that show my weakness"
- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
Code:
SELECT BG_BUG_ID as 'Defect ID',
BG_STATUS as 'Status',
convert(varchar,BG_DETECTION_DATE,101) as 'Start Date',
convert(varchar,max(ENDDATE.ENDTIME),101) as 'End Time'
From Bug WITH (NOLOCK)
LEFT OUTER JOIN
(SELECT AU_ENTITY_ID,max(AU_TIME) as 'EndTime'
FROM BUG,AUDIT_LOG, AUDIT_PROPERTIES WITH (NOLOCK)
WHERE AU_ENTITY_TYPE = 'BUG'
AND AP_FIELD_NAME = 'BG_STATUS'
AND AU_ACTION_ID = AP_ACTION_ID
AND (AP_NEW_VALUE = 'Deferred' or AP_NEW_VALUE = 'Closed')
AND BG_DETECTED_IN_RCYC = 1118
GROUP BY AU_ENTITY_ID
)as ENDDATE
ON ENDDATE.AU_ENTITY_ID = BUG.BG_BUG_ID
WHERE BG_DETECTED_IN_RCYC = 1118
AND BG_STATUS <> 'Invalid'
AND BG_STATUS <> 'New'
GROUP BY BG_BUG_ID, BG_STATUS, BG_DETECTION_DATEThis date returns like this:
What I want is it to display like this:
The Start count is the number of times that date appears in the start column and the same logic for the end_count. Which could return null or 0 it doesn't matter.
The total column is just Start - End. This helps me create a running total on the client side. I am not currently against doing a running total on the query side, I just know how to do it on the server side given a total.
Any ideas on how to convert my current date to display like that?
- Matt
"If I must boast, I will boast of the things that show my weakness"
- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
