using joins to create a report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using joins to create a report

Author

Topic

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 13:14:23

I've been asked to create this report and I am not sure how to do this. I'm not a DBA and we don't have one in the company. The request is to create a report that looks like the following. Can someone please help. I am including our tables below.

Date Partner GMID Subject ViewTotal
1/2/09 1 123ABC ABC 4
1/2/09 2 124DJF DEF 9
1/3/09 4 328EJF GHIH 5
1/3/09 2 124DJF DKJS 12

MAIL TABLE
[ID]
[GMID]
[UserID]
[CreatedDate]
[Status]
[Subject]
[bViewable]
[bRegistered]
[bAutomaticDelete]
[DeletionDate]
[FileSize]
[Duration]
[MailGUID]
[MailVersion]
[TotalViews]
[FirstView]
[LastView]
[CdnPath]
[PlayerThemeID]

MAIL_VIEWS TABLE
[ID]
[MailID]
[ViewDateTime]
[IP]
[GMStartDateTime]
[GMFinishDateTime]
[Completed]
[Name]
[Referrer]

USERS TABLE
[ID]
[UserID]
[FirstName]
[LastName]
[Email]
[Password]
[PIN]
[ProductStatus]
[ProductType]
[UserGUID]
[IsVerified]
[IsInActive]
[Sku]
[Partner]
[LeadSource]
[CreationDate]
[ModifyDate]
[GMUserId]
[CompanyId]
[PlayerThemeId]
[IsAdmin]
[BPPurchased]
[IsPartner]
[IsTrial]
[DefaultCompanyID]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 13:21:04

Where is Date Column coming from? Specify relationships between tables?

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 14:50:40

Sorry, i was meaning to post that info but forgot. The date column comes from the MAIL_views table, more specifically the ViewDateTime. The date should be in the form on MONTH-DAY-YEAR. The field is DateTime type. As for relations,

mail_views.mailid = mail.id
mail.userid=users.id

I hope this is the info you are looking for. Thanks for taking the time.

quote:
Originally posted by sodeep

Where is Date Column coming from? Specify relationships between tables?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 15:04:57

[code]Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from Mail ML inner join [MAIL VIEWS] MV
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject[/code]

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 15:43:35

This is what I get when I try to run it

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.

quote:

Originally posted by sodeep

Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from Mail ML inner join [MAIL VIEWS] MV
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-12 : 15:47:29

put a space here V
V
SUM(ML.TotalViews) as ViewTotal

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 15:58:40

Sorry, but that didn't help. still get the same thing

quote:
Originally posted by yosiasz

put a space here V
V
SUM(ML.TotalViews) as ViewTotal

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 16:01:10

Should from Mail ML inner join [MAIL VIEWS] MV be
from Mail ML inner join [MAIL_VIEWS] MV

*notice the underscore in MAIL_VIEWS. Probably couldn't tell from my post because of the underline. Anyways I tried it with an underscore but it still didn't work

quote:
Originally posted by modemgeek

This is what I get when I try to run it

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.

quote:
Originally posted by sodeep

Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from Mail ML inner join [MAIL VIEWS] MV
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 16:01:23

Put your real Tablename.

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 16:03:10

Not sure if this makes any difference but the columns don't actually have the [] in the name. i should have removed it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 16:05:43

Found the culprit. Remove this:

quote:
Originally posted by modemgeek

Should from Mail ML inner join [MAIL VIEWS] MV be
from Mail ML inner join [MAIL_VIEWS] MV

*notice the underscore in MAIL_VIEWS. Probably couldn't tell from my post because of the underline. Anyways I tried it with an underscore but it still didn't work

quote:
Originally posted by modemgeek

This is what I get when I try to run it

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.

quote:
Originally posted by sodeep

Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from Mail ML inner join [MAIL VIEWS] MV
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime ~~as [Date],~~U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 16:13:28

I did some testing and it doesn't like this bolded section

Group by MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject

quote:
Originally posted by modemgeek

This is what I get when I try to run it

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.

quote:
Originally posted by sodeep

Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from Mail ML inner join [MAIL VIEWS] MV
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 16:16:13

[code]Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from MAIL_VIEWS MV Inner join MAIL ML
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime,U.Partner,ML.GMID,ML.Subject
Order by MV.ViewDateTime,U.Partner,ML.GMID,ML.Subject[/code]

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 16:48:21

Ahhh...much better. But I would like to cut off the time portion of the date so it groups it better.

quote:

Originally posted by sodeep

Select MV.ViewDateTime as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from MAIL_VIEWS MV Inner join MAIL ML
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by MV.ViewDateTime,U.Partner,ML.GMID,ML.Subject
Order by MV.ViewDateTime,U.Partner,ML.GMID,ML.Subject

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 16:52:08

[code]Select Dateadd(dd,datediff(dd,0,MV.ViewDateTime),0)as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from MAIL_VIEWS MV Inner join MAIL ML
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by Dateadd(dd,datediff(dd,0,MV.ViewDateTime),0),U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 17:10:30

Or this:

Select Convert(Varchar (30),MV.ViewDateTime,101)as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from MAIL_VIEWS MV Inner join MAIL ML
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by Convert(Varchar (30),MV.ViewDateTime,101),U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject

modemgeek
Starting Member

12 Posts

Posted - 2009-02-12 : 17:39:34

You sir are a life saver. I ran the query and all looks good except for the view count. The view count appears to be doubling the actual # of total views of everyone. What I needed was the number of views per day per partner

quote:

Originally posted by sodeep

Or this:

Select Convert(Varchar (30),MV.ViewDateTime,101)as [Date],U.Partner,ML.GMID,ML.Subject,
SUM(ML.TotalViews)as ViewTotal
from MAIL_VIEWS MV Inner join MAIL ML
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Group by Convert(Varchar (30),MV.ViewDateTime,101),U.Partner,ML.GMID,ML.Subject
Order by [Date],U.Partner,ML.GMID,ML.Subject

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 17:54:22

quote:

Originally posted by sodeep

Maybe:

Select Distinct Dateadd(dd,datediff(dd,0,MV.ViewDateTime),0)as [Date],U.Partner,ML.GMID,ML.Subject,
COUNT(ML.TotalViews) OVER (Partition by Dateadd(dd,datediff(dd,0,MV.ViewDateTime),0),U.Partner)as ViewCount
from MAIL_VIEWS MV Inner join MAIL ML
ON ML.ID = MV.MAILID
Inner join Users U
On U.ID = ML.UserID
Order by [Date],U.Partner,ML.GMID,ML.Subject

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources