July 14, 2008 at 9:52 am
For that select statement i am not getting any data returned.
CREATE TABLE [dbo].[Audits_Comments](
[CommentId] [int] IDENTITY(1,1) NOT NULL,
[AuditInspectionItemId] [int] NULL,
[Comment] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Audits_Comments] PRIMARY KEY NONCLUSTERED
(
[CommentId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Data
977Tile halls need waxing along with break areas
1085Lobby needs a little carpet work
1192Some dusting needed in production area
12123Dusting needed along sout dock doors
July 14, 2008 at 11:42 am
I had to adjust some of the sample data you posted, since it doesn't appear to be a matched set of rows. Try this query:
select top 10 *
from dbo.Audits
inner join dbo.Audits_InspectionItems
on Audits.AuditID = Audits_InspectionItems.AuditID
inner join dbo.Audits_ItemsScore
on Audits_InspectionItems.ItemID = Audits_ItemsScore.AuditInspectionItemID
inner join dbo.InspectionItems
on Audits_ItemsScore.ItemID = InspectionItems.ItemID
left outer join dbo.Audits_Comments -- Can't figure this one out, data out-of-range
on Audits_InspectionItems.ItemID = Audits_Comments.AuditInspectionItemID
left outer join dbo.IX_SubInspectionTypes
on IX_SubInspectionTypes.InspectionItemId = Audits_InspectionItems.ItemID
left outer join dbo.SubInspectionTypes
on IX_SubInspectionTypes.SubInspectionTypeId = SubInspectionTypes.SubInspectionTypeId
This won't get your end-result. It's a step on the way. If it doesn't work, I'm going to need you to look at the join definitions it it, compare them to your data (and any foreign keys set up in the database), and let me know what to fix. I don't have access to your database, so I can't do this all with what little I do have to test it on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 11:56 am
Thanks a lot for working on this.
yeah i got some result of 10 rows, but i was wondering how this wud replace my cursor, as it is a row by row operation.
If you dont mind can you pls adjust this query into my sp, not sure how this simple select query wud change every thing.
July 14, 2008 at 12:26 pm
Does the query have the right data from each table for those rows? Once I know that, I can make it do the running total that you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 12:32 pm
yes it has the right data for those tables.
July 14, 2008 at 1:00 pm
Try this:
declare @AuditID int
select @auditid = 27039
;with PreviousAudit (Score) as
(select [value]
from dbo.Audits_InspectionItems
inner join dbo.Audits_ItemsScore
on Audits_InspectionItems.ItemID = Audits_ItemsScore.AuditInspectionItemID
where auditid =
(select top 1 paudit.AuditId
from dbo.Audits PAudit -- Prior audit
inner join dbo.Audits CAudit -- Audit selected
on paudit.floorid = caudit.floorid
and paudit.programinstanceid = caudit.programinstanceid
and paudit.auditdate <= caudit.auditdate
and paudit.auditid != @auditid
order by paudit.AuditDate desc))
select Audits_InspectionItems.ItemID as AuditItemID,
InspectionItems.InspectionItemName as GroupName,
case
when audits.auditid = @auditid then cast(1 as bit)
else cast(0 as bit)
end as IsGroupHeader,
case
when exists
(select *
from dbo.IX_SubInspectionTypes IX
inner join dbo.SubInspectionTypes SIT
on IX.SubInspectionTypeId = SIT.SubInspectionTypeId
inner join dbo.Audits_InspectionItems AIT
on IX.InspectionItemId = AIT.ItemID
where auditid = @auditid) then cast(1 as bit)
else cast(0 as bit)
end as GroupHeaderHasItems,
Audits_ItemsScore.ZoneInstanceId as ZoneID,
Audits_ItemsScore.Value as Score,
Audits_InspectionItems.ItemId as InspectionItemID,
Audits_Comments.Comment,
InspectionItems.ScoreTypeId,
PreviousAudit.Score as PreviousScore
from dbo.Audits
inner join dbo.Audits_InspectionItems
on Audits.AuditID = Audits_InspectionItems.AuditID
inner join dbo.Audits_ItemsScore
on Audits_InspectionItems.ItemID = Audits_ItemsScore.AuditInspectionItemID
inner join dbo.InspectionItems
on Audits_ItemsScore.ItemID = InspectionItems.ItemID
left outer join dbo.Audits_Comments -- Can't figure this one out
on Audits_InspectionItems.ItemID = Audits_Comments.AuditInspectionItemID
left outer join dbo.IX_SubInspectionTypes
on IX_SubInspectionTypes.InspectionItemId = Audits_InspectionItems.ItemID
left outer join dbo.SubInspectionTypes
on IX_SubInspectionTypes.SubInspectionTypeId = SubInspectionTypes.SubInspectionTypeId
cross join PreviousAudit
where audits.auditid = @auditid
Compare the results to the results from your current query, with the same input parameter value.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 1:02 pm
unfortunately there was no records returned to me.
its just empty, where am supposed to get 22 rows.
July 14, 2008 at 1:29 pm
Okay, so which of the joins isn't correct? I can't tell from here, because none of the sample data given returns any rows at all, even with the original query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 1:53 pm
joins shud reflect in my original store proc, am just trying to rewrite the original one, thats all.
July 14, 2008 at 1:57 pm
The original proc is using cursors instead of joins in many places. I tried to figure out from the cursors what the equivalent join would be, but I'm not the one with the database. I really need you to look at the select statement, check it in the database, and find what I missed. I missed something, but I can't tell what from here.
If you can't do that yourself, then what you need to do is hire a contractor who knows SQL well enough, and have that person dig into the actual database and work on it from there.
I've taken it as far as I can with the data given. You'll either need to take it the next step yourself, or get someone else on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 2:01 pm
thank you so much for the help, i'll keep debugging it.
July 14, 2008 at 2:18 pm
Okay. If you can let me know what I missed, I'll see if I can fix it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 2:29 pm
Just with simple joins this is not working for me, may be i need 2 CTE's in place of 2 cursors as it was earlier.
Its not just CTE's but i just need to replace cursors somehow.
July 30, 2008 at 9:45 am
Hey could any one give me a different approach than CTE's for this.
August 3, 2008 at 9:41 am
For what? The original code you posted?
If so, why not just insert the header information in a set based manner and then use that information to control another set based insert for the group information?
In other words... insert ALL the headers and then join to the temp table on that information to insert ALL the group information.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply