How to change the condition Using RIGHT JOIN

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 2005 Forums

Transact-SQL (2005)

How to change the condition Using RIGHT JOIN

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-03-05 : 12:06:02

I want to display the data for all the dates, if there is no data then also I need to display zeros for that date so I created calendar for the given date ranges, and RIGHT JOIN with the table that contains data

DECLARE @calendar TABLE
(
date DATETIME,
monthname VARCHAR(50),
yearnumber VARCHAR(50),
monthnumber INT
)

DECLARE @calendarStartDate DATETIME
DECLARE @calendarStoptDate DATETIME
DECLARE @thisDate DATETIME

SET @calendarStartDate = @startDate
SET @calendarStoptDate = @endDate
SET @thisDate = @calendarStartDate

WHILE @thisDate <= @calendarStoptDate
BEGIN
INSERT INTO
@calendar
SELECT
@thisDate,
LEFT(DATENAME(MONTH, @thisDate ),3) ,
YEAR(@thisDate),
MONTH(@thisDate)

SET @thisDate = @thisDate + 1
END

Am getting data populated for the given date range…

INSERT INTO
#tempExistingCustomer
SELECT
cal.date,
cal.monthName,
cal.yearNumber,
cal.monthnumber,
'Organic' sheetName,
2 sortOrder,
ISNULL(Existing_Cust_AD_Sales , 0 ) Existing_Cust_AD_Sales,
ISNULL(Existing_Cust_AD_noSales ,0 ) Existing_Cust_AD_noSales,
ISNULL(Existing_Cust_AD_Sales + Existing_Cust_AD_noSales , 0 ) AS [tot_Existing_Cust_AD],
CASE
WHEN (Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) <> 0
THEN ISNULL(Existing_Cust_AD_Sales / ((Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) *1.0),0.0)
ELSE 0.0
END AS [Percent_Existing_Cust_AD_Sales] ,
CASE
WHEN (Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) <> 0
THEN ISNULL(Existing_Cust_AD_noSales / ((Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) *1.0),0.0)
ELSE 0.0
END AS [Percent_Existing_Cust_AD_noSales]
FROM
prmExistingCustomer prm
RIGHT JOIN
@calendar cal
ON
prm.date = cal.date
WHERE
-- databaseName='db_customer'
-- AND
cal.date BETWEEN @startDate AND @endDate

If keep the condition like dbname=’db_customer’…data getting populated only if the data is present..if i don't put condition like dbname=’db_customer’ data getting populated for all the dates of given date range...
How to change the above code to diplay dates, if there is no data also

Please advice….

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:19:01

first of all you dont need to use loop for generating calendar table. you can use function in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

now for second part, I'm not clear what you're expecting you want display what all dates? if you use calendar table it will definitely show all dates in date range.

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

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-03-05 : 12:27:24

quote:
Originally posted by vision.v1

quote:
Originally posted by visakh16

first of all you dont need to use loop for generating calendar table. you can use function in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

now for second part, I'm not clear what you're expecting you want display what all dates? if you use calendar table it will definitely show all dates in date range.

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

if the data is not present in my Table for the Given date, then also it should display like

date field1 field2 field3
----- ----- ----- -------
03/03 0 0 0
03/04 0 0 0
03/05 7 9 9 --- Data is present only for 03/05/2010 date but need to display for 03/03 & 03/04 also as 0
----

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:29:33

ok. then whats the problem . thats what calendar table does for that . didnt understand fuss around databaseName='db_customer'

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

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-03-05 : 12:39:37

quote:
Originally posted by visakh16

ok. then whats the problem . thats what calendar table does for that . didnt understand fuss around databaseName='db_customer'

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

In the table prmExistingCustomer having data for only one date
i.e.,
databasename='db_customer'
date ='03/05/2010'
field1 - 7
field2 - 9
field3 - 9

so in the resultset am getting only one record like when i keep the where condition databasename='db_customer'
date field1 field2 field3
----- ----- ----- -------
03/05 7 9 9

if i remove the condition databasename='db_customer'

getting the result for the given date range with 0's if data is not present
date field1 field2 field3
----- ----- ----- -------
03/03 0 0 0
03/04 0 0 0
03/05 7 9 9 --- Data is present only for 03/05/2010 date but need to display for 03/03 & 03/04 also as 0

But i need to put the where condition databasename='db_customer'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:41:13

why you need where if your reqmnt was to display the data for all the dates, if there is no data then display zeros for that date

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

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-03-05 : 12:44:09

quote:
Originally posted by visakh16

why you need where if your reqmnt was to display the data for all the dates, if there is no data then display zeros for that date

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

I need to filter the data for 2 to 3 databases, so am filtering data based on databasename and datewise

so i need to put where databasename='' condition

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:48:06

seems like what you're looking for is this


INSERT INTO
#tempExistingCustomer
SELECT reqd columns...
FROM
(
SELECT
cal.date,
cal.monthName,
cal.yearNumber,
cal.monthnumber,
'Organic' sheetName,
2 sortOrder,
ISNULL(Existing_Cust_AD_Sales , 0 ) Existing_Cust_AD_Sales,
ISNULL(Existing_Cust_AD_noSales ,0 ) Existing_Cust_AD_noSales,
ISNULL(Existing_Cust_AD_Sales + Existing_Cust_AD_noSales , 0 ) AS [tot_Existing_Cust_AD],
CASE
WHEN (Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) <> 0
THEN ISNULL(Existing_Cust_AD_Sales / ((Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) *1.0),0.0) 
ELSE 0.0
END AS [Percent_Existing_Cust_AD_Sales] ,
CASE
WHEN (Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) <> 0
THEN ISNULL(Existing_Cust_AD_noSales / ((Existing_Cust_AD_Sales + Existing_Cust_AD_noSales) *1.0),0.0) 
ELSE 0.0
END AS [Percent_Existing_Cust_AD_noSales]
FROM
prmExistingCustomer prm
WHERE	databaseName='db_customer'
)t
RIGHT JOIN
@calendar cal
ON
t.date = cal.date
WHERE cal.date BETWEEN @startDate AND @endDate

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources