Basic Design Question Re Effective Relationship
Jul 20, 2005
Hi,
Probably a very simple question but I'm afraid my experience at this is
somewhat limited so...
In a database with a many to one relationship, say orders to customers, is
it preferable to have an UID for a customer and store this in the orders
table rather than the customer name, for example.
CUSTOMERS
++++++++++
UID: Name: Address: Postcode: etc
01 Alpha 12 Acacia Ave HN7YHH
02 Beta 23 Acacia Ave HN6YTH
03 Gamma 28 Acacia Ave HN7UYH
ORDERS
Customer Amount date
Alpha 100 20030823
Alpha 250 20030824
Beta 90 20030825
Alpha 800 20030825
Gamma 1000 20030826
Or is it is better to have:
ORDERS
Customer Amount date
01 100 20030823
01 250 20030824
02 90 20030825
01 800 20030825
03 1000 20030826
Assuming that the second option is the best, how far do I go? I can see how
it would beof benifit to do this for orders and customers where there may be
several hundred thousand orders and hundreds of customers, but for example
would I need to do this for a customer contact database with titles - eg
each title having a UID and using this ID in the customer table?
Thanks for taking the time to read this. Any advice is gratefully received.
Kind thanks
Chris S
View 4 Replies
ADVERTISEMENT
Sep 10, 2015
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code]...
View 4 Replies
View Related
Jul 3, 2007
I need to design a schema that will provide me a hierarchical view in reporting. I mean by this is, lets say:
Hospital
|_________> Hospital Entity
| | |
| | V
| | Clinic A
| | - Jane Doe MD
| | - Janette Brown MD
| | ACME Clinic
| | - Jennifer Smith MD
| | Clinic of Jennifer Smith MD
| | - Jennifer Smith MD
| | - Billy Johnson MD
| |
| V
| - Earl Brown MD (Hospital Entity Resident Practitioner)
| - Janette Brown MD
V
- John Smith MD
- Bob Jones MD
The design should also allow me to query a practitioner and the query will show all the entities he or she is a member of.
If this is not the correct place to post this posting, please let me know the correct place and I will make the necessary changes.
Thanks,
Stephen
View 7 Replies
View Related
Apr 17, 2007
When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION?
Then if there is a lookup table on the many side how should it be set?
View 3 Replies
View Related
Oct 30, 2007
i am a beginner and any response will help me a lot.
i have 20 types of products,each product has about 40 fields, most of them are common fileds for all products, but some of them are unique.
the number of the unique fields of all the product types is 75.
the question:Should i build one products table with 75 fields and leave nulls where a field is not property of a product type?Or should i build a products table for the 30 common fields and a 20 different tables for each product type extra fields?Or just build 20 different tables each product type?
other ideas are welcome
View 2 Replies
View Related
Dec 5, 2005
I have decided to build a Football Pool app as a way of teaching myself asp.net with non code-generating tools. I am stuck on some basic DB design thoughts and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like Users, Teams and Game Schedules. I need to be able to record whether a user has paid their weekly entrance fee but I don't know which table to store this info even what datatype would be the best solution.
Here is the table design I have so far http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the users tables would be the asp.net 2.0 mambership ones which are too many to list...
Thoughts please. TIA
View 2 Replies
View Related
Jan 26, 2005
Hi,
we have records with login.out of employees.
i.e.
this is a set of login/out for an employee for 1 day (but the actual data shows 2 dates )
login 1/1/2005 15:00
logout 1/1/2005 16:00
login 1/1/2005 17:00
logout 1/1/2005 18:00
login 1/1/2005 23:00
logout 1/2/2005 1:00
login 1/2/2005 2:00 <-----------this is the reason why we need a new table called schedules
logout 1/2/2005 3:00 some employee goes over the next day to finish their shift
I think in order to get the proper Start Time and Stop time we need a new table called schedules.
schedules will contain employee work schedules, kindly help me design how to accomplish this.
question is, will it be for each day?
so I'll have 365 record per employee?
help me design the columns
employeeID, startTime,stopTime, workdate -do I need only four?
Thanks.
View 2 Replies
View Related
Mar 27, 2006
Amanda writes "I'm a student currently taking a class working with the SQL database and I'm not doing too well in that class because I'm struggling on some basics. I have worksheets and everything i just need help working on them, and this being and on-line class makes it hard for me to work with my teacher. If someone is willing to work with me one on one through a messaging program or Email that would be great, i can send you the worksheets and we can work it out together."
View 5 Replies
View Related
Jun 21, 2007
Hi all, this is a really simple application to record results of a questionaiire. Basically, each question can have multiple yes/no type answers.
Any help appreciated. Also going to develop a c# front end.
View 2 Replies
View Related
Jun 24, 2007
I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me
View 3 Replies
View Related
Mar 25, 2006
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
View 3 Replies
View Related
Oct 14, 2015
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
View 9 Replies
View Related
Oct 3, 2001
Hi all,
I am a java developer having little bit knowledge in sql statements.
can any body guide me what is the effective way of using sql statement
to achieve the following goal.
here is the requirement.
1. There are 2 tables A & B.
2.The table 'B' may or maynot have an equivalent entry for the primary
key of table a.
3. select column1 (of A), column 2(of B) from table a and b.
4. now column2 should give a value, if there is a corresponding
entry in table b.other wise null.
5. at any time there may be 0 to 1 record in table b for the primary
of table a.
thanks in advance
krishna
View 1 Replies
View Related
Apr 4, 2006
I did a search of the forums and couldn't find any other threads on "Effective Dating" so please forgive me if this topic has already been covered.
Does anyone know of any support articles or tutorials covering the topic of "Effective dating" as a means for keeping a history for data?
I would like to keep a infinite history on certain tables and would like to avoid duplicating data structures if possible. Any suggestions?
Thanks,
JAndrews
View 4 Replies
View Related
Oct 6, 2006
My gut tells me this can't be done quite the way I'm hoping, but maybe someone has a little insight...
Is it possible to specify a specific timeframe for which triggers should be effective? I have some make-shift replication tasks which are currently done via pure insert/update queries and various times throughout the day. In the am our "target" table is completely wiped and repopulated from our "source" table which resides on a different server.
I'm hoping to setup update triggers throughout the day to catch changes to our "source" table. These triggers should only be active for a six-hour period throughout the day. Unfortunately true replication isn't an option here.
I can think of a couple ways to take care of this such as checking GETDATE() from the trigger itself and deciding whether or not to do anything, or perhaps scheduling a job to enable and disable triggers at the appropriate time. Is there a way to setup such a schedule for the trigger itself?
View 4 Replies
View Related
Feb 8, 2007
When I click on the properties of a 2005 sql server database and thenpermissions.If I select a group and then effective permission, I got an error,saying that "cannot execute as the principal server because the 'xxxgroup' does not exist, this type of principal cannot be impersonated,or you do not have permission (Microsoft SQL Server; Error: 15406)"I guess the error is valid, since the group does not exist in thedatabase as a user. If so, how do I get the effective permissions of agroup?
View 1 Replies
View Related
Dec 10, 2007
Hi, I am looking for a sql statemnt that find the most related phrases, how to do that? For example, I want to find the related phrases bellow for the following phrase: "How to install the software"
1- "Installation does not work"
2- "installation"
3- "steps to install the software"
It's obvoius that phrase number 2 and 3 are related to that phrase, how to do that using sql?
Regards.
View 1 Replies
View Related
Nov 25, 2003
Dear SQL,
since I create some multi-language table - I want to allow finding unicode text
so I made the field: Key_Words" (ntext)
It will have a string that can include some words in different languages, so that I can find by using:
SELECT Key_Words FROM MyTable WHERE Key_Words LIKE '%" & MyVar & "%' "...
The problem is that I can not apply clustered index on ntext field (or *any* index...)
Any ideas how to deal with it ?
View 2 Replies
View Related
Feb 25, 2004
I have built my own functions in the past that involve a timestamp and record lock column in each table (to support some other DB).
I am currently using SQL 2000 and was wondering if there is a better (OK, easier) way to lock records and even prevent edits from taking place when a record is open.
Any input appreciated.
View 25 Replies
View Related
Apr 4, 2006
Can anyone provide me with some insight on tracking the history of my data using "Effective Dating"? I would like to keep track of an infinite history for many of the tables in my database and am trying to avoid duplicating data structures if possible.
Any advice on this topic would be appreciated, I am most interested in any links to tutorials or support articles on subject that you may know about. I have searched the web and the forums section and have not come across much.
Thanks,
JAndrews
View 4 Replies
View Related
May 20, 2008
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
View 6 Replies
View Related
Mar 2, 2004
I have a large SQL Database im building, in the database there is a table for each user with certain things listed. One problem is, sometimes in a few collums, there will be like 100things listed, instead of one line.
For Example:
Products in that some users will have 1 product like "apples" but others will have many more products.
What is the most effective way of listing the data? Create new tables? Seperate the products with comma's or spaces?
How do I do it, and keep the overall db size smaller?
Thanks
View 5 Replies
View Related
Dec 17, 2014
i have 3 tables having refrential integrity on id on column on each table
TABLEA contains 5 million rows
TABLEB contains 50 thousand rows
TABLEC contails 5 thousand rows
i have a proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.what is the optimized way to write query to get records on the basis of in parameters from these tables.
View 7 Replies
View Related
Mar 10, 2008
Hi,
Not sure if you could help or not, but I need to pull the most recent effective date for this report I am trying to run, but I am getting know where. If someone can take a look at this, it would be great.
Example….
pcs number 00004344 effective dates 5/1/2006 and 5/1/2007. I need it to be the most recent effective date which would be, 5/1/2007 date.
Can someone help me?
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES] Script Date: 03/10/2008 09:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES]
AS
BEGIN
SET NOCOUNT ON
DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME
SET @STARTTIME = GetDate()
PRINT 'SP started on: ' + CAST(@StartTIME as varchar)
PRINT ''
DECLARE @PKey varchar(16), @pcsi_id1 varchar(8), @pcsi_id2 varchar(4) /**@pcsi_id3 varchar(4),@Lpcsi_id3 varchar(4)**/
DECLARE @LplID varchar(12), @LTrm Datetime, @Eff Datetime, @Trm Datetime, @Gap int, @Corrected bit
DECLARE @CTrm DATETIME, @i varchar(8), @LastID varchar(8), @LEff Datetime, @FinalEff DATETIME
SET @i = 0
IF OBJECT_ID('tempdb..#pcsiData') IS NOT NULL
DROP TABLE #pcsiData
IF OBJECT_ID('tempdb..#HoldKey') IS NOT NULL
DROP TABLE #HoldKey
IF OBJECT_ID('tempdb..#HoldKey2') IS NOT NULL
DROP TABLE #HoldKey2
SET DATEFORMAT mdy;
SELECT pcsi_id1 + pcsi_id2 AS pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO #pcsiData FROM pcsi p
WHERE (SELECT COUNT(pcsi_id1 + pcsi_id2) FROM pcsi WHERE pcsi_id1 = p.pcsi_id1) > 1 --AND p.pcsi_prd = 'dgh'
ORDER BY p.pcsi_id1 + p.pcsi_id2 ASC, p.pcsi_eff1 ASC
--SET TRM DATES TO NULL WHERE DATE IS 1977-03-23 00:00:00.000
--(IMPACT XSQL process uses that date in place of null!)
UPDATE #pcsiData
SET pcsi_trm1 = null
WHERE pcsi_trm1 = '1977-03-23 00:00:00.000'
SELECT pcsi_id1 + pcsi_id2 as Pkey, (COUNT(pcsi_id1 + pcsi_id2)) AS DupCount --pcsi_eff1, pcsi_trm1, COUNT(pcsi_id1 + pcsi_id2) AS DupCount
INTO #holdkey
FROM #pcsiData
GROUP BY pcsi_id1 + pcsi_id2
HAVING count(*) = 1
IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[NonDuppcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE NonDuppcsiDataForMalPracticeReport
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO NonDuppcsiDataForMalPracticeReport
FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN(SELECT pkey from #Holdkey)
DELETE FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN (SELECT pkey from #HoldKey)
DROP TABLE #HoldKey
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1, count(*) as NoofDup
INTO #HoldKey2
FROM #pcsiData
GROUP BY pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
HAVING count(*) > 1
SET NOCOUNT OFF
DELETE #pcsiData
FROM #pcsiData, #holdkey2
WHERE #pcsiData.pcsi_id1 = #holdkey2.pcsi_id1
AND #pcsiData.pcsi_id2 = #holdkey2.pcsi_id2
drop table #holdkey2
SET NOCOUNT ON
IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[pcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE pcsiDataForMalPracticeReport
--CREATE TABLE pcsiDataForMalPracticeReport (pcsi_pkey varchar(16) PRIMARY KEY, pcsi_id1 varchar(8), pcsi_id2 varchar(4), pcsi_id3 varchar(4), pcsi_eff1 varchar(8), pcsi_trm1 varchar(8), Corrected bit)
SELECT pcsi_id1 + pcsi_id2 as pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO pcsiDataForMalPracticeReport
FROM #pcsidata p
WHERE pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM #pcsidata WHERE pcsi_id1 = p.pcsi_id1 AND pcsi_id2 = p.pcsi_id2)
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM #pcsiData
--group by pcsi_pkey
Order By pcsi_id1 + pcsi_id2, pcsi_eff1 ASC
OPEN Cur
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = 2
DELETE FROM tmppcsiDatesWithGaps --Clear table used for debugging
WHILE @@FETCH_STATUS = 0
BEGIN --Begin While Loop
IF @pcsi_id1 + @pcsi_id2 = @lplID
BEGIN --If current record is for the same provider location as the last then...
SET @Gap = DATEDIFF(day, @Ltrm, @Eff)
IF @Gap > 2
BEGIN --If there is a gap greater than 1 day...
PRINT ''
PRINT 'GAP between fetch ' + str(@i - 1) + ' and ' + str(@i) + ' (' + @pcsi_id1 + ' ' + @pcsi_id2 + ' ' + '): ' + str(@gap) + ' days! '
PRINT 'Last Trm: ' + CAST(@LTrm AS varchar) + ' Eff: ' + CAST(@eff AS Varchar)
PRINT ''
--IF EXISTS (SELECT * FROM pcsiDataForMalPracticeReport WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2)
--IF @pcsi_id1 + @pcsi_id2 NOT IN (SELECT pcsi_id1 + pcsi_id2 FROM tmppcsiDatesWithGaps)
--BEGIN --Begin if effective date was not already updated
--IF @Leff > @Eff
UPDATE pcsiDataForMalPracticeReport
SET pcsi_eff1 = @Eff-- pcsi_Ltrm = @LTrm
WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_id3 = @lpcsi_id3, pcsi_eff1 = @LEff-- pcsi_Ltrm = @LTrm
--WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--INSERT INTO pcsiDataForMalPracticeReport (pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_id3, pcsi_eff1, pcsi_trm1)
--VALUES (@pcsi_id1 + @pcsi_id2 + @pcsi_id3, @pcsi_id1, @pcsi_id2, @lpcsi_id3, @LEff, @Ltrm)
INSERT INTO tmppcsiDatesWithGaps (pcsi_id1, pcsi_id2, lpcsiid, EffectiveDate) VALUES (@pcsi_id1, @pcsi_id2, @lplid, @Eff)
--END --End if effective date was not already updated
END --End if there is a gap greater than 1 day
END --End if the provider location is different than the last row
--Set current rows data in last rows variables...
SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm
--Get next row of data
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = @i + 1 --increment i
PRINT 'Iteration #' + str(@i) + ' -- ' + @pkey + ' Eff: ' + Cast(@Eff as varchar) + ' ' + ' Trm: ' + cast(@trm as varchar)
END --End While Loop
INSERT INTO pcsiDataForMalPracticeReport
SELECT distinct pcsi_id1 + pcsi_id2, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM NonDuppcsiDataForMalPracticeReport
/***
UPDATE #pcsidata
SET pcsi_trm1 = '20470101'
WHERE pcsi_trm1 is null OR pcsi_trm1 = ''
/**
SELECT p.pcsi_id1, p.pcsi_id2, MAX(p.pcsi_trm1)
INTO #HoldKey2
FROM #pcsidata p
group by p.pcsi_id1, p.pcsi_id2
ORDER BY p.pcsi_id1
UPDATE R SET pcsi_trm1 = I.pcsi_trm1 FROM pcsiDataForMalPracticeReport R
INNER JOIN #HoldKey I
ON r.pcsi_id1 = I.pcsi_id1
AND r.pcsi_id2 = I.pcsi_id2
**/
Print ''
Print ''
Print ''
Print 'SETTING MAX TERM VALUES NOW....(This may take a while)'
Print ''
Print ''
UPDATE pcsiDataForMalPracticeReport
set pcsi_trm1 = jp.MaxTrm
FROM pcsi p JOIN (SELECT pcsi_id1, pcsi_id2, MAX(pcsi_trm1) as maxtrm FROM pcsi p2
--WHERE p2.pcsi_id1 + p2.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2
GROUP BY p2.pcsi_id1, p2.pcsi_id2) jp ON (jp.pcsi_id1 + jp.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2)
DECLARE @NotTermed int
SET @NotTermed = (SELECT COUNT(*) FROM pcsiDataForMalPracticeReport WHERE pcsi_trm1 = '20470101')
PRINT''
PRINT 'Total non-duplicate records not termed: ' + str(@NotTermed)
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_trm1 = NULL
--WHERE pcsi_trm1 = '20470101'
**/
UPDATE pcsiDataForMalPracticeReport
SET pcsi_trm1 = NULL
PRINT ''
PRINT 'STEP TWO.......................'
PRINT 'Preparing the table names...tmpMalPracticeEffectiveDates'
PRINT''
--This step updates tmpMalPracticeEffectiveDates with the desired effective date and most recent termination date
--if there are no current records with a termination date = NULL
TRUNCATE TABLE tmpMalPracticeEffectiveDates
SET NOCOUNT OFF
PRINT 'Inserting new data into tmpMalPracticeEffectiveDates'
INSERT INTO tmpMalPracticeEffectiveDates
(pcsi_id1, pcsi_id2, pcsi_eff1)
SELECT DISTINCT pcsi_id1, pcsi_id2, pcsi_eff1
FROM pcsi p
WHERE p.pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM pcsi p2
WHERE p2.pcsi_id1 = p.pcsi_id1 AND p2.pcsi_id2 = p.pcsi_id2)
ORDER BY pcsi_id1, pcsi_id2
------------------------------
--Set temp bogus date to distinguis which records are current in
--subsequent statement
PRINT 'Setting bogus date to distinguish pcsi records that are not termed'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = '12/21/2049'
WHERE '03/23/1977' IN (SELECT pcsi_trm1 FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
-------------------------------
PRINT 'Setting most recent term date for pcsi records that are not currently active'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = (SELECT MAX(pcsi_trm1) FROM pcsi p
WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1
AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
WHERE tmpMalPracticeEffectiveDates.pcsi_trm1 is NULL
-------------------------------
PRINT 'Setting bogus dates back to NULL'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = NULL
WHERE pcsi_trm1 = '12/21/2049'
-------------------------------
--CORRECT EFFECTIVE DATES WITH GAPS...
PRINT 'Correcting Effective Dates for those records with gaps in credentialing records'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_eff1 = t.EffectiveDate
FROM tmppcsiDatesWithGaps t
WHERE tmpMalPracticeEffectiveDates.pcsi_id1 = t.pcsi_id1
AND tmpMalPracticeEffectiveDates.pcsi_id2 = t.pcsi_id2
----END OF SP---
DECLARE @Diff decimal
SET @ENDTIME = getdate()
PRINT ''
PRINT ''
DECLARE @GapCount int
SET @GapCount = (SELECT COUNT(*) FROM tmppcsiDatesWithGaps)
PRINT 'Total number of non-distinct provider locations: ' + Str(@i) + '.'
PRINT 'Total number of gaps found: ' + Str(@GapCount) + '.'
PRINT 'FINISHED ON: ' + cast(@ENDTIME as varchar)
SET @Diff = CAST(DATEDIFF(second, @StartTime, @EndTIME) AS varchar)
PRINT ''
PRINT 'Time elapsed: ' + str(@Diff) + ' seconds.'
PRINT ' = ' + str(@Diff/60) + ' Minutes!'
END
View 7 Replies
View Related
Jul 20, 2005
As our customers demand that we tighten our IT security in the company,I've been asked to prepare a report quarterly showing, for each user inActive directory, what his effective permissions are for every table inevery database that he has permission for on our SQL Server 2000 server. Isearched a bit for a tool to do this, but all I found was the PERMISSIONS()function for showing effective permissions of the current user. Is thereany way to do it for an arbitrary user, without logging in as them?
View 5 Replies
View Related
Mar 12, 2008
Reading about SQL Injection attacks I came across this example:
SSN="172-32-9999';DROP DATABASE pubs --"
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"
One remedy given was a parameterized query as follows:
Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)
Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.
Thanks
Mike Thomas
View 7 Replies
View Related
Jun 10, 2007
In SSMSE, if I open Server Properties and then Permissions, I can highlight a user or user group and click the 'Effective Permissions' button.
For entries like 'BUILTINUsers' or 'BUILTINAdminsitrators' I get the following error message. I don't understand what it's telling me - can anyone explain?
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot execute as the server principal because the principal "BUILTINUsers" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15406)
Notes: as fara as I know, BUILTINUsers does exist, and I do have permission.
View 1 Replies
View Related
Jul 13, 2006
Hi,
I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.
The caching SQL command now looks like this
select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]
and I've set up the parameters so that the business key is the first parameter and the source date is the second.
I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.
Can anyone suggest how I can improve the performance?
Thanks.
View 2 Replies
View Related
Mar 11, 2014
I would like to filter records with in effective date and expiration date; If there is no record within that range, then check for grace period records ( effective date -30 days and expiration date + 90 days)
Below is the detailed script for sample data...
declare @tab table ( sno int identity, name varchar(100), EFFECTIVE_DATE date, EXPIRATION_DATE date)
insert into @tab (name, EFFECTIVE_DATE , EXPIRATION_DATE )
SELECT 'chandu', GETDATE(), NULL union all
SELECT 'chandu', '2014-02-11 00:00:00' , '2014-03-20 00:00:00' union all
SELECT 'AAA', '2014-01-11 00:00:00' , '2014-05-11 00:00:00' union all
[Code] ...
Output:
/*
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
2chandu2014-02-112014-03-20
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
*/
Expected output:
snonameEFFECTIVE_DATEEXPIRATION_DATE
1chandu2014-03-11NULL
3AAA2014-01-112014-05-11
4CCC2014-04-09NULL
8DDD2014-03-102014-04-11
Looking for query WITH OUT using GROUP BY clause
View 10 Replies
View Related
Oct 16, 2005
I have a procedure that requires picking up the Rate based on Effective Date. This is what I have so far:
SELECT
SHPD.ProductID,
SHPD.ReceivedDate,
SHPD.Shipper,
SHIP.UnitRate
FROM tblShipmentDet SHPD
LEFT OUTER JOIN tblShippers ON SHIP.ProductID = SHPD.ProductID AND SHIP.Shipper = SHPD.Shipper AND Max???(SHIP.Effectivedate) <= SHPD.ReceivedDate
Because there can be more than 1 Shipper record, I would somehow need to pickup the Maximum EffectiveDate in each case. I realize I cannot use the Max aggregate in the JOIN. Not sure where to go from here. On the Mainframe I used a LOOKUP function that would return the correct EffectiveDate. Help would be appreciated.
View 8 Replies
View Related
Aug 6, 2007
Hi everyone,
I am looking for sql monitoring tool. I have found tools from Idera and Quest but the price is a bit expensive.
The tool I am lloking for is able to show historical information such as CPU/memory/disk usage.
Any good suggestion? Thank you in advance.
View 1 Replies
View Related
Jul 7, 2007
Hello All,
I'm hoping someone can help me with this puzzle.
Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....
My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -
SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.
So I decided to try the simplest possible scenario to make it work:
I've created a login DOMAINewEmployee with Windows authentication.
DOMAINewEmployee has been granted access to databasename.
By default, DOMAINewEmployee is a member of Public.
Public has been granted all available permissions on all objects.
ie... grant all on userTables to public
........grant all on sysobjects to public
........grant all on otherSystemTables to public
etc.
Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAINewEmployee, querying -
select * from dbo.sysobjects
via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).
To compare, I then created a login newEmployee2 with SQL Server authentication.
newEmployee2 has been granted access to databasename.
select * from dbo.sysobjects
runs successfully from Query Analyser (as to any queries on user-created tables).
What else is required to grant access to tables from a Windows authenticated login?
( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )
Any help would be appreciated.
Thanks.
Kim.
View 4 Replies
View Related
Nov 29, 2006
I just tried to install SP2 of the Sql Server Express but it wouldn't install.
The message was somthing like:
No effective changes to make.
Am I missing something here?
How do I check my version?
View 3 Replies
View Related