asked on 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:
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
and create 3 derived tables with the magic virtual IssueNo:
tblIssues
tblIssueRecipients
tblIssueDrawings
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]()
eg
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.
System UtilitiesMicrosoft Server AppsMicrosoft SQL Server
Last Comment
czwg
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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(@MDGContractN
--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
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(@MDGContractN
--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
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?