Sql query to retrieve data in described format

Question

Struggling to conceptualize this query. Any help will be appreciated.

Dataset

Table: Audit

Date   |Action  |PackageId
1/1/15 |Active  |1
1/2/15 |DeActive|1
11/3/16|Update  |2
12/3/16|Update  |2
13/3/16|Update  |2
14/3/16|Update  |2

Table: Package

Id|Name
1 |package1
2 |package2

Table: Item

Id|ItemName|PackageId
1 | item1  |1
2 | item2  |1
3 | item3  |1
4 | item4  |2
5 | item5  |2

The relationship between these tables is Audit.PackageID Is foreign key to Package.Id and Item.PackageId is foreign key to Pacakge.id

For the above data, I want to generate a report like this

Package.Name|Item.ItemName|Audit.Date|Audit.Action
package1    |  item1      | 1/1/15   | Active
package1    |  item2      | 1/2/15   | DeActive
package1    |  item3      | NULL     | NULL
package2    |  item4      | 11/3/16  | update
package2    |  item5      | 12/3/16  | update
package2    |  NULL       | 13/3/16  | update
package2    |  NULL       | 14/3/16  | update 

basically the item or audit information is not duplicated and package information is duplicated if item/audit information is more than number of packages. Hope it makes sense.

I basically need to write a stored procedure that will return a dataset as described above. This result is then fed into a report parser that will replace all NULL with blanks and generate an Excel report. The database is SQL-Server-2000.

Answer 1

Essentially what you are trying to do is fit two reports into one table; you have the Package Items report and the Package Audit Report. Typically you do this at the reporting layer with sub-reports.

If you have to do it this way, you'll have to generate the two reports into tables with keys so that they can be joined together. Since you are using SQL 2000, you don't have the ROW_NUMBER() function to generate some sequence numbers... which is unfortunate. So instead you can create temporary tables with incrementing identity fields.

Here's the schema you provided:

CREATE TABLE #Audit
    (Date varchar(7), Action varchar(8), PackageId int)
;

INSERT INTO #Audit
    (Date, Action, PackageId)
VALUES
    ('1/1/15', 'Active', 1),
    ('1/2/15', 'DeActive', 1),
    ('11/3/16', 'Update', 2),
    ('12/3/16', 'Update', 2),
    ('13/3/16', 'Update', 2),
    ('14/3/16', 'Update', 2)
;

CREATE TABLE #Package
    (Id int, Name varchar(8))
;

INSERT INTO #Package
    (Id, Name)
VALUES
    (1, 'package1'),
    (2, 'package2')
;

CREATE TABLE #Item
    (Id int, ItemName varchar(5), PackageId int)
;

INSERT INTO #Item
    (Id, ItemName, PackageId)
VALUES
    (1, 'item1', 1),
    (2, 'item2', 1),
    (3, 'item3', 1),
    (4, 'item4', 2),
    (5, 'item5', 2)
;

Then you create two more tables to hold your two separate reports and populate them. The report tables need an identity field so that it generates a unique number for each row in the report:

create table #PackageItemsReport 
(   PackageItemsId int identity(1,1), 
    PackageId int, 
    PackageName varchar(8), 
    ItemName varchar(5)
)

insert into #PackageItemsReport
(PackageId,PackageName,ItemName)
select #Package.Id, #package.Name, #item.ItemName 
from #Package
join #Item
on #Package.Id = #item.PackageId
order by #Package.Id, #item.ItemName 

create table #PackageAuditReport 
(   PackageAuditId int identity(1,1), 
    PackageID int, 
    PackageName varchar(8), 
    AuditDate varchar(7), 
    AuditAction varchar(8)
)

insert into #PackageAuditReport
(PackageID,PackageName,AuditDate,AuditAction)
select #Package.Id, #Package.Name, #Audit.Date, #Audit.Action 
from #Package
join #Audit
on #Audit.PackageId = #Package.Id
order by #Package.Id, #Audit.Date, #Audit.Action 

Then you need to join the two reports together using the package ID and the row numbers generated:

select ISNULL(PackageItemsReport.PackageName, PackageAuditReport.PackageName) Name,
    ItemName,
    AuditDate,
    AuditAction
from
(
    select #PackageItemsReport.*, PackageItemsId - MinPackageItemsId RowNum
    from #PackageItemsReport
    join
    (
    select PackageID, MIN(PackageItemsId) MinPackageItemsId
    From #PackageItemsReport
    group by PackageID
    ) MinPackageItemsIds
    on #PackageItemsReport.PackageID = MinPackageItemsIds.PackageID
) PackageItemsReport
full join
(
    select #PackageAuditReport.*, PackageAuditId - MinPackageAuditId RowNum
    from #PackageAuditReport
    join
    (
    select PackageID, MIN(PackageAuditId) MinPackageAuditId
    From #PackageAuditReport
    group by PackageID
    ) MinPackageAuditIds
    on #PackageAuditReport.PackageID = MinPackageAuditIds.PackageID
) PackageAuditReport
on PackageItemsReport.PackageID = PackageAuditReport.PackageID
    and PackageItemsReport.RowNum = PackageAuditReport.RowNum
order by ISNULL(PackageItemsReport.PackageID, PackageAuditReport.PackageID),
    ISNULL(PackageItemsReport.RowNum, PackageAuditReport.RowNum)

It's messy... thank goodness the ROW_NUMBER() function was added after SQL Server 2000. It's much easier now.