Combining 2 SQL's... - 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 2005 Forums

Transact-SQL (2005)

Combining 2 SQL's...

Author

Topic

Marteijn
Starting Member

28 Posts

Posted - 2009-12-14 : 07:28:24

Hi,

I've got 2 SQL's I'd like to combine.

SQL 1 contains a 'total list' , SQL 2 contains
the 'finished' records.
(SQL 1 about 36,000 records, SQL 2 24,000 records).

SQL 1:

SELECT a.WOZOBJEKTNR, a.DTB, a.DTE, a.WIJKCODE, a.BUURTCODE, a.STRAATNAAM, a.HUISNR, a.HUISLT, a.HUISTOEV, a.HUISAAND, a.CODE_WGEB,
a.SOORT_OBJ_GEM, a.SOORT_OBJ_GT, a.VWAARDE_WOZ, a.VWAARDE_HFDGEB, a.VWAARDE_KAVEL, a.VWAARDE_BIJGEB, a.VWAARDE_OZB,
a.VWAARDE_OZBG, a.RDN_VSL_WOZ_OZB, a.TAXATIEDATUM, a.TAXATEUR, a.CODE_BLOKKEREN, a.HOOFDCODE, a.TOESTANDSPEILDATUM,
a.WRDINGVOORSCHR, b.PEILJAAR, b.STAT_BWVG, b.STAT_FOTO, b.STAT_WRDB, b.STAT_INVT
FROM fact_GEO_GT_VOVERZICHT AS a INNER JOIN
fact_GEO_GT_STATUS AS b ON a.WOZOBJEKTNR = b.WOZOBJEKTNR
WHERE (a.DTE = CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (b.PEILJAAR = 2009) AND (a.SOORT_OBJ_GEM NOT IN ('9100', '9995')) AND
(a.SOORT_OBJ_GT <> 31)

SQL 2:

SELECT c.WOZOBJEKTNR, c.DTB, c.DTE, c.WIJKCODE, c.BUURTCODE, c.STRAATNAAM, c.HUISNR, c.HUISLT, c.HUISTOEV, c.HUISAAND, c.CODE_WGEB,
c.SOORT_OBJ_GEM, c.SOORT_OBJ_GT, c.VWAARDE_WOZ, c.VWAARDE_HFDGEB, c.VWAARDE_KAVEL, c.VWAARDE_BIJGEB, c.VWAARDE_OZB,
c.VWAARDE_OZBG, c.RDN_VSL_WOZ_OZB, c.TAXATIEDATUM, c.TAXATEUR, c.CODE_BLOKKEREN, c.HOOFDCODE, c.TOESTANDSPEILDATUM,
c.WRDINGVOORSCHR, d.PEILJAAR, d.STAT_BWVG, d.STAT_FOTO, d.STAT_WRDB, d.STAT_INVT
FROM fact_GEO_GT_VOVERZICHT AS c INNER JOIN
fact_GEO_GT_STATUS AS d ON c.WOZOBJEKTNR = d.WOZOBJEKTNR
WHERE (c.DTE = CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (d.PEILJAAR = 2009) AND (c.VWAARDE_WOZ > 0) AND
(NOT (c.TAXATIEDATUM IS NULL)) AND (NOT (c.TAXATEUR IS NULL)) AND (c.CODE_BLOKKEREN = 0) AND (d.STAT_WRDB > 2)

What I'd like to see:

The total amount of records from SQL 1 and the total amount of records from SQL 2.
And behind those totals, the % (SQL 2 contains how much % from SQL 1)
(24,000 / 36,000) * 100

I thank you already!!
Marteijn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 07:50:16

Try

select sql1.counting,sql2.counting,100.0*sql1.counting/sql2.counting as percentage from
(
select
(
SELECT count(*) as counting
FROM fact_GEO_GT_VOVERZICHT AS a INNER JOIN
fact_GEO_GT_STATUS AS b ON a.WOZOBJEKTNR = b.WOZOBJEKTNR
WHERE (a.DTE = CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (b.PEILJAAR = 2009) AND (a.SOORT_OBJ_GEM NOT IN ('9100', '9995')) AND
(a.SOORT_OBJ_GT <> 31)
) as sql1,
(
SELECT count(*) as counting
FROM fact_GEO_GT_VOVERZICHT AS c INNER JOIN
fact_GEO_GT_STATUS AS d ON c.WOZOBJEKTNR = d.WOZOBJEKTNR
WHERE (c.DTE = CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (d.PEILJAAR = 2009) AND (c.VWAARDE_WOZ > 0) AND
(NOT (c.TAXATIEDATUM IS NULL)) AND (NOT (c.TAXATEUR IS NULL)) AND (c.CODE_BLOKKEREN = 0) AND (d.STAT_WRDB > 2)
) as sql2
) as t

Madhivanan

Failing to plan is Planning to fail

Marteijn
Starting Member

28 Posts

Posted - 2009-12-14 : 08:36:04

Hi Madhivanan,

I get an error:

There is an error in the query. The multi-part identifier "sql1.counting" could not be bound.
The multi-part identifier "sql2.counting" could not be bound.
The multi-part identifier "sql1.counting" could not be bound.
The multi-part identifier "sql2.counting" could not be bound.

I'm using the SQL in Visual Studio 2005...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 08:50:55

Try this

select sql1_counting,sql2_counting,100.0*sql1_counting/sql2_counting as percentage from
(
select
(
SELECT count(*) as counting
FROM fact_GEO_GT_VOVERZICHT AS a INNER JOIN
fact_GEO_GT_STATUS AS b ON a.WOZOBJEKTNR = b.WOZOBJEKTNR
WHERE (a.DTE = CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (b.PEILJAAR = 2009) AND (a.SOORT_OBJ_GEM NOT IN ('9100', '9995')) AND
(a.SOORT_OBJ_GT <> 31)
) as sql1_counting,
(
SELECT count(*) as counting
FROM fact_GEO_GT_VOVERZICHT AS c INNER JOIN
fact_GEO_GT_STATUS AS d ON c.WOZOBJEKTNR = d.WOZOBJEKTNR
WHERE (c.DTE = CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) AND (d.PEILJAAR = 2009) AND (c.VWAARDE_WOZ > 0) AND
(NOT (c.TAXATIEDATUM IS NULL)) AND (NOT (c.TAXATEUR IS NULL)) AND (c.CODE_BLOKKEREN = 0) AND (d.STAT_WRDB > 2)
) as sql2_counting
) as t

Madhivanan

Failing to plan is Planning to fail

Marteijn
Starting Member

28 Posts

Posted - 2009-12-14 : 09:05:29

Hi Madhivanan,

That looks very good, only have to work on the format of
the percentage (I get 171% in stead of 71%).

Thank you! You made my day!
Marteijn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 09:06:07

quote:
Originally posted by Marteijn

Hi Madhivanan,

That looks very good, only have to work on the format of
the percentage (I get 171% in stead of 71%).

Thank you! You made my day!
Marteijn

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources