August 9, 2010 at 8:46 am
Hi all,
I have a requirement to update a table that will use the existing dates and derive two new dates for every row - that is start date and end date....
I have this query for input data and I have the desired output in the box,
Actually the requirement is that for every myseq column there is an year and all the months(but some months are repeating like 10 , 12 here in the example, in that case i take min date as my startdate and end date)
STARTDATE is normally same as CALdate (but if TWO DATES I take the MINIMUM one), but ENDDATE is need to be derived by using same sequence , same year and NEXT months CALDATE( mini(CALDATE) if two dates are there)
CREATE TABLE [dbo].[xyz1](
[caldate] datetime NULL,
[myyear] [numeric](4, 0) NULL,
[mymonth] [numeric](2, 0) NULL,
[myseq] [int] NULL,
STARTDATE datetime,
ENDDATE datetime
)
INSERT INTO xyz1 ( caldate,myyear,mymonth,myseq) VALUES ( 'Oct 1 1999 ', 1999, 10, 2)
INSERT INTO xyz1 ( caldate,myyear,mymonth,myseq) VALUES ( 'Apr 7 1999 ', 1999, 4, 3)
INSERT INTO xyz1 ( caldate,myyear,mymonth,myseq) VALUES ( 'May 6 1999 ', 1999, 5, 3)
INSERT INTO xyz1 ( caldate,myyear,mymonth,myseq) VALUES ( 'Jun 5 1999 ', 1999, 6, 3)
INSERT INTO xyz1 ( caldate,myyear,mymonth,myseq) VALUES ( 'Jul 7 1999 ', 1999, 7, 3)
INSERT INTO xyz1 ( caldate,myyear,mymonth,myseq) VALUES ( 'Dec 7 1999 ', 1999, 12, 3)
select * from xyz1
order by myyear,myseq,mymonth
Here is the desired output:
Here I am showing a different output table, but actually the same table need to be updated...adding two more columns
output table:
CREATE TABLE [dbo].[target_xyz](
[caldate] datetime NULL,
[myyear] [numeric](4, 0) NULL,
[mymonth] [numeric](2, 0) NULL,
[myseq] [int] NULL,
STARTDATE datetime,
ENDDATE datetime
)
CALDTE MYYEAR MYMONTH MYSEQ STARTDATE ENDDATE
Jan 5 1999 199912 Jan 5 1999 Feb 4 1999
Feb 4 1999 199922 Feb 4 1999 Mar 6 1999
Mar 6 1999 199932 Mar 6 1999 Apr 6 1999
Apr 6 1999 199942 Apr 6 1999 May 5 1999
May 5 1999 199952 May 5 1999 Jun 4 1999
Jun 4 1999 199962 Jun 4 1999 Jul 6 1999
Jul 6 1999 199972 Jul 6 1999 Aug 4 1999
Aug 4 1999 199982 Aug 4 1999 Sep 2 1999
Sep 2 1999 199992 Sep 2 1999 Oct 1 1999
Oct 2 1999 1999102 Oct 1 1999 Nov 2 1999
Oct 1 1999 1999102 Oct 1 1999 Nov 2 1999
any help on this update...
thanks
Thanks [/font]
August 9, 2010 at 12:40 pm
You've been around here enough that you should start posting what sort of solutions you're coming up with on your own, even if they're incomplete. You'll learn a lot more if you're trying different things than if you're just getting the answers from others.
;WITH cteXYZ AS
(SELECT ver = ROW_NUMBER() OVER(PARTITION BY myyear, mymonth ORDER BY caldate ASC),
caldate,
myyear,
mymonth
FROM #xyz)
,cteBase AS
(SELECT RN = ROW_NUMBER() OVER(ORDER BY caldate),
caldate,
myyear,
mymonth
FROM cteXYZ
WHERE ver = 1)
,cteFinal AS
(SELECT cc.myyear,
cc.mymonth,
cc.caldate AS 'beginDate',
cn.caldate AS 'endDate'
FROM cteBase cc --cteBase current
LEFT JOIN cteBase cn --cteBase next
ON cc.RN = cn.RN - 1)
UPDATE #xyz
SET STARTDATE = c.beginDate,
ENDDATE = c.endDate
FROM #xyz x
JOIN cteFinal c
ON c.myyear = x.myyear
AND c.mymonth = x.mymonth
SELECT *
FROM #xyz
ORDER BY caldate
*edit: typo
August 9, 2010 at 1:51 pm
Thanks a lot Bt,
I will try and post my approach even if incomplete with my questions...
Actually, I am not an expert in T-SQL, can think of doing in SSIS but in T-SQL always find it a liitle more difficult.....
Your query is working fine, but some results are not showing correct..
Your Query helped me to find results, I changed it a little as myseq should also be in the order by
I added two more CTE's to this code..
here is final query showing correct results...
--select * into #xyz from dbo.xyz1
--update #
;WITH cteXYZ AS
(SELECT ver = ROW_NUMBER() OVER(PARTITION BY myyear, mymonth,myseq ORDER BY caldate ASC),
caldate,myseq,
myyear,
mymonth
FROM #xyz)
,cteBase AS (
SELECT RN = ROW_NUMBER() OVER(ORDER BY myseq,caldate),
caldate,
myyear,
mymonth,myseq
FROM cteXYZ
WHERE ver = 1
)
,cteBase2 AS (
SELECT RN = ROW_NUMBER() OVER(ORDER BY myseq,caldate),
caldate,
myyear,
mymonth,myseq
FROM cteXYZ
WHERE ver = 1
)
,cteBase3 AS (
SELECT RN = RN -1,
caldate,
myyear,
mymonth,myseq
FROM cteBase2
)
,cteFinal AS(
SELECT cc.myyear,cc.myseq,
cc.mymonth,
cc.caldate AS 'beginDate',
cn.caldate AS 'endDate'
FROM cteBase cc --cteXYZ current
left JOIN cteBase3 cn --cteXYZ next
ON cc.RN = cn.RN
)
UPDATE #xyz
SET STARTDATE = c.beginDate,
ENDDATE = c.endDate
FROM #xyz x
JOIN cteFinal c
ON c.myyear = x.myyear
AND c.mymonth = x.mymonth
AND c.myseq= x.myseq
SELECT *
FROM #xyz
order by myYEAR,myseq,mymonth
Thanks again
Thanks [/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply