Seperating dates by field. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Seperating dates by field.

Author

Topic

Tristan
Starting Member

5 Posts

Posted - 2007-03-19 : 12:26:01

Hello, this is my first post to these forums so... Hi.

I'm currently doing a select statement and I cannot figure out how to get it to do what I want it to.

I'm trying get the statement to show how much activity was done in each year in a separate field and I don't have any idea where to start to get this accomplished.

Here's the code:

select distinct client.Number as [ClientNumber], client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip,
count(distinct [transaction].date) as VisitsWithinDateRange, client.LastVisit

from client

inner join [transaction] on client.number = [transaction].client

where

[transaction].date >= '01/01/1980'
and
[transaction].date <= '01/01/2008'
and
client.status like '%a%'
and
client.status not like '%n%'
and
client.status not like '%i%'
and
client.clienttype <> 'C'

group by client.number, client.Number, client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip, client.LastVisit

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-19 : 12:36:47

How do you want your results to look? What does the schema look like? Please provide more information in order for us to help you.

Tristan
Starting Member

5 Posts

Posted - 2007-03-19 : 13:03:16

What I'd like it to look like is this

Client Number - Firstname - LastName - Address - City - State - Zip - VisitsWithinDateRange - LastVisit - Year1Activity - Year2Activity - Year3Activity - Year4Activity - Year5Activity

I'm not sure if this is possible but that's what I'm looking to do.

Let me know if you need any more information.

Thanks! =)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 13:09:59

Something like

select a, b, c, d,
sum(case when col1 between '20020101' and '20021231' then col2 else 0 end) as year1,
sum(case when col1 between '20030101' and '20031231' then col2 else 0 end) as year2
FROM table1
group by a, b, c, d
order by

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources