August 18, 2011 at 2:45 pm
And for final reference because people may Google this one day. I used table variables and they were way easier to think about and work with.
Plus as an added bonus i never have to drop anything and they remain completely in memory. Below is the stored procedure i wrote and i am pretty happy with it. I know its really simple at least it looks to me that way now. I'm kinda new to this so last week i would have looked at this and growled.
Anyways here is the good and thank to @sean Lange for his help.
USE [DatabaseName]
GO
/****** Object: StoredProcedure [dbo].[sproc_Report_OurcoolReport] Script Date: 08/17/2011 17:08:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_Report_OurcoolReport] (
@BeginDateRange as Date,
@EndDateRange as Date,
@Offices as XML)
--DECLARE @BeginDateRange AS DATE
--DECLARE @EndDateRange AS DATE
--DECLARE @Offices AS XML
--SET @BeginDateRange = '06/15/2010'
--SET @EndDateRange = '06/17/2012'
--SET @Offices ='<Root><Key ID="4" /><Key ID="6" /><Key ID="8" /><Key ID="112" /><Key ID="128" /><Key ID="129" /><Key ID="130" /></Root>'
AS
BEGIN
DECLARE @Visits TABLE (
OfficeID INT,
PatientVisitCount INT)
INSERT INTO @Visits (OfficeID, PatientVisitCount)
SELECT Offices.OfficeID, COUNT(DISTINCT CONVERT(CHAR(10), Orders.OrderAdministeredTimestamp, 101)) AS UniqueVisitDates
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderAdministeredTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0) AND OrderAdministeredTimestamp IS NOT NULL
GROUP BY Offices.OfficeID
DECLARE @UniquePatients Table (
OfficeID INT,
UniquePatientVisits INT)
INSERT INTO @UniquePatients (OfficeID, UniquePatientVisits)
SELECT Offices.OfficeID, Count(DISTINCT Orders.PatientID) AS PatientCount
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderAdministeredTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0)
Group by Offices.OfficeID
DECLARE @TotalItems Table(
OfficeID INT,
TotalItems INT)
INSERT INTO @TotalItems (OfficeID, TotalItems)
SELECT Offices.OfficeID, COUNT(OrderItems.OrderItemID) as ItemCount
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0)
GROUP BY Offices.OfficeID
DECLARE @UniqueItems Table(
OfficeID INT,
UniqueItems INT)
INSERT INTO @UniqueItems (OfficeID,UniqueItems)
SELECT Offices.OfficeID, COUNT(DISTINCT OrderItems.OrderItemID)AS ItemCountUnique
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0)
GROUP BY Offices.OfficeID
SELECT Offices.OfficeName, Visits.PatientVisitCount, UniquePatients.UniquePatientVisits, TotalItems.TotalItems, UniqueItems.UniqueItems
FROM Offices
JOIN @Visits visits ON visits.OfficeID = Offices.OfficeID
JOIN @UniquePatients UniquePatients ON UniquePatients.OfficeID = Offices.OfficeID
JOIN @TotalItems TotalItems ON TotalItems.OfficeID = Offices.OfficeID
JOIN @UniqueItems UniqueItems ON UniqueItems.OfficeID = Offices.OfficeID
Order By Offices.OfficeID
END
August 18, 2011 at 2:50 pm
Looks like you got a nice solution (and one you can understand). Thanks for posting your final result. I am sure somebody along the way will appreciate it. And thanks for the plug, happy to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply