Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with query

Posted on 2009-05-12
4
Medium Priority
?
466 Views
Last Modified: 2012-05-06
I got this query but it is returning WI's which had the 'Ready for Deployment' status currently or previously. I just need the records with WI in that state currently.

I dont have more points else would have used them.
SELECT DISTINCT dbo.[File].[File], MAX(dbo.[File].__LastUpdatedTime) AS LastUpdated, dbo.[Work Item].System_Id, dbo.Person.Person
FROM         dbo.[File] INNER JOIN
                      dbo.[Code Churn] ON dbo.[File].__ID = dbo.[Code Churn].Filename INNER JOIN
                      dbo.Changeset ON dbo.[Code Churn].Changeset = dbo.Changeset.__ID INNER JOIN
                      dbo.[Work Item Changeset] ON dbo.Changeset.__ID = dbo.[Work Item Changeset].Changeset INNER JOIN
                      dbo.[Work Item] ON dbo.[Work Item Changeset].[Work Item] = dbo.[Work Item].__ID INNER JOIN
                      dbo.Person ON dbo.[Code Churn].[Checked In By] = dbo.Person.__ID
WHERE     (dbo.[File].[File Extension] = N'.sql')
GROUP BY dbo.[File].[File], dbo.[Work Item].System_State, dbo.[Work Item].System_Id, dbo.Person.Person
HAVING      (dbo.[Work Item].System_State = N'Ready for Deployment')
ORDER BY dbo.[Work Item].System_Id

Open in new window

0
Comment
Question by:shahprabal
  • 3
4 Comments
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24366015
Your SQL is sort of a mess, it's hard to read with the naming and all.... Try this, I put the max of the last update equal to the current updated so that shoudl be all current work items that have that status...


SELECT DISTINCT dbo.[File].[File], MAX(dbo.[File].__LastUpdatedTime) AS LastUpdated, dbo.[Work Item].System_Id, dbo.Person.Person
FROM         dbo.[File] INNER JOIN
                      dbo.[Code Churn] ON dbo.[File].__ID = dbo.[Code Churn].Filename INNER JOIN
                      dbo.Changeset ON dbo.[Code Churn].Changeset = dbo.Changeset.__ID INNER JOIN
                      dbo.[Work Item Changeset] ON dbo.Changeset.__ID = dbo.[Work Item Changeset].Changeset INNER JOIN
                      dbo.[Work Item] ON dbo.[Work Item Changeset].[Work Item] = dbo.[Work Item].__ID INNER JOIN
                      dbo.Person ON dbo.[Code Churn].[Checked In By] = dbo.Person.__ID
WHERE     (dbo.[File].[File Extension] = N'.sql')
GROUP BY dbo.[File].[File], dbo.[Work Item].System_State, dbo.[Work Item].System_Id, dbo.Person.Person
HAVING      (dbo.[Work Item].System_State = N'Ready for Deployment')
						AND
	         MAX(dbo.[File].__LastUpdatedTime) = dbo.[File].__LastUpdatedTime
ORDER BY dbo.[Work Item].System_Id

Open in new window

0
 
LVL 14

Author Comment

by:shahprabal
ID: 24366160
Thanks for trying but this is what I get :
Msg 8121, Level 16, State 1, Line 1
Column 'dbo.File.__LastUpdatedTime' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

I realise that without showing the schema it will be hard to fix this but I have attached the query that lists the important fields in the Work Item table. Also the second query returns one record per WI. I just need to incorporate that into the main query.
 Result of the 1st query:

__ID           Work Item           System_Id           System_Rev
 ----------------------------------------------------------------------------
1637                     366-1                     366                     1
1638                     366-2                     366                     2
1953                     366-3                     366                     3
2077                     366-4                     366                     4
2079                     366-5                     366                     5
2080                     366-6                     366                     6
2083                     366-7                     366                     7
2086                     366-8                     366                     8
2087                     366-9                     366                     9
(9 row(s) affected)
 
 
Result of the 2nd query:

                system_id
----------- -----------
2087          366
 
(1 row(s) affected)

select __ID, [Work Item], System_Id, System_Rev from [work item]
where system_id=366
 
select distinct max(__id), system_id  from [work item]
group by system_id
having system_id=366

Open in new window

0
 
LVL 14

Author Comment

by:shahprabal
ID: 24367272
I am sure there is a better way of getting the same data but the below query works... if someone can suggest a better way I will award the points to them.
SELECT     main.[File], main.LastUpdated, main.Person, sub.System_Id AS [Work Item]
FROM         (SELECT DISTINCT dbo.[File].[File], MAX(dbo.[File].__LastUpdatedTime) AS LastUpdated, dbo.Person.Person, [Work Item Changeset].[Work Item]
                       FROM          dbo.[File] INNER JOIN
                                              dbo.[Code Churn] ON dbo.[File].__ID = dbo.[Code Churn].Filename INNER JOIN
                                              dbo.Changeset ON dbo.[Code Churn].Changeset = dbo.Changeset.__ID INNER JOIN
                                              dbo.[Work Item Changeset] ON dbo.Changeset.__ID = dbo.[Work Item Changeset].Changeset INNER JOIN
                                              dbo.Person ON dbo.[Code Churn].[Checked In By] = dbo.Person.__ID
                       WHERE      (dbo.[File].[File Extension] = N'.sql')
                       GROUP BY dbo.[File].[File], dbo.Person.Person, [Work Item Changeset].[Work Item]) AS main INNER JOIN
                          (SELECT     one.ID, one.System_Id
                            FROM          (SELECT DISTINCT MAX(__ID) AS ID, System_Id
                                                    FROM          dbo.[Work Item]
                                                    GROUP BY System_Id) AS one INNER JOIN
                                                       (SELECT     __ID AS ID, System_Id
                                                         FROM          dbo.[Work Item] AS [Work Item_1]
                                                         WHERE      (System_State = N'Ready for Deployment')) AS two ON one.ID = two.ID) AS sub ON main.[Work Item] = sub.ID

Open in new window

0
 
LVL 14

Accepted Solution

by:
shahprabal earned 0 total points
ID: 24451334
Got it
SELECT     main.[File], main.LastUpdated, main.Person, sub.System_Id AS [Work Item]
FROM         (SELECT DISTINCT dbo.[File].[File], MAX(dbo.[File].__LastUpdatedTime) AS LastUpdated, dbo.Person.Person, [Work Item Changeset].[Work Item]
                       FROM          dbo.[File] INNER JOIN
                                              dbo.[Code Churn] ON dbo.[File].__ID = dbo.[Code Churn].Filename INNER JOIN
                                              dbo.Changeset ON dbo.[Code Churn].Changeset = dbo.Changeset.__ID INNER JOIN
                                              dbo.[Work Item Changeset] ON dbo.Changeset.__ID = dbo.[Work Item Changeset].Changeset INNER JOIN
                                              dbo.Person ON dbo.[Code Churn].[Checked In By] = dbo.Person.__ID
                       WHERE      (dbo.[File].[File Extension] = N'.sql')
                       GROUP BY dbo.[File].[File], dbo.Person.Person, [Work Item Changeset].[Work Item]) AS main INNER JOIN
                          (SELECT     one.ID, one.System_Id
                            FROM          (SELECT DISTINCT MAX(__ID) AS ID, System_Id
                                                    FROM          dbo.[Work Item]
                                                    GROUP BY System_Id) AS one INNER JOIN
                                                       (SELECT     __ID AS ID, System_Id
                                                         FROM          dbo.[Work Item] AS [Work Item_1]
                                                         WHERE      (System_State = N'Ready for Deployment')) AS two ON one.ID = two.ID) AS sub ON main.[Work Item] = sub.ID

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question