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

SQL Server 2012 :: Merging Two Tables Using Effective Start And Effective End Date Fields

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

Need Help On Entity-Relationship Design

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

A Basic Design Question

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

Basic Design Question

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

Basic DB Design Question

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

Table Basic Design

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

Basic Database Design

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

Basic Design To Record Questionaires

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

Loading Images In A SQL Server Express Database Table At Design Time Using Visual Basic Net Express Editon

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

How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?

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

Power Pivot :: Use Relationship Not Overriding Conflicting Active Relationship?

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

EFFECTIVE SQL STATEMENT SYNTAX

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

History Using Effective Dating

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

Triggers - Possible To Specify Effective Timeframe?

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

Effective Permission For A Group

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

What Is The Best And Most Effective Way To Find The Most Related Phrases?

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

How 2 Create An Effective Search On NTEXT ?

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

What Is The Most Effective Method Of Record Locking?

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

Tracking History Using Effective Dating

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

Working With Single Effective Date

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

Most Effective Method Of Managing The Data?

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

Effective Query When There Is Lot Of Rows Involved

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

I Need To Find The Most Recent Effective Date

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

Report Effective Permissions For All Users?

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

Why Is Param Query Effective Against SQL Injection?

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

Server Properties - Effective Permissions

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

Lookup Transformation Using Effective Dates

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

Filter Records Within Effective And Expiration Date

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

Pickup Rate Based On Effective Date

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

Good And Cost Effective Ms Sql Monitoring Tool

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

Permissions Not Effective For Windows Authentication Login

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

SQl Server Express SP2 Won't Install - No (effective) Changes To Be Made

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







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