Ordering results in UNION of tables

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 2005 Forums

Transact-SQL (2005)

Ordering results in UNION of tables

Author

Topic

almir
Starting Member

10 Posts

Posted - 2008-01-17 : 06:51:26

I have a SPROC that returns dataset from a union of the 2 tables. Recently we received new business requirements as follows:

T1
flag bit
dateAdded datetime

T2
flag bit
dateAdded datetime

Return a single dataset from the union of the 2 tables sorted in the following 4 categories:

1.Flag = false and today - dateAdded < 5 days
2.Flag = true and today - dateAdded < 5 days
3.Flag = false and today - dateAdded > 5 days
4.Flag = true and today - dateAdded > 5 days

What is the most efficient way to accomplish this task?

Any help will be greatly appreciated.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-17 : 06:58:10

quote:
Originally posted by almir

I have a SPROC that returns dataset from a union of the 2 tables. Recently we received new HOMEWORK ASSIGNMENTS as follows:

Fixed.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

pootle_flump

1064 Posts

Posted - 2008-01-17 : 07:00:26

When you day datadded < 5 days do you mean the difference between dateadded and today is less than 5 days?

Also, within those groups you require no further ordering yes?

pootle_flump

1064 Posts

Posted - 2008-01-17 : 07:02:21

Did you check his other posts? Doesn't look like his MO to me....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-17 : 07:04:16

sure reads like one tho. never seen a business requirement that told me to return a single dataset, just the data that was needed.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

almir
Starting Member

10 Posts

Posted - 2008-01-17 : 07:09:29

Sorry guys I meant difference between dateadded and today less than 5 days, the post above edited. The sql query is actually much more complex than this and the sproc I wrote does the job but executes really slowly.

I am not a DBA, I know enough T-Sql to get by, so I apologise if anyone is offended.

pootle_flump

1064 Posts

Posted - 2008-01-17 : 07:58:32

[code]ORDER BY
CASE WHEN DATEDIFF(d, DATEADD(d, DATEDIFF(d, 0, dateAdded), 0), DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) >= 5 THEN 1 END
, flag[/code]

almir
Starting Member

10 Posts

Posted - 2008-01-17 : 08:21:06

Thank you pootle flump that is exactly what I need.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-17 : 09:03:26

Maybe simpler?

ORDER BY	CASE
			WHEN DATEDIFF(DAY, dateAdded, GETDATE()) > 5 THEN 1
			ELSE 0
		END,
		Flag

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2008-01-17 : 09:34:28

I wasn't sure what the rule might be for handling time....

almir
Starting Member

10 Posts

Posted - 2008-01-17 : 10:35:08

I have what I need - Case/End construct now I can play with it until I get it right coz there are 5 variables to build in.

Thank you once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources