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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

T-sql Pivoting Problem for drawing issue register

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:

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)


and create 3 derived tables with the magic virtual IssueNo:




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
  • 2
1 Solution
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?
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 

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?
DRG-0000000001 Rev1 Dated ...
DRG-0000000001 Rev2 Dated ...

or does [fldDrawingRevision] contains all "parts". If it does please explain those parts.
czwgAuthor Commented:
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

            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

                        @DocCount = COUNT(1)
                        (SELECT DISTINCT
                                    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
                                    t.fldMDGContractNo = @MDGContractNo
                        ) x

                        @RecCount = COUNT(1)
                        (SELECT DISTINCT
                                    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
                                    t.fldMDGContractNo = @MDGContractNo
                        ) x

                        @DocCount AS DwgsIssued
                     ,@RecCount AS Recipients
                     ,38 - @RecCount AS DwgsPerSheet
                     ,c.fldMDGContractNo AS MDGContractNo
                                 ,'D' + FORMAT(Col, '00') AS ColNo
                        ) 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
                                             ,'R' + FORMAT(Col, '00') AS ColNo
                                 ) 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
                                             ,'B' + FORMAT(Col, '00') AS ColNo
                                 ) 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
                        (@PageNo IS NULL)
                        OR (D.PageNo = @PageNo)

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now