Taking a shot in the dark...

select

num_of_comms = isnull(count(distinct ccl.commsid),0),

percent_confirmed = case

when isnull((count(DISTINCT c.subject + Convert(varchar(4),u.userid))),0) = 0 then '0%'

else (cast(convert(numeric(10,0),((cast(count(distinct cr.id) as money)*100) / cast(count(DISTINCT c.subject + Convert(varchar(4),u.userid))...

This sounds like the adjacency list model would be well suited. It is not the perfect solution but it does sound like it might work in your situation. This is...

Aside from the subselect this is pretty straight forward. Just change this into a single select statement. I can't quite wrap my head around the whole thing. You need to...

anthony.green (5/1/2012)

---

couple of ways

SELECT TOP 1 MAX(dbo.BookedFinance.GrossProfit) AS GP,

dbo.BookedFinance.FinanceID

FROM dbo.BookedExtrasList INNER JOIN

...

That looks like you are using that column to hold two pieces of information (rack and BinNumber). It would make things a lot easier if you could separate those.

If...

You should have a "staging" environment. This is exactly what this is for. You have Dev, QA, Staging, Prod. I would advise strongly against making your production environment for anything...

Did the fast_forward make it better? Given the amount of data you might consider putting this in an SSIS package that can handle looping a lot better than t-sql.

hehe no worries. 😛

Now if I take your ddl and sample data. Then run my code it works just fine.

CREATE TABLE [dbo].[flightsClosed](

[recid] [int] IDENTITY(1,1) NOT NULL,

[FlightID] [int] NULL,

[StatusTypeID] [int] NOT...

That was a problem but the counts are still off.

Msg 110, Level 15, State 1, Line 2

There are fewer columns in the INSERT statement than values specified in the...

Something is not quite right with those.

Msg 110, Level 15, State 1, Line 1

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number...

Any chance you can turn that into insert statements for me?

I guess that demonstrates that the cursor is working. Short of you posting ddl and sample data there is really nothing else I can do to help.

wmaa (4/30/2012)

---

why not do if the rows exists then copy to temp table then send email for all rows in temp table and exit when done? that was my...

I should have also included that you need to

close FlightClosedList

deallocate FlightClosedList

OK how about the actual query then all by itself?

SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe

FROM flightsClosed as a

left JOIN flightsClosed2 b

ON a.FlightID = b.FlightID

WHERE a.StatusTypeID != b.StatusTypeID

AND a.StatusTypeID = 44