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
 Union Query and compare results

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-26 : 16:41:30
Hi All
I have a union query two retreive data of two requests where 1 request is from 2012 and 1 from 2013.

the below sql gets me two records 1 for each request. How do i compare each column and display YES whereever botht the columns are equal,Display NO where both columns are not same and Display NA where both columns are NULL

select requestid,receiveddate,neededby,reportfrequency,definesch,schedule,clmsrvdt,clmsrvdtthru,clmpddt,clmpddtthru,agentid,customername
from Customer_request  where requestid = 300430
union
select requestid,receiveddate,neededby,reportfrequency,definesch,schedule,clmsrvdt,clmsrvdtthru,clmpddt,clmpddtthru,agentid,customername
from Customer_request  where requestid = 223681


RESULTS
requestid receiveddate neededby reportfrequency defineschedule schedulebasedon clmsrvdt clmsrvdtthru clmpddt clmpddtthru agentid
300430 11/29/12 12/6/12 Recurring Monthly 1/1/12 12/31/12 65
223681 1/18/11 2/25/11 Recurring QuaterLY 1/1/11 1/31/11 65
NO NO YES NO NA NA NA N N YES

need help please

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2013-02-26 : 17:24:46
You could do something like:
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')

SELECT
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-27 : 08:27:02
How can i get the results from this query along with the values inserted
quote:
Originally posted by Lamprey

You could do something like:
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')

SELECT
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-02-27 : 09:19:08
Do you mean like shown below?
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')

SELECT
	a.*,
	b.*,
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END AS SameID,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END AS SameVal1,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END AS SameVal2,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END AS SameVal3
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-27 : 10:00:17
Nope I would like to see results as below if possible

RESULTS
requestid receiveddate neededby reportfrequency defineschedule schedulebasedon clmsrvdt clmsrvdtthru clmpddt clmpddtthru agentid
300430 11/29/12 12/6/12 Recurring Monthly 1/1/12 12/31/12 65
223681 1/18/11 2/25/11 Recurring QuaterLY 1/1/11 1/31/11 65
NO NO YES NO NA NA NA N N YES

quote:
Originally posted by James K

Do you mean like shown below?
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')

SELECT
	a.*,
	b.*,
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END AS SameID,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END AS SameVal1,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END AS SameVal2,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END AS SameVal3
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2013-02-27 : 11:37:53
Convert the current results to varchar and then union that with the results from my query is one way to do it. 
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')


SELECT 
	CAST(ID AS VARCHAR(MAX)) AS ID,
	CAST(Val1 AS VARCHAR(MAX)) AS Val1,
	CAST(Val2 AS VARCHAR(MAX)) AS Val2,
	CAST(Val3 AS VARCHAR(MAX)) AS Val3
FROM 
	@Foo 
WHERE ID = 1 

UNION ALL

SELECT 
	CAST(ID AS VARCHAR(MAX)) AS ID,
	CAST(Val1 AS VARCHAR(MAX)) AS Val1,
	CAST(Val2 AS VARCHAR(MAX)) AS Val2,
	CAST(Val3 AS VARCHAR(MAX)) AS Val3
FROM 
	@Foo  WHERE ID = 2

UNION ALL

SELECT
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-27 : 13:04:10
thank you
quote:
Originally posted by Lamprey

Convert the current results to varchar and then union that with the results from my query is one way to do it. 
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')


SELECT 
	CAST(ID AS VARCHAR(MAX)) AS ID,
	CAST(Val1 AS VARCHAR(MAX)) AS Val1,
	CAST(Val2 AS VARCHAR(MAX)) AS Val2,
	CAST(Val3 AS VARCHAR(MAX)) AS Val3
FROM 
	@Foo 
WHERE ID = 1 

UNION ALL

SELECT 
	CAST(ID AS VARCHAR(MAX)) AS ID,
	CAST(Val1 AS VARCHAR(MAX)) AS Val1,
	CAST(Val2 AS VARCHAR(MAX)) AS Val2,
	CAST(Val3 AS VARCHAR(MAX)) AS Val3
FROM 
	@Foo  WHERE ID = 2

UNION ALL

SELECT
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-27 : 13:04:40
Thank you
quote:
Originally posted by James K

Do you mean like shown below?
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')

SELECT
	a.*,
	b.*,
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END AS SameID,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END AS SameVal1,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END AS SameVal2,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END AS SameVal3
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-27 : 13:22:35
Can i ask one more favour please
Is the below possible without use of the temporary table. I wanted to use the same logic in Business Objects application but it is failing for the reason that the query uses temporary table

quote:
Originally posted by Lamprey

Convert the current results to varchar and then union that with the results from my query is one way to do it. 
DECLARE @Foo TABLE (ID INT, Val1 INT, Val2 DATETIME, Val3 VARCHAR(10))

INSERT @Foo
VALUES
(1, 1234, SYSDATETIME(), 'Foo'),
(2, 4321, SYSDATETIME(), 'Bar')


SELECT 
	CAST(ID AS VARCHAR(MAX)) AS ID,
	CAST(Val1 AS VARCHAR(MAX)) AS Val1,
	CAST(Val2 AS VARCHAR(MAX)) AS Val2,
	CAST(Val3 AS VARCHAR(MAX)) AS Val3
FROM 
	@Foo 
WHERE ID = 1 

UNION ALL

SELECT 
	CAST(ID AS VARCHAR(MAX)) AS ID,
	CAST(Val1 AS VARCHAR(MAX)) AS Val1,
	CAST(Val2 AS VARCHAR(MAX)) AS Val2,
	CAST(Val3 AS VARCHAR(MAX)) AS Val3
FROM 
	@Foo  WHERE ID = 2

UNION ALL

SELECT
	CASE WHEN A.ID = B.ID THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val1 = B.Val1 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val2 = B.Val2 THEN 'Yes' ELSE 'No' END,
	CASE WHEN A.Val3 = B.Val3 THEN 'Yes' ELSE 'No' END
FROM
	(SELECT * FROM @Foo WHERE ID = 1) AS A
INNER JOIN
	(SELECT * FROM @Foo WHERE ID = 2) AS B
	ON 1 = 1
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2013-02-27 : 13:29:58
Yeah, the temp table is only for the sample data. It's one of the basic things you should provide when asking a database question. But, it is not required for your implementation. Here are some links about how to ask database questions that might hlpe you understand why I posted sample data:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts
Posted - 2013-02-27 : 13:44:04
Thank You so much
quote:
Originally posted by Lamprey

Yeah, the temp table is only for the sample data. It's one of the basic things you should provide when asking a database question. But, it is not required for your implementation. Here are some links about how to ask database questions that might hlpe you understand why I posted sample data:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site