SQL server questions: tricky

Showing posts with label tricky. Show all posts

Tuesday, March 20, 2012

a tricky view to create in Access; SQL is welcome !

Hi I am now sitting for almost ages in front of the screen, and may be
someone can help me...

Here's the thing

I have a table that looks like the following:

Mat-Nr: There are several million different materials
Package-Rule: there are only in total 2 different package rules
Package-Class: there are in total 9 different classes
Package-Material: there are 4 different package materials
Package-Nr: each package material has 1 or more package-numbers
Amount: there is at least 1 material in the amount

An example looks like this:

My problem is now, I have to create a query or view in Access 2000 so
that the following information is in ONE row:

thank you very much
Stefanal_capone@.web.de (Stefan) wrote in message news:<249f98b3.0407070650.75f1ead5@.posting.google.com>...
> Hi I am now sitting for almost ages in front of the screen, and may be
> someone can help me...
> Here's the thing
> I have a table that looks like the following:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
>
> Mat-Nr: There are several million different materials
> Package-Rule: there are only in total 2 different package rules
> Package-Class: there are in total 9 different classes
> Package-Material: there are 4 different package materials
> Package-Nr: each package material has 1 or more package-numbers
> Amount: there is at least 1 material in the amount
> An example looks like this:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
> 102 01 01 V 003 10
> 102 01 01 Z 045 03
> 116 01 01 V 056 23
> 116 01 01 Z 067 20
>
> My problem is now, I have to create a query or view in Access 2000 so
> that the following information is in ONE row:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
> 102 01 01 V 003 10
> .
> .
> .
> Package-Material|Package-Nr|Amount|
> Z 045 03
> .
> .
> .
> thank you very much
> Stefan

Hello Stefan,

Just one question. Are you dealing with only two records for each
Mat-Nr field data? Meaning 102, 102, 166, 166 etc.?

If so, then perhaps you can make a grouping query to get
your data result like the following.

Create a query using your source table with all the fields.

Next, change it to a grouping/summing query.

Then, edit your last three fields to be like the example below.

PackMat01: Package-Material
PackNr01:Package-Nr
Amount01: Amount

Now, change Grouping to First

Then add 3 more fields accordingly.

PackMat02: Package-Material
PackNr02:Package-Nr
Amount02: Amount

Now, change Grouping to Last

Run the query. What this should show is the First record for 102
and the last record for 102 in your grouping query for the 01 and
02 field groups according to your requirements.

Good luck!

Regards,

Ray

a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

-------------------

A tricky query

I have to write a query to generate a report over some interesting
data. It's basically scheduling which days people are working. The
data looks like this:
Employee StartDate EndDate Roster
-- -- -- --
Bob 12-Jun-06 24-Jun-06 _*___**
Mary 12-Jun-06 24-Jun-06 *_*__*_
The trick is, the roster field contains a string with a _ or *
depending on wether the person is scheduled to work that day or not,
but the first character always starts on the sunday. The startdate and
enddate can be any day of the w

.
In the example above, the 12-jun is a monday, so monday corresponds to
the second character in the roster string, so Bob's working and Mary's
not. The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here! Now, this
roster string could be 7, 10, 14 days long. The startDate -> endDate
could be the length of the roster string or less (only show a subset of
the roster data).
So! I need to write a query to feed a report to format this into
something like:
Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
Friday 16-Jun
-- --
-- --
--
Bob Mary
Bob
Mary
I could get the report out if I can write a query to get it to this:
Employee DateWorking
-- --
Bob 12-Jun
Bob 16-Jun
Mary 13-Jun
Mary 16-Jun
Any ideas?
Thanks!
DaveDave
Can I ask you , why not just doing such reports on the client side? T-SQL
is not good for such things
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w

.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||>> I have to write a query to generate a report over some interesting data.
<<
First of all, you are not using ISO-8601 format dates. You might want
to do that, since what you did post was ambigous as well as
non-standard, I hope you do not think that ORACLE is a standard.
Next, you might want to read a book on programming principles. We do
not do reports in the database in a tiered architecture. This is more
fundamental than SQL.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. I
would love to see the LOGICAL definition of that silly bar chart you
labeled "roster" in your narrative since it is pure display.
CREATE TABLE Roster
(employee_name VARCHAR(20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (employee_name, start_date));
This is usually done with a Calendar table:
SELECT R.employee_name, C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Roster AS R
ON C.cal_date BETWEEN R.start_date AND R.end_date
WHERE C.cal_date BETWEEN @.my_start_date AND @.my_end_date;
Google for other uses of the Calendar and auxiliary tables.|||Dude, you don't do things by halves... <g>
I can't think of any way to do it without iterating over each employee...
step 1:
select employee, startDate, abs(datediff(day, startDate, endDate)) as
dateIterations, Roster
into #tmp1
from theTable
step 2:
-- loop the temp table
-- for each row
-- loop the roster string from 1 to dateIterations
-- if substring(roster, index, 1) = '*'
-- insert a row for employee, dateadd(day, index, startdate)
step 3:
join Thetable to #tmp1 by employee
It sounds like you need to denormalize the results so you could also start
by creating a date table from min(startDate) to max(endDate) first with all
the dates inbetween,
then outer join to that by date to #tmp1 and theTable
[Sidenote]
This is one of the reasons why normalization is a good thing. It's
extremely hard to pull peices-parts of data from a column that contains
multiple data points...
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w

. Look at
these three bits of the description:
- "the first character always starts on the sunday"
- "The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here!"
- " this roster string could be 7, 10, 14 days long."
With a string of length 7 or 14 I can see how it can wrap around and
still start on Sunday. With a length of 10 I can not see how this is
possible.
A second point of confusion is the sample data. The date ranges give
are far longer than the strings . This makes no sense.
Anyway, I believe that once those issues have been resolved the
following will work. Note that I changed the date range from the test
data you provided so that it did not exceed the length of the string.
CREATE TABLE Numbers
(nbr int not null)
INSERT Numbers
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT
5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT
10 UNION
SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '12 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '12 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
--A view that lines up the string with the StartDate
CREATE View Madness_V
AS
SELECT Employee, StartDate, EndDate,
SUBSTRING(RTRIM(Roster) + Roster,
Datepart(w

day,StartDate),
DateDiff(day,StartDate,EndDate)) as Shifted
FROM Madness
GO
--Lets see what the view gives us
SELECT *
FROM Madness_V
--Now for the desired results
SELECT M.Employee,
DATEADD(Day,Nbr-1,StartDate) as WorkingDate
FROM Madness_V as M
JOIN Numbers as N
ON Nbr <= Datediff(day,M.StartDate,M.EndDate)
WHERE SUBSTRING(Shifted,Nbr,1) = '*'
order by 1, 2
Roy Harvey
Beacon Falls, CT
On 21 Jun 2006 06:00:05 -0700, "Dave Newman" <ddangerous@.gmail.com>
wrote:

>I have to write a query to generate a report over some interesting
>data. It's basically scheduling which days people are working. The
>data looks like this:
>
>Employee StartDate EndDate Roster
>-- -- -- --
>Bob 12-Jun-06 24-Jun-06 _*___**
>Mary 12-Jun-06 24-Jun-06 *_*__*_
>The trick is, the roster field contains a string with a _ or *
>depending on wether the person is scheduled to work that day or not,
>but the first character always starts on the sunday. The startdate and
>enddate can be any day of the w

.
>In the example above, the 12-jun is a monday, so monday corresponds to
>the second character in the roster string, so Bob's working and Mary's
>not. The roster string wraps around, so the first character of the
>roster string actually corresponds with the enddate here! Now, this
>roster string could be 7, 10, 14 days long. The startDate -> endDate
>could be the length of the roster string or less (only show a subset of
>the roster data).
>So! I need to write a query to feed a report to format this into
>something like:
>Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
>Friday 16-Jun
>-- --
>-- --
>--
>Bob Mary
> Bob
> Mary
>I could get the report out if I can write a query to get it to this:
>Employee DateWorking
>-- --
>Bob 12-Jun
>Bob 16-Jun
>Mary 13-Jun
>Mary 16-Jun
>
>Any ideas?
>Thanks!
>Dave|||I don't understand your definition of how a roster day maps to an actual
date so I can't calculate that, but you can use recursion to "unflatten"
roster string into a row for each '*' and go from there. I also don't know
what the primary key of your table is, but I assume that name+start date
will work for that.
Create table #sched
(
name NVARCHAR(20),
start DATETIME,
[end] DATETIME,
roster NVARCHAR(14)
)
--TRUNCATE TABLE #sched
INSERT INTO #sched VALUES (N'bob', '12-JUN-06', '24-JUN-06', N'_*--**')
INSERT INTO #sched VALUES (N'mary', '12-JUN-06', '24-JUN-06', N'*_*__*_')
WITH workingDays
AS
(
-- get the first roster day for every entry in table
SELECT name, CHARINDEX(N'*', roster) AS rosterDay, start, roster FROM #sched
WHERE CHARINDEX(N'*', roster, 0) > 0
UNION ALL
-- now get subsequent roster days
SELECT S.name, CHARINDEX(N'*', S.roster, W.rosterDay + 1) AS rosterDay,
S.start,
S.roster from #sched AS S
JOIN workingDays AS W ON W.name = S.name and W.start = S.start AND
CHARINDEX(N'*', S.roster, W.rosterDay + 1) > 0
)
SELECT name, rosterDay, roster, start,
DATEADD(day, rosterDay-1, start) as DateWorking
FROM workingDays
ORDER BY name, rosterDay
name rosterDay roster start Date
Working
-- -- -- -- --
--
bob 2 _*--** 2006-06-12 00:00:00.000 2006
-06-13
00:00:00.000
bob 6 _*--** 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
bob 7 _*--** 2006-06-12 00:00:00.000 2006
-06-18
00:00:00.000
mary 1 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-12
00:00:00.000
mary 3 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-14
00:00:00.000
mary 6 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
If you replace the DateWorking column with a UDF that takes as input a roste
rDay
and start and produces a working date I think you will have what you want.
Dan

> I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate
> and enddate can be any day of the w

.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset
> of the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
> Any ideas?
> Thanks!
> Dave
>|||Hi Dave,
It would have been great if you had given the ddl. Anyways, here is the
solution.
Let me know if this works..
The ddl and ur sample data first
create table t1 (Employee varchar(10), StartDate datetime,
EndDate datetime, Roster varchar(10))
insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
'_*___**')
insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
'*_*__*_')
And here is the query. You need a temp table so that I don't loop through
that roster or whatever
--I have assumed that the size of the roster is 30 characters. If yit can be
100 or 1000 increase it accordingly
select top 30 identity(int,0,1) as num into #temp from sysobjects
--query
-- This is to remove any ambiguity
set datefirst 7
select Employee, StartDate - datepart(dw,startdate) + num + 1
from t1, #temp
where
substring(Roster,num%len(roster) + 1,1) = '*'
and StartDate - datepart(dw,startdate) + num + 1 between startdate and endda
te
Let me know if this was what you wanted.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi There,
I think this may solve your problem
Try it and let me know if it helped
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '13 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '13 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
Select identity(int,1,1) myid into Numbers from sysobjects
Select Case When myid<pos Then startdate+myid-1
Else startdate+pos-datepart(dw,startdate)
end ,
Employee, Case when substring(roster,pos,1)='*' then 'Working' else
'Not' End
From
(
Select myid,
case when (datepart(dw,startdate)+myid-1)%datalength(roster)= 0 then
datalength(roster) else
(datepart(dw,startdate)+myid-1)%datalength(roster) end Pos,
Employee,
Startdate,
Enddate,
Roster
>From Madness,Numbers
where myid <= datalength(Roster)
) XY order by 2,1
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Omnibuzz wrote:
> Hi Dave,
> It would have been great if you had given the ddl. Anyways, here is the
> solution.
> Let me know if this works..
> The ddl and ur sample data first
>
> create table t1 (Employee varchar(10), StartDate datetime,
> EndDate datetime, Roster varchar(10))
> insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
> '_*___**')
> insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
> '*_*__*_')
>
> And here is the query. You need a temp table so that I don't loop through
> that roster or whatever
> --I have assumed that the size of the roster is 30 characters. If yit can
be
> 100 or 1000 increase it accordingly
> select top 30 identity(int,0,1) as num into #temp from sysobjects
>
> --query
> -- This is to remove any ambiguity
> set datefirst 7
> select Employee, StartDate - datepart(dw,startdate) + num + 1
> from t1, #temp
> where
> substring(Roster,num%len(roster) + 1,1) = '*'
> and StartDate - datepart(dw,startdate) + num + 1 between startdate and end
date
> Let me know if this was what you wanted.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/sql

A tricky code problem!

I have a report that looks like the following:

record record record null null record record
supress supress supress null record1 null supress
supress supress supress record2 null null supress

This is the best way to describe my data, its 3 rows of records and I want to show record1 and record2 on the top row. I have the other data beneath supressed. I am sure this maybe be possible using SQL code or using a loop or something but I have no idea where to start and have been trawling the web for answers or some code samples but have found none to date as it is quite a specific thing. Its tricky I know but could someone help me please?Hi,
I want to make a page break after section3 in crystal report(ver. 8) after displaying 5 records. I'm using .Dsr report. I am using the following code.

'*******************************
Set Report = New CrystalReport1
Report.Database.AddADOCommand db, cmd
For i = 1 To rs.Recordcount

Set txtObj = Report.Section2.AddTextObject(l, 150, vHight)
txtObj.Width = 300
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!ITEM_DESC, 400, vHight)
txtObj.Width = 2000
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!narmst_sItemName, 2800, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate, 4200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemQty, 5700, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 7100, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 8500, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

If IsNull(rs!VAT_AMT) Then
m = 0
Else
m = Val(rs!VAT_AMT)
End If

Set txtObj = Report.Section2.AddTextObject(m, 9600, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(Val(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty + m), 11200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

If i > 5 Then
Report.Section3.NewPageAfter = True
End If

rs.MoveNext
vHight = vHight + 250
l = l + 1
Next i
'**************************

But it is not working. Please Help me.

with regards,
Arindam

Saturday, February 25, 2012

A question on ow to design some tables

Hi all,
I have a fairly tricky problem that I'm not sure how to approach.
I'm making a web application that manages drug trials. One of the
requirements of the system is if anyone makes changes to a field, the old
value and the new value need to be stored, along with the time of the change
and the reason for the change
The problem is I don't know how to support this for all the various fields
in all the various tables.
For example I have tables for storing basic patient details and then tables
for storing data on patient visits, patient screening data and so on.
Can anyone suggest how I could make a table or tables to store this audit
data for all the fields in all the tables? I'm not sure how to do it!
:-(
Thanks to anyone who can help
SimonHi Simon,
There is an in built database utility called a Trigger,
these perform an automatic response for an INSERT, UPDATE
or DELETE.
You can access Triggers in EA by clicking on the 'Design'
of a table in EA, its the button next to primary key.
Anyway in the following example I have created three
triggers in a database table which stores the before and
after values in an audit table.
Give it a try and see if its what you want, you may also
want to read up about triggers on BOL.
J
CREATE TABLE [dbo].[tblTestAudit] (
[Type] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] NULL ,
[OldVal] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[NewVal] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblTesting] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Testing] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblTesting] WITH NOCHECK ADD
CONSTRAINT [PK_tblTesting] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
CREATE TRIGGER tk_INSERT ON [dbo].[tblTesting]
FOR INSERT
AS
DECLARE @.TKTYPE as char(6)
DECLARE @.ID as int
DECLARE @.NEW as char(20)
select @.ID = ID, @.NEW = Testing from inserted
insert into tblTestAudit (Type, ID, OldVal, NewVal) Values
('INSERT', @.ID, '', @.NEW)
GO
CREATE TRIGGER tk_DELETE ON [dbo].[tblTesting]
FOR DELETE
AS
DECLARE @.TKTYPE as char(6)
DECLARE @.ID as int
DECLARE @.OLD as char(20)
select @.ID = ID, @.OLD = Testing from deleted
insert into tblTestAudit (Type, ID, OldVal, NewVal) Values
('DELETE', @.ID, '@.OLD', '')
GO
CREATE TRIGGER tk_UPDATE ON [dbo].[tblTesting]
FOR UPDATE
AS
DECLARE @.TKTYPE as char(6)
DECLARE @.ID as int
DECLARE @.NEW as char(20)
DECLARE @.OLD as char(20)
select @.OLD = Testing from deleted
select @.ID = ID, @.NEW = Testing from inserted
insert into tblTestAudit (Type, ID, OldVal, NewVal) Values
('DELETE', @.ID, '@.OLD', '@.NEW')
>--Original Message--
>Hi all,
>I have a fairly tricky problem that I'm not sure how to
approach.
>I'm making a web application that manages drug trials.
One of the
>requirements of the system is if anyone makes changes to
a field, the old
>value and the new value need to be stored, along with the
time of the change
>and the reason for the change
>The problem is I don't know how to support this for all
the various fields
>in all the various tables.
>For example I have tables for storing basic patient
details and then tables
>for storing data on patient visits, patient screening
data and so on.
>Can anyone suggest how I could make a table or tables to
store this audit
>data for all the fields in all the tables? I'm not sure
how to do it!
>:-(
>Thanks to anyone who can help
>Simon
>
>.
>|||Hi Julie,
Thanks for your reply.
What I'm really stuck on is how to arrange the audit tables so that they can
store all sorts of information from the different types of data from all the
tables
Do you have any ideas along those lines?
Thanks again for your help!
Simon|||What if you make duplicate tables and append "_archive" or similar to their
names, and use triggers to copy the original record in it's entirety to
these archive tables before inserting the new data into the main table?
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:eexDr4VHEHA.2260@.TK2MSFTNGP09.phx.gbl...
> Hi Julie,
> Thanks for your reply.
> What I'm really stuck on is how to arrange the audit tables so that they
can
> store all sorts of information from the different types of data from all
the
> tables
> Do you have any ideas along those lines?
> Thanks again for your help!
> Simon
>|||Hi Simon,
Keith has really answered the question. The best way I
have found how to do this is for every table you want to
audit, create an audit table.
In the example given to you two tables tblTesting is the
table the auditing is to take place, and tblTestAudit is
table where the changes will stored. You can cut paste and
run the code in Query Analyser and try it out if you want.
The example will capture every change made in the
tblTesting database and put the before and after values in
tblTestAudit.
What I actually showed you was quite basic, you can expand
it to show the name of the user who made the change, date
time of change, infact anything that can be programmed in.
So to recap.
1. The best way I have found is for each table you will to
audit create an audit table.
2. Cut and paste the demo, execute it in Query Analyser
and see what it does
3. Figure out what other things you need to change it.
Enjoy
J
>--Original Message--
>Hi Julie,
>Thanks for your reply.
>What I'm really stuck on is how to arrange the audit
tables so that they can
>store all sorts of information from the different types
of data from all the
>tables
>Do you have any ideas along those lines?
>Thanks again for your help!
>Simon
>
>.
>|||Many thanks to both of you!
:-)
Simon

Saturday, February 11, 2012

A littel help with table design....

Hi all,

I have a fairly tricky problem that I'm not sure how to approach.

I'm making a web application that manages drug trials. One of the requirements of the system is if anyone makes changes to a field, the old value and the new value need to be stored, along with the time of the change and the reason for the change

The problem is I don't know how to support this for all the various fields in all the various tables.

For example I have tables for storing basic patient details and then tables for storing data on patient visits, patient screening data and so on.

Can anyone suggest how I could make a table or tables to store this audit data for all the fields in all the tables? I'm not sure how to do it!

:-(

Thanks to anyone who can help

SimonPersonally I'd do that logic in the objects rather than the DB. But if you want to use the DB then you need to create an Audit Table(s). Then use a trigger to write the values and a timestamp into the Autit table whenever the value changes.|||I need to do the similar thing.
I created second database as the log for the main one. It has all the table as the main one, and with additional fields to save userId, updata type, update date/time, etc.
Trigers are added to the main tables to insert old value into log database.
I appreciate it if anybody in this forum has better idea.

Tuesday, March 20, 2012

a tricky view to create in Access; SQL is welcome !

a tricky query

A tricky query

A tricky code problem!

Saturday, February 25, 2012

A question on ow to design some tables

A question on ow to design some tables

A question on ow to design some tables

Saturday, February 11, 2012

A littel help with table design....

SQL server questions

Blog Archive

About Me