Simple problem doing my head in... -- sql server programming

Stu
6/28/2006 2:59:39 PM

People are probably gonna gripe about this being a kludge, but I'm
assuming that you're asking about a procedural problem rather than
comments on design, etc.

Anyway, this is a solution; you'd probably be better off doing this in
the reporting tool rather than at the database level, but your problem
may be more complex than what you listed, and this is an attempt to
illustrate the process.

--target table
DECLARE @t TABLE ([date] smalldatetime, [account] varchar(6),
DaysInReport int)
--source table
DECLARE @src TABLE ([date] smalldatetime, [account] varchar(6))

--load the source table with data; assuming this is an extract of some
sort
INSERT INTO @src
SELECT '20060101', 'XYZ123'
UNION ALL
SELECT '20060101', 'ABC987'

--insert into target table data from the source table, updating the row
as you go.
INSERT INTO @t
SELECT src.[date], src.[account],
DaysInReport = COALESCE(t1.DaysInReport, 0) + 1
FROM @src src LEFT JOIN @t t1 ON src.[account] = t1.[account]
AND src.[date] = DATEADD(d, 1, t1.[date])

--clean out the source table
DELETE FROM @src

--repeat the process from above
INSERT INTO @src
SELECT '20060102', 'XYZ123'
UNION ALL
SELECT '20060102', 'PQR123'

INSERT INTO @t
SELECT src.[date], src.[account],
DaysInReport = COALESCE(t1.DaysInReport, 0) + 1
FROM @src src LEFT JOIN @t t1 ON src.[account] = t1.[account]
AND src.[date] = DATEADD(d, 1, t1.[date])

DELETE FROM @src

INSERT INTO @src
SELECT '20060103', 'XYZ123'
UNION ALL
SELECT '20060103', 'PQR123'
UNION ALL
SELECT '20060103', 'ABC987'

INSERT INTO @t
SELECT src.[date], src.[account],
DaysInReport = COALESCE(t1.DaysInReport, 0) + 1
FROM @src src LEFT JOIN @t t1 ON src.[account] = t1.[account]
AND src.[date] = DATEADD(d, 1, t1.[date])

--show the final results
SELECT *
FROM @t

THe question is: what happens if you miss a day? Also, do your
extracts always split data by day?

HTH,
Stu

[quoted text, click to view]

Roy Harvey
6/28/2006 6:08:17 PM

If data is only added for a single day at a time (no INSERT with more
than one value for date), AND can be certain that the data for the
previous day was correct, there is a simple approach. It could be
done as part of the INSERT, or as an UPDATE afterward.

UPDATE OnReport
SET DaysOnReport = 1 +
COALESCE((select DaysOnReport from OnReport as X
where X.Account = OnReport.Account
and X.date = dateadd(day,-1,OnReport.Date)),0)
WHERE Date = (select max(Date) from OnReport)

There is probably a way to set DaysOnReport for every row all in one
UPDATE, but the SQL would be challenging, and the performance would
probably be none too good. If it becomes necessary, I would put a
variation of the above into a loop, incrementing through from the
lowest Date to the highest, and processing one day at a time.
Something like the following untested code:

declare @dfrom datetime
declare @dto datetime
SELECT @dfrom = min(date), @dto = max(date)
FROM OnReport

WHILE @dfrom <= @dto
BEGIN
UPDATE OnReport
SET DaysOnReport = 1 +
COALESCE((select DaysOnReport from OnReport as X
where X.Account = OnReport.Account
and X.date = dateadd(day,-1,OnReport.Date)),0)
WHERE date = @dfrom

SET @dfrom = dateadd(day,1,@dfrom)
END

Roy Harvey
Beacon Falls, CT

On Wed, 28 Jun 2006 21:00:23 GMT, toedipper
[quoted text, click to view]

Tracy McKibben
6/28/2006 7:53:57 PM

[quoted text, click to view]

Why not just create a computed column in the table that does this
calculation for you? You're essentially updating every record in this
table, a potential performance problem.

Define a column such as:

DaysInTable AS DATEDIFF(day, DateAdded, GETDATE())

Now, any time you query any record from the table, it will automatically

toedipper
6/28/2006 9:00:23 PM

Hi,

I have what appears to be a simple problem but it is cracking me up.

I have to insert records into a table every day. The tables deals with
account numbers. No problems there. But one of the fields is called
'daysonreport' which is basically a number which signifies how long the
record has been in the table. Each account / row can appear on different
days.

So keeping it simple imagine the table has a structure of

date
account
daysonreport

So I Monday I select accounts 12345 and 98765 and place them into the
table. I then have to run a check and look at the previous days data
and if the accounts exist then current date daysonreport = previous days
value + 1

If the account does not exist in previous day then assume that it is a
new/reentry and set daysonreport to 1. They want this as an account can
move in out of the table over time and they only want the most recent
occurrence totted up, not the first.

So looking at the table over a couple of days it would be like this

DATE ACCOUNT DAYSONREPORT
01/01/06 XYZ123 1
01/01/06 ABC987 1

02/01/06 XYZ123 2
02/01/06 PQR123 1

03/01/06 XYZ123 3
03/01/06 PQR123 2
03/01/06 ABC987 1

Note that last entry above ABC987 also appeared on 01/01/06 but dropped
out on 02/01/06 and there was set back to 1.

So the basic logic is if the account exists in previous day date then
daysonreport = prev day value + 1

But if the account does not exist in previous day then set daysonreport to 1

I can write dates and accounts back until the cows come home but any
ideas on how I can write back a number that signifies how long an
account has been in report?

Thanks in advance

td
Derry

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Simple problem doing my head in...