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.
Author |
Topic |
starzen
Starting Member
2 Posts |
Posted - 2011-09-05 : 10:36:25
|
Trying to figure out how to do the followingFollowing tables CustSchedDeptAccntSCHED contains multiple schedule records for each customerEach row in SCHED links to one department in DEPTThere can be many rows in ACCNT for each SCHED rowI need a query that will list the followingCount of unique customers in each department for a range of dates in SCHEDIn 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 insteadAnother 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 wellAny help would be greatly appreciatedMike |
|
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.customeridwhere datefield between '1/1/90' and getdate()select sum(coalesce(Units,0,1)) from customergroup by departmentGreghttp://www.freewebstore.org/tsqlcoderepositoryPowerful tool for SQL Server development |
|
|
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 followingFollowing tables CustSchedDeptAccntSCHED contains multiple schedule records for each customerEach row in SCHED links to one department in DEPTThere can be many rows in ACCNT for each SCHED rowI need a query that will list the followingCount of unique customers in each department for a range of dates in SCHEDIn 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 belowselect d.deptname,count(distinct customerid),sum(coalesce(nullif(units,0),1))from cust cinner join sched son s.custid = c.custidinner join dept don d.deptid = s.deptidwhere s.datefield between @startdate and @enddategroup by d.deptnameAnother 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 wellselect d.deptname,SUM(Amount)from sched sinner join dept don d.deptid = s.deptidinner join accnt aon a.schedid = s.schedidgroup by d.deptnameAny help would be greatly appreciatedMike
something like abovei've assumed columnnames as you've not provided anymake sure you replace them with correct column names------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
starzen
Starting Member
2 Posts |
Posted - 2011-09-07 : 22:12:03
|
@visakh16thanks. 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@gwilson67not sure about the queries how they would get the resultthis 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 unitfrom ( 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 temptb1group by department the inner query gets a list of sched records for a date range and adds the unit field from the customer tablethe 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 ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-09 : 02:31:45
|
quote: Originally posted by starzen @visakh16thanks. 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 customerscan you show some sample data to illustrate this? do you mean sum is repeating from each sched?@gwilson67not sure about the queries how they would get the resultthis 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 unitfrom ( 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 temptb1group by department the inner query gets a list of sched records for a date range and adds the unit field from the customer tablethe 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|