Help Me with query, please
-
Hi Folks,
I have a situation.
I have a following set of data.
And i'm trying hard to sort this data as below
So, Below is the output data.
1)There is a new column EndDate. It is calculated as,
if there is a one/duplicate record for StartDate, take it as StartDate and then the next nearest date should be the EndDate. and for next row, this EndDate should be as a StartDate and the next nearest for this startdate should be EndDate and it should continue for the ID.
2)For ID 1, i have 4 records. For this ID, If there is one record for FirstName, it should be picked and Null should be eliminated. And same for LastName and even MiddleName.
Please help and would be much appreciated. Thanks
USE [HR4U]
GO
/****** Object: Table [dbo].[temphs] Script Date: 02/06/2015 15:04:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temphs](
[ID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), NULL, N'Mode')
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), N'Mandy', NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x052C0B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0xA5320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0xA5320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x86340B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, N'Mode')
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), N'Mandy', NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (1, CAST(0x4B370B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x48320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x48320B00 AS Date), NULL, N'Gregory')
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x48320B00 AS Date), N'Rick', NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x48320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x48320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0xA5320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0xA5320B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0xC3330B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x3B340B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x3B340B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x6E340B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0x20380B00 AS Date), NULL, NULL)
INSERT [dbo].[temphs] ([ID], [StartDate], [FirstName], [LastName]) VALUES (2, CAST(0xB8380B00 AS Date), NULL, NULL)
-
Hi and welcome to the forums.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article 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/ -
Sample Data posted.. Thanks
-
abendigeri (2/6/2015)
Sample Data posted.. ThanksThis will help. What should be the output for the sample data you posted? Does the table really have no primary key? Also, why is the data so all over the place?
_______________________________________________________________
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/ -
Why in the world is Gender a bit? It should be a char(1) with a constraint of 'M' or 'F'. Which gender is 1 and which one is 0?
I have no idea what you are trying to do here really. Does something like this work?
select ResourceGlobalID
, MAX(EndDate) as EndDate
, MIN(EffectiveDate) as EffectiveDate
, MAX(FirstName)
, max(LastName)
, max(MiddleName)
, Gender --seriously?? Why is gender a bit??
, MAX(FamilySituationName)
, MAX(PrimaryNationality)
, MAX(SecondaryNationality)
, MAX(NickName)
, MAX(MiddleInitial)
, MAX(TitleName)
, MAX(MaidenName)
from temphs
group by ResourceGlobalID, Gender
_______________________________________________________________
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/ -
I appreciate for the attempt.
Apologies as i posted the Wrong SQL. Sorry again...
I Have updated the SQL
The output should have a new column called ENDDate.
EndDate is calculated as,
if there is a one/duplicate record for StartDate, take the date as StartDate and then the next nearest date should be the EndDate. and for next row, this EndDate should be as a StartDate and the next nearest for this startdate should be EndDate and it should continue for the ID.
Please refer to the image and you should get what is expected. Thanks
-
Your data is real disaster here to say the least. Not sure why your data is so sparse but whatever, it just adds another step in the process.
First we need get the actual values to use throughout the results. This can be accomplished by aggregation. Then we have to use those values to normalize all the missing data. Once it is all normalized this is actually pretty simple using Row_Number.
See if this makes sense.
with RealValues as
(
select ID
, MAX(FirstName) as FirstName
, MAX(LastName) as LastName
from temphs
group by ID
)
, NormalizedData as
(
select t.ID
, StartDate
, rv.FirstName
, rv.LastName
, ROW_NUMBER() over (Partition by t.ID order by t.StartDate) as RowNum
from temphs t
join RealValues rv on rv.ID = t.ID
group by t.ID
, t.StartDate
, rv.FirstName
, rv.LastName
)
select nd.ID
, nd.StartDate
, nd2.StartDate as EndDate
, nd.FirstName
, nd.LastName
from NormalizedData nd
left join NormalizedData nd2 on nd.RowNum = nd2.RowNum - 1
and nd.ID = nd2.ID
order by nd.ID
, nd.StartDate
_______________________________________________________________
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/ -
You made my day...Thankyou so much.
Yes i know the data is really bad...But this data is being extracted from some other multiple source and then it has to be transformed for migration process....
And again a big thanks for your excellent query solution....
-
abendigeri (2/6/2015)
You made my day...Thankyou so much.Yes i know the data is really bad...But this data is being extracted from some other multiple source and then it has to be transformed for migration process....
And again a big thanks for your excellent query solution....
You are welcome. Glad that works for you. Make certain you understand what it is doing.
_______________________________________________________________
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply