sorting data for reporting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sorting data for reporting

Author

Topic

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-08-25 : 16:44:44

Hello all, I'm new to the forum -- good stuff on here! Please forgive me being such a n00b; hope someone can help me out.

We're using MSSQL 2000, and need to generate a report that will pull similar data from 2 related tables and order it accordingly. Please don't raise a fuss about data types, design etc; this is from a canned app and we can't change the design...

Here are the tables (non-relevant columns removed), we'll call them "primary" and "related".

CREATE TABLE [primary] (
[primary_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
[order_no] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,
[subject_no] [smallint] NOT NULL ,
[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_date] [datetime] NOT NULL

CREATE TABLE [related] (
[related_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[subject_no] [smallint] NOT NULL ,
[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_date] [datetime] NOT NULL ,
[primary_id] [int] NULL

primary_id is primary key of table "primary"
related_id is primary key of table "related"
primary_id is foreign key relating 1:M between "primary" and "related"

Here's what we need to report:

- given order_no and subject_no,
- return file_type, file_no, file_date for all matching primary and related records;
- sort so that primary records are listed by date, with all related records for each primary record directly beneath the primary record, and sorted by date

That's kind of hard to say, so here's an illustration:

given the following data:

[table primary]
primary_id  order_no  subject_no  file_type  file_no  file_date
----------  --------  ----------  ---------  -------  ----------
1           1234      2           typeA      654      2003-01-16
6           1234      2           typeB      21       2004-08-11
102         1234      2           type4      762      2004-06-30

[table related]
related_id  order_no  subject_no  file_type  file_no  file_date   primary_id
----------  --------  ----------  ---------  -------  ----------  ----------
8           1234      2           typeC      834      2003-10-02  1
9           1234      2           type1      1023     2003-07-14  1
14          1234      2           type2      94       2005-07-12  6

The result would have to come out like this (columns in parens not shown to user):

(source)  (primary_id)  file_type  file_no  file_date
--------  ------------  ---------  -------  ---------
primary   1             typeA      654      2003-01-16
related   1             type1      1023     2003-07-14
related   1             typeC      834      2003-10-02
primary   102           type4      762      2004-06-30
primary   6             typeB      21       2004-08-11
related   6             type2      94       2005-07-12

file_date for primary records SHOULD always be earlier than file_date for any of its related records. It may be necessary to sort related records below each primary record in ascending or descending order.

My knowledge of SQL (and Transact-SQL) is limited, and I have not been able to come up with a way to accomplish this. Any help would be greatly appreciated!

Regards,

Daniel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-25 : 17:07:18

Try this:


select
	source = 'primary',
	primary_id,
	file_type,
	file_no,
	file_date
from
	primary
union all
select
	source = 'related',
	primary_id,
	file_type,
	file_no,
	file_date
from
	related
order by
	2, 1, 5

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-25 : 17:12:08

This is what I came up with:


SELECT 'primary' AS 'source', primary_id, file_type, file_no, file_date
FROM [primary]
UNION ALL
SELECT 'related' AS 'source', primary_id, file_type, file_no, file_date
FROM related
WHERE primary_id IN (SELECT primary_id FROM [primary])
ORDER BY primary_id, file_date

Tara

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-08-25 : 18:10:45

Michael and Tara,

Thanks for the quick responses!

Unfortunately we're not there yet..you see, records in primary may not be inserted in order by date. So if you sort first by primary_id you will get some out of date order.

Let me try to say this again in natural language since I don't know how to write the SQL. If you look at the example again you will see that the results are sorted (by hand, by me) as they need to appear for each report based on order_no and subject_no.

First, list primary records sorted by date. Then, under each primary record, list its related records (if any) sorted by date. So it is not as simple as order by 1, 2, 3...

Thanks,

Daniel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-25 : 19:01:16

How about this then:


SELECT source, primary_id, file_type, file_no, file_date
FROM
(
	SELECT 'primary' AS 'source', primary_id, file_type, file_no, file_date
	FROM [primary]
	UNION ALL
	SELECT 'related' AS 'source', primary_id, file_type, file_no, file_date
	FROM related
	WHERE primary_id IN (SELECT primary_id FROM [primary])
) t
ORDER BY primary_id, source, file_date

The only difference between your result set and mine is where the 102 row appears. We are sorting an integer column, so if you want 102 to appear with the other 1s, then you'll need to convert to varchar for that.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-25 : 19:03:15

In case anyone else wants to play along, here's the code:



SET NOCOUNT ON

CREATE TABLE [primary] (
[primary_id] [int] NOT NULL ,
[order_no] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,
[subject_no] [smallint] NOT NULL ,
[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_date] [datetime] NOT NULL)

CREATE TABLE [related] (
[related_id] [int] NOT NULL ,
[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[subject_no] [smallint] NOT NULL ,
[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_date] [datetime] NOT NULL ,
[primary_id] [int] NULL)

INSERT INTO [primary] VALUES(1,1234,2,'typeA',654,'2003-01-16')
INSERT INTO [primary] VALUES(6,1234,2,'typeB',21,'2004-08-11')
INSERT INTO [primary] VALUES(102,1234,2,'type4',762,'2004-06-30')

INSERT INTO [related] VALUES(8,1234,2,'typeC',834,'2003-10-02', 1)
INSERT INTO [related] VALUES(9,1234,2,'type1',102,'2003-07-14', 1)
INSERT INTO [related] VALUES(14,1234,2,'type2',94,'2005-07-12', 6)

SELECT source, primary_id, file_type, file_no, file_date
FROM
(
	SELECT 'primary' AS 'source', primary_id, file_type, file_no, file_date
	FROM [primary]
	UNION ALL
	SELECT 'related' AS 'source', primary_id, file_type, file_no, file_date
	FROM related
	WHERE primary_id IN (SELECT primary_id FROM [primary])
) t
ORDER BY primary_id, source, file_date

DROP TABLE [primary], related

Daniel,

If the query that I posted isn't correct, please provide extra sample data to show where it is going wrong. Please put it in the same format as shown above with INSERT INTO statements so that we can run this on our own machines. If you do provide extra sample data, please also provide the expected result set that goes along with it.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-25 : 19:12:05

Tara, thanks for the DDL & DML.

Now, to get the result he has, don't you just need to change your last ORDER BY to:
ORDER BY file_date, primary_id, source

sorting by File Date first?

EDIT: OOPS! Nope. That was just a fluke of the test data dates that made it work... Back to the drawing board.
---------------------------
EmeraldCityDomains.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-25 : 19:15:35

Yes that produces his expected result set, but I thought that it just happened that way as a coincidence based upon the sample data. It seems to me from his explanation that it was by primary_id first, then by source to get primary first, then by date on the related rows.



SELECT source, primary_id, file_type, file_no, file_date
FROM
(
	SELECT 'primary' AS 'source', primary_id, file_type, file_no, file_date
	FROM [primary]
	UNION ALL
	SELECT 'related' AS 'source', primary_id, file_type, file_no, file_date
	FROM related
	WHERE primary_id IN (SELECT primary_id FROM [primary])
) t
ORDER BY file_date, primary_id, source

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-25 : 19:19:39

You're right about the data fluke, but I got the impression that 102 had to come before 6 because the file date on primary for 102 is less than the file date for primary on 6.

By the way, Daniel, are these ONLY related by Primary ID? What about the Order Number and Subject Number? Do they need to be grouped by those, too? Do those override date sorting?

---------------------------
EmeraldCityDomains.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-25 : 19:24:40

Here we go...


SET NOCOUNT ON

CREATE TABLE [primary] (
[primary_id] [int] NOT NULL ,
[order_no] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,
[subject_no] [smallint] NOT NULL ,
[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_date] [datetime] NOT NULL)

CREATE TABLE [related] (
[related_id] [int] NOT NULL ,
[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[subject_no] [smallint] NOT NULL ,
[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[file_date] [datetime] NOT NULL ,
[primary_id] [int] NULL)

INSERT INTO [primary] VALUES(1,1234,2,'typeA',654,'2003-01-16')
INSERT INTO [primary] VALUES(6,1234,2,'typeB',21,'2004-08-11')
INSERT INTO [primary] VALUES(102,1234,2,'type4',762,'2004-06-30')

INSERT INTO [related] VALUES(8,1234,2,'typeC',834,'2003-10-02', 1)
INSERT INTO [related] VALUES(9,1234,2,'type1',1023,'2003-07-14', 1)
INSERT INTO [related] VALUES(14,1234,2,'type2',94,'2005-07-12', 6)
INSERT INTO [related] VALUES(17,1234,2,'QQQ',999,'2005-08-25', 1)

SELECT t.source, t.primary_id, t.file_type, t.file_no, t.file_date
FROM
(
	SELECT 'primary' AS 'source', primary_id, file_type, file_no, file_date, file_date as PrimaryFileDate
	FROM [primary]
	UNION ALL
	SELECT 'related' AS 'source', r.primary_id, r.file_type, r.file_no, r.file_date, p.file_date as PrimaryFileDate
	FROM related r
	JOIN [primary] p ON r.primary_id = p.primary_id
	--WHERE primary_id IN (SELECT primary_id FROM [primary])
) t
ORDER BY t.PrimaryFileDate, t.primary_id, t.source, t.file_date

DROP TABLE [primary], related

Note that I added a QQQ record that is out of date order for PrimaryID 1 to override the test data fluke. Also had to add Primary.file_Date to the UNIONs so that it could be used for sorting.

---------------------------
EmeraldCityDomains.com

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-08-26 : 09:41:07

Hey folks,

Tara -- thanks for creating and posting the DML and DDL. After posting I figured out how to generate the "create table" scripts, but I don't know how to create the "insert into" scripts from existing data...

AjarnMark -- order number and subject number are used only for filtering. So, when we generate a report, we'll include:

WHERE order_no = 'someval'
AND subject_no = someval

Ideally I'll put all this in a stored procedure and just call it with values for order number and subject number to have it return the report data.

Sorry about the data fluke! I just dashed off some sample data by hand and put it up. From the sample dataset, yes, 102 has to come before 6 because its file_date is earlier.

I'm going through your posts now to try your suggestions.

Thanks!

Daniel

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-26 : 09:58:02

oo... 'insert into' scripts:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-08-26 : 10:38:10

OK, I've made a couple of (mostly cosmetic) changes to the DML and DDL. First, I realized no table should be named "primary" when I tried:

SELECT * FROM primary

So I've renamed the example tables "testPrimary" and "testRelated". Scripts follow:


SET NOCOUNT ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testPrimary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testPrimary]
GO

CREATE TABLE [dbo].[testPrimary] (
	[primary_id] [int] NOT NULL ,
	[order_no] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,
	[subject_no] [smallint] NOT NULL ,
	[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_date] [datetime] NOT NULL 
)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testRelated]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testRelated]
GO

CREATE TABLE [dbo].[testRelated] (
	[related_id] [int] NOT NULL ,
	[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
	[subject_no] [smallint] NOT NULL ,
	[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_date] [datetime] NOT NULL ,
	[primary_id] [int] NULL 
)
GO

INSERT INTO [testPrimary] VALUES(1,1234,2,'typeA','654.15','2003-01-16')
INSERT INTO [testPrimary] VALUES(6,1234,2,'typeB','21-F46','2004-08-11')
INSERT INTO [testPrimary] VALUES(102,1234,2,'type4','762','2004-06-30')

INSERT INTO [testRelated] VALUES(8,1234,2,'typeC','834.23','2003-10-02',1)
INSERT INTO [testRelated] VALUES(9,1234,2,'type1','1023','2003-07-14',1)
INSERT INTO [testRelated] VALUES(14,1234,2,'type2','94.z0','2005-07-12',6)
INSERT INTO [testRelated] VALUES(17,1234,2,'QQQ','999','2005-08-25',1)
INSERT INTO [testRelated] VALUES(39,1234,2,'type1','156.441','2003-02-12',6)

Note that file_no is actually VARCHAR -- you had them as numerics in your import which gave some funny values. These file "numbers" can actually be alphanumeric or contain symbols like '.' or '-', etc., so I modified the sample data accordingly, as you will see above. Also, just in case there is ever a "related" record with a file_date earlier than its associated "primary" filing, I added a record to testRelated like that (for primary_id = 6).

Then I ran your queries to see how things come out!

Tara, yours orders results by file_date, but messes up the grouping -- try your query again and look at the result set. Take a look:


source  primary_id  file_type  file_no  file_date                                              
------- ----------- ---------- -------- -----------------------
primary 1           typeA      654.15   2003-01-16 00:00:00.000
related 6           type1      156.441  2003-02-12 00:00:00.000
related 1           type1      1023     2003-07-14 00:00:00.000
related 1           typeC      834.23   2003-10-02 00:00:00.000
primary 102         type4      762      2004-06-30 00:00:00.000
primary 6           typeB      21-F46   2004-08-11 00:00:00.000
related 6           type2      94.z0    2005-07-12 00:00:00.000
related 1           QQQ        999      2005-08-25 00:00:00.000

You see, there is a related record for primary_id 6 in the middle of the set for primary_id 1, and one of the related records for primary_id 1 is all the way at the bottom.

AjarnMark, your query is very cool! I added t.PrimaryFileDate to the results so I could see exactly what it was doing. I never would have thought to use an "extra" column like that. That works great.


source  PrimaryFileDate         primary_id  file_type  file_no  file_date              
------- ----------------------- ----------- ---------- -------- -----------------------
primary 2003-01-16 00:00:00.000 1           typeA       654.15   2003-01-16 00:00:00.000
related 2003-01-16 00:00:00.000 1           type1       1023     2003-07-14 00:00:00.000
related 2003-01-16 00:00:00.000 1           typeC       834.23   2003-10-02 00:00:00.000
related 2003-01-16 00:00:00.000 1           QQQ         999      2005-08-25 00:00:00.000
primary 2004-06-30 00:00:00.000 102         type4       762      2004-06-30 00:00:00.000
primary 2004-08-11 00:00:00.000 6           typeB       21-F46   2004-08-11 00:00:00.000
related 2004-08-11 00:00:00.000 6           type1       156.441  2003-02-12 00:00:00.000
related 2004-08-11 00:00:00.000 6           type2       94.z0    2005-07-12 00:00:00.000

Now I need to make sure we return only records for a particular order number and subject number. I'll try writing that part and see what happens.

Thank you,

Daniel

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-08-26 : 11:54:15

OK, so far so good -- plan to use this to create a view, then select from the view using a stored procedure and filtering by order_no and subject_no.


SELECT
			t.order_no
		,	t.subject_no
		,	t.source
		,	t.sort_date
		,	t.primary_id
		,	t.file_type
		,	t.file_no
		,	t.file_date
FROM
(	SELECT
				order_no
			,	subject_no
			,	'primary'	AS	source
			,	primary_id
			,	file_type
			,	file_no
			,	file_date
			,	file_date	AS	sort_date
	FROM
			testPrimary
	UNION ALL
	SELECT
				r.order_no
			,	r.subject_no
			,	'related'	AS	source
			,	r.primary_id
			,	r.file_type
			,	r.file_no
			,	r.file_date
			,	p.file_date	AS	sort_date
	FROM
			testRelated r
	JOIN
			testPrimary p
			ON
			r.primary_id = p.primary_id
) t
ORDER BY
			t.sort_date
		,	t.primary_id
		,	t.source
		,	t.file_date

Look OK to you? This is based on AjarnMark's query, just formatted for readability. (My tab size is set to 4, so it may look a little more spaced out here than intended!)

Daniel

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-08-26 : 12:53:37

The next bit is a stored procedure to return the desired data (and format the date as MM/DD/YYYY per user request). Here goes:


SET NOCOUNT ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[testReportSP]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE	testReportSP
(
		@OrderNo	VARCHAR(10)		-- order number to be used as filter criteria
	,	@SubjectNo	INT				-- subject number to be used as filter criteria
)
AS
SELECT
			file_type
		,	file_no
		,	CONVERT(varchar,file_date,101)	AS	file_date
FROM
			testReportView
WHERE
			order_no = @OrderNo
AND
			subject_no = @SubjectNo

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Now to use it for our sample data:


EXECUTE testReportSP '1234', 2

Et voila! We have our result set exactly as desired:


file_type  file_no  file_date 
---------- -------- ----------
typeA      654.15   01/16/2003
type1      1023     07/14/2003
typeC      834.23   10/02/2003
QQQ        999      08/25/2005
type4      762      06/30/2004
type1      156.441  02/12/2003
typeB      21-F46   08/11/2004
type2      94.z0    07/12/2005

What do you think? Is this a good (read: logical, efficient, reusable, etc.) way to handle this need?

Thank you,

Daniel

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-30 : 14:28:23

Daniel, this looks pretty good. Although I probably would have skipped the step of creating a View and just put the whole thing in a stored procedure. Or maybe in a table-valued function if you need to work with the results in other JOIN clauses or processing. It's not wrong to use the view, just may not be necessary.

---------------------------
EmeraldCityDomains.com

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-06 : 10:08:02

Hello AjarnMark,

Tried putting the whole thing in a stored procedure, but the sort order comes out wrong. Just based it on my view, returned only the desired columns, and put where clauses in as seemed appropriate. Can't see why it would return different results than the view. Can you? Give it a try and you'll see what I mean -- the 3rd and 4th records are reversed from the desired order.

I've included commented lines which you can uncomment to see the columns used for sorting. Viewing the full result set clearly demonstrates that the final ORDER BY is not followed as expected.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[testReportSP]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE	testReportSP
(
		@OrderNo	VARCHAR(10)		-- order number to be used as filter criteria
	,	@SubjectNo	INT				-- subject number to be used as filter criteria
)
AS

SELECT
--			t.order_no
--		,	t.subject_no
--		,	t.source
--		,	t.sort_date
--		,	t.primary_id
			t.file_type
		,	t.file_no
		,	CONVERT(VARCHAR,t.file_date,101)	AS	file_date
FROM
(	SELECT
				order_no
			,	subject_no
			,	'primary'	AS	source
			,	primary_id
			,	file_type
			,	file_no
			,	file_date
			,	file_date	AS	sort_date
	FROM
			testPrimary
	WHERE		order_no	=	@OrderNo
	AND			subject_no	=	@SubjectNo
	UNION ALL
	SELECT
				r.order_no
			,	r.subject_no
			,	'related'	AS	source
			,	r.primary_id
			,	r.file_type
			,	r.file_no
			,	r.file_date
			,	p.file_date	AS	sort_date
	FROM
			testRelated r
	JOIN
			testPrimary p
			ON
			r.primary_id = p.primary_id
	WHERE		r.order_no		=	@OrderNo
	AND			r.subject_no	=	@SubjectNo
) t
ORDER BY
			t.sort_date
		,	t.primary_id
		,	t.source
		,	t.file_date

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This would of course be called the same way, using:

EXECUTE testReportSP '1234', 2

As for your other suggestion, I'm not familiar with table-valued functions..don't even know what they are!

Thank you,

Daniel

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-09-06 : 15:10:14

A table-valued function is just a user-defined function that returns a table instead of a single value. You can then reference that returned table anywhere that you would normally use a table or view. It's a convenient way to dynamically create a recordset that requires more code than a view allows.

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources