T-SQL (SS2K8) :: Show Changes From And To In History Table

Jun 28, 2014

I have a table history of Employee data.

id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser

I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.

Result must be:

EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

View 4 Replies


ADVERTISEMENT

Employee Data - Show Changes From And To In History Table

Jun 27, 2014

I have a table history of Employee data.

id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser

I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.

Result must be:

EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

View 4 Replies View Related

T-SQL (SS2K8) :: Get Data Changes From History Table?

Mar 25, 2015

how to get products on which SalesPerson changed. Here is table with data.

View 9 Replies View Related

T-SQL (SS2K8) :: Get Recent Changed Value From History Table

Apr 6, 2015

I have a history table with the following values

CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')

[Code] ....

I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result

OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Show Table Products With Its Modes

Nov 28, 2014

Query that show me a Products Tables and its models, 1 product could have 2 or more models

Example Table Products

id Name
1 Product 1
2 Product 2
3 Product 3

Example Table Models

idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1

And I want to show:

Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1

How can I query to show that?

View 5 Replies View Related

Can I Show History In Reports Via Previous Snapshots Of The Same Report?

May 2, 2008

I have a report that runs monthly and they want to see the previous 2 months of data. This data is stored no place other than the snapshot of the same report for the previous 2 months. Is there any way I can display the last 2 snapshots/history withing my report? Thanks.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Show Total Sales

Mar 10, 2014

My Table struct

create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float

[code]....

like i wanna display each pincodes how to make a code?

View 6 Replies View Related

T-SQL (SS2K8) :: How To Show ED / CESS In Query

Nov 5, 2014

Detail:

create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)

insert into detail values

('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')

i wanna display ED ,CESS ,HCESS,VAT in separate columns in output How to that?

View 4 Replies View Related

T-SQL (SS2K8) :: Split String To Show Only Customer

Aug 7, 2014

The data we are getting from our shipping company has the customer name and customer number attached.

so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

I need to match this up with our customer table... on Customer Name.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Show Last Year YTD Month With Procedure

Feb 22, 2015

I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels

in level2:

my procedure display the output of (Actuals in lakhs)

RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS
SALES Sales Net Sales 444.65
Sales Sales LESS TRD 22.55
SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD)
Finance LESS ED LESS ED 40
Sales Totals Sales 382.10(RETURNS - ED)

(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)

Materials .... ... ..
production ..... ............ ........

i made a procedure for above

create procedure Pl_level2

@fmdate datetime,
@todate datetime,
@category varchar(200)
as
begin
create table #temp1

[code]....

like i m inserted so many accounts in temp tables .

IF (@category IS NULL or @catagory=' ' )
begin
select
responsibility,
dept,
category,
round(Max(Actuals)/100000,1,2) as Actuals from #temp

[code]....

here i can display only current YTD only how to display previous year (13-14) YTD in level1

How to do?

View 1 Replies View Related

T-SQL (SS2K8) :: Displaying Sum Of Records And Show Row Even If It Is Blank Or Zero

Mar 10, 2015

I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.

Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3

[code]...

My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.

Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name

View 5 Replies View Related

T-SQL (SS2K8) :: How To Show Last Purchased Rate In All Months Of Output

Nov 12, 2014

I've the table like

create table test
(
product varchar(50),
QTY int,
rate float,
Bill_date datetime,
total_val float
)

insert into test values ('Milk','50','500','2014-04-20', '25000')
values ('Milk','20','560','2014-05-20','12000')
values ('Milk','30','450','2014-06-18','13500')
values ('Milk','30','700','2014-06-18','21000')

My expecting output:

Month product Qty Rate T_val
April Milk 50 700 25000
MAY MILK 20 700 12000

Like I've to show the price of latest price in all of months product billed. How to make Query?

View 9 Replies View Related

T-SQL (SS2K8) :: Query To Show Database Objects Last Change

Mar 17, 2015

If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?

View 7 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 Replies View Related

T-SQL (SS2K8) :: Finding Tree Structure - Show All Upward And Downward Nodes

Jun 3, 2015

I have the following table:

SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL

[Code] .....

I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output

View 3 Replies View Related

Instances And History In One Table

Jan 11, 2008

I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.

Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.

So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.

I want to keep track of which users used a instance of a wheelchair.

No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this


UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns

The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.

If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.

Is this a good or a common practice? Why use it, or stay away from it?

Henri
~~~~
There's no place like 127.0.0.1

View 1 Replies View Related

Common Need: History Table

Jan 22, 2008

Hi there!

I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.

When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.

First, what do you think of that implementation?

We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...

What is the common solution for this need?

Thanks
Frantz

View 4 Replies View Related

History Table Design Issue

Nov 15, 2007

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

View 1 Replies View Related

How To Design The History Table To Be More Efficient?

Feb 8, 2007

I am running a website of crossword puzzle and Sudoku games. The website is designed to be:
There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.


The current solution is:
3 tables are designed for the functions mentioned above.
Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied

PS: Fields listed above are only related ones, not the complete structure.

Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.

When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.

When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.

Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.

Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?

View 5 Replies View Related

SQL 2012 :: Query For History Table?

Nov 3, 2015

I have order history table that tracks who worked on a order

ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538

I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4

I have order history table that tracks who worked on a order

Another example

ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789

I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2

View 1 Replies View Related

History Table &> X Days Between Progression

May 21, 2008

I work for a college and have recently been working on our enquiries and applications process (getting it onto our big enrollment db rather than standalone). It has all been going well but now they have asked for a report of students where it has taken more than x days or weeks to progress to the next stage code.

For stage codes they basically follow something like application, guidance interview, programme area interview, conditional/ unconditional offer... Although they could skip a stage code.

Any ideas how to do this bearing in mind I can't guarantee them to go to every stage so really I need to look in the history table and find records more than x days apart where one is the next progression date of the other. Hope I explained that ok.

history table is similar to:

StudentID CourseCode StageDate InputDate Stage

View 1 Replies View Related

New Unique Number In History Table

May 23, 2008

Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

This is the table "People" I have created:

|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....

Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.

My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.

As always, thanks for the help!

View 4 Replies View Related

Referencing A Users Table For History

May 6, 2008

I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.

For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.

Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?

View 1 Replies View Related

Nothing Written In View History Table

Mar 25, 2008



Hi guys,

acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...

Can any one tell's me about this...

View 3 Replies View Related

DB Engine :: Table Transaction History

Oct 22, 2015

i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Show Only Top Or Main Team For Team Member

Nov 2, 2014

Team members appear twice or more if they belong to more than one team. I need to be able to show their name and main team. Team is not important at the moment but I just like to include all team members and display a team name against them.This is what I have at the moment:

SELECT SystemUser.systemuserid, FullName, TeamMembership.TeamID, TeamName
FROM Team
RIGHT OUTER JOIN TeamMembership ON Team.teamid = TeamMembership.teamid
LEFT OUTER JOIN SystemUser ON TeamMembership.systemuserid = SystemUser.systemuserid
order by FullName

View 3 Replies View Related

T-SQL (SS2K8) :: Creating A Query To Show Data Side By Side

Nov 26, 2014

I am working to create a phone list that will contain Last Name, First Name, and Phone Number sorted by last name. For printing purposes I would like to have three columns of data instead of the standard of one column.

Is it possible to create a query to present data in three columns showing the data side by side?

View 9 Replies View Related

SQL Server 2012 :: Password History Table

May 7, 2014

I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....

I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)

This table contains the following columns : Username, Password , and Password_Date.

I searched a lot but could not find something similar in my opinion need SP for it.

- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).

View 9 Replies View Related

SQL 2012 :: Select Statement From History Table?

Jan 26, 2015

Project has 2 tables process(parent) and processchild(child).

Project workflow recorddsany changes to these tables as a history.

I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).

Here is example.

Process table

PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...

ProcessChild table

PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..

I want to find out all the processes where processchildid=2 and processchild.status =1

View 3 Replies View Related

Complex Stored Procedure On History Table

Jun 7, 2006

Hi All,

I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?



Thanks in advance : )

View 1 Replies View Related

Updating Daily Information In A History Table (was Help-Brainstorming)

Feb 25, 2005

Hi everyone,
I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this:
date ID A B C D
1/1/65 X Null Null Null Null
1/4/65 X 1 2 3 4
...
2/25/05 X 2 3 4 5
1/1/65 Y Null Null Null Null
1/4/65 Y Null Null Null Null
...
2/25/05 Y 2 3 4 5
...

The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all?

Thank you everyone for the help.
shiparsons

View 8 Replies View Related

SQL Query History Table To Return Only Active Rows

Mar 5, 2008



Given the following data how do I make a SQL query that returns only 1 row per product?

The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }).
The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.


Example Data:
HistoryID ProductID Name Color DateEffective RowTimeStamp
(auto-number PK)
1 1 Wheel Red 2/1/2008 2/1/2008
2 1 Wheel Blue 3/5/2008 3/1/2008
3 1 Wheel Orange 3/5/2008 3/2/2008
4 1 Wheel Black 1/1/2010 3/3/2008
5 2 Knob Blue 3/2/2008 3/2/2008
6 2 Knob Green 3/3/2008 3/3/2008

Query should return:
3 1 Wheel Orange 3/5/2008 3/2/2008
5 2 Knob Green 3/3/2008 3/3/2008


The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes.
Can anyone help?










View 15 Replies View Related

A Basic History Table - Foreign Keys And Deletes

Nov 19, 2007

Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.

An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.

If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.

I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?

Thanks,
-Dan

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved