SQL Server Performance Forum – Threads Archive
Need a SQL trick…help..
Dear All, Can you help me with this case… I have a row data, for example as below vin call_datebook_catgshow_catg================= ========= ========= =========
MR053HY423900543216-Feb-04BOOKING SHOW
MR053HY423900543229-Sep-04BOOKING SHOW
MR053HY423900544021-Jan-04NOT BOOKINGNOT SHOW
MR053HY423900544028-Jan-05NOT BOOKINGSHOW
MR053HY423900544028-Apr-05NOT BOOKINGSHOW I have to add a new column (integer) in the table to numbering the occurence of vin, which this trick is so easy if we are doing it in MS Excel, the example like below vin call_datebook_catgshow_catg flag
================= ========= ========= ========= ====
MR053HY423900543216-Feb-04BOOKING SHOW 1
MR053HY423900543229-Sep-04BOOKING SHOW 2
MR053HY423900544021-Jan-04NOT BOOKINGNOT SHOW 1
MR053HY423900544028-Jan-05NOT BOOKINGSHOW 2
MR053HY423900544028-Apr-05NOT BOOKINGSHOW 3 Is there anyone has any solution or suggestion how to handle this case in SQL server, so that i don’t have to export the data to Excel everytime I face this situation, thx in advance guys…
What about UPDATE O
SET SlNo = (SELECT COUNT(*) FROM YourTable I WHERE I.call_date <= O.call_date AND I.vin = O.vin)
FROM YourTable O Roji. P. Thomas
http://toponewithties.blogspot.com
… except you shouldn’t normally store aggregate data. The point is that when you insert, update or delete any row, you have to recalculate the value on all rows with the same VIN code – and if you changed the VIN code, for both the old VIN code and the new one. — The only valid reason for storing aggregate data is in a reporting database, when calculating the aggregate values takes up a lot of time. I would create a view that gives you the COUNT(*) for each VIN code: CREATE VIEW dbo.vwVINCount
AS
SELECT t.VIN, COUNT(*) AS VinCount
FROM dbo.MyTable t
GROUP BY t.VIN
GO … and use the VINCount column from the view in your presentation. In case you were thinking of a computed column, unfortunately you cannot use a (sub)query as the formula.
1 Where do you want to show your data?
2 If you use reports, group the report by show_catg and reset the recordnumber in each group
3 you should do this type of numbering in front end application. If you dont use anyone, then dont mind doing it in EXCEL. In my opinion that is the way to go Madhivanan Failing to plan is Planning to fail
hi Madhivanan, Actually, I plan to show it in the new created table that only used for report. So this new table is not a dynamic table, instead it is only static table. So, it won’t affect any front end application. But, i think you have a good point that this type of numbering should be done in the front end level. Thank you all for the replies…i’m really appreciate…
]]>