sql server programming: Hi
I need to display each and every month and total customers shopped in a , say a supermarket by month and the new members shopped in very month in other column i.e the members shopped in a month which is not in the previous mont

The Tabe is something like this

Member transaction_date sho
A 2003-11-01 zzz
A 2003-12-15 zzz
B 2003-11-01 zzz
B 2003-12-01 zzz
C 2003-12-05 zzz

So, C is the new member in DEC 2003

I need to find this for all months for the current year & the previous year

Thanks In Advanc

Since you haven't posted DDLs & expected results, here is a guess:

SELECT DISTINCT t1.member
FROM tbl t1
WHERE CONVERT( CHAR(6), t1.trans_dt, 112 )
= ( SELECT MAX( CONVERT(CHAR(6), t2.trans_dt, 112) )
FROM tbl t2 ) ;

--
Anith

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Here is a guess based on pretty much nothing:

CREATE TABLE Sales
(member_id CHAR(10) NOT NULL
REFERENCES Membership (member_id),
trans_date DATETIME NOT NULL,
store_id CHAR(10) NOT NULL
REFERENCES Stores (store_id),
..
PRIMARY KEY (member_id, trans_date));

CREATE TABLE Membership
(member_id CHAR(10) NOT NULL PRIMARY KEY,
membership_date DATETIME NOT NULL,
...);

Creating a calendar table is a standard programming trick in SQL. In
this case number the months from some starting point, say 1900-01-01.

CREATE TABLE Calendar
(month_nbr INTEGER NOT NULL,
month_name CHAR(8) NOT NULL, --'2004 Jan'
month_start DATETIME NOT NULL,
month_finish DATETIME NOT NULL,
...);

[quoted text, click to view]
shopped in very month in other column i.e the members shopped in a month
which is not in the previous month <<

Count each customer once in a month, no matter how many transactions he
had.

SELECT S1.month_name,
COUNT(DISTINCT S1.member_id) AS cust_tally
COUNT(DISTINCT S2.member_id) AS old_cust_tally
FROM Calendar AS C1
INNER JOIN
Sales AS S1
ON S1.trans_date BETWEEN C1.month_start
AND C1.month_finish
LEFT OUTER JOIN
Sales AS S2 -- previous month
ON S2.month_nbr + 1 = S1.month_nbr
AND S2.member_id = S1.member_id
WHERE S1.month_name = @my_month_name;

This is untested.




--CELKO--

*** Sent via Developersdex http://www.developersdex.com ***