SQL Query

Kai

Howdy.

I have 3 tables, a subject table and a usage table and a DateRange table.

The Subject table contains Email info such as Subject, body etc.

The Usage table contains a list of the email subjects and the dates they were sent on.

The DateRange table has date/Month info.



Now what i want to do is get a list of Emails that were never used by Month.
The Subject and Usage table join on the Subject Field (Dont ask!).

I can get a list of Emails that were never used by running :

SELECT     ID, MailSubject, MailCategory, MailBody, MailCreateDate
FROM         dbo.Subject S
WHERE     (MailSubject NOT IN
                          (SELECT     MailSubject
                            FROM          dbo.Usage U))

but that doesnt have them by month which comes from my daterange table. how do i join that in ?

Evil Phil

SELECT     ID, MailSubject, MailCategory, MailBody, MailCreateDate
FROM         dbo.Subject S
INNER JOIN dbo.DateRange dr ON s.MailCreateDate = dr.MailCreateDate
WHERE     (MailSubject NOT IN
                          (SELECT     MailSubject
                            FROM          dbo.Usage U))

something like that maybe?

<edit>
It's just for illustration purposes btw

Kai

Yea but thats joined on the Subject table rather than the Usage Table.

I need to join the Usage table to the DateRange table in order to get What Emails were not used in each Month.

Ive tried it a few different ways and i cant seem to get it.

Zaph0d

These questions are easier to answer if you post the table definitions. Just type sp_help <table_name> and paste the output.

What is the purpose of the DateRange table and what are the fields in the DateRange table that you need? Is it just the corresponding month? If so you can derive this from the MailCreateDate field.

If so you, try a query like this:

SELECT   datename(year,s.MailCreateDate),
         datename(month,S.MailCreateDate), S.ID, S.MailSubject, 
         S.MailCategory, S.MailBody, S.MailCreateDate

FROM     dbo.Subject S LEFT OUTER JOIN dbo.Usage U
             ON S.MailSubject = U.MailSubject

WHERE    U.MailSubject IS NULL

ORDER BY S.MailCreateDate

Kai

Heres the 2 tables :

CREATE TABLE [MailUsage] (
	[ID] [int] NOT NULL ,
	[RepScreenName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[MemberScreenName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[MailSubject] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[TimeDate] [smalldatetime] NULL ,
	[AccountNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[HistoryCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL 
) ON [PRIMARY]
GO



CREATE TABLE [MailSubject] (
	[ID] [int] NOT NULL ,
	[MailSubject] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[MailCategory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[MailCreateDate] [smalldatetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I can see what you have in mind in the SQL you have above Zaphod but its not giving me what i want.

I want to get a list of Months and for each month a list of the Mail types that were not sent.

Zaph0d

Could you post the definition of the DateRange table and explain its purpose?

Also what should the output of this query look like? Is it something like this?

Month    Subject      ID
-------  ------------ --
Jan      Re: meeting  23
Jan      Hello        29
Feb      Greetings    35

Kai

The Date Range table has 2 fields: Date and Month. All it does is proved a lookup on a datefield so i can get the Month, WeekNo, Sasdate and a few other types of dates that i use but im only interested in the monthly stuff for this particular query.

Yea the Result should be what you have above.

amen

as an aside using MailSubject [nvarchar] (50) as a key to join the two tables is a bad idea. What you should have done is use the ID from MailSubject (make it unique) and have that in MailUseage much neater.

Anyway the SQL below should work.

SELECT D.MailMonth,S.*
FROM #MailSubject S FULL OUTER JOIN #MailUsage U ON S.MailSubject = U.MailSubject
join #DateRange D ON D.MailID = DATEPART(MONTH,S.MailCreateDate)
WHERE U.ID IS NULL
ORDER by D.MailMonth

I took the month out of the date and added a key to DateRange MailID
with 1= JAN, 2=FEB etc
let me know how you get on

I don't really like your table designs.
1: The use of MailSubject as a key (nvarchar)
2: The general use of nvarchars (waste of space) e.g having RepScreenName/MemberScreenName as nvarchar(50) is silly
A: waste of db space. Should have a look up table MailUsers with a UserID INT and RepScreenName/MemberScreenName here. Then place the UserID into
MailUseage. Much neater
B: If a RepScreenName/MemberScreenName using your table design you will have to update all the recordes to reflect the name change. Using my design you only need to use 1 row in 1 table
3: I think MailCategory should be treated as above.

Kai

amen wrote:

I don't really like your table designs.
1: The use of MailSubject as a key (nvarchar)
2: The general use of nvarchars (waste of space) e.g having RepScreenName/MemberScreenName as nvarchar(50) is silly
A: waste of db space. Should have a look up table MailUsers with a UserID INT and RepScreenName/MemberScreenName here. Then place the UserID into
MailUseage. Much neater
B: If a RepScreenName/MemberScreenName using your table design you will have to update all the recordes to reflect the name change. Using my design you only need to use 1 row in 1 table
3: I think MailCategory should be treated as above.

Just for the record its not my design. Its just a set of tables ive inherited which is tied to a live application so i cant change the tables either. Im stuck with what i have and the guy who designed it is long gone :mad:

Thanks for the reply ill check it out now in a few mins.