1

my dilemma looks like this:

     FileName    Version    Type    Doc_Number    Owner    Request    Date
1.   ECN 0001       3        ECN      NULL        NULL      NULL      NULL
2.   ECN 0001       1        NULL     NULL        NULL      NULL      2017-08-14
3.   ECN 0001       2        NULL     NULL        NULL      NULL      08/14/17
4.   ECN 0001       2        NULL     NULL        NULL      NULL      NULL
5.   ECN 0001       2        NULL     NULL        JOHN      NULL      NULL
6.   ECN 0001       1        NULL     Form        NULL      NULL      NULL
7.   ECN 0001       2        NULL     0001        NULL      NULL      NULL
8.   ECN 0002 ect...
N.   ECN 9999 ect...

The list of files is long so the solution should take into account changing filenames, I wish to condense duplicate filenames into one row. Sometimes the data will have more than one value in a row or column for the same file.

A rule is that there can only be a maximum of one column value per version as shown above, for example ECN 0001 version 1 only has one date listed, but version 2 can have another date listed. Version 3 would be the latest version but the only change made from v2 to v3 was the type.

I want the new list to look like this:

     FileName    LatestVersion    Type    Doc_Number    Owner    Request    Date
1.   ECN 0001          3          ECN       0001        JOHN      NULL      08/14/17
2.   ECN 0002          6          PGT       0002        BOB       JAKE      08/14/17
3.   ECN 0003          2          ECN       0003        KYLE      BOB       08/14/17
4.   ECN 0004          4          ECN       0001        TOM       BILL      08/14/17
5.   ECN 0005          8          ECN       0001        KATE      KELVIN    08/14/17
6.   ECN 0006          2          ECN       0001        STEVE     JOHN      08/14/17
7.   ECN 0007          5          ECN       0001        JOHN      STEVE     08/14/17
n.   ECN 9999          99         EXR       9999        CHERYL    CARL      09/14/26

The main idea is that the new list is condensed to one row per file with all info from the latest version, it may be that some values are unchanged since version 1 or 2 and so should appear still in the latest version table. If more than one value is present per file then the latest should be used. It is also possible that none of the versions had the info present as in the case of ECN 0001 Request.

I thought of using max, but the newest value is not always the largest value, so the decision really needs to take the version number of the data into consideration when trimming off older data.

This is my current code if it helps:

select FileName, Version,
(case when VariableName = 'Document Type' then  ValueText end) as Type,
(case when VariableName = 'Document Number' then  ValueText end) as Doc_Number,
(case when VariableName = 'Owner' then  ValueText end) as Owner,
(case when VariableName = 'Requested By' then  ValueText end) as Request,
(case when VariableName = 'Date Written' then  ValueText end) as Date
FROM
(
SELECT Documents.Filename,VariableValue.ValueText,VariableValue.Version,Variable.VariableName
FROM Documents INNER JOIN VariableValue ON Documents.DocumentID = VariableValue.DocumentID INNER JOIN Variable ON VariableValue.VariableID = Variable.VariableID
WHERE Filename LIKE 'ECN_____.pdf' AND Filename <> 'ECN Form.PDF'
) t
Order by Filename
Jsaville
  • 11
  • 1
  • 1
    what flavor of sql are you working with? – S3S Aug 17 '17 at 13:20
  • 1
    Tag your question with the database you are using. – Gordon Linoff Aug 17 '17 at 13:52
  • Hi I tried to tag it with SQL Server 12.0.5203.0 – Jsaville Aug 17 '17 at 15:55
  • I believe the flavor is TSQL... This is the sql server for Solidworks EPDM. – Jsaville Aug 17 '17 at 15:56
  • Are you really looking for pure SQL solution? because I think you may not get one... and you can implement trivial script to do this for you in a sane way... – user3012759 Aug 17 '17 at 16:05
  • I would prefer to do pure sql, I could take care of this in excel even, but the report generation software we use only takes sql. Any other language would require some sort of post process on the report. – Jsaville Aug 17 '17 at 16:32
  • Can it be run in multiple statements, or does it have to be a single statement? I have done this using temporary tables – AndrewK Aug 17 '17 at 21:40
  • i believe multiple statements would be ok, it couldn't be too hard to find a way to make multiple statements work. – Jsaville Aug 21 '17 at 12:09
  • Does the PDM search tool give you the results you're looking for? If so, there's a method to get the SQL query used to display the results. – ehcanadian Aug 22 '17 at 03:08

1 Answers1

0

The only option I see for pure sql solution is user defined aggregate function. Here's a sample they provide.

This does involve writing code in clr compatible language and copying result dll to sql server folder, but from viewpoint of the application connecting to sql server it will look like pure sql solution.

Vlad
  • 793
  • 6
  • 15
  • This looks pretty powerful, thanks for pointing me in this direction, ill see what i can learn about it and report back. – Jsaville Aug 21 '17 at 12:15