September 11, 2015 at 11:54 am
Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.
Background: Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.
The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.
Like I said at the beginning, I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied. Any thoughts would be appreciated. The logic is just escaping me right now.
The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask).
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE ( RefOnly = 0
OR RefOnly IS NULL
)
AND ( AddressId IN ( SELECT AddressId
FROM Inspections
WHERE InspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126' ) )
AND ( ViolationDate < ( SELECT InspectionDate
FROM Inspections
WHERE ( InspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126' )
) )
AND ( ( InspectionId <> '1DBC9981-57C3-49F1-BC19-20544F9D4126' )
AND (InspectionNumber LIKE LEFT(InspectionNumber,
CHARINDEX(InspectionNumber, '.'))
+ '%')
)
AND ( ( CorrectedDate >= ( SELECT InspectionDate
FROM Inspections
WHERE InspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126'
)
OR CorrectedDate IS NULL
)
OR CorrectedInspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126'
OR CorrectedDate IS NULL
);
September 11, 2015 at 12:24 pm
We would need some DDL and sample data to try to understand what's going on in here and give proper advice.
This is what I got from your query, but it might not give the same results as I have nothing to test against.
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE (RefOnly = 0 OR RefOnly IS NULL)
AND InspectionId <> '1DBC9981-57C3-49F1-BC19-20544F9D4126'
AND InspectionNumber IS NOT NULL
AND EXISTS (
SELECT AddressId
FROM Inspections i
WHERE InspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126'
AND v_InspectionDetailsReports.AddressId = i.AddressId
AND v_InspectionDetailsReports.ViolationDate < i.InspectionDate
AND ( v_InspectionDetailsReports.CorrectedDate >= i.InspectionDate
OR v_InspectionDetailsReports.CorrectedInspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126'
OR v_InspectionDetailsReports.CorrectedDate IS NULL
)
)
;
There was a clause that really surprised me, here's a test I set up for it. What were you expecting to get?
DECLARE @test-2 TABLE( InspectionNumber varchar(100));
INSERT INTO @test-2 VALUES('fadsoijfl.341'), ('dnfuiaerf'), ('.fujrefr'), ('.'), (''), (NULL);
SELECT *
FROM @test-2
WHERE (InspectionNumber LIKE LEFT(InspectionNumber, CHARINDEX(InspectionNumber, '.')) + '%')
Finally, try to remove that hard code to facilitate your work.
September 11, 2015 at 1:59 pm
From what you've described, I think this will select the rows you need. Btw, I hope the table isn't clustered on that guid, even though that is the PK.
Edit: Corrected InspectionNumber WHERE condition range.
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE
( RefOnly = 0
OR RefOnly IS NULL
)
AND InspectionNumber BETWEEN (
SELECT LEFT(InspectionNumber, CHARINDEX('.', InspectionNumber + '.') - 1) AS InspectionNumber
FROM dbo.v_InspectionDetailsReports
WHERE InspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126'
)
AND (
SELECT InspectionNumber
FROM dbo.v_InspectionDetailsReports
WHERE InspectionId = '1DBC9981-57C3-49F1-BC19-20544F9D4126'
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 11, 2015 at 2:51 pm
ScottPletcher,
Getting:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
September 11, 2015 at 2:58 pm
Hmm, interesting. I thought the InspectionId was a "primary key", and therefore unique. Sorry, I'm confused now about the data structure and relationships.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 11, 2015 at 3:12 pm
My mistake, I too was confused about what data this view was gathering. After some further investigation, each time a violation (InspectionDetailId) is created during an Inspection (InspectionId), it adds a row to the InspectionDetails table linked to the Inspection via the InspectionId. So, the view is using the InspectionId to gather all related data to that inspection. This might contain multiple rows with the same InspectionId.
I hope that makes sense....This has been a long day :doze:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply