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 2000 Forums
 Transact-SQL (2000)
 Are transactional system possible without cursors?

Author  Topic 

gdeconto
Posting Yak Master

107 Posts
Posted - 2003-04-11 : 18:13:31
This is sort of a hazy, non-specific question.

The kind you usually try to post after a really long day on a really hard Friday afternoon.

Say I have a transaction based system (a timesheet system, a pricing system, etc) where I have some base data (timesheet projects or item prices) and make occasional changes during the year (ie add time to a project each day or change an items price every now and then) via some transactions in a table (ie add 3 hours to project 123 today, and then add another 1.5 hours tomorrow)

Is there any way to apply those transactions on a scheduled basis (ie add 3 hours to project 123 today, or change cheeto prices end of next month) so that I can see a summary for a given day WITHOUT USING CURSORS?? ie via view.

While I could use SUM, there are situations where this is not desirable or feasible via a simple select; such as when items are added to or removed from a list (ie new product or discontinued project); when you delete an item, you dont want it to show up

I was wondering if anyone had come up with a technique to handle this type of situation.

We would probably be looking at situation where the table to be changed/updated looked something like this:

CREATE TABLE [titem] (
[itemid] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [text] NOT NULL
...
)

and the table containing the transaction info looked like this:

CREATE TABLE [tpricechange] (
[itemid] [int] IDENTITY (1, 1) NOT NULL ,
[newprice] [float],
[action] char
...
)

where the action field would contain 'U' for updating the price on an existing item, 'D' for deleting an item and 'I' for inserting a new item.

Any ideas anyone??

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-04-12 : 09:07:42
Can you provide some sample data, and an example of the output or results that you're looking for? I'm particularly stumped by this part:
quote:
While I could use SUM, there are situations where this is not desirable or feasible via a simple select; such as when items are added to or removed from a list (ie new product or discontinued project); when you delete an item, you dont want it to show up
...because I don't understand why SUM wouldn't work for you.

In any case, the answer to your topic question is yes, you can write transactional systems without cursors, but your particular situation needs to be a described a little more.

Also, I'd recommend NOT using text datatype for your description column, varchar will serve you well up to 8,000 characters, and if something can't be adequately described with that many characters there's something wrong with your data.

Edited by - robvolk on 04/12/2003 09:10:38
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-12 : 12:25:07
Well, first of all I hope you are storing dates in your "change" tables because I don't know how you expect to go "back in time" to view things as it was without storing those.

Having said that, there are lots of ways to SUM() things or view things from history tables. I do admit they can be really tricky to use, our HR system stores everything about employees in various history tables (jobs, pay, positions, status, location, etc) and it can be really difficult to do point-in-time or range reporting using multiple non-aligning history tables in SQL -- but certainly not impossible and it doesn't require cursors.

Let me give you two types of transactional tables and some examples that will help you get the data you need. Of course, these tables must have a "date" field.

Suppose we have a transaction table of sales for employees:

EmpID, Date, Sold

The primary key is EmpID/Date, and Sold is a money field which is how much the employee sold on that day.

To get a balance as of any point in time, just use a WHERE clause:

SELECT EmpID, sum(Sold) as TotalSales
from Sales
WHERE Date <= @AsOfDate

What is a little harder is a table like this, say a table of employee salaries:

EmpID, Date, Salary

You don't want to SUM() up this table, but rather take the latest row for each employee AS OF a certain date. That is accomplished like this:

Select salaries.EmpID, salaries.Date, salaries.Salary
FROM
(
SELECT EmpID, Max(Date) as MaxDate
FROM
Salaries
WHERE Date <= @AsOfDate
GROUP BY EmpID
)
a
INNER JOIN
Salaries
on a.empID = salaries.empID and
a.MaxDate = salaries.Date

(there are other ways to do this query, that's only 1 example.)

Lining up more than 1 history table like that for a single "as of" date can be tricky, because you need to do this or a techinique like this multiple times with EACH history table.

For example, if we add a table of Locations with fields of:

EmpID, Date, Location

and for a given date we want all employee's salary AND location, it is a big trickier but certainly possible w/o a cursor:

Select salaries.EmpID, salaries.Date as SalaryDate, salaries.Salary, Locations.Date as LocationDate, Locations.Location
FROM
(
SELECT EmpID, Max(Date) as MaxDate
FROM
Salaries
WHERE Date <= @AsOfDate
GROUP BY EmpID
)
a
INNER JOIN
Salaries
on a.empID = salaries.empID and
a.MaxDate = salaries.Date
INNER JOIN
(select EmpID, max(Date) as MaxDate
FROM
Locations
WHERE Date <= @asOfDate
GROUP BY EmpID)
b
on a.empID = b.empID
INNER JOIN
locations L
on L.empID = b.EmpID and
L.Date = b.MaxDate

I would probably write the above differently in most cases (usually as a LEFT OUTER JOIN from a table of ALL employees to the history tables so I don't drop out records) but hopefully you get the idea.

OTher tricks are to have a "Current?" field in each history table that you maintain using triggers so it is really easy and quick to query for the current salary and/or location or all employees:

Select a.EmpID, a.Salary, b.Location
FROM
Salaries a
INNER JOIN
Locations b
on a.empID = b.EmpID
WHERE
a.Current = 1 and
b.Current = 1

And of course, you can combine these history tables with the sales transactional table to get an employee's current salary, current location and total sales as of any date:

Select salaries.EmpID, salaries.Date as SalaryDate, salaries.Salary, Locations.Date as LocationDate, Locations.Location, c.TotalSales
FROM
(
SELECT EmpID, Max(Date) as MaxDate
FROM
Salaries
WHERE Date <= @AsOfDate
GROUP BY EmpID
)
a
INNER JOIN
Salaries
on a.empID = salaries.empID and
a.MaxDate = salaries.Date
INNER JOIN
(select EmpID, max(Date) as MaxDate
FROM
Locations
WHERE Date <= @asOfDate
GROUP BY EmpID)
b
on a.empID = b.empID
INNER JOIN
locations L
on L.empID = b.EmpID and
L.Date = b.MaxDate
LEFT OUTER JOIN
(select empID, sum(sales) as TotalSales
From Sales WHERE Date <= @asOfDate
GROUP BY empID) c
on a.EmpID = c.EmpID

If you have specific querying questions about this kind of stuff, let me know, I have done MUCH work with databases using these types of transactional and/or history tables.

I hope this helps a little.

- Jeff



Edited by - jsmith8858 on 04/12/2003 12:30:44
Go to Top of Page

gdeconto
Posting Yak Master

107 Posts
Posted - 2003-04-13 : 11:23:21
thx for the replies guys. comments/answers to both replies:

1. rob: the reason that SUM wont work is that the transactions also include the insertion and deletion of records, not just the updating of them. while I guess SUM can work for insertion, deletions are not possible via SUM. ie I could find out a given item price on a certain day without problem using SUM but that item may have been discontinued prior to that day and so no value should be returned (no item = no price)

am using the single char variable since the only three transactions that I can think of so far are insert, delete and update. thought a single char would be best to indicate to the processing sproc what the transaction was for (ie it is not a description but a processing instruction)

this type of system also lets me produce historical/auditing reports of what has happened to a record to date.

3. jsmith: yes, a date would be part of the transaction (it was a long day so some details may have been left out). I am also very familiar with tsql stmts .

guess my question was more philosophical in nature (ie more what is the best way than how do I do this). anyhow, SUM can work for insertion but how do you handle deletions (ie would imagine you do not usually want to know salaries for people who left a couple of years ago) via transactions like this.

thx again guys. look forward to any comments you have.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-13 : 19:09:10
I gotta admit, I'm a litle confused... you asked if it is possible to see the price of cheetos on a given day w/o using cursors, I showed you how, and then you reply "thanks, but I know SQL already."

Then you also must know that to not show salaries of employees who are no longer active you can just use a WHERE clause to fitler them out?

- Jeff
Go to Top of Page

gdeconto
Posting Yak Master

107 Posts
Posted - 2003-04-14 : 10:21:59
My fault. As I mentioned originally, my own thoughts about the process and handling are a bit hazy.

I was in the middle of writing a reply to you (using a simple set of items and transactions) when the answer hit me. I guess that trying to explain the problems I thought were there made me simplify the concepts and showed me that there no problems there after all.

I had been thinking about the 'D' transactions differently than I was thinking about the 'I' and 'U' transactions.

I was trying to think about how not to have 'D'eleted items returned via a SELECT when all I needed was to look for items with NULL as the most recent newprice value and filter them out.

Guess thats why these forums are a cool tool: we already know the answers to our own questions. we just have to talk about them with others for the information to gel.

thx for the assist.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site