September 23, 2011 at 3:37 pm
What I'm trying to do is grab a combined set of data. I COULD do this another way, but I'm wondering if there's a way to do it right in SQL. From what I've found online, this isn't possible. Since I know that anything is usually possible (and my last request here was answered very quickly!), I'm trying again.
Problem: One table (FoodInspection and FoodRiskInspection) contains unique values whereas the second does not (FoodViolation and FoodRiskViolation) but it links to the unique values on the first uniquely.
Hopeful solution: Merge each in the columns of Violations in some way (valuerow1; valuerow2; etc) for each of the Violation columns. *edit*-Some inspections have up to 64 violations (current max), with 7 columns needing merged.
I thought of taking the inspections and selecting them first as inspections then the violations together but I still ran into the same problem AND it made it longer and (IMO) harder to read.
SELECT FoodFacility.ParentUNID, FoodFacility.AssignedTo, FoodFacility.PhysicalAddress, FoodFacility.PhysicalCity, FoodFacility.PhysicalMunicipality,
FoodFacility.PhysicalProvince AS PhysicalState, FoodFacility.PhysicalPostalCode, FoodFacility.PHF, FoodFacility.PHFCooked, FoodFacility.PHFRaw,
FoodInspection.SourceDatabaseName, FoodInspection.HSUNID, FoodInspection.FacilityName, FoodInspection.FacilityType, FoodInspection.Type AS InspectionType,
FoodInspection.InspectedBy, FoodInspection.InspectionDate, FoodInspection.NumCritical, FoodInspection.NumNonCritical, FoodInspection.TimeSpent,
FoodInspection.Enforcement, FoodInspection.NonSmoking, FoodViolation.Code, FoodViolation.Description, FoodViolation.IsCritical, FoodViolation.IsRepeat,
FoodViolation.Observations, FoodViolation.CorrectiveActions, FoodViolation.ViolCorrectedSet, FoodFacility.Name, FoodFacility.Stage, FoodFacility.Status,
FoodFacility.Type AS FacilityType, FoodFacility.DocumentID, FoodInspection.UNID, FoodViolation.UNID AS ViolUNID
FROM FoodFacility RIGHT OUTER JOIN
FoodInspection ON FoodFacility.UNID = FoodInspection.ParentUNID FULL OUTER JOIN
FoodViolation ON FoodInspection.UNID = FoodViolation.UNID
WHERE (FoodInspection.SourceDatabaseName = @db) AND (FoodInspection.InspectionDate BETWEEN CONVERT(DATETIME, @start, 102) AND
CONVERT(DATETIME, @end, 102))
UNION ALL
SELECT FoodFacility_1.ParentUNID, FoodFacility_1.AssignedTo, FoodFacility_1.PhysicalAddress, FoodFacility_1.PhysicalCity, FoodFacility_1.PhysicalMunicipality,
FoodFacility_1.PhysicalProvince AS PhysicalState, FoodFacility_1.PhysicalPostalCode, FoodFacility_1.PHF, FoodFacility_1.PHFCooked, FoodFacility_1.PHFRaw,
FoodRiskInspection.SourceDatabaseName, FoodRiskInspection.HSUNID, FoodRiskInspection.FacilityName, FoodRiskInspection.FacilityType,
FoodRiskInspection.Type AS InspectionType, FoodRiskInspection.InspectedBy, FoodRiskInspection.InspectionDate, FoodRiskInspection.NumCritical,
FoodRiskInspection.NumNonCritical, '0' AS TimeSpent, '' AS Enforcement, FoodRiskInspection.NonSmoking, FoodRiskViolation_1.Code,
FoodRiskViolation_1.Description, FoodRiskViolation_1.IsCritical, FoodRiskViolation_1.IsRepeat, FoodRiskViolation_1.Observations,
FoodRiskViolation_1.CorrectiveActions, FoodRiskViolation_1.ViolCorrectedSet, FoodFacility_1.Name, FoodFacility_1.Stage, FoodFacility_1.Status,
FoodFacility_1.Type AS FacilityType, FoodFacility_1.DocumentID, FoodRiskInspection.UNID, FoodRiskViolation_1.UNID AS ViolUNID
FROM FoodFacility AS FoodFacility_1 RIGHT OUTER JOIN
FoodRiskInspection ON FoodFacility_1.UNID = FoodRiskInspection.ParentUNID FULL OUTER JOIN
FoodRiskViolation AS FoodRiskViolation_1 ON FoodRiskInspection.UNID = FoodRiskViolation_1.UNID
WHERE (FoodRiskInspection.SourceDatabaseName = @db) AND (FoodRiskInspection.InspectionDate BETWEEN CONVERT(DATETIME, @start,
102) AND CONVERT(DATETIME, @end, 102))
September 23, 2011 at 3:59 pm
Kinder, I think I'm understanding what you're trying to do, but not well enough.
Can you provide table DDL and sample data? You'll see what we're looking for in the first link in my signature. The union and description alone isn't enough to fully grok (at least for me) your intent and problem.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 23, 2011 at 5:36 pm
OK.... I've never done this before, so I'm not sure how to test it. I tried running it as a Query from Visual Studio 2008 and get errors. Our system exports to the DB via a plugin and builds the tables. Hopefully I set this up right. Can this be ran and completed from VS?
Though I made it look like all values are unique in the Violations, they are not. The only unique are the UNID and ID. Others may be identical or may be unique.
What I would hope to see as a row result for FoodInspection.UNID = 'MKIR-123451' is:
SELECT '14','XYZ', 'MKIR-123451', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete', 'ABC12340; ABC12341', 'Desc1; Desc1', 'Y; Y', 'N; Y', 'Obs10; Obs11', 'CA10; CA11', 'Corrected; Not Corrected'
Insert code per directions, I think!
--===== If the test table already exists, drop it
DROP TABLE [dbo].[FoodInspection]
--===== Create the test table with
CREATE TABLE [dbo].[FoodInspection]
(
IDint IDENTITY (1, 1) NOT NULL,
SourceDatabaseNamevarchar(1000),
HSUNIDvarchar(11),
FacilityNamevarchar(1000),
FacilityTypevarchar(1000),
Typevarchar(1000),
InspectedByvarchar(1000),
InspectionDatedatetime,
NumCriticalint,
NumNonCriticalint,
TimeSpentdecimal(9,2),
Enforcementvarchar(1000),
NonSmokingvarchar(1000)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #FoodInspection ON
--===== Insert the test data into the test table INSERT INTO #FoodInspection
(ID, SourceDatabaseName, HSUNID, FacilityName, FacilityType, Type, InspectedBy, InspectionDate, NumCritical, NumNonCritical, TimeSpent, Enforcement, NonSmoking)
SELECT '14','XYZ', 'MKIR-123451', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '37','XYZ', 'MKIR-123452', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '44','XYZ', 'MKIR-123453', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '54','XYZ', 'MKIR-123454', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '55','XYZ', 'MKIR-123455', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '81','XYZ', 'MKIR-123456', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '86','XYZ', 'MKIR-123457', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '96','XYZ', 'MKIR-123458', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #FoodInspection OFF
--===== If the test table already exists, drop it
DROP TABLE [dbo].[FoodViolation]
--===== Create the test table with
CREATE TABLE [dbo].[FoodViolation]
(
IDint IDENTITY (1, 1) NOT NULL,
HSUNIDvarchar(11),
Codevarchar(1000),
Descriptionvarchar(1000),
IsCriticalvarchar(1),
IsRepeatvarchar(1),
Observationsvarchar(1000),
CorrectiveActionsvarchar(1000),
ViolCorrectedSetvarchar(13)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #FoodViolation ON
--===== Insert the test data into the test table INSERT INTO #FoodViolation
(ID, HSUNID, Code, Description, IsCritical, IsRepeat, Observations, CorrectiveActions, ViolCorrectedSet)
SELECT '140', 'MKIR-123451', 'ABC12340', 'Desc1', 'Y', 'N', 'Obs10, 'CA10', 'Corrected' UNION ALL
SELECT '141', 'MKIR-123451', 'ABC12341', 'Desc1', 'Y', 'N', 'Obs11, 'CA11', 'Not Corrected' UNION ALL
SELECT '142', 'MKIR-123452', 'ABC12342', 'Desc1', 'Y', 'N', 'Obs12, 'CA12', 'Corrected' UNION ALL
SELECT '143', 'MKIR-123452', 'ABC12343', 'Desc1', 'Y', 'N', 'Obs13, 'CA13', 'Corrected' UNION ALL
SELECT '144', 'MKIR-123453', 'ABC12344', 'Desc1', 'Y', 'N', 'Obs14, 'CA14', 'Corrected' UNION ALL
SELECT '145', 'MKIR-123453', 'ABC12345', 'Desc1', 'Y', 'N', 'Obs15, 'CA15', 'Corrected' UNION ALL
SELECT '146', 'MKIR-123453', 'ABC12346', 'Desc1', 'Y', 'N', 'Obs16, 'CA16', 'Corrected' UNION ALL
SELECT '147', 'MKIR-123454', 'ABC12347', 'Desc1', 'Y', 'N', 'Obs17, 'CA17', 'Corrected' UNION ALL
SELECT '148', 'MKIR-123454', 'ABC12348', 'Desc1', 'Y', 'N', 'Obs18, 'CA18', 'Corrected' UNION ALL
SELECT '149', 'MKIR-123454', 'ABC12349', 'Desc1', 'Y', 'N', 'Obs19, 'CA19', 'Not Corrected' UNION ALL
SELECT '150', 'MKIR-123454', 'ABC12350', 'Desc1', 'Y', 'N', 'Obs20, 'CA20', 'Corrected' UNION ALL
SELECT '151', 'MKIR-123454', 'ABC12341', 'Desc1', 'Y', 'N', 'Obs21, 'CA21', 'Corrected' UNION ALL
SELECT '152', 'MKIR-123455', 'ABC12342', 'Desc1', 'Y', 'N', 'Obs22, 'CA22', 'Corrected' UNION ALL
SELECT '153', 'MKIR-123455', 'ABC12343', 'Desc1', 'Y', 'N', 'Obs23, 'CA23', '' UNION ALL
SELECT '154', 'MKIR-123456', 'ABC12343', 'Desc1', 'Y', 'N', 'Obs24, 'CA24', '' UNION ALL
SELECT '155', 'MKIR-123456', 'ABC12344', 'Desc1', 'Y', 'N', 'Obs25, 'CA25', 'Corrected' UNION ALL
SELECT '156', 'MKIR-123456', 'ABC12345', 'Desc1', 'Y', 'N', 'Obs26, 'CA26', 'Corrected' UNION ALL
SELECT '157', 'MKIR-123456', 'ABC12346', 'Desc1', 'Y', 'N', 'Obs27, 'CA27', 'Corrected' UNION ALL
SELECT '158', 'MKIR-123456', 'ABC12347', 'Desc1', 'Y', 'N', 'Obs28, 'CA28', 'Corrected'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #FoodViolation OFF
--===== If the test table already exists, drop it
DROP TABLE [dbo].[FoodInspection]
--===== Create the test table with
CREATE TABLE [dbo].[FoodInspection]
(
IDint IDENTITY (1, 1) NOT NULL,
SourceDatabaseNamevarchar(1000),
HSUNIDvarchar(11),
FacilityNamevarchar(1000),
FacilityTypevarchar(1000),
Typevarchar(1000),
InspectedByvarchar(1000),
InspectionDatedatetime,
NumCriticalint,
NumNonCriticalint,
TimeSpentdecimal(9,2),
Enforcementvarchar(1000),
NonSmokingvarchar(1000)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #FoodRiskInspection ON
--===== Insert the test data into the test table
INSERT INTO #FoodRiskInspection
(ID, SourceDatabaseName, HSUNID, FacilityName, FacilityType, Type, InspectedBy, InspectionDate, NumCritical, NumNonCritical, TimeSpent, Enforcement, NonSmoking)
SELECT '14','XYZ', 'MKIR-R23451', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '37','XYZ', 'MKIR-R23452', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '44','XYZ', 'MKIR-R23453', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '54','XYZ', 'MKIR-R23454', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '55','XYZ', 'MKIR-R23455', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '81','XYZ', 'MKIR-R23456', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '86','XYZ', 'MKIR-R23457', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete' UNION ALL
SELECT '96','XYZ', 'MKIR-R23458', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #FoodRiskInspection OFF
--===== If the test table already exists, drop it
DROP TABLE [dbo].[FoodRiskViolation]
--===== Create the test table with
CREATE TABLE [dbo].[FoodRiskViolation]
(
IDint IDENTITY (1, 1) NOT NULL,
HSUNIDvarchar(11),
Codevarchar(1000),
Descriptionvarchar(1000),
IsCriticalvarchar(1),
IsRepeatvarchar(1),
Observationsvarchar(1000),
CorrectiveActionsvarchar(1000),
ViolCorrectedSetvarchar(13)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #FoodRiskViolation ON
--===== Insert the test data into the test table
INSERT INTO #FoodRiskViolation
(ID, HSUNID, Code, Description, IsCritical, IsRepeat, Observations, CorrectiveActions, ViolCorrectedSet)
SELECT '140', 'MKIR-R23451', 'ABC12340', 'Desc1', 'Y', 'N', 'Obs10, 'CA10', 'Corrected' UNION ALL
SELECT '141', 'MKIR-R23451', 'ABC12341', 'Desc1', 'Y', 'N', 'Obs11, 'CA11', 'Not Corrected' UNION ALL
SELECT '142', 'MKIR-R23452', 'ABC12342', 'Desc1', 'Y', 'N', 'Obs12, 'CA12', 'Corrected' UNION ALL
SELECT '143', 'MKIR-R23452', 'ABC12343', 'Desc1', 'Y', 'N', 'Obs13, 'CA13', 'Corrected' UNION ALL
SELECT '144', 'MKIR-R23453', 'ABC12344', 'Desc1', 'Y', 'N', 'Obs14, 'CA14', 'Corrected' UNION ALL
SELECT '145', 'MKIR-R23453', 'ABC12345', 'Desc1', 'Y', 'N', 'Obs15, 'CA15', 'Corrected' UNION ALL
SELECT '146', 'MKIR-R23453', 'ABC12346', 'Desc1', 'Y', 'N', 'Obs16, 'CA16', 'Corrected' UNION ALL
SELECT '147', 'MKIR-R23454', 'ABC12347', 'Desc1', 'Y', 'N', 'Obs17, 'CA17', 'Corrected' UNION ALL
SELECT '148', 'MKIR-R23454', 'ABC12348', 'Desc1', 'Y', 'N', 'Obs18, 'CA18', 'Corrected' UNION ALL
SELECT '149', 'MKIR-R23454', 'ABC12349', 'Desc1', 'Y', 'N', 'Obs19, 'CA19', 'Not Corrected' UNION ALL
SELECT '150', 'MKIR-R23454', 'ABC12350', 'Desc1', 'Y', 'N', 'Obs20, 'CA20', 'Corrected' UNION ALL
SELECT '151', 'MKIR-R23454', 'ABC12341', 'Desc1', 'Y', 'N', 'Obs21, 'CA21', 'Corrected' UNION ALL
SELECT '152', 'MKIR-R23455', 'ABC12342', 'Desc1', 'Y', 'N', 'Obs22, 'CA22', 'Corrected' UNION ALL
SELECT '153', 'MKIR-R23455', 'ABC12343', 'Desc1', 'Y', 'N', 'Obs23, 'CA23', '' UNION ALL
SELECT '154', 'MKIR-R23456', 'ABC12343', 'Desc1', 'Y', 'N', 'Obs24, 'CA24', '' UNION ALL
SELECT '155', 'MKIR-R23456', 'ABC12344', 'Desc1', 'Y', 'N', 'Obs25, 'CA25', 'Corrected' UNION ALL
SELECT '156', 'MKIR-R23456', 'ABC12345', 'Desc1', 'Y', 'N', 'Obs26, 'CA26', 'Corrected' UNION ALL
SELECT '157', 'MKIR-R23456', 'ABC12346', 'Desc1', 'Y', 'N', 'Obs27, 'CA27', 'Corrected' UNION ALL
SELECT '158', 'MKIR-R23456', 'ABC12347', 'Desc1', 'Y', 'N', 'Obs28, 'CA28', 'Corrected'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #FoodRiskViolation OFF
September 26, 2011 at 12:35 pm
kinderdesign (9/23/2011)
OK.... I've never done this before, so I'm not sure how to test it. I tried running it as a Query from Visual Studio 2008 and get errors. Our system exports to the DB via a plugin and builds the tables. Hopefully I set this up right. Can this be ran and completed from VS?
You want code that when you run it in SSMS (Management Services) it runs end to end in a query window. If VS is involved that means some coder built this at some point and it's probably directly integrated into the code, you'll most likely need them involved.
Btw, your code doesn't work, at least not end to end. The OBS10 code is missing its trailing apostrophe's and I stopped trying there.
However... Noooooooo:
What I would hope to see as a row result for FoodInspection.UNID = 'MKIR-123451' is:
SELECT '14','XYZ', 'MKIR-123451', 'ABC', 'ABC', 'MSK', 'Oct 17 2007 12:00AM', '1', '0', '1.1', 'NO', 'Complete', 'ABC12340; ABC12341', 'Desc1; Desc1', 'Y; Y', 'N; Y', 'Obs10; Obs11', 'CA10; CA11', 'Corrected; Not Corrected'
You're thinking about this the wrong way. You want to present the data vertically, not horizontally, and particularly not in the SAME column with semicolon separators.
There are ways to do it, in particular using the FOR XML operator. It's incredibly expensive to do that in the SQL interface though particularly for multiple columns. This process sounds like the front end coders interfaced directly into the DB rather then proc calls with a more standard build. I would recommend speaking with them directly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply