How get only update data list in sql server 2008

Posted by Cpatil1000 under Sql Server on 10/1/2015 | Points: 10 | Views : 717 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi,
I want display only latest updated data from my table. in the my table LAST_MODIFIED is my date field. without using group by because there are so many column/field i am using this query.

Select A.ID, Case When A.MONTH = 1 Then 'JANUARY'
When A.MONTH = 2 Then 'FEBRUARY'
When A.MONTH = 3 Then 'MARCH'
When A.MONTH = 4 Then 'APRIL'
When A.MONTH = 5 Then 'MAY'
When A.MONTH = 6 Then 'JUNE'
When A.MONTH = 7 Then 'JULY'
When A.MONTH = 8 Then 'AUGUST'
When A.MONTH = 9 Then 'SEPTEMBER'
When A.MONTH = 10 Then 'OCTOBER'
When A.MONTH = 11 Then 'NOVEMBER'
When A.MONTH = 12 Then 'DECEMBER' End As 'MONTH',
A.YEAR, A.REGION_ID, Upper(R.REGION) As 'REGION',
A.CIRCLE_ID, Upper(C.CIRCLE) As 'CIRCLE',
A.DIVISION_ID,Upper(D.DIVISION) As 'DIVISION',
A.DISTRICT_ID, Upper(DIV.DISTRICT) As 'DISTRICT',
A.TALUKA_ID, Upper(T.TALUKA) As 'TALUKA', A.NAME_OF_SCHEME, Upper(S.SCHEME) As 'SCHEME',
A.WHETHER_SCHEME_IS_NRDWP_OR_OTHER_THAN_NRDWP,
A.PROGRAMME_OF_SCHEME, A.TOTAL_DESIGN_POPULATION,
A.ARP_COST, A.MNP_COST, A.PC_COST, A.TOTAL_COST,
Case When Convert(varchar, A.DATE_OF_AA, 105) = '01-01-1990' Then '' Else Convert(varchar, A.DATE_OF_AA, 105) End As 'DATE_OF_AA',
Convert(varchar, A.DATE_OF_TS, 105) As 'DATE_OF_TS',
Convert(varchar, A.DATE_OF_WORK_ORDER_GIVEN, 105) As 'DATE_OF_WORK_ORDER_GIVEN',
A.WHETHER_REVISION_IS_NECESSARY,
A.REVISED_COST_OF_SCHEME,
Convert(varchar, A.DATE_OF_REVISED_AA,105) As DATE_OF_REVISED_AA,
Convert(varchar, A.DATE_OF_REVISED_TS,105) As DATE_OF_REVISED_TS,
A.ACCEPTED_TENDER_COST,
A.GROSS_EXPECTED_UPTO_3_2012,
A.GROSS_EXPECTED_UPTO_3_2013,
A.EXPECTED_DURING_THIS_MONTH,
A.EXPECTED_UPTO_THIS_MONTH_DURING_FINANCIAL_YEAR,
A.NON_TARGETED_LAST_MONTH_TOTAL, A.NON_TARGETED_CURRENT_MONTH_NON_WATER_QUALITY, A.NON_TARGETED_CURRENT_MONTH_WATER_QUALITY,
A.NON_TARGETED_CURRENT_MONTH_TOTAL, A.NON_TARGETED_CUMMULATIVE_NON_WATER_QUALITY, A.NON_TARGETED_CUMMULATIVE_WATER_QUALITY,
A.NON_TARGETED_CUMMULATIVE_TOTAL, A.TARGETED_NOT_STARTED, A.TARGETED_STAGE_1, A.TARGETED_STAGE_2,
A.TARGETED_STAGE_3, A.NON_TARGETED_NOT_STARTED, A.NON_TARGETED_STAGE_1, A.NON_TARGETED_STAGE_2, A.NON_TARGETED_STAGE_3,
A.TARGETED_NOT_STARTED_NON_WATER_QUALITY,
A.TARGETED_STAGE_1_NON_WATER_QUALITY,
A.TARGETED_STAGE_2_NON_WATER_QUALITY,
A.TARGETED_STAGE_3_NON_WATER_QUALITY,
A.TARGETED_NOT_STARTED_WATER_QUALITY,
A.TARGETED_STAGE_1_WATER_QUALITY,
A.TARGETED_STAGE_2_WATER_QUALITY,
A.TARGETED_STAGE_3_WATER_QUALITY,
A.REVISED_ARP,
A.REVISED_MNP,
A.REVISED_PC,
A.REVISED_OTHER,
A.WHETHER_RE_REVISION_IS_NECESSARY,
A.RE_REVISED_COST_OF_SCHEME,
A.RE_REVISED_ARP,
A.RE_REVISED_MNP,
A.RE_REVISED_PC,
A.RE_REVISED_OTHER,
Convert(varchar, A.DATE_OF_RE_REVISED_AA, 105) As DATE_OF_RE_REVISED_AA,
Convert(varchar, A.DATE_OF_RE_REVISED_TS, 105) As DATE_OF_RE_REVISED_TS,
ESTIMATED_BASED_ON_DSR_YEAR
From NEW_RURAL_MPR_STATEMENT_A A
Inner Join NEW_RURAL_MPR_REGION_MASTER R On R.ID = A.REGION_ID
Left Outer Join NEW_RURAL_MPR_CIRCLE_MASTER C On C.ID = A.CIRCLE_ID
Left Outer Join NEW_RURAL_MPR_DIVISION_MASTER D On D.ID = A.DIVISION_ID
Left Outer Join NEW_RURAL_MPR_DISTRICT_MASTER DIV On DIV.ID = A.DISTRICT_ID
Left Outer Join NEW_RURAL_MPR_TALUKA_MASTER T On T.ID = A.TALUKA_ID
Left Outer Join NEW_RURAL_MPR_SCHEME_MASTER S On S.ID = A.NAME_OF_SCHEME
Where S.STATUS = 1
Order By A.LAST_MODIFIED Desc

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 10/1/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

1	This is a kind of auditing. I know few ways to do so - a) Using the Magic Tables (http://wikidba.net/tag/sql-server-magic-table-example/) b) OUTPUT Clause (Check the UPDATE CLAUSE http://blogs.msdn.com/b/sqltips/archive/2005/06/13/output-clause.aspx) c) Else, the easy way is to have a datetime field (if LAST_MODIFIED field/column is not datetime and only date) and query on that. e.g. with cte as( select rn=RANK() Over(Partition by LAST_MODIFIED Order by (Select 1)), * From Sometable) select * from cte where rn=1 So, the above query will rank the records and the latest timestamped records will be ranked FIRST(hence filtering by 1) Hope this will shed light to your question and you can move ahead. Feel free to ask in case you need further assistance. Thanks -- Thanks & Regards, RNA Team Cpatil1000, if this helps please login to Mark As Answer. \| Alert Moderator

This is a kind of auditing. I know few ways to do so -

a) Using the Magic Tables (http://wikidba.net/tag/sql-server-magic-table-example/)

b) OUTPUT Clause (Check the UPDATE CLAUSE http://blogs.msdn.com/b/sqltips/archive/2005/06/13/output-clause.aspx)

c) Else, the easy way is to have a datetime field (if LAST_MODIFIED field/column is not datetime and only date) and query on that.

e.g.

with cte as(
select
rn=RANK() Over(Partition by LAST_MODIFIED Order by (Select 1)),
*
From Sometable)
select *
from cte
where rn=1

So, the above query will rank the records and the latest timestamped records will be ranked FIRST(hence filtering by 1)

Hope this will shed light to your question and you can move ahead.
Feel free to ask in case you need further assistance.
Thanks

--
Thanks & Regards,
RNA Team

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts