simplify 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)

simplify query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-06-01 : 02:50:37

Hi,
Can you see if you can place the following two select statements into one?
The two statements are from the same tables with the same joins but logic is different.
Thanks

SELECT
dbo.RReport.BCode AS MyCode, dbo.RReport.Currency AS MyItem,
dbo.RReport.percentage AS MyValue, dbo.RReport.MySRef
FROM
dbo.RReport
INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate
AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno
AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost
WHERE
(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin')
AND (dbo.RReport.MySRef = 13)
AND (dbo.MyRunningDate.UserName = 'myusername')

SELECT
dbo.RReport.BCode AS MyCode, dbo.RReport.Currency AS MyItem,
MyValue =
CASE WHEN dbo.RReport.percentage > .25 THEN - .25 ELSE - dbo.RReport.percentage END,
dbo.RReport.MySRef
FROM
dbo.RReport
INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate
AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost
AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno
WHERE
(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin')
AND (dbo.RReport.MySRef = 9)
AND (dbo.MyRunningDate.UserName = 'myusername')
AND (dbo.RReport.BCode = @BCode)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-01 : 02:59:47

They are returning different rows. What do you want in the resultset for rows that are in one but not the other?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-01 : 03:03:30

maybe something like

SELECT
dbo.RReport.BCode AS MyCode,
dbo.RReport.Currency AS MyItem,
dbo.RReport.MySRef ,
MyValue = CASE WHEN RReport.MySRef = 9 then case when dbo.RReport.percentage > .25 THEN - .25 ELSE - dbo.RReport.percentage END end
type = case when RReport.MySRef = 9 then 'query2' else 'query1' end
FROM
dbo.RReport
INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate
AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost
AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno
WHERE
(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin')
AND ((dbo.RReport.MySRef = 9 and dbo.RReport.BCode = @BCode) or (dbo.RReport.MySRef = 13))
AND (dbo.MyRunningDate.UserName = 'myusername')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-06-01 : 03:03:47

My goal is to simplify thiese queries below and get to the final select. How can this be done please? thanks.


insert into @tblCurrency
SELECT     
	dbo.RReport.BCode AS MyCode, dbo.RReport.Currency AS MyItem, 
	dbo.RReport.percentage AS MyValue, dbo.RReport.MySRef
FROM         
	dbo.RReport 
	INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate 
		AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno 
		AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost
WHERE     
	(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin') 
	AND (dbo.RReport.MySRef = 13) 
	AND (dbo.MyRunningDate.UserName = 'myusername')

insert into @tblDetails
SELECT     
	dbo.RReport.BCode AS MyCode, dbo.RReport.Currency AS MyItem, 
	MyValue =
		CASE WHEN dbo.RReport.percentage > .25 THEN - .25 ELSE - dbo.RReport.percentage END,
	dbo.RReport.MySRef
FROM         
	dbo.RReport 
	INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate 
		AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost 
		AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno
WHERE     
	(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin')
	AND (dbo.RReport.MySRef = 9) 
	AND (dbo.MyRunningDate.UserName = 'myusername')
	AND (dbo.RReport.BCode = @BCode)
	
select 
	AFX.MyCode, AFX.MyItem, MyValue = (AFX.MyValue - B.MyValue)
from 
	@tblCurrency as AFX
	inner join @tblDetails as B on AFX.MyCode = B.MyCode
		AND AFX.MyItem = B.MyItem

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-01 : 04:14:41

Was the other one I posted for the first queries the sort of thing you were looking for?
This one looks pretty simple as it is - what do yoou think the problem is?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-01 : 04:16:48

If yoou want a single query then

;with tblCurrency as
( SELECT
dbo.RReport.BCode AS MyCode, dbo.RReport.Currency AS MyItem,
dbo.RReport.percentage AS MyValue, dbo.RReport.MySRef
FROM
dbo.RReport
INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate
AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno
AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost
WHERE
(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin')
AND (dbo.RReport.MySRef = 13)
AND (dbo.MyRunningDate.UserName = 'myusername')
) ,
tblDetails as
(SELECT
dbo.RReport.BCode AS MyCode, dbo.RReport.Currency AS MyItem,
MyValue =
CASE WHEN dbo.RReport.percentage > .25 THEN - .25 ELSE - dbo.RReport.percentage END,
dbo.RReport.MySRef
FROM
dbo.RReport
INNER JOIN dbo.MyRunningDate ON dbo.RReport.AsOfDate = dbo.MyRunningDate.MyVDate
AND dbo.RReport.MyPost = dbo.MyRunningDate.MyPost
AND dbo.RReport.UseAno = dbo.MyRunningDate.UsePno
WHERE
(dbo.RReport.UserName = 'myusername' OR dbo.RReport.UserName = 'admin')
AND (dbo.RReport.MySRef = 9)
AND (dbo.MyRunningDate.UserName = 'myusername')
AND (dbo.RReport.BCode = @BCode)
)
select
AFX.MyCode, AFX.MyItem, MyValue = (AFX.MyValue - B.MyValue)
from
tblCurrency as AFX
inner join tblDetails as B on AFX.MyCode = B.MyCode
AND AFX.MyItem = B.MyItem

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-06-01 : 05:18:14

Any suggestions on my previous question please?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-01 : 05:49:07

I've given suggestions for both questions - not sure what you are looking for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources