SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Union Query and compare results		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
277 Posts
Posted - 02/26/2013 :  16:41:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 02/26/2013 :  17:24:46  Show Profile  Reply with Quote
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

USA
277 Posts
Posted - 02/27/2013 :  08:27:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1527 Posts
Posted - 02/27/2013 :  09:19:08  Show Profile  Reply with Quote
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

USA
277 Posts
Posted - 02/27/2013 :  10:00:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 02/27/2013 :  11:37:53  Show Profile  Reply with Quote
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

USA
277 Posts
Posted - 02/27/2013 :  13:04:10  Show Profile  Reply with Quote
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

USA
277 Posts
Posted - 02/27/2013 :  13:04:40  Show Profile  Reply with Quote
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

USA
277 Posts
Posted - 02/27/2013 :  13:22:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 02/27/2013 :  13:29:58  Show Profile  Reply with Quote
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

USA
277 Posts
Posted - 02/27/2013 :  13:44:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000