Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: Help needed for creating view

    Hi

    Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
    Suppose I have five records

    item itemgroup status
    1 1 complete
    2 1 Xyz
    3 2 complete
    4 2 complete
    5 2 complete

    my view should be

    itemgroup status
    1 incomplete
    2 complete

    All the Statuses are not predefined...they get added as and when required........

    Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.


    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Question: If anything in an itemgroup does not say complete, then it's incomplete?

    Sounds simple enough....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2004
    Posts
    3

    Is that an anwer or a question?

    Is that an anwer or a question?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well it was a question...but...how's about

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
    GO
    
    INSERT INTO myTable99(item, itemgroup, status)
    SELECT 1, 1, 'complete' UNION ALL
    SELECT 2, 1, 'Xyz' 	UNION ALL
    SELECT 3, 2, 'complete' UNION ALL
    SELECT 4, 2, 'complete' UNION ALL
    SELECT 5, 2, 'complete'
    GO
    
    CREATE VIEW myView99
    AS
        SELECT DISTINCT l.itemgroup
    	, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
          FROM myTable99 l 
    LEFT JOIN (  SELECT itemgroup, COUNT(*) AS Status_COUNT 
    		FROM myTable99 
    	       WHERE status <> 'Complete'
    	    GROUP BY itemgroup) AS r
    	ON l.itemgroup = r.itemgroup
    GO
    
    
    SELECT * FROM myView99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh, oh! Can I play too?
    Code:
    SELECT DISTINCT a.itemgroup
    ,  CASE
          WHEN EXISTS (SELECT *
             FROM myTable AS b
             WHERE  b.itemgroup = a.itemgroup
                AND b.status <> 'complete') THEN 'incomplete'
          ELSE 'complete'
       END AS groupStatus
       FROM myTable AS a
    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I like that one better.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2004
    Posts
    3
    Thanks Guys...Both of them are much better than the function I have

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •