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
 Could use help with a query

Author  Topic 

starzen
Starting Member

2 Posts

Posted - 2011-09-05 : 10:36:25
Trying to figure out how to do the following

Following tables

Cust
Sched
Dept
Accnt

SCHED contains multiple schedule records for each customer
Each row in SCHED links to one department in DEPT
There can be many rows in ACCNT for each SCHED row

I need a query that will list the following

Count of unique customers in each department for a range of dates in SCHED
In addition CUST has a field called UNITS that needs to be totaled up per department as well also only for unique customers. Also this field could contain a zero which should be treated as a one instead

Another query with almost the same scenario but it also involves the ACCNT table which can have many records for each SCHED and I need a field called AMOUNT totaled up as well

Any help would be greatly appreciated

Mike

gwilson67
Starting Member

42 Posts

Posted - 2011-09-05 : 11:29:08
Off hand I would think something like this would work:

Select Distinct count(*) from Customers c inner join Schedule s on c.customerid = s.customerid
where datefield between '1/1/90' and getdate()

select sum(coalesce(Units,0,1)) from customer
group by department

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 12:59:04
quote:
Originally posted by starzen

Trying to figure out how to do the following

Following tables

Cust
Sched
Dept
Accnt

SCHED contains multiple schedule records for each customer
Each row in SCHED links to one department in DEPT
There can be many rows in ACCNT for each SCHED row

I need a query that will list the following

Count of unique customers in each department for a range of dates in SCHED
In addition CUST has a field called UNITS that needs to be totaled up per department as well also only for unique customers. Also this field could contain a zero which should be treated as a one instead
something like below

select d.deptname,count(distinct customerid),sum(coalesce(nullif(units,0),1))
from cust c
inner join sched s
on s.custid = c.custid
inner join dept d
on d.deptid = s.deptid
where s.datefield between @startdate and @enddate
group by d.deptname


Another query with almost the same scenario but it also involves the ACCNT table which can have many records for each SCHED and I need a field called AMOUNT totaled up as well

select d.deptname,SUM(Amount)
from sched s
inner join dept d
on d.deptid = s.deptid
inner join accnt a
on a.schedid = s.schedid
group by d.deptname

Any help would be greatly appreciated

Mike





something like above
i've assumed columnnames as you've not provided any
make sure you replace them with correct column names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

starzen
Starting Member

2 Posts

Posted - 2011-09-07 : 22:12:03
@visakh16

thanks. problem is that the sum will create a sum of the unit field for each SCHED row not for each Customer so if i have 16 unique customers but 1000 schedule records it will add it up for each of the 1000 rows not for the 16 customers

@gwilson67

not sure about the queries how they would get the result

this is what i came up with. seemed a little complicated and i am not sure if it is definately correct


select department, count(*) as clientcount, sum(unit) as unit
from (
select distinct(custno), department, max(unit) as unit

from (
select department, custno, c.unit
from sched s
join cust c on c.custno = s.custno
where date between '2011-02-01' and '2011-02-28'
) as temptb
group by custno, department
) as temptb1
group by department


the inner query gets a list of sched records for a date range and adds the unit field from the customer table

the next query gets a list of distinct customers from the first and using the max operator fudges the unit field back into the query (value will be the same for all rows in a customer so this should work, right ?)

next query then counts all customer rows and creates a sum of the unit column sorted and grouped by department

see anything wrong with this or anything that could be done better ?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:31:45
quote:
Originally posted by starzen

@visakh16

thanks. problem is that the sum will create a sum of the unit field for each SCHED row not for each Customer so if i have 16 unique customers but 1000 schedule records it will add it up for each of the 1000 rows not for the 16 customers

can you show some sample data to illustrate this? do you mean sum is repeating from each sched?
@gwilson67

not sure about the queries how they would get the result

this is what i came up with. seemed a little complicated and i am not sure if it is definately correct


select department, count(*) as clientcount, sum(unit) as unit
from (
select distinct(custno), department, max(unit) as unit

from (
select department, custno, c.unit
from sched s
join cust c on c.custno = s.custno
where date between '2011-02-01' and '2011-02-28'
) as temptb
group by custno, department
) as temptb1
group by department


the inner query gets a list of sched records for a date range and adds the unit field from the customer table

the next query gets a list of distinct customers from the first and using the max operator fudges the unit field back into the query (value will be the same for all rows in a customer so this should work, right ?)

next query then counts all customer rows and creates a sum of the unit column sorted and grouped by department

see anything wrong with this or anything that could be done better ?





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -