Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplif
y
your search. Remember, your holidays might not be the same as mine so having
the
table will eliminate such.
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wan
t
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where no
t
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> glsD
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplify
your search. Remember, your holidays might not be the same as mine so having the
table will eliminate such.
--
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
> to select all rows from that table, excluding days which fall on holidays or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
--
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> ¼¶¼g©ó¶l¥ó·s»D
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.

Suggestions are welcome!

Sincerely,
Nils Magnus Englund"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:2rT%b.103$72.176991232@.news.telia.no...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.

That's probably your best idea.

Your holidays may not be mine.

> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund|||Nils Magnus Englund (nils.magnus.englund@.orkfin.no) writes:
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
> want to select all rows from that table, excluding days which fall on
> holidays or weekends. What is the best way to accomplish this? I
> considered creating a new table called "holidays" and then selecting all
> rows (sort of "where not in (select * from holidays)") , but I was
> looking for a better solution since that implies that I have to populate
> the "holidays" table.

And how would you expect SQL Server to know about syttende maj or when
Midsummer is?

You can of course make the holidays table more or less sophisticated.
You can just put in all Mondays to Fridays that are not dates from now
to 2020 or whatever.

You can also write a stored procedure that fills in the table given the
rules about currently known holidays. You would need to find data on
where Easter falls, to determine days for Easter, Whitsun and Ascenion Day.

Yet an alternative is to put all days in that table, and then a flag
whether the day is a working day or not, no matter whether it's Friday
or Sunday.

And finally, for the SELECT it self I prefer:

SELECT *
FROM tbl t
WHERE NOT EXISTS (SELECT *
FROM holidays h
WHERE t.date = h.date)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hoe to get Encrypted Password value varbinary field

I just converted from SQL 7 to SQL 2000 but have one issue.
My login table contains a password field that is a varbinary type. I
encrypted the value by using the encrypt('password') when inserting users
into this table.
How can I see the actual value of the password field. Is there a way to
decrypt?
For some reason, all the users passwords don't work. If I redo each one, it
works but there are too many to redo manually."yodakt" <dev1on11@.gmail.com> wrote in message
news:6E45D35E-970E-4656-AECF-B192BD1035D7@.microsoft.com...
>I just converted from SQL 7 to SQL 2000 but have one issue.
> My login table contains a password field that is a varbinary type. I
> encrypted the value by using the encrypt('password') when inserting users
> into this table.
> How can I see the actual value of the password field. Is there a way to
> decrypt?
> For some reason, all the users passwords don't work. If I redo each one,
> it
> works but there are too many to redo manually.
Try this:
SELECT CAST(myPassword AS NVARCHAR(100))
FROM myTable
Where myPassword is the VARBINARY column containing the passwords.

Hoe to Find Duplicate values?

I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert

Hmm - unique id is in Binary data - how do I query against it?

Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.

I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:

I have a function in my data accsess class that queries the database:

Public Function getDetailsById(ByVal Id As System.Byte) As DataSet

Dim con As New SqlConnection(conStr)
Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@.Id", con)

cmd.Parameters.Add("@.Id", SqlDbType.Binary)
cmd.Parameters("@.Id").Value = Id

Dim ds As New DataSet
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(ds)

Return ds

End Function

And then I call this function from my page with the gridview using:

Dim da As New myDataAccess
Dim ds As New DataSet
ds = da.getDetailsById(GridView1.SelectedDataKey.Value)

But I get the error:

Conversion from type 'Byte()' to type 'Byte' is not valid.

Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?

Thanks

loydall:

Hi - I've got a table in SQL server that has its unique ID field as binary data.

Not trying to be funny... but making your primary key a binary is where you are going wrong.

I know I've queried off of binary fields before, but I don't have any of that code infront of me now.

|||

Thanks but it's not my db so I have no control over how the ID is stored...

I've fixed it anyway - I just need to use system.byte() rather than system.byte as a parameter in my function - it obviously needed to treat the data as an array...

|||

Make sure you're getting the correct records back... I seem to remember that SQL will store a binary as 0x00000047 where .Net will see the same value as 0x47000000.

|||

You are using the wrong ADO.NET command object, you should use ExecuteScalar. Try the link below for complete sample code from Jeff Prosise Programming .NET. The code is in C# but it shows you the correct way to use ExecuteScalar. Hope this helps.

http://kurinjikumaravel.tripod.com/Asp.Net/AspWintellect.txt

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Exampl
e
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.co...echspace1.shtml
Thanks
GYK
"John" wrote:

> I have a number of tables with filled with duplicate indexed columns (Exam
ple
> 1 below). Is it better to have a column in one index per table rather the
n
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column orde
r
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2
It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com...chspace1.shtml
Thanks
GYK
"John" wrote:

> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

historyLogMessage

Does anyone know what table the historyLogMessage is written to?

The docs mention the distribution database but do not specify the table name?

historyLogMessage
Log message provided by the custom business logic. The log message gets written to the distribution database.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.businesslogicsupport.businesslogicmodule.inserthandler.aspx

check dbo.MSmerge_history.

History Table Design Issue

Hi all,

this is more of a design issue for a History table.

Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.

Regards,

General Problem

As a general rule of thumb, EVERY table should have a primary key.

I'm sure that there are exceptions (it's a scary world out there!), but the design assumption should be "A primary key is required!" until proven otherwise.

As for foreign keys, that's a different issue.

Let's say you have an employee table.

If you set up an FK from your history table to your employee table, you will not be able to delete an employee from the employee table as long as you maintain those history recorrds.

If you intended for the employee table to be defined as "A list of current employees of the business.", you have just changed the definition to "A list of current and past employees of the business."

That may, or may not, be what you want!

Now, if the primary key of the employee is a meaningless number, and you need to know which employee did that transaction, you might be stuck with keeping past employees in the employee table. Or, of course, you could add the employee ssn and name to the history table. If you used a natural key (like their SSN), then someone could always look it up in the paper trail. Lots of options here!

History table

Hi I wanted to create a history table, but it doesn′t really works:
CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
FOR INSERT, UPDATE AS
INSERT into dbo.Helpdesk_History
(
Helpdesk_History.Computer_Idn,
Helpdesk_History.DeviceName,
Helpdesk_History.InsertDate,
Helpdesk_History.ProblemTitle,
Helpdesk_History.Duration,
Helpdesk_History.ProblemDetails,
Helpdesk_History.ProblemSolution,
Helpdesk_History.CallDispatcher,
Helpdesk_History.Responsible,
Helpdesk_History.Status
)
SELECT
Helpdesk.Computer_Idn,
Helpdesk.DeviceName,
Helpdesk.InsertDate,
Helpdesk.ProblemTitle,
Helpdesk.Duration,
Helpdesk.ProblemDetails,
Helpdesk.ProblemSolution,
Helpdesk.CallDispatcher,
Helpdesk.Responsible,
Helpdesk.Status
from Helpdesk
With my trigger all values will be written in the helpdesk_history table.
But I want that only new oder changed collums will should be insert in the
history table.
Any idea ?
Thx
WolfgangI think this is what you want
CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
FOR INSERT, UPDATE AS
INSERT into dbo.Helpdesk_History
(
Helpdesk_History.Computer_Idn,
Helpdesk_History.DeviceName,
Helpdesk_History.InsertDate,
Helpdesk_History.ProblemTitle,
Helpdesk_History.Duration,
Helpdesk_History.ProblemDetails,
Helpdesk_History.ProblemSolution,
Helpdesk_History.CallDispatcher,
Helpdesk_History.Responsible,
Helpdesk_History.Status
)
SELECT
d.Computer_Idn,
d.DeviceName,
d.InsertDate, -- this would probably be getdate() if you want
today's date
d.ProblemTitle,
d.Duration,
d.ProblemDetails,
d.ProblemSolution,
d.CallDispatcher,
d.Responsible,
d.Status
from deleted d
http://sqlservercode.blogspot.com/|||Hi in case of update this works perfect,
but if I insert a new record the complete table helpdesk will be copied to
the helpdesk_history table.
Any idea?
thx
Wolfgang
"SQL" wrote:

> I think this is what you want
> CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
> FOR INSERT, UPDATE AS
> INSERT into dbo.Helpdesk_History
>
> (
> Helpdesk_History.Computer_Idn,
> Helpdesk_History.DeviceName,
> Helpdesk_History.InsertDate,
> Helpdesk_History.ProblemTitle,
> Helpdesk_History.Duration,
> Helpdesk_History.ProblemDetails,
> Helpdesk_History.ProblemSolution,
> Helpdesk_History.CallDispatcher,
> Helpdesk_History.Responsible,
> Helpdesk_History.Status
> )
> SELECT
>
> d.Computer_Idn,
> d.DeviceName,
> d.InsertDate, -- this would probably be getdate() if you want
> today's date
> d.ProblemTitle,
> d.Duration,
> d.ProblemDetails,
> d.ProblemSolution,
> d.CallDispatcher,
> d.Responsible,
> d.Status
>
> from deleted d
>
> http://sqlservercode.blogspot.com/
>|||When you do an insert the deleted table should not be available
Is there possible another trigger on the table that updates after
inserting?
http://sqlservercode.blogspot.com/|||Hi Wolfgang !
That would mean to build a block of code, comparing each column to the
new one, and keeping the information that this value was changed and
then issueing something like a dynamic sql statement, to only store the
information that were changed.
HTH, Jens Suessmeyer.|||Hi,
yes there is another trigger:
CREATE TRIGGER [Computer_idn] ON dbo.Helpdesk
FOR INSERT
AS
UPDATE Helpdesk SET Computer_idn =
(Select Computer.Computer_idn
from Computer
where
computer.devicename = helpdesk.devicename
and computer.SWLastScanDate =
(SELECT MAX(computer.SWLastScanDate) FROM computer))
This trigger updates the Computer_Idn ...and with this trigger it updates
the whole table. How can I change this trigger that only the Computer_Idn of
the new inserted value will be updated ?
Thanks
Wolfgang
"SQL" wrote:

> When you do an insert the deleted table should not be available
> Is there possible another trigger on the table that updates after
> inserting?
> http://sqlservercode.blogspot.com/
>|||On Wed, 9 Nov 2005 00:10:11 -0800, Wolfgang Dausend wrote:

>Hi,
>yes there is another trigger:
>
>CREATE TRIGGER [Computer_idn] ON dbo.Helpdesk
>FOR INSERT
>AS
>UPDATE Helpdesk SET Computer_idn =
>(Select Computer.Computer_idn
>from Computer
>where
>computer.devicename = helpdesk.devicename
>and computer.SWLastScanDate =
>(SELECT MAX(computer.SWLastScanDate) FROM computer))
>
>This trigger updates the Computer_Idn ...and with this trigger it updates
>the whole table. How can I change this trigger that only the Computer_Idn o
f
>the new inserted value will be updated ?
Hi Wolfgang,
Add
WHERE EXISTS
(SELECT *
FROM inserted
WHERE inserted.WhateverYourKeyIs = Helpdesk.WhateverYourKeyIs)
at the end of the UPDATE statement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

History of data for documents

Hello,
I have following situation:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[CountryId] [uniqueidentifier] NOT NULL ,
[StreetName] [nvarchar] (100) NOT NULL ,
[StreetNo] [nvarchar] (10) NOT NULL ,
[LocalNo] [nvarchar] (10) NOT NULL ,
[PostalCode] [nvarchar] (20) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OwnerContractorId] [uniqueidentifier] NOT NULL ,
[TargetContractorId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL ,
[OrderId] [uniqueidentifier] NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[Quantity] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[CultureName] [nvarchar] (20) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
The Problem is:
Order - needs information about contractor and his address (address is
related to the contractor by one-to-many
relation). Furthermore, the order needs information about products (its
names, which are stored in
separate table - each product can have several names, depending on language)
.
If a name of a product would change, it will be changed in all orders. But
order is the document and I need to
keep it as it was at the moment of creation (constractor data - names,
address etc. , product data - names etc.).
To do this, I have to design some kind of history of data connection to each
other. It is bad situation to me,
when I change address of some contractor - all orders will have changed
addresses, the proper data connections
will collapse.
My idea is to copy all related data for the record which is edited. For
example if an address for contractor is
changed, the new contractor record is created with new id (guid) and the
same symbol (is unique for all active
records) (old one have flag "history" for example - is deactivated) and the
new address is related to this new contractor record. The same story applies
when a name of product is edited.
This mechanism lets to store correct data for each order in the database -
all documents(orders) are connected to the same data as at the moment of
their creation.
Reports in this case are performed by queries operationg in the "Symbol" of
the contractor - this gives the
possibility to find all orders of one contractor - each having correct
address for the moment of creation of
order.
My question is: is this design corresponding to the "rules of art" somehow?
What are your ideas for solving such problems?
I will be thankful for your opinions
Adam Rozycki & friends>> My question is: is this design corresponding to the "rules of art"
No
I am not sure where you got the idea of using a globally unique identifier
type for every key in your tables.
We all work within existing computational constraints; so no business
segment requires a machine generated global identifier for a table unless
you are researching on such values. Other than the "" factor, abuse &
hype, there is nothing simple, pragmatic or meaningful about using a
uniqueidentifier column as a key for Orders or Products table as in your
situation.
One approach to your problem, based on your narratives, can be like:
CREATE TABLE Orders (
Order_nbr INT NOT NULL PRIMARY KEY,
Product_id INT NOT NULL,
REFERENCES Products ( Product_id )
Contractor_id INT NOT NULL
Address_id INT NOT NULL,
REFERENCES Contractors ( Contractor_id, Address_id )
.. ) ;
CREATE TABLE Contractors (
Contractor_id INT NOT NULL,
Address_id INT NOT NULL,
REFERENCES Addresses ( Address_id ) ,
Name...
PRIMARY KEY ( Contractor_id, Address_id )
) ;
CREATE TABLE Addresses (
Address_id INT NOT NULL PRIMARY KEY,
Address VARCHAR( 40 ) NOT NULL,
City_state_zip VARCHAR( 40 ) NOT NULL,
UNIQUE ( Address, City_state_zip )
.. ) ;
CREATE TABLE Products (
Product_id INT NOT NULL PRIMARY KEY,
Product_name ...
) ;
To keep track of history of change in Product names use another table with
temporal datatypes like:
CREATE TABLE ProductHistory (
Product_id INT NOT NULL,
Product_name VARCHAR ( 100 ) NOT NULL,
Assigned_date DATETIME NOT NULL,
Withdrawn_date DATETIME NOT NULL,
..
PRIMARY KEY ( Product_id, Assigned_date )
CHECK ( Withdrawn_date >= Assigned_date )
);
The same approach can be used for changes in other attributes like symbols
which you mentioned as well.
Anith|||
"Anith Sen" wrote:

> I am not sure where you got the idea of using a globally unique identifier
> type for every key in your tables.
My idea was to single keys instead of complex ones.

> We all work within existing computational constraints; so no business
> segment requires a machine generated global identifier for a table unless
> you are researching on such values.
The reason why I used GUIDs is that this database has to be replicable and
data exchangable with several separate databases. There is to be one master
DB and several slave DBs. Since data can be added in some independent places
- I think I need to use the global identifiers.
My problem is that I have to have a data corresponding to Order, just the
same as it was at the moment of creating of Order. I cannot have situation
when changed contractor data changes data in all orders.
The database has to store names for products in several languages, since
that I cannot put all information about products in one table.
I would like to achieve rather some sort of document revision than history
of action on documents.
Adam|||>> My idea was to single keys instead of complex ones.
Good. Simple keys are a recommended consideration for a primary key.
However, having a uniqueidentifier in a table as the only key in your table
does nothing for entity identification, which is the main purpose of a key
in the first place.
Not necessarily. You could opt for any arbitrary namespace to determine
independent databases distributed over different servers. While
uniqueidentifier type guarantees the value to be unique globally, it cannot
guarantee the uniqueness of the corresponding entity.
For instance, a customer by name Adam in a table in database A cannot be
distinguished from another customer by same name Adam in similar table in a
replicable database B or even in the same table in the same database, just
by virtue of arbitrary GUIDs alone. All you'll have is duplicated entries of
Adam in the table with different GUIDs associated with them. How do you
identify the row corresponding to Adam? How will you enforce entity
integrity? How do you track down an alleged error, for instance in data
entry? Can you use GUIDs for referencing keys reliably without cascading
changes?
It is mostly hard to provide any specific meaningful suggestions here. While
you are familiar with your business model regarding the orders, customers,
languages, symbols, revisions etc., others in this newsgroup have no clue on
what they are or how they are related. You did provide a set of CREATE TABLE
statements without any keys, constraints, references etc. however databases
cannot be designed based on such. Esp. when only a couple of lines of
narrative are provided, there is a high chance that the overall business
model and rules are miscommunicated, misrepresented and/or misunderstood.
As a general suggestion, your approach to use GUIDs all over the table as
primary keys with no identifying attribute seems inherently flawed. However,
if you have made provisions for entity identification using UNIQUE NOT NULL
constraints, perhaps you might be able to work it out to some extent.
Consider using temporal datatypes for tracking historical information unless
you are using them already.
Also a few general design rules of thumb, if it helps:
* When you have a one-to-one relationship between two entity types, unless
there are any non-dependency preserving relationships, you may represent
them in a single table.
* When you have a many-to-one relationship between two entity types, you
should use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have a many-to-many relationship between two or more entity
types, you should introduce an "association" table which reduces the schema
to two or more many-to-one relationships on each table representing these
entity types.
Anith|||
"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creationof tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Is it now possible that you provide some judge of my record-history idea or
provide some other ideas how to perform this correctly?
Adam|||"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creation of tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Can you now provide some judgement of my record-history idea or provide some
other ideas how could it be performed correctly?
Adam

History Cross-Tab via Cursors

I have a table with a PK of id and timestamp, plus other variables: an
update of a row adds another row with the same id but a later timestamp. I
need to create a report that lists the variables that have changed. The
following proof of concept code works fine, but I was wondering if the same
thing can be done more simply--perhaps without cursors.
Thanks for taking a look. All comments appreciated.
--FINAL RESULT TABLE FOR HISTORY REPORTING
drop table rpt_tbl
go
create table rpt_tbl
(fld_name varchar(20),
old_rpt_id int,
old_rpt_timestamp datetime,
old_fld varchar(20),
new_rpt_id int,
new_rpt_timestamp datetime,
new_fld varchar(20))
--TEST DATA
create table PROD
(prod_id int,
prod_timestamp datetime,
prod_name varchar(20),
prod_categ varchar(20))
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/07/05', 'Acme', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/08/08', 'Best', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2004/07/10', 'Pink', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/01/05', 'Red', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/02/17', 'Fuchsia', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(3, '2005/02/17', 'Ivory', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(4, '2005/4/22', 'Yellow', 'Color')
--VIEW
--GET ROWS IN PROD TABLE WITH UDPATE HISTORY
--that is, multiple rows for same prod_id, with different timestamps
create view DUPES as
select * from prod
where prod_id in
(select prod_id from prod
group by prod_id
having count(*) > 1)
--CREATE 1-ROW TABLE FOR COMPARE
create function create_table
(@.prod_id int,
@.prod_timestamp datetime)
returns table
as return
(select
prod_id, prod_timestamp,
prod_name, prod_categ
from dupes where
(prod_id = @.prod_id and prod_timestamp = @.prod_timestamp))
--COMPARE 2 ROWS
create proc compare_2_rows
(@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime)
as
declare
@.a_prod_name varchar(20),
@.a_prod_categ varchar(20),
@.b_prod_name varchar(20),
@.b_prod_categ varchar(20)
declare xc2 cursor for
select * from
create_table (@.a_prod_id, @.a_prod_timestamp) t1
join
create_table (@.b_prod_id, @.b_prod_timestamp) t2
on t1.prod_id = t2.prod_id
open xc2
fetch next from xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
while @.@.fetch_status = 0
begin
if @.a_prod_name <> @.b_prod_name
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('name',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_name,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_name)
if @.a_prod_categ <> @.b_prod_categ
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('categ',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_categ)
fetch xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
end
close xc2
deallocate xc2
create proc history_proc as
--OVERALL PROC
--1. empty report table
--2. use cursor to find paired PK's
--3. exec proc compare_2_rows with PK params for paired data
-- a. use cursor to join function tables for paired data
-- b. compare 2 prod_names and insert paired data into
-- rpt_tbl
-- c. compare 2 prod_categs and insert paired data into
-- rpt_tbl
truncate table rpt_tbl --empty report table
declare --the keys for the rows to be compared
--@.a refers to the older row
--@.b refers to the newer (more recent) row
@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime
declare xc cursor for select prod_id, prod_timestamp
from dupes
open xc
--1st record (older row)
fetch xc into @.a_prod_id, @.a_prod_timestamp
--2nd record (newer row): has to be at least a pair for same id
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
while @.@.fetch_status = 0
begin
--if a break in keys, don't compare them,
--and move newer key to older key variables
if @.b_prod_id <> @.a_prod_id
begin
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
goto skip
end
exec compare_2_rows
@.a_prod_id, @.a_prod_timestamp,
@.b_prod_id, @.b_prod_timestamp
--after compare, move newer key to older key variables
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
skip:
--get key for new row
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
end
close xc
deallocate xc
--THIS RUNS THE OVERALL PROC
exec history_proc
--THIS SHOWS WHAT'S IN THE FINAL RESULTS TABLE
select * from rpt_tblBased on your example data, the primary key should be prod_id ,
prod_categ and prod_timestamp.
Try this:
select ProdNew.prod_id
, ProdNew.prod_categ
, ProdNew.prod_timestamp
, ProdNew.prod_name
, ProdOld.prod_timestamp
, ProdOld.prod_name
from (select Prod6.prod_id, Prod6.prod_categ ,
max(Prod6.prod_timestamp)
from Prod as Prod6
group by Prod6.prod_id, Prod6.prod_categ
) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
join Prod as ProdNew
on ProdNew.prod_id = ProdLastest.prod_id
and ProdNew.prod_categ = ProdLastest.prod_categ
and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
left outer join
(
select Prod1.prod_id
, Prod1.prod_categ
, Prod1.prod_timestamp
, Prod1.prod_name
from Prod as Prod1
join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
from Prod
join (select prod_id, prod_categ , max(prod_timestamp)
from Prod
group by Prod.prod_id, Prod.prod_categ
) as Prod3 ( prod_id, prod_categ , prod_timestamp)
on Prod.prod_id = Prod3.prod_id
and Prod.prod_categ = Prod3.prod_categ
and Prod.prod_timestamp < Prod3.prod_timestamp
group by Prod.prod_id, Prod.prod_categ
) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
on Prod1.prod_id = ProdOlder.prod_id
and Prod1.prod_categ = ProdOlder.prod_categ
and Prod1.prod_timestamp = ProdOlder.prod_timestamp
) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
on ProdOld.prod_id = ProdNew.prod_id
and ProdOld.prod_categ = ProdNew.prod_categ
order by ProdNew.prod_id
, ProdNew.prod_categ
*** Sent via Developersdex http://www.examnotes.net ***|||Check out the doc for the RAC utility.
Focus on the 'What' instead of the 'How'.
www.rac4sql.net|||Thanks--I'll have to take a look at your code in the morning. I'm too blear
y.
The pk is prod_id/prod_timestamp. In the real world, the timestamp would
have year-month-day-time.
There's no limit to the possible number of rows per id. I need to compare
every column (there will be about 10 or 20, in the real world) in every row
for the same prod_id, so that the final table has a row for every old/new
column pair which are different.
"Carl Federl" wrote:

> Based on your example data, the primary key should be prod_id ,
> prod_categ and prod_timestamp.
> Try this:
> select ProdNew.prod_id
> , ProdNew.prod_categ
> , ProdNew.prod_timestamp
> , ProdNew.prod_name
> , ProdOld.prod_timestamp
> , ProdOld.prod_name
> from (select Prod6.prod_id, Prod6.prod_categ ,
> max(Prod6.prod_timestamp)
> from Prod as Prod6
> group by Prod6.prod_id, Prod6.prod_categ
> ) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
> join Prod as ProdNew
> on ProdNew.prod_id = ProdLastest.prod_id
> and ProdNew.prod_categ = ProdLastest.prod_categ
> and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
> left outer join
> (
> select Prod1.prod_id
> , Prod1.prod_categ
> , Prod1.prod_timestamp
> , Prod1.prod_name
> from Prod as Prod1
> join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
> from Prod
> join (select prod_id, prod_categ , max(prod_timestamp)
> from Prod
> group by Prod.prod_id, Prod.prod_categ
> ) as Prod3 ( prod_id, prod_categ , prod_timestamp)
> on Prod.prod_id = Prod3.prod_id
> and Prod.prod_categ = Prod3.prod_categ
> and Prod.prod_timestamp < Prod3.prod_timestamp
> group by Prod.prod_id, Prod.prod_categ
> ) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
> on Prod1.prod_id = ProdOlder.prod_id
> and Prod1.prod_categ = ProdOlder.prod_categ
> and Prod1.prod_timestamp = ProdOlder.prod_timestamp
> ) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
> on ProdOld.prod_id = ProdNew.prod_id
> and ProdOld.prod_categ = ProdNew.prod_categ
> order by ProdNew.prod_id
> , ProdNew.prod_categ
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

historical monthly/weekly/daily totals breakdown

I have a sales table( id, date , techid, amount)
id date techid amount
---
1 01/01/2005 tsmith 99.99
...
1054 08/16/2005 jborrow 29.99
I need to create historical report&graph with monthly totals (amount) for
every agent: Jan,Feb...Aug.
Jan Feb... Aug
tsmith 15898.44 69352.88 586311.55
How can I aggregate daily data in order to get historical
monthly/weekly/daily totals breakdown.
Is there any function or option (in Report designer)?
Thank you!You may want to read up on "grouping" in the RS Books Online and also look
at the "Company Sales" and the "Employee Sales Summary" sample reports of RS
2000.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:4C7B3EAA-7FAC-400F-A80F-9586A4A1EFF1@.microsoft.com...
>I have a sales table( id, date , techid, amount)
> id date techid amount
> ---
> 1 01/01/2005 tsmith 99.99
> ...
> 1054 08/16/2005 jborrow 29.99
> I need to create historical report&graph with monthly totals (amount) for
> every agent: Jan,Feb...Aug.
> Jan Feb... Aug
> tsmith 15898.44 69352.88 586311.55
>
> How can I aggregate daily data in order to get historical
> monthly/weekly/daily totals breakdown.
> Is there any function or option (in Report designer)?
> Thank you!
>sql

Highlighting in tables.

I would like to highlight every other line in a table to enhance readability. Is there an easy way to do this? If so, how?

TIAHi Tia,

I just started writing custom code today - so naturally I think it can solve all problems!

I'm thinking you may be able to write custom code to set the table row's background
property based on if it is an "even" or "odd" row. I looked around and there doesn't
seem to be an easy way like we have on ASP.Net grid controls. Has anyone
done something like this using custom code?

Jane|||actually I thgt abt it for a while and its not very complicated. in your properties tab for that textbox or for the row, click on the color property and click on "Expressions" and you can write an expression over there like a regular one

 IIF(Fields!yourval.value <10, " Red", "Blue")

hth|||Dinakar,

Thanks! the expression I finally chose was:

=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")

set on the row level properties tab in BackgroundColor. This highlights every other row of my table, making it easier to read.

Regards,

Daniel|||

chanbe backgound property of the table as =iif(((rownumber("table1") mod 2)=0),"White","#FFEBBD")

here "table1" is ur table name

sql

highlighting a table row

Have generated a report that uses a table. In the table are 2 fields, the
first is a severity and the last is the timeOfResponse which is a numeric. I
would like to highlight a row if the combination of severity and
timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
a matrix?
The function would be like
iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)In the properties window, put your IIF statement under background color. So
it would be something like
= iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
"Glass" wrote:
> Have generated a report that uses a table. In the table are 2 fields, the
> first is a severity and the last is the timeOfResponse which is a numeric. I
> would like to highlight a row if the combination of severity and
> timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> a matrix?
> The function would be like
> iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)|||scraejtp:
I actually have this in the background color property and it colors all rows
of the table pink.
= iif( (fields!timeOfResponse > 900),"pink","transparent")
A warning is also issued:
Value of the background color property for the textbox 'timeOfResponse' is
"transparent", which is not a valid background color.
I tried changing "transparent" to "blue". No warnings but all rows returned
pink.
Glass
"scraejtp" wrote:
> In the properties window, put your IIF statement under background color. So
> it would be something like
> = iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
> "Glass" wrote:
> > Have generated a report that uses a table. In the table are 2 fields, the
> > first is a severity and the last is the timeOfResponse which is a numeric. I
> > would like to highlight a row if the combination of severity and
> > timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> > a matrix?
> >
> > The function would be like
> > iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)|||Okay... problem solved. Have placed the expression under the background
properties for 'tablerow'. Also, discovered that cannot use 'transparent' or
'nothing' so used 'white'.
"Glass" wrote:
> scraejtp:
> I actually have this in the background color property and it colors all rows
> of the table pink.
> = iif( (fields!timeOfResponse > 900),"pink","transparent")
> A warning is also issued:
> Value of the background color property for the textbox 'timeOfResponse' is
> "transparent", which is not a valid background color.
> I tried changing "transparent" to "blue". No warnings but all rows returned
> pink.
> Glass
> "scraejtp" wrote:
> > In the properties window, put your IIF statement under background color. So
> > it would be something like
> > = iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
> >
> > "Glass" wrote:
> >
> > > Have generated a report that uses a table. In the table are 2 fields, the
> > > first is a severity and the last is the timeOfResponse which is a numeric. I
> > > would like to highlight a row if the combination of severity and
> > > timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> > > a matrix?
> > >
> > > The function would be like
> > > iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)|||Yeah, I thought you were already under table row, or an individual cell.
That is weird that it will not allow transparent because that looks to be
the default background for mine.
"Glass" wrote:
> Okay... problem solved. Have placed the expression under the background
> properties for 'tablerow'. Also, discovered that cannot use 'transparent' or
> 'nothing' so used 'white'.
>
> "Glass" wrote:
> > scraejtp:
> >
> > I actually have this in the background color property and it colors all rows
> > of the table pink.
> >
> > = iif( (fields!timeOfResponse > 900),"pink","transparent")
> >
> > A warning is also issued:
> >
> > Value of the background color property for the textbox 'timeOfResponse' is
> > "transparent", which is not a valid background color.
> >
> > I tried changing "transparent" to "blue". No warnings but all rows returned
> > pink.
> >
> > Glass
> >
> > "scraejtp" wrote:
> >
> > > In the properties window, put your IIF statement under background color. So
> > > it would be something like
> > > = iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
> > >
> > > "Glass" wrote:
> > >
> > > > Have generated a report that uses a table. In the table are 2 fields, the
> > > > first is a severity and the last is the timeOfResponse which is a numeric. I
> > > > would like to highlight a row if the combination of severity and
> > > > timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> > > > a matrix?
> > > >
> > > > The function would be like
> > > > iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)

Highest week of sales/usage

I'm working with Crystal Reports 10 with a Sql Server 2000 database. I've got a table which stores the details of items including the saletimes (a date/time field). I need to calculate the sales figure for the highest week of sales. In other words I need to find out what the highest amount of sales recorded in any given week. How can I do this?Quickest way would be to create a group based on date, and in the group options select "for each week" as the required grouping.
Create Sum(sigma sysmbol) fields for the data.
Suppress your details section.
In the group footer you will have Totals based on weekly aggregates.
In the Report menu, select TopN/Sort Group Expert.
Select the Tab for your weekly group (if you have more than one group that is).
In"for This Group Sort" change All to TopN.
Enter 1 in the "Where N is" field.
Remove the tick from the "Show others" checkbox.
OK that dialog.
Run you report. Should see only highest weeks.
May want to format the Date Field in your group to show start end end date of the week that finished top........

Hope this helps

dave|||re above - this is all client-side in Crystal.
would be quicker if could achieve it in SQL on server-side, but above solution is quick-ish, because Crystal's query to SQL Server will include an SQL-compliant "TopN" modifier.

dave

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95
SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>
|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;[vbcol=seagreen]
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500
>
|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did[vbcol=seagreen]
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do
>

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500[vbcol=seagreen]
>|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do[vbcol=seagreen]
>sql