none
show manager approve documents tHRough store procedure

    Question

  • i have a scenario that when any user first upload document then this go for approval first to supervisor then when supervisor approve/reject documents then only approve documents go to manager then again when manager approve/reject document then only approve document shows to director for final approval

     
    ALTER PROCEDURE [dbo].[ALLDOCUMNETS]
    AS
    begin
    SELECT DocumentInfo.DocID as DocumentID, 
     dbo.DocumentInfo.DocName as DocumentName,
     dbo.DocumentInfo.Uploadfile as FileUploaded, 
     dbo.DocumentInfo.UploadedBy as UploadedBy,
     dbo.Userss.Email as UserEmail,
     dbo.DocType.DocType as Document, 
     dbo.Department.DepType as Department ,
      dbo.Approval.AppoveBy, 
     
      dbo.ApproveType.ApproveType as Status
      
      FROM dbo.Department
      left JOIN dbo.DocumentInfo
      ON dbo.DocumentInfo.DepID=dbo.Department.DepID 
    
      left JOIN dbo.DocType
      ON  dbo.DocumentInfo.DocTypeID=dbo.DocType.DocTypeID
        inner JOIN dbo.Userss on Userss.UserName =dbo.DocumentInfo.UploadedBy 
       
        inner join  dbo.Approval ON dbo.DocumentInfo.DocID = dbo.Approval.DocID INNER JOIN
                         dbo.ApproveType ON dbo.Approval.ApproveID = dbo.ApproveType.ApproveID 
          AND  dbo.ApproveType.ApproveType = 'Approve'  
    
    
    end

    here above record kanez is manager and sundus is supervisor

    here i want to show only kanez approve documents show to director then he will be able to final approval but here both supervisor and manager documents go to director but i want to show only kanez document approve documents to director??


    table viewS

    how i done this?

    Wednesday, December 25, 2013 1:58 PM

All replies

  • I have some difficulties to understand what you are asking for. It appears that it relates to people's roles, that is whether they are supervisor, manager or director. But I can't see apparent any place in your tables where this information is codified?

    You table diagram aslo opens for questions. There is an UploadedBy in Documents which appears to be a user id from your sample data, but there is also a UsedID in that column, but there only seem to be one foriegn key from Documents to Users. Likewise, I would expect Approval.ApprovedBy to be a foreign key to Users. But I may be misunderstanding the diagrams. It would help if you posted the scripts instead; they are less ambiguous.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 25, 2013 2:48 PM
  • this is my designation table

    DesignID DesigType

    1


    SuperVisor
    2


    Manager
    3


    Director
    4


    BasicUsers






    and this is my approval table

    SeqNo DocID ApproveID AppoveBy          ApproveDate

    258  30             1            sundus       2013-12-25 18:33:36.373
    259    
    30             1             kaneez      2013-12-25 18:34:47.347





    now plesae tell me what can i do

    Wednesday, December 25, 2013 6:03 PM
  • and i want to show only kaneez record to director
    Wednesday, December 25, 2013 6:04 PM
  • I'm not 100% sure what and i want to show only kaneez record to director means, but maybe this is what you are looking for:

    SELECT DI.DocID as DocumentID,
           DI.DocName as DocumentName,
           DI.Uploadfile as FileUploaded,
           DI.UploadedBy as UploadedBy,
           U.Email as UserEmail,
           DT.DocType as Document,
           D.DepType as Department ,
           A.AppoveBy,
           AT.ApproveType as Status
    FROM        dbo.Department D
    JOIN        dbo.DocumentInfo DI ON DI.DepID     = D.DepID
    LEFT   JOIN dbo.DocType      DT ON DI.DocTypeID = DT.DocTypeID
    JOIN        dbo.Userss        U ON U.UserName   = DI.UploadedBy
    JOIN        dbo.Approval      A ON DI.DocID     = A.DocID
    JOIN        dbo.ApproveType  AT ON A.ApproveID    = AT.ApproveID
                                   AND AT.ApproveType = 'Approve' 
    JOIN        dbo.Userss     AppU ON A.ApprovedBy = AppU.UserName
    JOIN        dbo.Designation  Dg ON Appu.DesigID = Dg.DesigID
                                   AND Dg.DesigType = 'Manager'

    I've introduced table aliases to make the query more slender and easier to read. I've also changed the join to DocumentInfo to be an inner join, because LEFT JOIN did not make any sense.

    The stuff in bold is what I added to the query to satisfy your request.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 25, 2013 7:28 PM
  • He asked this on Code Project too, where I answered but he did not reply.  Kaneez is the name of the director he wants to filter by, as far as I can tell.


    Christian Graus

    Wednesday, December 25, 2013 9:09 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed horribly). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Posting pictures instead of DDL is also considered rude. 

    This is minimal polite behavior on SQL forums. 

    Why do you rename data elements? Is it called a “doc_name" or "document_name"?? Are you trying to do display formatting in the query, in violation of a tiered architecture? 

    Did you fail to build a data dictionary and follow ISO-11179 rules?  

    How can a “doc_type” be a table?? A “<something>_type” is an attribute so it is a column by definition. That column will be constrained by a CHECK() or a REFERENCES. 

    Why did you split the information about the documents from the documents? Thihk about that. 

    Post the DDL so we can try to repair this. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, December 25, 2013 10:53 PM
  • >but i want to show only kanez document approve documents to director??

    Any progress?

    Do you want to hard-wire the logic to kanez or use some sort of employee class?


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, January 08, 2014 9:35 AM