Order Not receive -- sql server programming

qjlee
1/27/2007 9:41:01 PM

I think there is another way to script it without have to create the month
table.
Month can be calculated by (current month - 1), (current month -2 ), etc.
then for each month, find out those clients who are in table A (client
information table, which is a list of all clients) but not in table B (order
information table). If I want do it this way, how to script it?

Thanks,

qjlee

[quoted text, click to view]

Roy Harvey
1/27/2007 11:15:41 PM

I am going to assume a Months table exists, keyed by a datetime with
one row for the first day of each month. I will also assume that
OrderMonth is in the same format.

Something along these lines should do the job.

SELECT C.ClientName, M.CalendarMonth
FROM Clients as C CROSS JOIN Months as M
WHERE NOT EXISTS
(select * from Orders as O
where O.ClientName = C.ClientName
and O.OrderMonth = Months.CalendarMonth)
AND datediff(month, M.CalendarMonth, getdate()) between 1 and 6

Roy Harvey
Beacon Falls, CT

On Sat, 27 Jan 2007 19:00:00 -0800, qjlee
[quoted text, click to view]

Uri Dimant
1/28/2007 9:25:54 AM

I think Roy answered your question. I'd like to ask, can you get OrderMonth
=5 and recdate '20070701'?
I meant 5- May and recdate is July?

This is a sample data, what would be output?

create table tableA (ClientName varchar(50) , OrderMonth int , Recdate
datetime)
create table tableB (row_id int not null primary key, ClientName varchar(50)
, ClientAdd varchar(50))

insert into TableA values ('John Smith',1 ,'20070101')
insert into TableA values ('Bill Clinton',2 ,'20070102')
insert into TableA values ('Bill Clinton',3 ,'20070103')
insert into TableA values ('Vladimir Putin',1 ,'20070110')

insert into TableB values (1,'John Smith','Usa 57 Street')
insert into TableB values (2,'Bill Clinton','Usa Arkansas')
insert into TableB values (3,'Vladimir Putin','Moscow Kremlin')

---goes a new order

insert into TableA values ('Vladimir Putin',1 ,'20060101')

[quoted text, click to view]

select * from TableA where not exists
( select * from TableB where TableA.ClientName =TableB.ClientName and
TableA.Recdate >= dateadd(month,-6,getdate()))

[quoted text, click to view]

Erland Sommarskog
1/28/2007 3:02:00 PM

qjlee (qjlee@discussions.microsoft.com) writes:
[quoted text, click to view]

Correct. You can do it without a month table. Nevertheless, you will need
something that creates the month span. So if it is not a table, it's
something else. A common solution to problems of this kind is to use a
table of numbers. That is a one-column table with numbers from 1, 2, 3
and up to some chosen limit (which you hopefully never exceeds!). Here
is an example:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

qjlee
1/28/2007 6:45:00 PM

Hi, Roy:

What if I also want to get the client address information?

Thanks,

Qjlee

[quoted text, click to view]

Roy Harvey
1/28/2007 11:43:56 PM

On Sun, 28 Jan 2007 18:45:00 -0800, qjlee
[quoted text, click to view]

Add them to the SELECT list, since (I assume) they are columns in the
Clients table (designated as Table B in the original post.)

Roy Harvey

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Order Not receive