Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-sql Pivoting Problem for drawing issue register

Posted on 2013-11-20
3
Medium Priority
?
160 Views
Last Modified: 2014-11-18
I'm preparing a drawing issue register. I've been doing this for years but utilising formal issues in a separate table. I need VIRTUAL issues to be produced from the data. A virtual issue, the column (In) in my pivot table, is a unique set of recipients (Rn) for a date. As you can see some drawings (not shown) will have been issued on the same date (Dn) but to a different unique set of recipients, or on a different date to the same set:
eg

Pivot arrangement
This should be simple but I can't get my head round it. Maybe this is the onset of middle age...

I want to take (simplified)

tblRevIssues
fldIssuedDate
fldIssuedTo
fldDrawingRevision

and create 3 derived tables with the magic virtual IssueNo:

tblIssues
fldIssueNo
fldIssuedDate

tblIssueRecipients
fldIssueNo
fldIssuedTo

tblIssueDrawings
fldIssueNo
fldDrawingRevision

I'm comfortable with cursors, table variables etc. Less so with the pivot command and ranking but happy to look at these methods. Just need a steer in the right direction!

Then I can generate the register.
I'm familiar with this process.

Finished register
0
Comment
Question by:czwg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39665036
Me middle aged too, which is perhaps why I'm a little confused about what you really want.

The best way of helping us to help you is to provide sample data and an expected result.

I guess that means some records from tblRevIssues and from those records what result you expect.

"the magic virtual IssueNo:"
so issue numbers don't have to be unique?
i.e. if one runs the report today you get an Issue-1
, and if you run it next month for different data you also get an Issue-1

Is ordering this IssueNo by fldIssuedDate expected?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1500 total points
ID: 39665057
Sorry,more questions. You provide a schematic like #1:
|--  #1  --|
i1 i2 i3 i4
D1 D1 D2 D3
R1 R3 R1 R1
R2    R2 R3

Open in new window

but the second row shows only 3 D(ates) D1, D2, D3
So if using a pivot around those dates wouldn't you expect this?
|- #2 -|
i1 i2 i3
D1 D2 D3
R1 R1 R1
R2 R3 R2 
R3

Open in new window

while I'm here I'd like to see more columns of tblRevIssues, e,g, is there a drawing number that is common for revisions?
e.g.
DRG-0000000001 Rev1 Dated ...
DRG-0000000001 Rev2 Dated ...

or does [fldDrawingRevision] contains all "parts". If it does please explain those parts.
0
 

Author Closing Comment

by:czwg
ID: 40449684
I'm accepting this as a solution because I'm embarrassed not to have responded to your help. The virtual issue isn't actually used: it corresponds to a date column associated with a particular set of revisons. It is quite possible that two or more sets of revisions could be issued on the same date to different recipients. I thought I would need derived issue numbers to deal with this. However, in the end I did it all by building a table in memory and pivoting the results several times. Looking back now, even I have difficulty figuring out how I did it maybe I am getting too old for this!

However, I'm shown the final procedure because it might be useful to someone:

ALTER PROC [spRegisterPages]
      @MDGContractNo INT
   ,@PageNo INT = NULL
AS --spRegisterPages @MDGContractNo = 64283, @PageNo = 2
      BEGIN

            SET NOCOUNT ON

            DECLARE @RegisterPages TABLE
                  (
                   Col INT
                  ,PageNo INT
                  ,IssueNo INT
                  ,fldIssueDate DATETIME
                  ,Issue NVARCHAR(20)
                  ,Docs INT
                  ,Recips NVARCHAR(100)
                  ,IssuedBy NVARCHAR(50)
                  )

            INSERT INTO @RegisterPages
                        EXECUTE spContractIssues
                              @MDGContractNo = @MDGContractNo


--SELECT * FROM @RegisterPages AS rp WHERE PageNo = 1
--SELECT * FROM fnIssuePages(@MDGContractNo, Null)
--EXEC spContractIssues @MDGContractNo

            DECLARE @DocCount INT
            DECLARE @RecCount INT
            DECLARE @DwgsPerSheet INT

            SELECT
                        @DocCount = COUNT(1)
                  FROM
                        (SELECT DISTINCT
                                    d.fldDocID
                              FROM
                                    tblRevIssues AS i
                              INNER JOIN dbo.tblRevisions AS r
                              ON      i.fldRevID = r.fldRevID
                              INNER JOIN dbo.tblDocuments AS d
                              ON      r.fldDocID = d.fldDocID
                              INNER JOIN dbo.tblTasks AS t
                              ON      d.fldTWDTag = t.fldTWDTag
                              WHERE
                                    t.fldMDGContractNo = @MDGContractNo
                        ) x

            SELECT
                        @RecCount = COUNT(1)
                  FROM
                        (SELECT DISTINCT
                                    i.fldIssuedTo
                              FROM
                                    tblRevIssues AS i
                              INNER JOIN dbo.tblRevisions AS r
                              ON      i.fldRevID = r.fldRevID
                              INNER JOIN dbo.tblDocuments AS d
                              ON      r.fldDocID = d.fldDocID
                              INNER JOIN dbo.tblTasks AS t
                              ON      d.fldTWDTag = t.fldTWDTag
                              WHERE
                                    t.fldMDGContractNo = @MDGContractNo
                        ) x

            SELECT
                        @DocCount AS DwgsIssued
                     ,@RecCount AS Recipients
                     ,38 - @RecCount AS DwgsPerSheet
                     ,c.fldMDGContractNo AS MDGContractNo
                     ,c.cContract
                     ,D.*
                     ,R.R01
                     ,R.R02
                     ,R.R03
                     ,R.R04
                     ,R.R05
                     ,R.R06
                     ,R.R07
                     ,R.R08
                     ,R.R09
                     ,R.R10
                     ,R.R11
                     ,R.R12
                     ,R.R13
                     ,R.R14
                     ,R.R15
                     ,R.R16
                     ,R.R17
                     ,R.R18
                     ,R.R19
                     ,R.R20
                     ,R.R21
                     ,R.R22
                     ,R.R23
                     ,B.B01
                     ,B.B02
                     ,B.B03
                     ,B.B04
                     ,B.B05
                     ,B.B06
                     ,B.B07
                     ,B.B08
                     ,B.B09
                     ,B.B10
                     ,B.B11
                     ,B.B12
                     ,B.B13
                     ,B.B14
                     ,B.B15
                     ,B.B16
                     ,B.B17
                     ,B.B18
                     ,B.B19
                     ,B.B20
                     ,B.B21
                     ,B.B22
                     ,B.B23
                  FROM
                        (SELECT
                                    PageNo
                                 ,'D' + FORMAT(Col, '00') AS ColNo
                                 ,fldIssueDate
                              FROM
                                    @RegisterPages
                        ) AS X PIVOT ( MAX(fldIssueDate) FOR ColNo IN ([D01], [D02], [D03], [D04], [D05], [D06], [D07], [D08], [D09], [D10], [D11], [D12], [D13], [D14], [D15], [D16], [D17], [D18], [D19], [D20], [D21], [D22], [D23]) ) as D
                  INNER JOIN (SELECT
                                                PageNo
                                             ,'R' + FORMAT(Col, '00') AS ColNo
                                             ,Recips
                                          FROM
                                                @RegisterPages
                                 ) AS Y PIVOT ( MAX(Recips) FOR ColNo IN ([R01], [R02], [R03], [R04], [R05], [R06], [R07], [R08], [R09], [R10], [R11], [R12], [R13], [R14], [R15], [R16], [R17], [R18], [R19], [R20], [R21], [R22], [R23]) ) as R
                  ON      D.PageNo = R.PageNo
                  INNER JOIN (SELECT
                                                PageNo
                                             ,'B' + FORMAT(Col, '00') AS ColNo
                                             ,IssuedBy
                                          FROM
                                                @RegisterPages
                                 ) AS Z PIVOT ( MAX(IssuedBy) FOR ColNo IN ([B01], [B02], [B03], [B04], [B05], [B06], [B07], [B08], [B09], [B10], [B11], [B12], [B13], [B14], [B15], [B16], [B17], [B18], [B19], [B20], [B21], [B22], [B23]) ) as B
                  ON      D.PageNo = B.PageNo
                  LEFT OUTER JOIN tblContracts c
                  ON      c.fldMDGContractNo = @MDGContractNo
                  WHERE
                        (@PageNo IS NULL)
                        OR (D.PageNo = @PageNo)
      END
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.

721 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