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
 Hide Selected Coumns from displaying		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
277 Posts
Posted - 03/05/2013 :  11:15:52  Show Profile  Reply with Quote
Hi All
I have a union query which retreives information from two differant requests compares them and displays YES,NO,NA based on the comparison in a third row.

Below is my SQL query

SELECT CAST(RR.reqestno AS VARCHAR(MAX)) reqestno,
CAST(RR.receiveddate AS VARCHAR(MAX)) AS receiveddate,
CAST(RR.rptcomments AS VARCHAR(MAX)) AS rptcomments,
CAST(RR.reportfrequency AS VARCHAR(MAX)) AS reportfrequency,
CAST(RR.schedule AS VARCHAR(MAX)) AS schedule,
CAST(RR.schedulebasedon AS VARCHAR(MAX)) AS schedulebasedon,
CAST(RR.clmsrvdtfrom AS VARCHAR(MAX)) AS clmsrvdtfrom,
CAST(RR.clmsrvdtthru AS VARCHAR(MAX)) AS clmsrvdtthru,
CAST(RR.clmpddtfrom AS VARCHAR(MAX)) AS clmpddtfrom,
CAST(RR.clmpddtthru AS VARCHAR(MAX)) AS clmpddtthru,
CAST(RR.customer AS VARCHAR(MAX)) AS customer
from REPORT_REQUEST RR 
where RR.reqestno = xxxxxx
UNION ALL
SELECT CAST(RR.reqestno AS VARCHAR(MAX)) reqestno,
CAST(RR.receiveddate AS VARCHAR(MAX)) AS receiveddate,
CAST(RR.rptcomments AS VARCHAR(MAX)) AS rptcomments,
CAST(RR.reportfrequency AS VARCHAR(MAX)) AS reportfrequency,
CAST(RR.schedule AS VARCHAR(MAX)) AS schedule,
CAST(RR.schedulebasedon AS VARCHAR(MAX)) AS schedulebasedon,
CAST(RR.clmsrvdtfrom AS VARCHAR(MAX)) AS clmsrvdtfrom,
CAST(RR.clmsrvdtthru AS VARCHAR(MAX)) AS clmsrvdtthru,
CAST(RR.clmpddtfrom AS VARCHAR(MAX)) AS clmpddtfrom,
CAST(RR.clmpddtthru AS VARCHAR(MAX)) AS clmpddtthru,
CAST(RR.customer AS VARCHAR(MAX)) AS customer
from REPORT_REQUEST RR 
where RR.reqestno = yyyyyy
UNION ALL
SELECT 
      CASE WHEN A.reqestno = B.reqestno THEN 'Yes' ELSE 'No'  END as reqestno,
      CASE WHEN A.receiveddate = B.receiveddate THEN 'Yes' ELSE 'No' END as receiveddate,
      CASE  WHEN A.rptcomments is null and B.rptcomments is null then 'NA' WHEN A.rptcomments = B.rptcomments THEN 'Yes' ELSE 'No' END rptcomments,
      CASE WHEN A.reportfrequency = B.reportfrequency THEN 'Yes' ELSE 'No' END as reportfrequency,
      CASE WHEN A.schedule = 'NONE' and B.schedule = 'NONE' THEN 'NA'WHEN A.schedule = B.schedule THEN 'Yes' ELSE 'No' END as schedule,
      CASE WHEN A.schedulebasedon = 'NONE' and B.schedulebasedon = 'NONE' THEN 'NA' WHEN A.schedulebasedon = B.schedulebasedon THEN 'Yes' ELSE 'No' END as schedulebasedon,
      CASE WHEN A.clmsrvdtfrom is null and B.clmsrvdtfrom is Null Then 'NA' WHEN A.clmsrvdtfrom = B.clmsrvdtfrom THEN 'Yes' ELSE 'No' END as clmsrvdtfrom,
      CASE WHEN A.clmsrvdtthru is null and B.clmsrvdtthru is null then 'NA' WHEN A.clmsrvdtthru = B.clmsrvdtthru THEN 'Yes' ELSE 'No' END as clmsrvdtthru,
      CASE WHEN A.clmpddtfrom is Null and B.clmpddtfrom is null then 'NA' WHEN A.clmpddtfrom = B.clmpddtfrom THEN 'Yes' ELSE 'No' END as clmpddtfrom,
      CASE WHEN A.clmpddtthru is null and B.clmpddtthru is null then 'NA' WHEN A.clmpddtthru = B.clmpddtthru THEN 'Yes' ELSE 'No' END as clmpddtthru,
      CASE WHEN A.customer = B.customer THEN 'Yes' ELSE 'No' END as customer
    
FROM
            (SELECT  reqestno,receiveddate,rptcomments,reportfrequency,schedule,schedulebasedon,clmsrvdtfrom,
clmsrvdtthru,clmpddtfrom,clmpddtthru,customer  FROM REPORT_REQUEST WHERE reqestno = xxxxxx) AS A
INNER JOIN
                 (SELECT  reqestno,receiveddate,rptcomments,reportfrequency,schedule,schedulebasedon,clmsrvdtfrom,
clmsrvdtthru,clmpddtfrom,clmpddtthru,customer  FROM REPORT_REQUEST WHERE reqestno = yyyyyy) AS B
      ON 1 = 1


Question
How should i edit the above query to make it display only columns that have only a value of 'NO' after comparison and in some way hide the other columns whereever comparison returns YES or NA



Sample Results
requestno receiveddate rptcomments reportfrequency schedule schedulebasedon clmsrvdtfrom clmsrvdtthru clmpddtfrom clmpddtthru customer
300431 Nov 29 2012 10:28AM Recurring Calendar Year May 1 2012 12:00AM Oct 31 2012 12:00AM Jan 1 1900 12:00AM Jan 1 1900 12:00AM LIFTONE, LLC
223681 Jan 18 2011 9:49AM Please clone from 2010 request. T. Rowe is "sliced business" with UHC and Carefirst. They are moving to a Datza Warehouse to gain a full picture of their population. Data Warehousing: UHC to submit via FTP monthly medical claims to Thompson Reuters Recurring Calendar Year Jan 1 1900 12:00AM Jan 1 1900 12:00AM Jan 1 2011 12:00AM Jan 31 2011 12:00AM T. ROWE PRICE GROUP, INC.
No No No Yes NA Yes No No No No No


Need help please
Thanks in advance

MIK_2008
Aged Yak Warrior

Pakistan
826 Posts
Posted - 03/05/2013 :  11:36:01  Show Profile  Reply with Quote
Strange logic, First of all I think there would be no way to get yes from

CASE WHEN A.reqestno = B.reqestno THEN 'Yes' ELSE 'No' END as reqestno,

When you're joining the two sets with two different requestno and then joining them

"reqestno = xxxxxx" when joins with the record "reqestno = yyyyyy"

Any way if you think your query is working correctly per business logic then what you can do is to apply an outer select e.g.
SELECT * FROM
(
your query
)A
Where A.ColumnName = 'No' [and A.columnName2='no' And ...]

Cheers
MIK
Edited by - MIK_2008 on 03/05/2013 11:36:38
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
277 Posts
Posted - 03/05/2013 :  11:56:09  Show Profile  Reply with Quote
The Requestno is never going to come as yes as always provide 2 unique request numbers.

I was not able to make your help work as we dont know what columns are going to be NO after comparison
thanks

quote:
Originally posted by MIK_2008

Strange logic, First of all I think there would be no way to get yes from

CASE WHEN A.reqestno = B.reqestno THEN 'Yes' ELSE 'No' END as reqestno,

When you're joining the two sets with two different requestno and then joining them

"reqestno = xxxxxx" when joins with the record "reqestno = yyyyyy"

Any way if you think your query is working correctly per business logic then what you can do is to apply an outer select e.g.
SELECT * FROM
(
your query
)A
Where A.ColumnName = 'No' [and A.columnName2='no' And ...]

Cheers
MIK
Edited by - jim_jim on 03/05/2013 11:57:07
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
277 Posts
Posted - 03/05/2013 :  14:58:38  Show Profile  Reply with Quote
I'm still looking for help on my initial query


thanks
Go to Top of Page

MIK_2008
Aged Yak Warrior

Pakistan
826 Posts
Posted - 03/06/2013 :  04:41:16  Show Profile  Reply with Quote
As I mentioned: assuming your query is correct per your requirements, then use and outer select on that whole query along with required "Where condition(s)" to have the desired output. For example

SELECT * FROM
(
Paste here the complete SQL query you mentioned on the top
) A
WHERE receiveddate='YES' AND rptcomments='YES' -- Assuming that you want to have only the records having ReceiveDate and RptComments as Yes. If you want to include any other column you can simply use AND/OR operator as per your requirements in this Where condition

However, I would suggest that you come up with sample data in the form of INSERT Statements and the desired output you wanted to have based on that given sample data. That way the SQL team would be able to help you exactly as you wanted to have ..
Thanks!

Cheers
MIK
Edited by - MIK_2008 on 03/06/2013 04:47:27
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
277 Posts
Posted - 03/06/2013 :  09:08:15  Show Profile  Reply with Quote
Hi
I would like to see only the columns that have returned as NO in a particular record

My present query returns all columns for a record after comparing and i want to return only the columns that have 'NO' in the third






quote:
Originally posted by MIK_2008

As I mentioned: assuming your query is correct per your requirements, then use and outer select on that whole query along with required "Where condition(s)" to have the desired output. For example

SELECT * FROM
(
Paste here the complete SQL query you mentioned on the top
) A
WHERE receiveddate='YES' AND rptcomments='YES' -- Assuming that you want to have only the records having ReceiveDate and RptComments as Yes. If you want to include any other column you can simply use AND/OR operator as per your requirements in this Where condition

However, I would suggest that you come up with sample data in the form of INSERT Statements and the desired output you wanted to have based on that given sample data. That way the SQL team would be able to help you exactly as you wanted to have ..
Thanks!

Cheers
MIK
Go to Top of Page

MIK_2008
Aged Yak Warrior

Pakistan
826 Posts
Posted - 03/06/2013 :  10:02:02  Show Profile  Reply with Quote
Sorry put No in the conditions instead of Yes.

Cheers
MIK
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3832 Posts
Posted - 03/06/2013 :  12:57:01  Show Profile  Reply with Quote
The short answer is NO, you cannot hide columns based on a condition. However, you coulld do some really bad/funky Dynamic SQL that would determine which columns have a "Yes" value and then ommit them from the select list. But, I'd think that ignoring them or having your presentaion layer handle that would be better.
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