merge two similar queries for use in SSRS

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

Development Tools

Reporting Services Development

merge two similar queries for use in SSRS

Author

Topic

divya
Starting Member

5 Posts

Posted - 2007-03-06 : 13:30:51

SELECT COUNT(U_USER_ID) AS '# OF USERS',TOTAL_SAVD_SRCH
FROM
(
SELECT
USS.U_USER_ID,
COUNT(LSS.I_SEARCH_TYPE) AS 'TOTAL_SAVD_SRCH'
FROM
T_USER_SAVED_SEARCH USS
INNER JOIN
T_LOOKUP_SAVED_SEARCH LSS
ON USS.U_SAVED_SEARCH_ID=LSS.U_SAVED_SEARCH_ID
WHERE
USS.B_ALERT_STATUS_RESEARCH_FLAG = 1
GROUP BY
USS.U_USER_ID
) TEMP_SVD_SRCH
GROUP BY
TOTAL_SAVD_SRCH

This is one query,
the other one is same but in the where clause..the condition is
USS.B_ALERT_STATUS_FILINGS_FLAG = 1

Finally i need to get 4 columns..2 for each condition

jhermiz

3564 Posts

Posted - 2007-03-06 : 13:33:17

quote:
Originally posted by divya

SELECT COUNT(U_USER_ID) AS '# OF USERS',TOTAL_SAVD_SRCH
FROM
(
SELECT
USS.U_USER_ID,
COUNT(LSS.I_SEARCH_TYPE) AS 'TOTAL_SAVD_SRCH'
FROM
T_USER_SAVED_SEARCH USS
INNER JOIN
T_LOOKUP_SAVED_SEARCH LSS
ON USS.U_SAVED_SEARCH_ID=LSS.U_SAVED_SEARCH_ID
WHERE
USS.B_ALERT_STATUS_RESEARCH_FLAG = 1
GROUP BY
USS.U_USER_ID
) TEMP_SVD_SRCH
GROUP BY
TOTAL_SAVD_SRCH

This is one query,
the other one is same but in the where clause..the condition is
USS.B_ALERT_STATUS_FILINGS_FLAG = 1

Finally i need to get 4 columns..2 for each condition

If the second query is the same number of fields, types, etc, you can then make use of the UNION keyword

SELECT...
FROM...
WHERE...
UNION
SELECT...
FROM...
WHERE...

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

divya
Starting Member

5 Posts

Posted - 2007-03-07 : 09:40:37

Hey i need 4 COLUMNS...with UNION i can populate rows only

jhermiz

3564 Posts

Posted - 2007-03-07 : 12:30:08

Heard of the CASE statement? Or how about giving us MUCH more info...and don't refer to people by "Hey" it's very unprofessional. How can you expect anyone to know what you really need without posting DDL, SELECT / INSERT statements, sample data, sample expected results, etc etc.

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

divya
Starting Member

5 Posts

Posted - 2007-03-07 : 23:19:21

Sorry for the unformal approach, I am new to this.
I got what i wanted using Join(Full Outer Join).
My output has columns
#OfUsers, TOTAL_SAVD_SRCH, #OfUsers, TOTAL_SAVD_SRCH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources