Noob question on joining results 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

General SQL Server Forums

New to SQL Server Programming

Noob question on joining results tables

Author

Topic

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 07:26:15

Hi All,

I have two separate queries:

1: Returns a list of properties showing their 'Date on Market' and 'Date Sold'

2: Returns a list of branches showing their 'Average Days On Market'

I'm tryin to combine these so my results will show a list of properties with 'Date on Market' and 'Date Sold' but have this sectioned by Branch, and at the bottom of each section, show the 'Average Days On Market' as a summary of this branch.

Here is a couple of images showing the current data results.

Thanks in advance.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 07:38:20

THat type of summary isn't exactly simply in SQL. You can join the data on OFFICE = BRANCH which would put the AverageDaysOnMarket in the same ROW as the data in Result1. There isn't really a way to do this in a Select statement (not easily anyway).

It would be theoretically possible to join them, and use a combination of row_number() functions to force the order using a UNION instead of a Join, but that might be tricky. Presentational things are best handled on the presentation layer.

Poor planning on your part does not constitute an emergency on my part.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 07:45:19

Thanks for the info.

Yes it sounds a bit too tricky/complicated for the level I'm at right now.

I guess it will hlave to be done at the front end, but I'm trying to learn crystal reports myself and have no idea about the SQL syntax in Crystal :(

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 07:57:53

Something like this "might" work for you...


Create table #office 
(office char(3) null,propertyinfo int not null, date1 datetime not null,date2 datetime not null)
Insert INTO #office 
SELECT 'ABN',1,'20101101','20101214' UNION
SELECT 'ABN',2, '20101012','20101201' UNION

SELECT 'BAC',1, '20101012','20101201' UNION
SELECT 'BAC',2, '20101012','20101201' 


SELECT Office,PropertyInfo,date1,date2,avgdays 
FROM
		(

				SELECT 0 as ordering,Office, PropertyInfo, date1,date2,AVGDays  =NULL
				FROM #office 
				
				UNION
				SELECT	1 as ordering,Office,NULL,NULL,NULL,
						AvgDays = Avg(DATEDIFF(d,date1,date2))
				FROM #office
				GROUP BY Office
		) t

ORDER BY Office,Ordering

DROP TABLE #office

results :


Office	PropertyInfo	date1	date2	avgdays
ABN	1	2010-11-01 00:00:00.000	2010-12-14 00:00:00.000	NULL
ABN	2	2010-10-12 00:00:00.000	2010-12-01 00:00:00.000	NULL
ABN	NULL	NULL	NULL	46
BAC	1	2010-10-12 00:00:00.000	2010-12-01 00:00:00.000	NULL
BAC	2	2010-10-12 00:00:00.000	2010-12-01 00:00:00.000	NULL
BAC	NULL	NULL	NULL	50

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 07:59:42

quote:
Originally posted by Johnathan

Thanks for the info.

Yes it sounds a bit too tricky/complicated for the level I'm at right now.

I guess it will hlave to be done at the front end, but I'm trying to learn crystal reports myself and have no idea about the SQL syntax in Crystal :(

Crystal would work for this, but yeah..the syntax is a PITA. See my above potential T-SQL methodology using UNION

Poor planning on your part does not constitute an emergency on my part.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 09:28:49

really not sure how that code works at all! :/

Would it help if I put up the code I have?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 09:33:03

quote:
Would it help if I put up the code I have?

Always.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 09:51:55

Date On Market against Date Sold (for each property):

SELECT pocode AS OfficeCode,
	   pcode AS PropertyCode,
	   phsename AS HouseName,
	   phseno AS HouseNumber,
	   paddress1 AS Address1,
	   paddress2 AS Address2,
	   paddress3 AS Address3,
	   ppostcode AS PostCode,
	   pforsdate AS DateOnMarket,
	   pexchdate AS DateSold
	   
FROM property
	   
WHERE ptype='S' AND synchDel='0' AND pexchdate BETWEEN '2010-11-01' AND '2010-11-30'


UNION ALL

 
(SELECT pocode AS OfficeCode,
		pcode AS PropertyCode,
	    phsename AS HouseName,
	    phseno AS HouseNumber,
	    paddress1 AS Address1,
	    paddress2 AS Address2,
	    paddress3 AS Address3,
	    ppostcode AS PostCode,
	    pforsdate AS DateOnMarket,
	    pexchdate AS DateSold

FROM proparch

WHERE ptype='S' AND synchDel='0' AND pexchdate BETWEEN '2010-11-01' AND '2010-11-30')
ORDER BY pocode

Date on Market against Date Sold (Average Days per Branch)

SELECT Branch, AVG(dys) AS AverageDaysOnMarket
FROM
(
SELECT pocode AS Branch,
	   dys = DATEDIFF(dd,pforsdate,pexchdate)
	   
FROM property
	   
WHERE ptype='S' and synchDel='0' and pexchdate between '2010-12-01' and '2010-12-30'


UNION ALL
 
(SELECT pocode AS Branch, 
		dys = DATEDIFF(dd, pforsdate, pexchdate)

FROM proparch

WHERE ptype='S' and synchDel='0' and pexchdate between '2010-12-01' and '2010-12-30')
)a
GROUP BY Branch

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 10:13:42

Give this a shot:

;WITH P(OfficeCode,PropertyCode,HouseName,HouseNumber,Address1,Address2,Address3,PostCode,DateOnMarket,DateSold) AS
(SELECT pocode AS OfficeCode,
	   pcode AS PropertyCode,
	   phsename AS HouseName,
	   phseno AS HouseNumber,
	   paddress1 AS Address1,
	   paddress2 AS Address2,
	   paddress3 AS Address3,
	   ppostcode AS PostCode,
	   pforsdate AS DateOnMarket,
	   pexchdate AS DateSold
FROM property
WHERE ptype='S' AND synchDel='0' AND pexchdate BETWEEN '2010-11-01' AND '2010-11-30'
UNION ALL
SELECT pocode AS OfficeCode,
	    pcode AS PropertyCode,
	    phsename AS HouseName,
	    phseno AS HouseNumber,
	    paddress1 AS Address1,
	    paddress2 AS Address2,
	    paddress3 AS Address3,
	    ppostcode AS PostCode,
	    pforsdate AS DateOnMarket,
	    pexchdate AS DateSold
FROM proparch
WHERE ptype='S' AND synchDel='0' AND pexchdate BETWEEN '2010-11-01' AND '2010-11-30')
SELECT P.*, AVG(DateDiff(dd, DateOnMarket, DateSold)) OVER (PARTITION BY OfficeCode) AverageDaysOnMarket
FROM P

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 10:37:58

Unfortunately, this is throwing the following error:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'OVER'.

:(

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 10:52:43

What version of SQL Server are you using?

If Crystal Reports turns out to be the problem, I suggest creating a view from this SQL and referencing it from Crystal.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 10:59:12

SQL Server 2008 R2.

I'm not entirely sure what you mean. I've tried presenting the data in Crystal, but don't fully understand how it works. I've tried copying across SQL from SQL Server into the Crystal Reports SQL Expression Fields but it never seems to like what I put in. Even with simple queries.

Maybe I need training using Crystal Reports!

Any Ideas?
Thanks again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 11:22:05

I doubt Crystal Reports will parse this SQL at all, and I don't suggest you let it process this query as-is. Can you run this query through Management Studio or some other query tool to validate that it works?

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 11:36:15

Hi,

It's management studio I am currently using! My two separate queries work fine though SQL server 2008 Management Studio. But the code that you provided above currently produces the error code I've shown.

I've gone through the syntax plenty times and can't see anything wrong with it at all... :(

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 11:41:45

Is your database compatibility set to 8.0? That might cause the problem.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 11:53:30

How do I check this?

Out database is written/run by another company so I'm not sure exactly how I would go checking this.

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-01-21 : 12:45:20

Johnathan,

While I too have divulged data on here for the purpose of providing sample data, I have always anonymised any identifiable data. From your screenshot I can see the date that someone's house was sold.

Though this information might be freely available on the Land Registry, personally I would still not be comfortable with such details about MY house being made open this way. I don't know exactly what misuse someone could potentially make of it (and for obvious reasons I'm going to keep my ideas on the matter to myself), but I'd ask that either you alter it, or the Moderators do (whether before or after your issue is resolved being up to them, and I hope it is resolved.)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 13:44:55

In Management Studio, right click the database and choose Properties, then look under the Options tab for compatibility level.

And I agree 100% with Jim, you should edit your post to remove or obscure that data. I can do that for you if you prefer, but I'd just remove the images.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-25 : 07:55:14

Thanks for the advice, have done now.

SQL Server 2000 (80) is the compatablilty level this database is set to. Could this be why the above query fails?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-25 : 10:06:04

Assuming you can't change the compatibility level:

SELECT D.*, S.AverageDaysOnMarket FROM (
SELECT pocode AS OfficeCode,
	   pcode AS PropertyCode,
	   phsename AS HouseName,
	   phseno AS HouseNumber,
	   paddress1 AS Address1,
	   paddress2 AS Address2,
	   paddress3 AS Address3,
	   ppostcode AS PostCode,
	   pforsdate AS DateOnMarket,
	   pexchdate AS DateSold
FROM property
WHERE ptype='S' AND synchDel='0' AND pexchdate BETWEEN '2010-11-01' AND '2010-11-30'
UNION ALL
SELECT pocode AS OfficeCode,
		pcode AS PropertyCode,
	    phsename AS HouseName,
	    phseno AS HouseNumber,
	    paddress1 AS Address1,
	    paddress2 AS Address2,
	    paddress3 AS Address3,
	    ppostcode AS PostCode,
	    pforsdate AS DateOnMarket,
	    pexchdate AS DateSold
FROM proparch
WHERE ptype='S' AND synchDel='0' AND pexchdate BETWEEN '2010-11-01' AND '2010-11-30') D
INNER JOIN (SELECT Branch, AVG(dys) AS AverageDaysOnMarket
FROM (SELECT pocode AS Branch, dys = DATEDIFF(dd,pforsdate,pexchdate)
FROM property
WHERE ptype='S' and synchDel='0' and pexchdate between '2010-12-01' and '2010-12-30'
UNION ALL
SELECT pocode AS Branch, dys = DATEDIFF(dd, pforsdate, pexchdate)
FROM proparch
WHERE ptype='S' and synchDel='0' and pexchdate between '2010-12-01' and '2010-12-30') a
GROUP BY Branch) S ON D.OfficeCode=S.Branch

Johnathan
Starting Member

30 Posts

Posted - 2011-01-25 : 11:55:49

Hi,

That code works great. Thank you very much again for the help.

S ON D.OfficeCode=S.Branch

Can I just ask for a little clarity on what this does? joining datasets D and S by Branch?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources