July 17, 2012 at 12:24 pm
Hello,
Table A - Facilities (currently 100 rows - distinct rows)
FacilitiesID int
Facility varchar(30)
Sample Data for Table A
Boston
Chicago
Miami
Table B - ReportItems (currently 3500 rows)
ReportID int
ReportDate datetime
ReportedItem varchar(250)
Facility varchar(30)
Select distinct reporteditem from reportitems (80 distinct report items)
Sample Data for Table B
1 1/1/12 Parking Lot issue Miami
2 2/1/12 Trash spilled Chicago
3 3/1/12 Car accident Boston
My Challenge
I'm trying to create a query that shows the count of all the reportitems for each facility submitted by the user. The resultset has to show all 80 reportitems even if that particular facility didn't have any of that specific reportitems.
Declare @ReportTbl table(reporteditems nvarchar(250))
insert into @ReportTbl(reporteditems)
select distinct reporteditems from ReportItems
order by reporteditems-------this gives me the 80 distinct report items.
Select c.facility, a.reporteditem, b.reportdate
from @ReportTbl a
left outer join ReportItems b
on a.reporteditems = b.reporteditems
inner join facility c
on b.facility = c.facility
where c.facility in ('Miami', "Boston', 'Chicago')
group by c.facility, b.reporteditem
This is not showing me 80 items for Miami, 80 items for Boston and 80 items for Chicago.
Does anyone have an idea how to show the 80 items for each facility queried?
Hopefully the above is coherent enough to solicity some help. If not, I will clarify anything that is confusing.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
July 18, 2012 at 4:43 am
As per my understanding Hope the following query'd return the No.of Reported Items of each Facility
Select distinct F.Facility,Count(ReportedItem) [No.of.Items] from ReportItems R
RIGHT Join Facility F on R.Facility= F.Facility
Group By F.Facility
Please let me know if any concerns... 😉
Thanks
Kivan.G
July 18, 2012 at 5:21 am
Select distinct F.Facility,Count(ReportedItem) AS [No.of.Items] from Facility F
LEFT OUTER JOIN on ReportItems R
F.Facility = R.Facility
Group By F.Facility
This should work fine
Thanks
July 18, 2012 at 9:33 am
I appreciate the help. However, what I need is to list ALL incidents for each facility whether they had any incidents or not.
For example,
if Table b had the following:
Sample Data for Table B
1 1/1/12 Parking Lot issue Miami
2 2/1/12 Trash spilled Chicago
3 3/1/12 Car accident Boston
I need a query that returns the following:
Facility Reporteditems ReportedItemCount
===== ========= ============
Miami Parking Lot Issue 1
Miami Trash Spilled 0
Miami Car accident 0
Chicago Parking Lot Issue 0
Chicago Trash Spilled 1
Chicago Car accident 0
Boston Parking Lot Issue 0
Boston Trash Spilled 0
Boston Car accident 1
It returns for each facility all of the dinstinct reporteditems and its count for that facility.
Thank you.
Tony
Things will work out. Get back up, change some parameters and recode.
July 18, 2012 at 10:27 am
First, please note how I setup your test data. This is what you should do for us to make it easier to help you. Please, this lets you insure that the data types are accurate.
Also, it makes it cut, paste, and execute to setup a test environment to work on your problem.
Here is what I came up with to solve your problem.
DECLARE @TableA TABLE (
FacilitiesID INT,
Facility varchar(30)
);
DECLARE @TableB TABLE(
ReportID INT,
ReportDate DATETIME,
ReportedItem varchar(250),
Facility varchar(30)
);
INSERT INTO @TableA
( FacilitiesID, Facility )
VALUES (1, 'Boston'),(2,'Chicago'),(3,'Miami');
INSERT INTO @TableB
( ReportDate ,
ReportID ,
Facility ,
ReportedItem
)
VALUES ('20120101',1,'Miami','Parking Lot Issue'),
('20120201',2,'Chicago','Trash Spilled'),
('20120301',3,'Boston','Car Accident');
WITH Issues AS (
SELECT
a1.Facility,
i1.ReportedItem
FROM
@TableA a1
CROSS JOIN (
SELECT DISTINCT
ReportedItem
FROM
@TableB
) i1
)
SELECT
i.Facility,
i.ReportedItem,
COUNT(b.ReportedItem) ReportedItemCount
FROM
Issues i
LEFT OUTER JOIN @TableB b
ON (i.Facility = b.Facility
AND i.ReportedItem = b.ReportedItem)
GROUP BY
i.Facility,
i.ReportedItem
ORDER BY
i.Facility,
i.ReportedItem;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply