April 12, 2013 at 12:49 pm
Here's the scenario.
Table CustomersA:
RowIDCustomerNameCustomerIDCreationDateCreatedByValidation
1 Vam 00024504/01/2013 Cris
2 Vam 00024504/01/2013 Cris
3 Vam 00024504/02/2013 Cris
4 Vam 00024504/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 !!
April 12, 2013 at 1:00 pm
How are the two tables related?
April 12, 2013 at 1:07 pm
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...
April 12, 2013 at 1:15 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 2:17 pm
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...
April 12, 2013 at 2:26 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 2:49 pm
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..
April 12, 2013 at 2:59 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 4:16 pm
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
April 15, 2013 at 7:48 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2013 at 9:54 am
sean i really do get what you mean...let me try to get you more info...
basically the customer information in the customersA table on any given day can have multiple same customer info..
example : Vam is the customer in the sample table i sent..he has multiple rows created by different agents(CreatedBy) because for some reason he has been approached by the same agent for different products but according to the new rule no matter how many products are sold or introduced to a customer he should be only considered once and also a particular customer row is considered active...for only the time frame described in the Parameters Table...
so THE FIRST vam row in this case is active for one day and if any new rows for the same customer have been created in that particular day they are considered inactive and assigned a valid status as '9(NO)'...
April 15, 2013 at 10:02 am
parikiti (4/15/2013)
sean i really do get what you mean...let me try to get you more info...basically the customer information in the customersA table on any given day can have multiple same customer info..
example : Vam is the customer in the sample table i sent..he has multiple rows created by different agents(CreatedBy) because for some reason he has been approached by the same agent for different products but according to the new rule no matter how many products are sold or introduced to a customer he should be only considered once and also a particular customer row is considered active...for only the time frame described in the Parameters Table...
so THE FIRST vam row in this case is active for one day and if any new rows for the same customer have been created in that particular day they are considered inactive and assigned a valid status as '9(NO)'...
But what is the time frame described in the Parameters table? It has a value of 1. 1 what? 1 hour? day? month? year?
You are basically going to end up looking for gaps and islands of data. See if this article helps point you in the right direction. http://www.sqlservercentral.com/articles/T-SQL/71550/[/url]
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2013 at 10:05 am
1 day....
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply