Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Assign Validation Codes Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 11:56 AM
Points: 10, Visits: 155
Here's the scenario.

Table CustomersA:

RowID CustomerName CustomerID CreationDate CreatedBy Validation
1 Vam 000245 04/01/2013 Cris
2 Vam 000245 04/01/2013 Cris
3 Vam 000245 04/02/2013 Cris
4 Vam 000245 04/01/2013 ted


Table ParametersB:

Name Lock_Count
Customer 1

In this case i have Table A with Customer Data and another Table B which has the validity range for a customer. now i need to compare Table A with Itself,If more than one customer is present with the same name,created by the smae person then i compare the dates of those customers and if the difference between the two dates is less than Lock_Count then only the first row is credited as valid and the rest as invalid and so on...

in the present example the result should as

RowID CustomerName CustomerID CreationDate CreatedBy Validation
1 Vam 000245 04/01/2013 Cris Yes(as this was created first)
2 Vam 000245 04/01/2013 cris No(a row exists for time frame)
3 Vam 000245 04/02/2013 cris Yes(first row for new time frame)
4 Vam 000245 04/01/2013 ted No (Already exists and different created by)


Scripts for Creating Test Tables:

IF OBJECT_ID('dbo.CustomerA','U') IS NOT NULL
DROP TABLE dbo.CustomerA

CREATE TABLE [dbo].[CustomerA](
[RowID] [int] NULL,
[CustomerName] [varchar](100) NULL,
[CustomerID] [varchar](100) NULL,
[CreationDate] [datetime] NULL,
[CreatedBy] [varchar](100) NULL,
[Valid] [varchar](30) NULL
) ON [PRIMARY]

GO

IF OBJECT_ID('dbo.ParametersB','U') IS NOT NULL
DROP TABLE dbo.ParametersB

CREATE TABLE [dbo].[ParametersB](
[ParameterName] [varchar](100) NULL,
[Lock_Count] [int] NULL
) ON [PRIMARY]

GO

Data Insert Script for Table CustomersA:

insert into dbo.CustomerA
(RowID, CustomerName, CustomerID, CreationDate, CreatedBy, Valid) VALUES
('1', 'Vam', '000245', '4/1/2013 12:00:00 AM', 'cris', '{null}'),
('2', 'Vam', '000245', '4/1/2013 12:00:00 AM', 'cris', '{null}'),
('3', 'Vam', '000245', '4/2/2013 12:00:00 AM', 'cris', '{null}'),
('4', 'Vam', '000245', '4/1/2013 12:00:00 AM', 'ted', '{null}')

Data For ParametersB:

Insert Into dbo.ParametersB
(ParameterName, Lock_Count) VALUES
('Customer', '1')

Thanks For all the Help !!
Post #1441849
Posted Friday, April 12, 2013 1:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
How are the two tables related?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441862
Posted Friday, April 12, 2013 1:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 11:56 AM
Points: 10, Visits: 155
The two Tables are on the same server.Table B is used as Dynamic storage to change values depending on the requirement..i can create a variable in the stored procedure and assign the lock value to that variable so that i can use it in the comparison...
Post #1441867
Posted Friday, April 12, 2013 1:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
parikiti (4/12/2013)
The two Tables are on the same server.Table B is used as Dynamic storage to change values depending on the requirement..i can create a variable in the stored procedure and assign the lock value to that variable so that i can use it in the comparison...


Can you try to explain more clearly what is going on here? We don't know your system or the requirements and your description is not explaining this very well.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1441871
Posted Friday, April 12, 2013 2:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 11:56 AM
Points: 10, Visits: 155
Let me try to explain what's going on then..

There is a Table Customers(Table A) which will get Customers Data as any new prospective Customers join the Product list. A single customer can Have multiple entries as its possible that two product Employees can refer the same customer and that too on the same date. After the data is in the table i need to create a stored procedure where it loops through customer rows and whenever two or more customers with the same name are present then it needs to compare their CreatedBy and CreationDate Columns
if Case 1:
CreatedBy are Different then the row which was created first based on CreationDate is Considered Valid .
if Case 2:
CreatedBy are same,then CreationDate are compared and
if Case 2a:
the Datediff between the two CreationDate is greater than Customer_lock(Table B) Value Both are considered as Valid ,in other sense the first customer row will not be updated.
if Case 2b:
the Datediff between the two CreationDates is equal to or less than Customer_lock(Table B) then only the one with Min(CreationDate) is Valid and the other row is invalid.

I hope you get the idea...
Post #1441923
Posted Friday, April 12, 2013 2:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
That is somewhat more clear. What would be best at this point is to post ddl (create table scripts), sample data (insert statements) and sample output based on your sample data. I have a pretty good idea of what you want but some of it is still not very clear. I think with something to work with we can figure this out fairly easily. If you want details about how to best post this type of information please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1441925
Posted Friday, April 12, 2013 2:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 11:56 AM
Points: 10, Visits: 155
i have updated the sample scripts along with the insert statements in my original post...i was just reading guidelines for posting a question,hope that helps..
Post #1441933
Posted Friday, April 12, 2013 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
parikiti (4/12/2013)
i have updated the sample scripts along with the insert statements...i was just reading guidelines for posting a question,hope that helps..


We are getting there.

The sample data is excellent. That helps a lot.

Now we have your rules:


if Case 1:
CreatedBy are Different then the row which was created first based on CreationDate is Considered Valid .
if Case 2:
CreatedBy are same,then CreationDate are compared and
if Case 2a:
the Datediff between the two CreationDate is greater than Customer_lock(Table B) Value Both are considered as Valid ,in other sense the first customer row will not be updated.
if Case 2b:
the Datediff between the two CreationDates is equal to or less than Customer_lock(Table B) then only the one with Min(CreationDate) is Valid and the other row is invalid.


Case 1 - this is pretty clear. However in your sample data this is impossible because there is no way based on creation data to tell which row was created first. You have two users and also two rows all with the same creation date. Which one is "first"?

case 2 - I don't understand what you mean by any of this. If CreatedBy are the same? The same as what?

I have absolutely no idea what ParametersB has to do with anything here.

We need a LOT of clarification on the rules. Also, it would help if you could post what you expect CustomerA to look like after this process is done.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1441935
Posted Friday, April 12, 2013 4:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 11:56 AM
Points: 10, Visits: 155
sorry for the late reply...
Case 1:the first column can be considered as first one and also there is a time assigned when the that particular row was created like 04/01/2013 6:30:00AM ...something along those lines

Case 2: i emant when we are comparing two rows with the same customer name and also those rows if they have same createdBy like in the sample Cris is repeated as the one who created that row...
example :
RowID CustomerName CustomerID CreationDate CreatedBy Validation
row 1 Vam 000245 04/01/2013 6:30:00am Cris
row 2 Vam 000245 04/01/2013 7:45:00am Cris

These two have the same customerName and same CreatedBy then the difference between the CreationDate should be greater Than the Lock_Count Value which we get from Table ParametersB.

After the process is done The CustomerA should like this

RowID CustomerName CustomerID CreationDate CreatedBy Validation
1 Vam 000245 04/01/2013 Cris 8(as this was created first)

2 Vam 000245 04/01/2013 cris 9(a row exists for time frame)

3 Vam 000245 04/02/2013 cris 8(first row for new time frame)

4 Vam 000245 04/01/2013 ted 9(Already exists and different created by)

8-Means that row is valid;
9-Means that row is invalid


hope this helps
Post #1441956
Posted Monday, April 15, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
parikiti (4/12/2013)
sorry for the late reply...
Case 1:the first column can be considered as first one and also there is a time assigned when the that particular row was created like 04/01/2013 6:30:00AM ...something along those lines

Case 2: i emant when we are comparing two rows with the same customer name and also those rows if they have same createdBy like in the sample Cris is repeated as the one who created that row...
example :
RowID CustomerName CustomerID CreationDate CreatedBy Validation
row 1 Vam 000245 04/01/2013 6:30:00am Cris
row 2 Vam 000245 04/01/2013 7:45:00am Cris

These two have the same customerName and same CreatedBy then the difference between the CreationDate should be greater Than the Lock_Count Value which we get from Table ParametersB.

After the process is done The CustomerA should like this

RowID CustomerName CustomerID CreationDate CreatedBy Validation
1 Vam 000245 04/01/2013 Cris 8(as this was created first)

2 Vam 000245 04/01/2013 cris 9(a row exists for time frame)

3 Vam 000245 04/02/2013 cris 8(first row for new time frame)

4 Vam 000245 04/01/2013 ted 9(Already exists and different created by)

8-Means that row is valid;
9-Means that row is invalid


hope this helps


I don't why but I will try one more time. This is totally unclear.


These two have the same customerName and same CreatedBy then the difference between the CreationDate should be greater Than the Lock_Count Value which we get from Table ParametersB.


This makes no sense. The difference between the CreationDates? I get there are two rows and the difference needs to be greater than 1. But what are we using to calculate the difference? DATEDIFF? What unit of time are we using? I am totally unable to figure out what you are asking. Put yourself in my shoes and ask yourself if you could answer the question based on what is posted.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442303
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse