T-SQL problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL problem

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 07:51:45

Greetings friends,

I have the following table:


if object_id('tempdb..#ExtractHistory') > 0
drop table #ExtractHistory

create table #ExtractHistory (Id int, DateDTS datetime, UsageCount int)

insert into #ExtractHistory (Id, DateDTS, UsageCount)
select 1, getdate()-5, 1 union all
select 1, getdate()-3, 2 union all
select 1, getdate()-12, 1 union all
select 1, getdate()-7, 3 union all
select 2, getdate(), 1 union all
select 3, getdate()-16, 3

if object_id('tempdb..#MainTable') > 0
drop table #MainTable

create table #MainTable (Id int, ClientId int, Forename varchar(100), Surname varchar(100))

insert into #MainTable (Id, ClientId, Forename, Surname)
select 1, 1, 'Bob', 'Smith' union all
select 1, 1, 'Joe', 'Hawkes' union all
select 2, 1, 'Barbara', 'Tucker' union all
select 2, 1, 'Alex', 'Ferdinand' union all
select 3, 2, 'George', 'Brown' union all
select 4, 2, 'Alessandro', 'De Biaggio' union all
select 5, 1, 'Adolfo', 'Montana'

if object_id('tempdb..#Client') > 0
drop table #Client

create table #Client (ClientId int, Client varchar(50))

insert into #Client (ClientId, Client)
select 1, 'Client #1' union all
select 2, 'Client #2' union all
select 3, 'Client #3' union all
select 4, 'Clinet #4'

What I want to produce is for each client the number of IDs that had a usage count of 0, 1 , 2, 3 etc..

How do I do this?

I tried the following but it doesn't give back the right result:


select count(a.Id), b.Client, sum(c.UsageCount)
from #MainTable as a
inner join #Client as b
	on a.ClientId  = b.ClientId
left join  #ExtractHistory as c
	on a.Id = c.Id
group by b.Client
having sum(c.UsageCount) = 0
union all
select count(a.Id), b.Client, sum(c.UsageCount)
from #MainTable as a
inner join #Client as b
	on a.ClientId  = b.ClientId
left join  #ExtractHistory as c
	on a.Id = c.Id
group by b.Client
having sum(c.UsageCount) = 1
union all
select count(a.Id), b.Client, sum(c.UsageCount)
from #MainTable as a
inner join #Client as b
	on a.ClientId  = b.ClientId
left join  #ExtractHistory as c
	on a.Id = c.Id
group by b.Client
having sum(c.UsageCount) = 2

Your advice would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 08:01:24

is this what you want? some sample output for you provided data will help

select c.Client,
sum(case when eh.UsageCount=1 then 1 else 0 end),
sum(case when eh.UsageCount=2 then 1 else 0 end),
sum(case when eh.UsageCount=3 then 1 else 0 end),
sum(case when eh.UsageCount=4 then 1 else 0 end),
...
from #MainTable m
inner join #Client c
on c.ClientId=m.ClientId
inner join #ExtractHistory eh
on eh.id=m.id
group by c.Client

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 10:07:16

quote:

Originally posted by visakh16

is this what you want? some sample output for you provided data will help

select c.Client,
sum(case when eh.UsageCount=1 then 1 else 0 end),
sum(case when eh.UsageCount=2 then 1 else 0 end),
sum(case when eh.UsageCount=3 then 1 else 0 end),
sum(case when eh.UsageCount=4 then 1 else 0 end),
...
from #MainTable m
inner join #Client c
on c.ClientId=m.ClientId
inner join #ExtractHistory eh
on eh.id=m.id
group by c.Client

Hi again, I am sorry I wasn't clear before. This is what I a have and what I am after. Hope it makes sense:


if object_id('tempdb..#UsageHistory') > 0
drop table #UsageHistory

create table #UsageHistory (Id int, DateDTS datetime, UsageCount int)

insert into #UsageHistory (Id, DateDTS, UsageCount)
select 1, getdate()-5, 1 union all
select 1, getdate()-3, 2 union all
select 2, getdate()-3, 1 union all
select 2, getdate()-10, 5


if object_id('tempdb..#MainTable') > 0
drop table #MainTable

create table #MainTable (Id int, ClientId int, Forename varchar(100), Surname varchar(100))

insert into #MainTable (Id, ClientId, Forename, Surname)
select 1, 1, 'Bob', 'Smith' union all
select 2, 1, 'Barbara', 'Tucker' union all
select 3, 3, 'George', 'Brown'


if object_id('tempdb..#Client') > 0
drop table #Client

create table #Client (ClientId int, Client varchar(50))

insert into #Client (ClientId, Client)
select 1, 'Client #1' union all
select 2, 'Client #2' union all
select 3, 'Client #2'

if object_id('tempdb..#ExpectedResultSet') > 0
drop table #ExpectedResultSet

create table #ExpectedResultSet (Client varchar(50), [0_Usages] int, [1_Usages] int, [2_Usages] int, [3_Usages] int, [4_Usages] int, [5_Usages] int, [6_Usages] int)

insert into #ExpectedResultSet (Client, [0_Usages], [1_Usages], [2_Usages], [3_Usages], [4_Usages], [5_Usages], [6_Usages])
select 'Client #1', 0, 0, 0, 1, 0, 0, 0 union all
select 'Client #2', 0, 0, 0, 0, 0, 0, 1 union all
select 'Client #3', 1, 0, 0, 0, 0, 0, 0 

select * from #UsageHistory
select * from #MainTable
select * from  #Client
select * from #ExpectedResultSet

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 10:18:20

I created an expectResults table to show what I am trying to get at. Any help on how to write a query to get the data in te format of ExpectedResultSet would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 10:27:51

can you explain how client 2 got 1 in 6 usages? i cant find a matching record for client 2 in maintable. or is it that client id is directly related to id in #UsageHistory?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 10:40:41

I will explain yes,

We'll start with the #UsageHistory table. What this says is that for Id = 1 the record was used on 3 occasions (if you sum up the usage count from the two records). Id = 2 was used 6 times (again you sum up the usagecount column).

My question is : give me back a list of all my clients, and thenumber of IDs that were used 0 times, 1 times, 2 times etc... In order to get the client we must join the usagehistory with the MainTable, and then from there we join to client to get the client name etc...

I got this far for now and I trying but to no avail:


select a.*, b.*, c.*
from #MainTable as a
inner join #Client as b
	on a.ClientId = b.ClientId
left join #UsageHistory as c
	on a.Id = c.Id

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 10:44:49

My sample data is wrong, I apologise.

It should be :


if object_id('tempdb..#UsageHistory') > 0
drop table #UsageHistory

create table #UsageHistory (Id int, DateDTS datetime, UsageCount int)

insert into #UsageHistory (Id, DateDTS, UsageCount)
select 1, getdate()-5, 1 union all
select 1, getdate()-3, 2 union all
select 2, getdate()-3, 1 union all
select 2, getdate()-10, 5


if object_id('tempdb..#MainTable') > 0
drop table #MainTable

create table #MainTable (Id int, ClientId int, Forename varchar(100), Surname varchar(100))

insert into #MainTable (Id, ClientId, Forename, Surname)
select 1, 1, 'Bob', 'Smith' union all
select 2, 2, 'Barbara', 'Tucker' union all
select 3, 3, 'George', 'Brown'


if object_id('tempdb..#Client') > 0
drop table #Client

create table #Client (ClientId int, Client varchar(50))

insert into #Client (ClientId, Client)
select 1, 'Client #1' union all
select 2, 'Client #2' union all
select 3, 'Client #3'

if object_id('tempdb..#ExpectedResultSet') > 0
drop table #ExpectedResultSet

create table #ExpectedResultSet (Client varchar(50), [0_Usages] int, [1_Usages] int, [2_Usages] int, [3_Usages] int, [4_Usages] int, [5_Usages] int, [6_Usages] int)

insert into #ExpectedResultSet (Client, [0_Usages], [1_Usages], [2_Usages], [3_Usages], [4_Usages], [5_Usages], [6_Usages])
select 'Client #1', 0, 0, 0, 1, 0, 0, 0 union all
select 'Client #2', 0, 0, 0, 0, 0, 0, 1 union all
select 'Client #3', 1, 0, 0, 0, 0, 0, 0

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 10:49:06

Hi again,

This is my solution, what do you guys think?


select
	Client,
	case when UsageCount = 0 then 1 else 0 end as [0_Usage]
	,case when UsageCount = 1 then 1 else 0 end as [1_Usage]
	,case when UsageCount = 2 then 1 else 0 end as [2_Usage]
	,case when UsageCount = 3 then 1 else 0 end as [3_Usage]
	,case when UsageCount = 4 then 1 else 0 end as [4_Usage]
	,case when UsageCount = 5 then 1 else 0 end as [5_Usage]
	,case when UsageCount = 6 then 1 else 0 end as [6_Usage]
from
(select b.Client, count( distinct isnull(c.Id, a.Id)) as URN, sum(isnull(UsageCount, 0)) as UsageCount
from #MainTable as a
inner join #Client as b
	on a.ClientId = b.ClientId
left join #UsageHistory as c
	on a.Id = c.Id
group by b.Client) as d

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 10:58:04

Grrrr.. my sulotion doesn't work...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 11:01:14

quote:
Originally posted by Abu-Dina

Grrrr.. my sulotion doesn't work...

why? what's the error?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 11:15:33

Okay Viskah, let me explain more detail

Please run the following to create the sample data then run the select to view all 4 tables. I will talk you through what is happening.

if object_id('tempdb..#UsageHistory') > 0
drop table #UsageHistory

create table #UsageHistory (Id int, DateDTS datetime, UsageCount int)

insert into #UsageHistory (Id, DateDTS, UsageCount)
select 1, getdate()-5, 1 union all
select 1, getdate()-3, 2 union all
select 2, getdate()-3, 1 union all
select 2, getdate()-10, 5 union all
select 4, getdate()-4, 3


if object_id('tempdb..#MainTable') > 0
drop table #MainTable

create table #MainTable (Id int, ClientId int, Forename varchar(100), Surname varchar(100))

insert into #MainTable (Id, ClientId, Forename, Surname)
select 1, 1, 'Bob', 'Smith' union all
select 2, 2, 'Barbara', 'Tucker' union all
select 3, 3, 'George', 'Brown' union all
select 4, 1, 'n00b', 'noobie'


if object_id('tempdb..#Client') > 0
drop table #Client

create table #Client (ClientId int, Client varchar(50))

insert into #Client (ClientId, Client)
select 1, 'Client #1' union all
select 2, 'Client #2' union all
select 3, 'Client #3'

if object_id('tempdb..#ExpectedResultSet') > 0
drop table #ExpectedResultSet

create table #ExpectedResultSet (Client varchar(50), Number_of_Ids int, [0_Usages] int, [1_Usages] int, [2_Usages] int, [3_Usages] int, [4_Usages] int, [5_Usages] int, [6_Usages] int)

insert into #ExpectedResultSet (Client, Number_of_Ids, [0_Usages], [1_Usages], [2_Usages], [3_Usages], [4_Usages], [5_Usages], [6_Usages])
select 'Client #1', 2, 0, 0, 0, 1, 0, 0, 0 union all
select 'Client #2', 1, 0, 0, 0, 0, 0, 0, 1 union all
select 'Client #3', 1, 1, 0, 0, 0, 0, 0, 0 

select * from #UsageHistory
select * from #MainTable
select * from  #Client
select * from #ExpectedResultSet

Let's look at the #UsageHistory table. For Id = 1 we can see that the Id was used 3 times. Once on the 13/07 and twice on 15/07. To know which client this record belongs to, we have the #MainTable. All we use from this is the ClientId. So based on this we can say that Client #1 has a record Id = 1 that was used 3 times. Now if you look at record Id = 4 in #UsageHistory you can see that it also belongs to ClientId = 1 and it was also used 3 times. The #ExpectedResultSet table is what I am after. So it says that for Client # 1 there are two unique (1 and 4) that were used 3 times each).

Apply the same logic to Id = 2. It was accessed 6 times. Once on the 15/07 and 5 times on 08/07. From
#MainTable we see that it belongs to clientId = 2. So in #ExpectedResultSet there is only 1 record that belongs to Client #2 and that was accessed 6 times.

Does that make sense?

So far I have:


select
	Client,
	URN
	,case when UsageCount = 0 then 1 else 0 end as [0_Usage]
	,case when UsageCount = 1 then 1 else 0 end as [1_Usage]
	,case when UsageCount = 2 then 1 else 0 end as [2_Usage]
	,case when UsageCount = 3 then 1 else 0 end as [3_Usage]
	,case when UsageCount = 4 then 1 else 0 end as [4_Usage]
	,case when UsageCount = 5 then 1 else 0 end as [5_Usage]
	,case when UsageCount = 6 then 1 else 0 end as [6_Usage]
from
(select b.Client, count( distinct isnull(c.Id, a.Id)) as URN, sum(isnull(UsageCount, 0)) as UsageCount
from #MainTable as a
inner join #Client as b
	on a.ClientId = b.ClientId
left join #UsageHistory as c
	on a.Id = c.Id
group by b.Client) as d

But this says that there are 2 records for Client #1 that were used 6 tims each which is not true, they were used 3 times each. I'm almost there but it's summing the usage count for some reason.

Any help would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 11:30:03

[code]select
Client,
numberofids,
,case when UsageCount = 0 then 1 else 0 end as [0_Usage]
,case when UsageCount = 1 then 1 else 0 end as [1_Usage]
,case when UsageCount = 2 then 1 else 0 end as [2_Usage]
,case when UsageCount = 3 then 1 else 0 end as [3_Usage]
,case when UsageCount = 4 then 1 else 0 end as [4_Usage]
,case when UsageCount = 5 then 1 else 0 end as [5_Usage]
,case when UsageCount = 6 then 1 else 0 end as [6_Usage]
from
(select b.Client, count(b.id) as numberofids,
sum(when c.Id is not null then c.IdSum else 0 end) as UsageCount
from #MainTable as a
inner join (select distinct ClientId,Id as idcount
from #Client)as b
on a.ClientId = b.ClientId
left join (select Id,sum(UsageCount) as IdSum
from #UsageHistory
group by Id)as c
on a.Id = c.Id
group by b.Client) as d[/code]

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-07-18 : 11:34:49

This is what I've come up with, it's a dirty solution but it does the job. I'd be intersted to know if anyone can come up with a better one:


select
	e.Client
	,e.Number_Of_URNS as URNCount
	,case when UsageCount = 0 then 1 else 0 end as [0_Usage]
	,case when UsageCount = 1 then 1 else 0 end as [1_Usage]
	,case when UsageCount = 2 then 1 else 0 end as [2_Usage]
	,case when UsageCount = 3 then 1 else 0 end as [3_Usage]
	,case when UsageCount = 4 then 1 else 0 end as [4_Usage]
	,case when UsageCount = 5 then 1 else 0 end as [5_Usage]
	,case when UsageCount = 6 then 1 else 0 end as [6_Usage]
from
	(select 
	Client, count(Id) as Number_Of_URNS, UsageCount
	from 
		(select b.Client, a.Id, isnull(sum(UsageCount), 0) as UsageCount
		from #MainTable as a
		inner join #Client as b
			on a.ClientId = b.ClientId
		left join #UsageHistory as c
			on a.Id = c.Id
		group by b.Client, a.Id) as d
	group by Client, UsageCount) as e

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources