Consolidating Dates From Multiple Rows
Feb 19, 2008
I am having a bit of a problem over here. I am trying to consolidate dates from multiple records into a time line that has no date overlap. I'll give you an example to make things clear.
Let's say I have 3 data records:
RowID BeginDate EndDate Price ($)
----------------------------------------------------
1 01/01/2008 01/10/2008 1.00
2 01/05/2008 01/15/2008 2.00
3 12/20/2007 02/01/2008 1.50
The result I would like to see should look like this:
12/20/2007 - 12/31/2007 the price was 1.50
01/01/2008 - 01/10/2008 the price was 2.50 because row 1 and 3 overlap.
01/05/2008 - 01/15/2008 the price was 3.50 because row 2 and 3 overlap.
01/16/2008 - 02/01/2008 the price was 1.50 because of the row 3.
Any idea on how I can automate generation of this data?
I have a lot of code written for that but I can't get the result I want.
I don't know if someone wants to see my code, I got around 500 lines of it.
I would appreciate any help with this.
Thanks!
View 3 Replies
ADVERTISEMENT
Jul 31, 2006
I have a table (thanks to r937 and others) that list the 3 closest stores to each customer. I now want to take this data and 'group by' the customer so it shows only one record with the 3 closest stores in separate fields within the record. I am still new with SQL so if this is not possible or extremely easy, i apologize. Thanks in advance.
What it looks like now:
Code:
Cust# ClosestStore
customer1 508
customer1 604
customer1 414
customer2 579
customer2 987
customer2 417
customer3 751
customer3 487
customer3 113
What I want it to look like:
Code:
Cust # closestStore1 closestStore2 closestStore3
Customer1 508 604 414
Customer2 579 987 417
Customer3 751 487 113
View 1 Replies
View Related
Dec 4, 2007
I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:
John Smith 1
John Smith 1
John Smith 1
Jane Doh 1
Jane Doh 1
Al Johnson 1
but I need it to return totals like this
John Smith 3
Jane Doh 2
Al Johnson 1
This is the query I am using:
Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID)
from Player INNER JOIN
PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID
join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID
join Action on Treatment.Action_ID = Action.Action_ID
group by Player.First_Name, Player.Last_Name, Action.Employee_Id;
View 4 Replies
View Related
Feb 18, 2007
In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)
Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.
Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?
View 4 Replies
View Related
Jan 4, 2015
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
View 1 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Oct 31, 2013
I have two tables a stock table and a price table and I want to select the correct price for the Stock Date.
Problem is sometimes there is a promotion date in the price table between the live dates so the rows double up. Below is my sql but I get over 17,000 rows when it should be about 16,964.
I will post up the table and data.
SELECT a.[Company]
,a.[ProductID]
,a.[ColourSize]
,a.[StockDate]
,a.[Quantity]
,b.[Ticket Price]
FROM[Stock_Ledger] a
LEFT OUTER JOIN [Product_Prices] b
ON a.[Company] = b.[Company]
AND a.[ProductID] = b.[ProductID]
AND a.[ColourSize] = b.[ColourSize]
AND a.[StockDate] BETWEEN b.[StartDate] AND b.[EndDate]
ORDER BY a.[StockDate],a.[ProductID],a.[ColourSize]
View 7 Replies
View Related
May 10, 2008
Hi,
I have a table that holds pay rate changes with a field for the rate start date and a field for the rate end date. When an employee gets given a new pay rate, the existing rate is given an end date and a new row is added with the rate start date being the day following the end date of the old pay rate.
I need to identify the staff who have had a rate change within the past month, therefore an end date on one row that is within one month of the current month, and a start date on another row that is one day after an end date on a separate row and within one month of the current month.
Is someone able to help me out please?
Thanks
View 8 Replies
View Related
Nov 27, 2007
This is tricky so please read it through
For displaying data on the report I am using the following query
SELECT ReferenceNumber, ActivityID, ActivityTimeStamp, ActivityType, ActivityPerformedBy FROM ActivityDetails
ORDER BY ReferenceNumber, ActivityID
The result set is
Issue Reference #
Activity ID
Activity Date/Time
Activity Type
100819
4521404
11/4/07 2:06 PM
INIT
100819
4521405
11/4/07 2:07 PM
LOG
100819
4521406
11/4/07 2:07 PM
LOG
100819
4521473
11/4/07 2:28 PM
TR
100819
4521501
11/4/07 2:33 PM
WIP
100819
4521839
11/4/07 3:25 PM
RE
100819
4521844
11/4/07 3:27 PM
RE_Method
100819
4522575
11/4/07 8:53 PM
CL
100820
4521412
11/4/07 2:10 PM
INIT
100820
4521419
11/4/07 2:13 PM
ATTACHTDOC
100820
4525856
11/5/07 2:49 PM
ATTACHTDOC
100820
4525859
11/5/07 2:49 PM
LOG
100820
4525869
11/5/07 2:49 PM
CL
100821
4521423
11/4/07 2:14 PM
INIT
100821
4521425
11/4/07 2:14 PM
LOG
100821
4521429
11/4/07 2:14 PM
TR
100821
4521432
11/4/07 2:14 PM
ACK
100821
4522219
11/4/07 4:58 PM
RE
100821
4522221
11/4/07 4:58 PM
RE_Method
100821
4522447
11/4/07 6:51 PM
CL
On the report I have used the grouped by clause on 'Issue Reference #'. I want one more column which would calculate the difference between two consecutive Activity Date/Time of the same reference #.
e.g. Time difference between 4521404 and 4521405, 4521405 and 4521406, 4521406 and 4521473 etc. Please note that the difference between 4521412 and 4522575 will NOT be calculated since they are from different Reference Numbers.
Thanks,
View 2 Replies
View Related
Sep 26, 2006
Hi all,
I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)
...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...
At the moment, I have the following query that I think is on the right track:
€¦
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
€¦
However, this gives me too much data:
€¦
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
€¦
So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I€™d like to do this in a single query.
Thanks for any help you have to offer,
Regards,
Stephen.
View 4 Replies
View Related
Dec 25, 2005
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
View 1 Replies
View Related
May 9, 2007
I'm hoping that someone can help. This is my first time posting and fortunately I can normally find what I need but this time I am stumped.
I have a query that produces a date range. The problem is I want to insert or at lease display the dates between even if they don't show up in table. Here's the problem.
My intial query is
select date_of_call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
order by date_of_call
Results:
2001-09-03 00:00:00.000
2001-09-07 00:00:00.000
2001-09-10 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-14 00:00:00.000
When I do a group by and count it looks like this:
select date_of_call, count(date_of_call) as Count_Date_of_Call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
group by date_of_call
order by date_of_call
Date_Of_Call, Count_Date_of_Call
2001-09-03 00:00:00.0001
2001-09-07 00:00:00.0001
2001-09-10 00:00:00.0001
2001-09-13 00:00:00.0005
2001-09-14 00:00:00.0001
If you notice out of 15 days it only shows 5 days. I am wondering how I can insert the days missing and insert either null or 0 values in the count column so it would look something like this:
Date_Of_Call, Count_Date_of_Call
2001-09-01 00:00:00.0000
2001-09-02 00:00:00.0000
2001-09-03 00:00:00.0001
2001-09-04 00:00:00.0000
2001-09-05 00:00:00.0000
2001-09-06 00:00:00.0000
2001-09-07 00:00:00.0001
2001-09-08 00:00:00.0000
2001-09-09 00:00:00.0000
2001-09-10 00:00:00.0001
2001-09-11 00:00:00.0000
2001-09-12 00:00:00.0000
2001-09-13 00:00:00.0005
2001-09-14 00:00:00.0001
2001-09-15 00:00:00.0000
Any help would be much appreciates.
View 5 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0Â Â Â | Value1Â Â Â | Value2Â Â Â Â | Value3Â Â Â |Â Value4Â |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Jan 16, 2007
I have been looking for the answer to this for a while, but probably haven't found the right place or query. I want to achieve the following:Table 1Table 2
DateShiftDateShiftData
---------------------------------------------------------------------------------
1/1/200711/1/20072Some data
1/1/200721/2/20073Some more data
1/1/20073
1/2/20071
1/1/20072
1/1/20073
and generate:
Table 3
DateShiftData
--------------------------------------------------------
1/1/20071NULL
1/1/20072Some data
1/1/20073NULL
1/2/20071NULL
1/1/20072NULL
1/1/20073Some more data
This way, the information can be displayed and show that some of the entries were not entered for the dates with NULL. Thanks for the help, -Syn
View 2 Replies
View Related
Jan 21, 2014
I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:
EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234
Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.
View 7 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Apr 23, 2008
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
View 3 Replies
View Related
Feb 1, 2008
I need help figuring out how to consolidate duplicate records. For example We have a termed client same info until you get to the number of live because they are different. How can I take a both these lines and have them combined. There are other clients that are dups too. Please help if you can.
25-001610270-00000-00013 WEXFORD HEALTH SOURCES 12/31/200747
25-001610270-00000-00013 WEXFORD HEALTH SOURCES 12/31/200775
View 7 Replies
View Related
Sep 26, 2005
Let's say I have two tables:CREATE TABLE dbo.OldTable(OldID int NOT NULL,OldNote varchar(100) NULL) ON [PRIMARY]GOANDCREATE TABLE dbo.NewTable(NewID int NOT NULL IDENTITY (1, 1),OldID int NULL,ComboNote varchar(255) NULL) ON [PRIMARY]GOALTER TABLE dbo.NewTable ADD CONSTRAINTPK_NewTable PRIMARY KEY CLUSTERED(NewID) ON [PRIMARY]GOOldTable's data looks like this:OldID OldNote----- -------1 aaa2 bbb3 ccc2 ddd4 eeeNewTable's data (which is derived from the OldTable) should look likethis:NewID OldID ComboNote----- ----- ---------1 1 aaa2 2 bbb + char(13) + ddd3 3 ccc4 4 dddHow can I combine the notes from OldTable where two (or more) recordshave the same OldID into the NewTable's ComboNote?
View 6 Replies
View Related
Feb 18, 2015
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
View 9 Replies
View Related
Apr 8, 2015
I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
View 2 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 12, 2015
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
View 1 Replies
View Related
Aug 14, 2015
I have the following  database structure
Stock     Depth41   Depth12   Depth34
AAA Â Â Â Â Â Â 1 Â Â Â Â Â Â Â 2 Â Â Â Â Â Â Â 1
BBB Â Â Â Â Â Â 2 Â Â Â Â Â Â 2 Â Â Â Â Â Â Â 4
How can I show  Each Depth column as seperate row
AAA Â Â Â Â Â 1
AAA Â Â Â Â Â 2
AAA Â Â Â Â Â 1 Â as follows
View 3 Replies
View Related
Sep 26, 2007
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies
View Related
Jun 18, 2014
I have a field called 'LOG_COMMENTS' in a table named T_PRODUCTION_WORK_LOG.
In the 'LOG_COMMENTS' whenever a request is placed on hold comments are added by the application, such as 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open' along with a 'LOG_DATESTAMP' field. A request can go on and off Hold multiple times, how do I determine the days a request is On Hold?
I know I can use the sql function DATEDIFF ( datepart , startdate , enddate ), but how do I account for the possiblity that the request was On Hold more than once? And how would I get LOG_DATESTAMP' times for 'LOG_COMMENTS' that contain 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open''?
View 7 Replies
View Related
Apr 4, 2007
I am about to move 8 SQL 2000 clusters instances residing on 2 seperate MCS clusters (4 instances each with 2 nodes each active/passive) to one single MCS cluster (2 nodes active/passive). The SQL Cluster VMs will have the same DNS names and IPs, however the MCS VM and nodes will have different names.
The planned method for moving the DBs is just to stop all SQL services copy the system and user MDF and LDF files and then restart SQL. From everything I have read this should be fine. However here are my concerns on a cluster platform:
Will the change in node names on the target cluster be a problem when moving the master DBs over from source SQL VMs? Are the cluster nodes listed somewhere in the Master DB?
A couple of the SQL instance VMs are invovled in transactional replication. If all of the SQL files are copied over and the target VM has the same DNS and IP name, will there be a problem with the transactional replication when SQL is restarted?
Thanks,
Chris
View 1 Replies
View Related
Jun 5, 2007
Hello All,
I have some groups set up in a matrix that basically group by transaction names. I am trying to consolidate all but one into the same group. Right now I have the expression of...
Code Snippet
=iif(Fields!TestName.Value="Name Search","Name Search","Logon Function")
this consolidates the aggregate results and group fine but it does not label the groups as expected. "Name Search" comes up as "Name Search" but instead instead of "Logon Function", It displays the rolled up group as the name of the fist group field. Is there a way to make an alias inside of an expression?
View 1 Replies
View Related
Apr 21, 2015
I am trying to find an easy way to create multiple of just two date in a single sql statement.
E.G.
A statement using the parameters
@StartDate = '2015-01-01'
@EndDate = '2015-01-05'
Ends up with rows:
'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'
What would be the best way to do this ?
View 3 Replies
View Related
Dec 2, 2015
This question is extension from the topic Updating table Rows with overlapping dates:Â [URL] .....
I am actually having a table as following:
Table Name: PromotionList
Note that the NULL in endDate means no end date or infinite end date.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
Flag = 1, success merge row. Flag = 2, fail row
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.
Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.
Original table:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL
Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL
Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).
By taking the min date and max date of the same group, the following table should be achieve after the table is updated.
Final outcome:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL
View 2 Replies
View Related
