Need Help with this query - 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 2008 Forums

Transact-SQL (2008)

Need Help with this query

Author

Topic

sudhakamathm
Starting Member

2 Posts

Posted - 2011-03-07 : 06:27:53

I need to Join these 2 views

CREATE VIEW v_Companies_add_Lastweek AS
SELECT company_initiated_by.Initiated_By,Count(company_initiated_by.Company_ID) as Companies_add_Lastweek
FROM company_initiated_by
WHERE company_initiated_by.Initiated_Date <=current_date AND company_initiated_by.Initiated_Date >=current_date-7
GROUP BY company_initiated_by.Initiated_By;

CREATE VIEW v_Contacts_made_lastweek AS
SELECT Contact_initiated_by.Initiated_By,COUNT(Contact_initiated_by.Contact_ID) as Contacts_made_lastweek
FROM Contact_initiated_by
WHERE Contact_initiated_by.Initiated_Date <=current_date AND Contact_initiated_by.Initiated_Date >=current_date-7
GROUP BY Contact_initiated_by.Initiated_By;

[b]Like This[\b]

CREATE VIEW TOT_COUN AS
SELECT
CASE
WHEN (v_Companies_add_Lastweek.Companies_add_Lastweek IS NULL)
THEN '0'
ELSE v_Companies_add_Lastweek.Companies_add_Lastweek
END AS COMP_ADD_LAST_WEEK,
CASE WHEN (v_Companies_add_Lastweek.Initiated_By IS NULL OR v_Companies_add_Lastweek.Initiated_By='')
THEN 'N'
ELSE v_Companies_add_Lastweek.Initiated_By
END AS COMP_LASTWEEK_INITIATED_BY,
CASE
WHEN (v_Contacts_made_lastweek.Contacts_made_lastweek IS NULL )
THEN '0'
ELSE v_Contacts_made_lastweek.Contacts_made_lastweek
END AS CONT_ADD_LAST_WEEK,
CASE WHEN (v_Contacts_made_lastweek.Initiated_By IS NULL OR v_Contacts_made_lastweek.Initiated_By='')
THEN 'N'
ELSE v_Contacts_made_lastweek.Initiated_By
END AS CON_LAST_WEEK_INITIATED_BY FROM v_Companies_add_Lastweek,v_Contacts_made_lastweek

BUT IS THERE IS A NULL VALUE IT DOES NOT GIV ME 0 IT SHOWS BLANK TABLE.. How do i set it right??

Sachin.Nand

2937 Posts

Posted - 2011-03-07 : 07:26:10

The two Views you created seem to be having the same where clause.


CREATE VIEW v_Companies_add_Lastweek AS
SELECT company_initiated_by.Initiated_By,Count(company_initiated_by.Company_ID) as Companies_add_Lastweek 
FROM company_initiated_by 
WHERE company_initiated_by.Initiated_Date <=current_date AND company_initiated_by.Initiated_Date >=current_date-7
GROUP BY company_initiated_by.Initiated_By;


CREATE VIEW v_Contacts_made_lastweek AS
SELECT Contact_initiated_by.Initiated_By,COUNT(Contact_initiated_by.Contact_ID) as Contacts_made_lastweek
FROM Contact_initiated_by 
WHERE Contact_initiated_by.Initiated_Date <=current_date AND Contact_initiated_by.Initiated_Date >=current_date-7
GROUP BY Contact_initiated_by.Initiated_By;

PBUH

sudhakamathm
Starting Member

2 Posts

Posted - 2011-03-07 : 22:55:00

quote:

Originally posted by Sachin.Nand

The two Views you created seem to be having the same where clause.


CREATE VIEW v_Companies_add_Lastweek AS
SELECT company_initiated_by.Initiated_By,Count(company_initiated_by.Company_ID) as Companies_add_Lastweek 
FROM company_initiated_by 
WHERE company_initiated_by.Initiated_Date <=current_date AND company_initiated_by.Initiated_Date >=current_date-7
GROUP BY company_initiated_by.Initiated_By;


CREATE VIEW v_Contacts_made_lastweek AS
SELECT Contact_initiated_by.Initiated_By,COUNT(Contact_initiated_by.Contact_ID) as Contacts_made_lastweek
FROM Contact_initiated_by 
WHERE Contact_initiated_by.Initiated_Date <=current_date AND Contact_initiated_by.Initiated_Date >=current_date-7
GROUP BY Contact_initiated_by.Initiated_By;

PBUH

Yeah it not same its similar. i need to find out the companies that were added last week and the contacts that were added last week So u can see the 1st where clause is COMPANY_initiated_by and 2nd is CONTACT_INITIATED_BY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 11:23:57

why cant you do it in same query?


SELECT company_initiated_by.Initiated_By,Count(company_initiated_by.Company_ID) as Companies_add_Lastweek ,COUNT(Contact_initiated_by.Contact_ID) as Contacts_made_lastweek
FROM company_initiated_by 
WHERE company_initiated_by.Initiated_Date <=current_date AND company_initiated_by.Initiated_Date >=current_date-7
GROUP BY company_initiated_by.Initiated_By;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources