Hi

I am using SQL Server 2008 R2 BIDS and need to produce a weekly report whos output only shows changes from the previous weeks report and not sure if this can be done or where to start so any help very much appreciated.

The scenario is this.

I have a table where data is uploaded automatically (date created) and there are three main fields that can change. There are over two thousand rows added at each upload.

Column1 will be the date created

Column 2 will be a monetary value

Column 3 will be a reference number

In the example above with one reference number the first three rows there is no Amount change so each time the report is run this would not be included in the output. In Row 4 there is an amount change so this row should be included in the report output.Row 5 changes again so the next time the report runs this should show in report output. There is no change to Amount in Row 6 or 7 so these should not show in the next report output.

If the report was run on 20/03/2015 then there would be no output as no previous changes

Similarly if the next report was run on 10/04/2015 then it should show the changes on 26/03/2015 and 09/04/2015.

There could be NULL value in the Amount column and again if at some stage an Amount was uploaded then this would show a change and should be included in the next time the report run.

Any changes to data from automatic upload then need to manually adjusted and to save a user comparing thousand of rows i was hoping that by creating a report to only highlight changes would be a great time saver.

I hope i have explained this okay and any thoughts or assitance would be great

Thanks

John

Try this one:

--DECLARE @InputDate Date = '2015-05-03'

SELECT x.Date,x.Amount,x.Ref
FROM(
  select *, rn = row_number() over (partition by Amount order by Ref)
  from [ETLFramework].[dbo].[TEST]
  ) x
  Where rn = 1

--AND Date < @InputDate

Hi John,

I have tested the query Sary Awwad provided which is fine but need some improvement, please find the modified query like below and add into the dataset:

;WITH cte AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY DateCreated)
-ROW_NUMBER() OVER(PARTITION BY Amount ORDER BY DateCreated) rn
FROM case0203
)
SELECT MIN(DateCreated)as DateCreated,Amount,Reference FROM cte 
WHERE rn <>0 and DateCreated < @InputDate 

GROUP BY RN,Amount,Reference

Sample data like below:

Preview the result like below:

If you still have any problem, please feel free to ask.

Regards,
Vic

HI

Thank you both for your help i will test this later today

John

Hi

I have put the query into SQL Server Management studio to test using the table field names but it doesnt seem to giving me the expected output. This returned over 111392 rows. I have filtered this to just show the reuslts for tency_seq_no 200002 which returns 57 rows.

DECLARE @InputDate Date = '2015-04-09'
;WITH cte AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY trans_date)
-ROW_NUMBER() OVER(PARTITION BY grs_val_trans ORDER BY trans_date) rn
FROM hratrans
)
SELECT MIN(trans_date)as DateCreated, grs_val_trans, tency_seq_no, rn FROM cte 
WHERE rn <> 0 and trans_date < @InputDate and con_sou_cd = 'HB' and tency_seq_no = '200002'

GROUP BY RN,grs_val_trans,tency_seq_no

This is in fact showing all weekly transactions over the past year for this particular tency_seq_no.

However i only want the report to show any changes since the last time the report was run. So in the image shown below for tency_seq_no 200002 if i select 09/04/2015 as the input date i would want two rows in the output showing -62.11 both on the 09/04/2015 as this was a change from the previous -61.11 recorded on 26/03/2015.

The image below just shows part of the table hratrans

Likewise if i had used an input date of 12/03/2015 at -60.71 then there would be no output for this as the previous date of 05/03/2015 was also -60.71.

Thanks

John

Hi

Does anyone have any thoughts on a solution to this

Regards

John