Help with combining two queries

I have a stored procedure that needs to retrieve the top 1000 sent items and
the top 1000 received items - each ordered by date. So, effectively, the
most recent 1000 sent items and the most recent 1000 received items. Then, I
need to combine them into one result set and again take the top 1000 items
when ordered by date.
Originally, the stored procedure created a temp table and inserted the
results of each query consecutively, then did a SELECT TOP to get the final
results. Because of high traffic, this is killing the DB server. I suspect
that the queries could be combined into one query. I also tried a UNION of
the two selects, but that doesn't work because I need to do have accurate
results on the subquery first (top 1000 ordered by date). Can anyone help me
determine a more effecient solution, preferrably to combine the two queries
into one? I have slimmed down the two queries significantly to show only the
differences. They are below.
DECLARE @.userName AS VARCHAR(25)
SELECT @.userName = 'MyUserName'
-- Gets the sent items
--
SELECT TOP 1000
@.userName as SenderName,
'Sent' AS SentReceived,
u.[user_name] as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[contact_user_id] =
u.[user_id]
WHERE
ch.[user_id] = @.UserID
ORDER BY
ch.createstamp DESC
-- Gets the received items
--
SELECT TOP 1000
u.[user_name] as SenderName,
'Received' AS SentReceived,
@.userName as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[user_id] =
u.[user_id]
WHERE
ch.[contact_user_id] = @.UserID
ORDER BY
ch.createstamp DESCHello, karch
You probably want a query like this:
SELECT TOP 1000 *
FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
UNION ALL
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) x
ORDER BY TheDate
However, I think the above query will give the same results as this
query (as long as TheDate is unique among all rows):
SELECT TOP 1000 *
FROM (
SELECT <your columns>
FROM <sent items>
UNION ALL
SELECT <your columns>
FROM <sent items>
) x
ORDER BY TheDate
Razvan|||Yes, but I dont think you can have ORDER BY in the subqueries if you UNION -
I tried this approach and received an error. But, yes, logically that is
what I want to accomplish.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146597584.838889.18770@.v46g2000cwv.googlegroups.com...
> Hello, karch
> You probably want a query like this:
> SELECT TOP 1000 *
> FROM (
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> UNION ALL
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> ) x
> ORDER BY TheDate
> However, I think the above query will give the same results as this
> query (as long as TheDate is unique among all rows):
> SELECT TOP 1000 *
> FROM (
> SELECT <your columns>
> FROM <sent items>
> UNION ALL
> SELECT <your columns>
> FROM <sent items>
> ) x
> ORDER BY TheDate
> Razvan
>|||In this case, try another level of subqueries:
SELECT TOP 1000 *
FROM (
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) a
UNION ALL
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) b
) x
ORDER BY TheDate
Razvan

help with an update

Table A
--
Listing_Number | Sort_Order
=====================
2000 | 1
2000 | 3
2000 | 4
2000 | 6
2000 | 9
I want the Sort_Order ordered like 1,2,3,4,5,6 etc. for every
listing_number. Could someone help with this problem ASAP. This is SQL
Server 2000.
Thank you,
ShivaWhat is this sort order, is it a autogenerated column or something you selec
t.
You need to give more information Shiva. Where are 3 and 5 that you want in
the sort order.
"Shiva" wrote:

> Table A
> --
> Listing_Number | Sort_Order
> =====================
> 2000 | 1
> 2000 | 3
> 2000 | 4
> 2000 | 6
> 2000 | 9
> I want the Sort_Order ordered like 1,2,3,4,5,6 etc. for every
> listing_number. Could someone help with this problem ASAP. This is SQL
> Server 2000.
> Thank you,
> Shiva
>
>|||Sort_Order should be always in sequence without losing a digit in between.
So the sequence should be 1,2,3, etc for a listing_number. So how can i
re-order the the sort_order in the table i have below?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4D64DA4E-29CF-4549-8B84-C0783D2DF756@.microsoft.com...
> What is this sort order, is it a autogenerated column or something you
> select.
> You need to give more information Shiva. Where are 3 and 5 that you want
> in
> the sort order.
> "Shiva" wrote:
>|||Shiva
declare @.j int
set @.j=0
update yourtable
set @.j=i=@.j+1
"Shiva" <arbitsquare@.hotmail.com> wrote in message
news:u90lLIXfGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Sort_Order should be always in sequence without losing a digit in between.
> So the sequence should be 1,2,3, etc for a listing_number. So how can i
> re-order the the sort_order in the table i have below?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4D64DA4E-29CF-4549-8B84-C0783D2DF756@.microsoft.com...
>|||Sorry , "i" is yout List_Order column
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O69CJaXfGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Shiva
> declare @.j int
> set @.j=0
> update yourtable
> set @.j=i=@.j+1
>
> "Shiva" <arbitsquare@.hotmail.com> wrote in message
> news:u90lLIXfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>|||This won't work Uri. He wanted it reset for every listing number.
Btw, Shiva,what is the primary for this table?
"Uri Dimant" wrote:

> Sorry , "i" is yout List_Order column
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O69CJaXfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>
>|||The table and the data already exists Uri.. He needs just the query :)
Anyways.. If the primary key is Listing_number,sort_order
then maybe we can try this..
declare @.a int, @.b int
set @.b = 0
set @.a = 0
update tableA set
@.a = Sort_Order = case when @.b = Listing_Number then @.a + 1 else 1 end,
@.b = Listing_Number|||Well, it is easy to "convert" SELECT to the UPDATE and there is no need to
declare variables
update #test set Sort_Order=(select count(*)from #test t where
t.Sort_Order<=#test.Sort_Order and
t.Listing_Number=#test.Listing_Number)
where exists (select *from #test t where
t.Sort_Order<=#test.Sort_Order and
t.Listing_Number=#test.Listing_Number)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:C748F79B-DE79-42FB-872C-48028E79179F@.microsoft.com...
> The table and the data already exists Uri.. He needs just the query :)
> Anyways.. If the primary key is Listing_number,sort_order
> then maybe we can try this..
> declare @.a int, @.b int
> set @.b = 0
> set @.a = 0
> update tableA set
> @.a = Sort_Order = case when @.b = Listing_Number then @.a + 1 else 1 end,
> @.b = Listing_Number|||the previous update query you suggested was fine.
But why did you give an exists clause there?
what is the use of it?|||Hehehehehe, it is a habit to me , always put a WHERE condition , especially
for those requestes where people have not provided FULL ddl +primary
keys...
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:902CCA11-E48A-46A0-A35D-BEA8594CD79E@.microsoft.com...
> the previous update query you suggested was fine.
> But why did you give an exists clause there?
> what is the use of it?sql

Help with a SQL statement

My problem can best be described using the Northwind db as an example.
I would like to select all Customers that have never ordered a
particular Product. Thank you.There are several way to do this
(Don′t have the Northwind here but is think I get the column names right :-
) )
Select * from Customers C
Where not Exists
(
Select * from orders O where O.CustomerId = C.CustomerId
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"rstewart27104@.gmail.com" wrote:

> My problem can best be described using the Northwind db as an example.
> I would like to select all Customers that have never ordered a
> particular Product. Thank you.
>|||Well, wouldn't you need to tie in orderdetails and productid, since the OP
wants customers that haven't ordered a specific product, not customers who
haven't ordered at all?
(BTW, how many customers that have never ordered, would you expect to find
in the database? :-) )
A
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com...
> There are several way to do this
> (Don′t have the Northwind here but is think I get the column names right
> :-) )
> Select * from Customers C
> Where not Exists
> (
> Select * from orders O where O.CustomerId = C.CustomerId
> )
> --
> HTH, Jens Suessmeyer.|||Well If you want to make them an offer you sure have the customer in the
database with a customerid...
Didn′t saw the word "particular", then you should go by this:
Select * from Customers C
Where not Exists
(
Select * from orders O
inner join [Order Details] od on od.orderid = o.orderid
inner join [Products] P on o.productid = od.productid
where p.ProductName = 'SomeProduct'
and O.CustomerId = C.CustomerId
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, wouldn't you need to tie in orderdetails and productid, since the OP
> wants customers that haven't ordered a specific product, not customers who
> haven't ordered at all?
> (BTW, how many customers that have never ordered, would you expect to find
> in the database? :-) )
> A
>
>
> "Jens Sü?meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com..
.
>
>|||> Well If you want to make them an offer you sure have the customer in the
> database with a customerid...
Right, but my point was, most online stores won't have data about a customer
unless they've purchased from them once. :-)|||OK, but what would you expect from a query displaying all customers which
didn′t by anything if you refer to the online store example. If you have on
e
customer that′ll be
mankind - 1 = SelectList
;-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Aaron Bertrand [SQL Server MVP]" wrote:

> Right, but my point was, most online stores won't have data about a custom
er
> unless they've purchased from them once. :-)
>
>|||> OK, but what would you expect from a query displaying all customers which
> didn′t by anything if you refer to the online store example.
Typically, I wouldn't expect such a query to have any practical meaning. I
suppose there is probably a sliding scale, the less credible the company,
the lower their percentage of actual customers in their "customer" list.
:-)|||or
SELECT c.[CustomerID]
FROM [Customers] c
LEFT OUTER JOIN [Orders] o
ON c.[CustomerID]=o.[CustomerID]
WHERE o.[CustomerID] is null
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com...
> There are several way to do this
> (Dont have the Northwind here but is think I get the column names right
:-) )
> Select * from Customers C
> Where not Exists
> (
> Select * from orders O where O.CustomerId = C.CustomerId
> )
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "rstewart27104@.gmail.com" wrote:
>

Help suppress

I am trying to suppress some details, what I have is 3 columns 1st is order number, 2nd is product, 3rd stock status ie allocated/back ordered. It prints a line per product and the status. What I am trying to do is if the order has one line on back order then I want to suppress the whole order.

Thanks

RichardHi,
Can you post the expected result with some sample data?

Madhivanan|||What version on Crystal are you using? I use 8.5, so you may need to modify the following to work with your version.

You can use a conditional suppress. I will assume that you have each order appearing in the Details section. Go into the Format of the details section. Find where you can check to Suppress that section. You should see a button that looks like 'x-2'. Click the button and enter a formula. The Detail section will be supressed each time the formula evaluates to True.

Example, put this in the Formula box when you click 'x-2':
{StockStatus} = "BackOrdered"