| Author |
 Topic  |
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
 Posted - 06/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/01/2012 : 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
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 06/01/2012 : 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
|
Edited by - arkiboys on 06/01/2012 03:29:01 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/01/2012 : 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
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 06/01/2012 : 05:18:14
|
| Any suggestions on my previous question please? |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/01/2012 : 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. |
|
|
| |
Topic |
|
simplify query
