I would do it using a user defined function. Some / Many may not consider this a SET based approach.

PLEASE NOTE this example creates a permanat table and user defined...

There is a new requirement.

We need a report for multiple from AV.

@pFromModel or @pToModel can be more than one model. (i.e Upgrades of multiple models to a model or...

/*

This is the new sample data with 0 complaints and with your new query.

This works great for combinations of from to I can think of.

I will play with real data....

Vasc (12/12/2007)

---

--I think the whole design is flawed...

--nothing tells me when the machine is working K with no complains. In your sample data WHAT will tell me that there were...

It doesn't work well when we use a Model in between.

SET @pFromModel='Model 2'

SET @pToModel='Model 3'

Should not list machine '34000904'. It does.

Vasc (12/12/2007)

---

--@tmp might be a killer without the right index

--dunno...

Unless you have a row number or a way to sort the values to get this result set I don't think it is possible.

If you have a row number

DECLARE @MyTable...

Vasc

I see a small glitch in the query. Please see the following query. I added a two new machines 'Not Upgraded' and a 'New Machine' which should not get involved...

Vasc (12/11/2007)

---

Those monthly reports probably makes sense for "monthly periods". Not this case because a model is not changed at the begining of a month so how I m supposed...

Thanks Vasc.

It makes a lot of sense to have fixed period of time for analysis and charts. The way our all monthly reports run is for the month. I brought...

Vasc

I have to do this as monthly analysis. I cannot use 30 days for monthly.

Can you please give me a query with weekday so that I can modify for day...

It works great with real data. Thank you again.

I got a question in the specs now. When the model is changed from 2 to 3 then 4. If the user...

Cool. COALESCE did not struck my mind. I was thinking all routs other than simple ones. Thanks a lot.

Let me try this in actual data.

Vasc I found another mismatch.

It is considering all the upgrades no matter whether we give @pFromModel or not.

I guess We should consider @t_from data when we get @t_to data like

INSERT...

Great. Thanks a million.

I just modified the query a bit as the user may want to see how an upgrade to a model from any model or to any model...

Thanks Vasc. It looks like now I am going some where.

One point I did not explain well enough is MachineID is not needed in the result set as this is...