Add or Update based on date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add or Update based on date

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-08-01 : 11:10:30

Hi, currently we have a process that run every night to write details to a table. We need to also run a process afterwards that will summarize the data based on what the date is. Here is the criteria:

If the date in the last row of the summary table is the 1st of the month, add a new record with the current date, else update the last record.

If the job ran on 8/1/08, this is what it should do:

[Date] [30 Day] [60 Day]...
6/1/08 19 9...(move next)
7/1/08 8 13...(move next)
7/31/08 12 9...(update to 8/1/08)

If the report is run on 8/2/08:
[Date] [30 Day] [60 Day]...
6/1/08 19 9...(move next)
7/1/08 8 13...(move next)
8/1/08 12 9...(move next)
8/2/08 16 4...(add new record)

Report ran on 8/3/08:
6/1/08 19 9...(move next)
7/1/08 8 13...(move next)
8/1/08 12 9...(move next)
8/3/08 10 6...(update record)

I've not written anything yet for this but I'm thinking of a store procedure with a case statement to execute functions based on the date. Am I on the right track? Or write a trigger in the summary table to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 12:38:18

i think a procedure would do the job for you. Where's your source data coming from?

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-08-07 : 11:57:21

quote:
Originally posted by visakh16

i think a procedure would do the job for you. Where's your source data coming from?

I'm not sure what you mean by source data but I have a table called Table_Ageing which contains the detailed information of accounts. It runs every night and gets all the previous day's ageing information. My job is to either update or insert into Table_Ageing_Summary the summary of most of the fields in the detail table based on the office location. If the report date is not the 1st of the month then I need to update the last record in the summary table, else add a new record.

Does that help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 12:54:14

Seems like you want this

DECLARE @MaxDate datetime

SELECT @MaxDate=MAX(Date)
FROM Table_Ageing_Summary

IF DAY(@MaxDate)=1
BEGIN
  INSERT INTO Table_Ageing_Summary
  SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0),othervalues..
END
ELSE
BEGIN 
  UPDATE Table_Ageing_Summary
  SET Date=DATEADD(d,DATEDIFF(d,0,GETDATE()),0),
     other values..
  WHERE Date=@MaxDate
END

put the above coide in your procedure. Also rememeber to put logic for other columns

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources