[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Multiple tables - Multiple Rows Maths Functions

stumpy_uk via SQLMonster.com

3/28/2007 5:16:00 PM

This is a cry for help as I have spent a week trying to find ways of doing
this and apart from a million SQL insert commands I really have no clue
(basically my SQL is only beaten by my dieting and believe me thats not good!)
3 Answers

masri999

3/28/2007 5:41:00 PM

0

On Mar 28, 10:15 pm, "stumpy_uk via SQLMonster.com" <u15773@uwe>
wrote:
> This is a cry for help as I have spent a week trying to find ways of doing
> this and apart from a million SQL insert commands I really have no clue
> (basically my SQL is only beaten by my dieting and believe me thats not good!)


What is your problem ?

stumpy_uk via SQLMonster.com

3/28/2007 6:04:00 PM

0

M A Srinivas wrote:
>On Mar 28, 10:15 pm, "stumpy_uk via SQLMonster.com" <u15773@uwe>
>wrote:
>> This is a cry for help as I have spent a week trying to find ways of doing
>> this and apart from a million SQL insert commands I really have no clue
>> (basically my SQL is only beaten by my dieting and believe me thats not good!)
>
>What is your problem ?

The Dieting is I eat too much, but in terms of this its create a temp table
which provides a single view of the employee / team / site

I will try and explain with some test data.....

Table 1
===================
DateFormulae varchar(50)
Employee varchar(8)
Itemcodes Varchar(20)
itemspicked int
itemssold int
itemsheld int

Data
-------

D-2703, 2814419, 10,3,5,6
D-2703, 2814419, 11,1,1,1
D-2703, 2814419, 12,5,5,5
D-2703, 1646686, 12,5,5,5


Table 2
===================
DateFormulae varchar(50)
Employee varchar(8)
CustomerCode varchar(20)
CustomersMet int
CustomersHeld int
Owners int

Data
-------

D-2703, 2814419, AA,3,5,6
D-2703, 2814419, BB,20,20,20
D-2703, 2814419, CC,2,2,2
D-2703, 9768276, CC,2,2,2


Table 3
===================
DateFormulae varchar(50)
Employee varchar(8)
Owners int
Cats int
Dogs int

Data
-------

D-2703, 2814419, 9,9,9
D-2703, 2814419, 10,10,10
D-2703, 2814419, 50,50,50



Table 4
=============================
Employee varchar(9) PK (unique)
Supervisor varchar(9)
Area varchar(20)
Site varchar(30)

Data
-------

Lee(2814419), Dave, Bristol, UK
Paul(9768276), Dave, Bristol, UK
Amar(2814421), Shailesh, Mumbai, India
Scott(1646686), Dave, Bristol, UK

So am after a way to get stats off the system to show Lee's Activity

User: Lee
Date D-2703
Items Picked 9 (Total of all items picked in Table 1)
Items Picked / Per Customer 9/27 (total picked / total
customers met)
Total Animals 138 (Total Dogs and Total Cats)


Then they want a report which shows Daves Team


User: Daves Team
Date D-2703
Items Picked 14 (Total items picked in Table 1 by Lee and Scott etc)
Items Picked / Per Customer etc
Total Animals etc


I can cope with one to many but many to many is freaking me out.......as is
the fact I am working with four tables all at once and they have to be used
in maths equasions etc.

Does that make any more sense...sorry if I am just confusing the matter...

Cheers for any help

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...

masri999

3/29/2007 5:59:00 AM

0

On Mar 28, 11:04 pm, "stumpy_uk via SQLMonster.com" <u15773@uwe>
wrote:
> M A Srinivas wrote:
> >On Mar 28, 10:15 pm, "stumpy_uk via SQLMonster.com" <u15773@uwe>
> >wrote:
> >> This is a cry for help as I have spent a week trying to find ways of doing
> >> this and apart from a million SQL insert commands I really have no clue
> >> (basically my SQL is only beaten by my dieting and believe me thats not good!)
>
> >What is your problem ?
>
> The Dieting is I eat too much, but in terms of this its create a temp table
> which provides a single view of the employee / team / site
>
> I will try and explain with some test data.....
>
> Table 1
> ===================
> DateFormulae varchar(50)
> Employee varchar(8)
> Itemcodes Varchar(20)
> itemspicked int
> itemssold int
> itemsheld int
>
> Data
> -------
>
> D-2703, 2814419, 10,3,5,6
> D-2703, 2814419, 11,1,1,1
> D-2703, 2814419, 12,5,5,5
> D-2703, 1646686, 12,5,5,5
>
> Table 2
> ===================
> DateFormulae varchar(50)
> Employee varchar(8)
> CustomerCode varchar(20)
> CustomersMet int
> CustomersHeld int
> Owners int
>
> Data
> -------
>
> D-2703, 2814419, AA,3,5,6
> D-2703, 2814419, BB,20,20,20
> D-2703, 2814419, CC,2,2,2
> D-2703, 9768276, CC,2,2,2
>
> Table 3
> ===================
> DateFormulae varchar(50)
> Employee varchar(8)
> Owners int
> Cats int
> Dogs int
>
> Data
> -------
>
> D-2703, 2814419, 9,9,9
> D-2703, 2814419, 10,10,10
> D-2703, 2814419, 50,50,50
>
> Table 4
> =============================
> Employee varchar(9) PK (unique)
> Supervisor varchar(9)
> Area varchar(20)
> Site varchar(30)
>
> Data
> -------
>
> Lee(2814419), Dave, Bristol, UK
> Paul(9768276), Dave, Bristol, UK
> Amar(2814421), Shailesh, Mumbai, India
> Scott(1646686), Dave, Bristol, UK
>
> So am after a way to get stats off the system to show Lee's Activity
>
> User: Lee
> Date D-2703
> Items Picked 9 (Total of all items picked in Table 1)
> Items Picked / Per Customer 9/27 (total picked / total
> customers met)
> Total Animals 138 (Total Dogs and Total Cats)
>
> Then they want a report which shows Daves Team
>
> User: Daves Team
> Date D-2703
> Items Picked 14 (Total items picked in Table 1 by Lee and Scott etc)
> Items Picked / Per Customer etc
> Total Animals etc
>
> I can cope with one to many but many to many is freaking me out.......as is
> the fact I am working with four tables all at once and they have to be used
> in maths equasions etc.
>
> Does that make any more sense...sorry if I am just confusing the matter...
>
> Cheers for any help
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-progra......

Check this
I will try and explain with some test data.....

/*
1
===================
*/
create table #tempa (
DateFormulae varchar(50),
Employee varchar(8),
Itemcodes Varchar(20),
itemspicked int ,
itemssold int ,
itemsheld int
)

/*Data
-------
*/


insert into #tempa values ('D-2703', '2814419', '10',3,5,6 )
insert into #tempa values ('D-2703', '2814419', '11',1,1,1 )
insert into #tempa values ('D-2703', '2814419', '12',5,5,5 )
insert into #tempa values ('D-2703', '1646686', '12',5,5,5 )

select * from #tempa

/*
2
===================
*/

create table #tempb (
DateFormulae varchar(50),
Employee varchar(8),
CustomerCode varchar(20),
CustomersMet int,
CustomersHeld int,
Owners int
)

/*
Data
-------
*/


insert into #tempb values('D-2703', '2814419', 'AA',3,5,6 )
insert into #tempb values('D-2703', '2814419', 'BB',20,20,20 )
insert into #tempb values('D-2703', '2814419', 'CC',2,2,2 )
insert into #tempb values('D-2703', '9768276', 'CC',2,2,2 )

select * from #tempb

/*
3
===================
*/

create table #tempc (
DateFormulae varchar(50) ,
Employee varchar(8),
Owners int ,
Cats int ,
Dogs int
)


/*
Data
-------
*/


insert into #tempc values ('D-2703', '2814419', 9,9,9 )
insert into #tempc values ('D-2703', '2814419', 10,10,10 )
insert into #tempc values ('D-2703', '2814419', 50,50,50 )

/*
4
=============================
*/
create table #tempd
(
Employee varchar(9) PRIMARY KEY ,
Supervisor varchar(9) ,
Area varchar(20) ,
Site varchar(30) ,
)

insert into #tempd values('2814419','Dave', 'Bristol', 'UK')
insert into #tempd values('9768276','Dave', 'Bristol', 'UK')
insert into #tempd values('2814421','Shailesh', 'Mumbai', 'India')
insert into #tempd values('1646686','Dave', 'Bristol', 'UK')

create table #tempe (Employee varchar(8), EmployeeName varchar(25))

insert into #tempe values ('2814419','Lee')
insert into #tempe values ('9768276','Paul')
insert into #tempe values ('2814421','Amar')
insert into #tempe values ('1646686','Scott')


/*
Data
-------


Lee(2814419), Dave, Bristol, UK
Paul(9768276), Dave, Bristol, UK
Amar(2814421), Shailesh, Mumbai, India
Scott(1646686), Dave, Bristol, UK


So am after a way to get stats off the system to show Lee's Activity


User: Lee
Date D-2703
Items Picked 9 (Total of all items picked in 1)
Items Picked / Per Customer 9/27 (total picked /
total
customers met)
Total Animals 138 (Total Dogs and Total Cats)



*/
drop table #tempf

select a.Employee,
e.EmployeeName ,
a.DateFormulae,
a.itemspicked,
CustomersMet,
c.TotalAnimals
into #tempf
from
( select
Employee,
DateFormulae,
sum(itemspicked) as itemspicked
from #tempa
group by
Employee,
DateFormulae ) a
left outer join
( select
Employee,
DateFormulae,
sum(CustomersMet) as CustomersMet
from #tempb
group by
Employee,
DateFormulae ) b
on a.Employee = b. Employee
and a.DateFormulae = b. DateFormulae
left outer join
( select
Employee,
DateFormulae,
sum(cats + dogs) as TotalAnimals
from #tempc
group by
Employee,
DateFormulae ) c
on a.Employee = c. Employee
and a.DateFormulae = c. DateFormulae
inner join
#tempe e
on a.Employee = e. Employee

select * from #tempf

select d.Supervisor, f.DateFormulae,
sum(f.itemspicked) as itemspicked,
sum(f.customersmet) as customersmet,
sum(f.totalanimals) as totalanimals
from #tempf f, #tempd d
where f.employee = d.employee
group by
d.Supervisor, f.DateFormulae


/*

Then they want a report which shows Daves Team


User: Daves Team
Date D-2703
Items Picked 14 (Total items picked in 1 by Lee
and Scott etc)
Items Picked / Per Customer etc
Total Animals etc


I can cope with one to many but many to many is freaking me
out.......as is
the fact I am working with four s all at once and they have to be
used
in maths equasions etc.


Does that make any more sense...sorry if I am just confusing the
matter...


Cheers for any help
*/