help with setting up a reminder

Hi there,

I need help with setting up a reminder in my sql.
It needs to check the inventory on hand for a product and if it is
below 3 unit, it needs to send an e-mail reminder to the user.

how do I set this up?

Thanks,<behnaz.behzadi@.gmail.comwrote in message
news:3a300438-ed45-4398-b940-5cb0500f46d8@.18g2000hsf.googlegroups.com...

Quote:

Originally Posted by

Hi there,
>
I need help with setting up a reminder in my sql.
It needs to check the inventory on hand for a product and if it is
below 3 unit, it needs to send an e-mail reminder to the user.
>
how do I set this up?
>
Thanks,

A simple way is to setup a scheduled task to run say every 5 minutes that
checks the number and emails the user.

If you want more than that, look into Notification Services.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Help with search results query

I need some help with a query. I have a table with inventory that I
need to allow customer searches on. Based on their search criteria, a
preference level is calculated; the higher the preference level, the
higher on the order on the search results.

The hard part is when the results are supposed to be limited to a
maximum number of stores and items. Let's say that they only want to
see 3 stores and a max of 5 items per store. What needs to be
returned is the 3 stores with the best Preference and the 5 best items
at each store.

Create Table Inventory( StoreId int, ItemId int, Preference int )Loads of questions that your spec leaves unanswered: What is the primary key
of this table? Is Preference unique? Is (itemid, preference) unique or
(storeid, preference) unique? What if multiple items/stores have the same
value for Preference? Could there be more than X items with the same
preference (where X is the number of items required)? How do you decide
which are the "top" stores: by SUM(preference) for the storeid; or
SUM(preference) for the "top" X items; or some other method?

Here's a best guess:

CREATE TABLE Inventory (storeid INTEGER, itemid INTEGER, preference INTEGER
NOT NULL, PRIMARY KEY (storeid, itemid))

DECLARE @.no_stores INTEGER, @.no_items_per_store INTEGER

SET @.no_stores = 3
SET @.no_items_per_store = 5

SELECT I1.storeid, I1.itemid, I1.preference
FROM Inventory AS I1
LEFT JOIN Inventory AS I2
ON I1.storeid = I2.storeid
AND I1.preference < I2.preference
JOIN
(SELECT I1.storeid
FROM
(SELECT storeid, SUM(preference)
FROM Inventory
GROUP BY storeid)
AS I1 (storeid,preference)
LEFT JOIN
(SELECT storeid, SUM(preference)
FROM Inventory
GROUP BY storeid)
AS I2 (storeid,preference)
ON I1.preference < I2.preference
GROUP BY I1.storeid
HAVING COUNT(I2.preference) < @.no_stores) AS S
ON I1.storeid = S.storeid
GROUP BY I1.storeid, I1.itemid, I1.preference
HAVING COUNT(I2.storeid) < @.no_items_per_store

--
David Portas
----
Please reply only to the newsgroup
--

Help With Replication Configuration

Hi
I have not had much luck setting this up. Here is my
situation.
I have 3 stores which have inventory. Each store updates
their own inventory every few hours. I want all stores to
be able to see inventory from all stores.
I want this to be as close to real time as possible. I
used transactional replication with queued fail-over. I
set up Store A as the publisher and pushed subscriptions
to store B and C.
Here is my problem. When data is updated at store B it
overwrites the databases at A and C instead of just
syncing them. Same happens with A and C as they update in
a round robin fashion every few hours.
How can I set this up so A, B, and C can each update
their own inventory yet see everything from the other
stores?
Basically I want A,B and C to be in sync all the time.
Thanks
Sal DiStefano
Sal,
there are different ways to set this up, but the easiest is to have separate
tables for each store and have a view at each store that unions the 3
store's data. Replication is typically implemented using merge, with each
table being published separately. Technically it doesn't matter who is the
publisher as you ensure the store records can only be changed at the host
store.
HTH,
Paul Ibison

Help with NOT EXISTS query

I am having trouble with what will surely be a simple query for you experts.

I have 2 tables with inventory data.
IMITMIDX contains the master item info
IMINVLOC contains location specific data such as quantity on hand at that
location.

These tables have 2 commons fields, ITEM_NO and LOC

I need to search the IMINVLOC table for any records where ITEM_NO and LOC do
not match that in the IMITMIDX table.

The following query give me zero records even though I can manually find
some records:

SELECT *
FROM IMINVLOC_SQL INNER JOIN
IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
IMINVLOC_SQL.loc)

Any ideas?
Thanks.Hi

It is better to post DDL ( CREATE TABLE statements etc...) and example data
( as Insert statements ) than a description of pseudo code.

Either

SELECT L.*
FROM IMINVLOC L
WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

OR

SELECT L.*
FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC
WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> I am having trouble with what will surely be a simple query for you
experts.
> I have 2 tables with inventory data.
> IMITMIDX contains the master item info
> IMINVLOC contains location specific data such as quantity on hand at that
> location.
> These tables have 2 commons fields, ITEM_NO and LOC
> I need to search the IMINVLOC table for any records where ITEM_NO and LOC
do
> not match that in the IMITMIDX table.
> The following query give me zero records even though I can manually find
> some records:
> SELECT *
> FROM IMINVLOC_SQL INNER JOIN
> IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> IMINVLOC_SQL.loc)
>
> Any ideas?
> Thanks.|||Thank you very much for your help. I'm getting closer, let me try to state
my problem more clearly.
Every record in IMITMIDX must have a matching record in IMINVLOC with the
same ITEM_NO and LOC. IMINVLOC can have multiple records for the same item
in IMITMIDX (each location has a record). The query you provided gives me
records with item_no and loc that don't match that in imitmidx.

Example data:
Table: IMITMIDX
Item_no Loc
BRONZE SD

Table: IMINVLOC
Item_no Loc
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE SD
BRONZE VIS
BRONZE WSD
BRONZE RAW

Your query returns the following: (record with LOC = SD is not
returned)
BRONZE GSN
BRONZE RMN
BRONZE NS
BRONZE SA
BRONZE VIS
BRONZE WSD
BRONZE RAW

I need a query that will tell me when the IMINVLOC table does not contain
the same Item_no/Loc combination as the Imitmidx table.

Thanks again for the help.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> Hi
> It is better to post DDL ( CREATE TABLE statements etc...) and example
data
> ( as Insert statements ) than a description of pseudo code.
> Either
> SELECT L.*
> FROM IMINVLOC L
> WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> OR
> SELECT L.*
> FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC
> WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > I am having trouble with what will surely be a simple query for you
> experts.
> > I have 2 tables with inventory data.
> > IMITMIDX contains the master item info
> > IMINVLOC contains location specific data such as quantity on hand at
that
> > location.
> > These tables have 2 commons fields, ITEM_NO and LOC
> > I need to search the IMINVLOC table for any records where ITEM_NO and
LOC
> do
> > not match that in the IMITMIDX table.
> > The following query give me zero records even though I can manually find
> > some records:
> > SELECT *
> > FROM IMINVLOC_SQL INNER JOIN
> > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
> > IMINVLOC_SQL.loc)
> > Any ideas?
> > Thanks.|||Hi

Maybe this way around?

SELECT M.*
FROM IMITMIDX M
WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
AND M.LOC = L.LOC )

John

"RDRaider" <rdraider@.sbcglobal.net> wrote in message
news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> Thank you very much for your help. I'm getting closer, let me try to
state
> my problem more clearly.
> Every record in IMITMIDX must have a matching record in IMINVLOC with the
> same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
item
> in IMITMIDX (each location has a record). The query you provided gives me
> records with item_no and loc that don't match that in imitmidx.
> Example data:
> Table: IMITMIDX
> Item_no Loc
> BRONZE SD
> Table: IMINVLOC
> Item_no Loc
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE SD
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> Your query returns the following: (record with LOC = SD is not
> returned)
> BRONZE GSN
> BRONZE RMN
> BRONZE NS
> BRONZE SA
> BRONZE VIS
> BRONZE WSD
> BRONZE RAW
>
> I need a query that will tell me when the IMINVLOC table does not contain
> the same Item_no/Loc combination as the Imitmidx table.
> Thanks again for the help.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > Hi
> > It is better to post DDL ( CREATE TABLE statements etc...) and example
> data
> > ( as Insert statements ) than a description of pseudo code.
> > Either
> > SELECT L.*
> > FROM IMINVLOC L
> > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC )
> > OR
> > SELECT L.*
> > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > AND M.LOC = L.LOC
> > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > John
> > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > I am having trouble with what will surely be a simple query for you
> > experts.
> > > > I have 2 tables with inventory data.
> > > IMITMIDX contains the master item info
> > > IMINVLOC contains location specific data such as quantity on hand at
> that
> > > location.
> > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > I need to search the IMINVLOC table for any records where ITEM_NO and
> LOC
> > do
> > > not match that in the IMITMIDX table.
> > > > The following query give me zero records even though I can manually
find
> > > some records:
> > > > SELECT *
> > > FROM IMINVLOC_SQL INNER JOIN
> > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc
=
> > > IMINVLOC_SQL.loc)
> > > > > Any ideas?
> > > Thanks.
> >|||Thank you, that works!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:iwZEc.1256$Dv5.10834047@.news-text.cableinet.net...
> Hi
> Maybe this way around?
> SELECT M.*
> FROM IMITMIDX M
> WHERE NOT EXISTS ( SELECT * FROM IMINVLOC L WHERE M.ITEM_NO = L.ITEM_NO
> AND M.LOC = L.LOC )
> John
> "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> news:EnZEc.7044$Ul1.576@.newssvr27.news.prodigy.com ...
> > Thank you very much for your help. I'm getting closer, let me try to
> state
> > my problem more clearly.
> > Every record in IMITMIDX must have a matching record in IMINVLOC with
the
> > same ITEM_NO and LOC. IMINVLOC can have multiple records for the same
> item
> > in IMITMIDX (each location has a record). The query you provided gives
me
> > records with item_no and loc that don't match that in imitmidx.
> > Example data:
> > Table: IMITMIDX
> > Item_no Loc
> > BRONZE SD
> > Table: IMINVLOC
> > Item_no Loc
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE SD
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > Your query returns the following: (record with LOC = SD is not
> > returned)
> > BRONZE GSN
> > BRONZE RMN
> > BRONZE NS
> > BRONZE SA
> > BRONZE VIS
> > BRONZE WSD
> > BRONZE RAW
> > I need a query that will tell me when the IMINVLOC table does not
contain
> > the same Item_no/Loc combination as the Imitmidx table.
> > Thanks again for the help.
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:_rYEc.1174$rR4.10041557@.news-text.cableinet.net...
> > > Hi
> > > > It is better to post DDL ( CREATE TABLE statements etc...) and example
> > data
> > > ( as Insert statements ) than a description of pseudo code.
> > > > Either
> > > > SELECT L.*
> > > FROM IMINVLOC L
> > > WHERE NOT EXISTS ( SELECT * FROM IMITMIDX M WHERE M.ITEM_NO =
L.ITEM_NO
> > > AND M.LOC = L.LOC )
> > > > OR
> > > > SELECT L.*
> > > FROM IMINVLOC L LEFT JOIN IMITMIDX M ON M.ITEM_NO = L.ITEM_NO
> > > AND M.LOC = L.LOC
> > > WHERE M.ITEM_NO IS NULL AND M.LOC IS NULL
> > > > John
> > > > "RDRaider" <rdraider@.sbcglobal.net> wrote in message
> > > news:AQXEc.7015$qG.6055@.newssvr27.news.prodigy.com ...
> > > > I am having trouble with what will surely be a simple query for you
> > > experts.
> > > > > > I have 2 tables with inventory data.
> > > > IMITMIDX contains the master item info
> > > > IMINVLOC contains location specific data such as quantity on hand at
> > that
> > > > location.
> > > > > > These tables have 2 commons fields, ITEM_NO and LOC
> > > > > > I need to search the IMINVLOC table for any records where ITEM_NO
and
> > LOC
> > > do
> > > > not match that in the IMITMIDX table.
> > > > > > The following query give me zero records even though I can manually
> find
> > > > some records:
> > > > > > SELECT *
> > > > FROM IMINVLOC_SQL INNER JOIN
> > > > IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
> > > > where not exists (select loc from iminvloc_sql where
IMITMIDX_SQL.loc
> =
> > > > IMINVLOC_SQL.loc)
> > > > > > > > Any ideas?
> > > > Thanks.
> > > > > > >|||> I have 2 tables with inventory data. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

>> IMITMIDX contains the master item info;
IMINVLOC contains location specific data such as quantity on hand at
that
location. These tables have 2 common fields [sic], ITEM_NO and LOC <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I would assume from this
narrative that IMITMIDX should not have a location at all, but only
information about the items -- UPC, size, weight, color, etc. and that
it would be referenced by the
IMINVLOC table for the quantity at each location (warehouses?,
stores?).

>> I need to search the IMINVLOC table for any records [sic] where
ITEM_NO and LOC do not match that in the IMITMIDX table. <<

>> The following query give me zero records [sic]though I can manually
find some records [sic] <<

Why did you put "_SQL" postfixes on the names in the query? Never use
SELECT * in production code; I have no choice because I have no DDL:

SELECT I1.*, L1.*
FROM Imitmidx AS I1
LERFT OUTER JOIN
IminvLoc AS L1
ON I1.item_no = L1.item_no
AND I1.loc = L1.loc;

This will give you NULLs for the unmatched rows.

Never use uppercase letters for names (it is unreadable; that is why
newspapers and books are mixed case). Get a copy of ISO-11179 and
starting using the standards for data element names, too.