May 7, 2010 at 5:28 am
dear sql guru,
kindly look into my problem i have a table as mentioned in sample code below
it code gives the missing date and the values for the last data is present.
EG:
1232010-05-01 00:00:00.000100
1232010-05-02 00:00:00.000100
1232010-05-03 00:00:00.000100
1232010-05-04 00:00:00.000100
1232010-05-05 00:00:00.000100
1232010-05-06 00:00:00.000100
1232010-05-07 00:00:00.000100
1232010-05-08 00:00:00.000100
1242010-04-12 00:00:00.00095
1242010-04-13 00:00:00.00095
1242010-04-14 00:00:00.00095
1242010-04-15 00:00:00.00095
1242010-04-16 00:00:00.00095
1242010-04-17 00:00:00.00095
1242010-04-18 00:00:00.00095
1242010-04-19 00:00:00.00095
1242010-04-20 00:00:00.00095
1242010-04-21 00:00:00.00095
1242010-04-22 00:00:00.00095
1252010-04-23 00:00:00.000120
1252010-04-24 00:00:00.000120
1252010-04-25 00:00:00.000120
1252010-04-26 00:00:00.000120
1252010-04-27 00:00:00.000120
1252010-04-28 00:00:00.000120
1252010-04-29 00:00:00.000120
1252010-04-30 00:00:00.000120
this output is wrong as it is not considering the column "code"
my actual should be
EG: code 125 as shown in sample code it should retrive data from date 2010-04-23 - till date as amount "120"
2) for code 124 as below
124,'2010-04-12', 95
data should retrieve from 2010-04-12 - till date
DECLARE @Table TABLE
(
code int,
DateTimesDATETIME,
AmountINT
)
INSERT @Table VALUES(123,'2010-05-01', 100)
INSERT @Table VALUES(124,'2010-04-12', 95)
INSERT @Table VALUES(125,'2010-04-23', 120)
;WITH Dates(code,DateTimes, Amount)AS
(
SELECT code,DateTimes, Amount FROM @Table
UNION ALL
SELECT code,
DateTimes +
CASE
WHEN YEAR(DateTimes)=12 THEN 100-11
ELSE 1
END
,Amount
FROM Dates
WHERE
DateTimes < GETDATE()
AND DateTimes +
CASE
WHEN YEAR(DateTimes)=12 THEN 100-11
ELSE 1
END NOT IN (SELECT DateTimes FROM @Table)
)
SELECT *
FROM Dates
ORDER BY code
OPTION (MAXRECURSION 0);
thanks in advance and helping hand is required
May 7, 2010 at 5:45 am
You gave us what you didn't want instead of what you wanted.
But still, i hope this will give you what you want
; WITH cte_Dates AS
(
SELECTcode, DateTimes, Amount
FROM@Table
UNION ALL
SELECTcode, DateTimes + 1, Amount
FROMcte_Dates
WHEREDateTimes + 1 < GETDATE ()
)
SELECT*
FROMcte_Dates
ORDER BY code
OPTION (MAXRECURSION 0);
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2010 at 6:25 am
No its giving me wrong out put
as its giving me repeted data for client code 124 code AD
DECLARE @Table TABLE
(
code int,
ast_cls_cd varchar(25),
DateTimesDATETIME,
AmountINT
)
INSERT @Table VALUES(123,'AB', '2010-05-01', 100)
INSERT @Table VALUES(123,'AC', '2010-05-01', 80)
INSERT @Table VALUES(124,'AD', '2010-04-12', 95)
INSERT @Table VALUES(124,'AD', '2010-04-23', 45)
INSERT @Table VALUES(125,'AS', '2010-04-23', 120)
;WITH Dates(code,ast_cls_cd, DateTimes, Amount)AS
(
SELECT code,ast_cls_cd, DateTimes, Amount FROM @Table
UNION ALL
SELECT code,ast_cls_cd,
DateTimes +
CASE
WHEN MONTH(DateTimes)=12 THEN 100-11
ELSE 1
END
,Amount
FROM Dates
WHERE
DateTimes < GETDATE()
-- AND DateTimes +
-- CASE
-- WHEN YEAR(DateTimes)=12 THEN 100-11
-- ELSE 1
-- END NOT IN (SELECT DateTimes FROM @Table)
)
SELECT *
FROM Dates
ORDER BY code, ast_cls_cd,DateTimes, Amount
OPTION (MAXRECURSION 0);
May 7, 2010 at 6:26 am
and i need latest date data present client wise and asset wise
May 7, 2010 at 6:31 am
I want output like mentioned in blelow code but dont want use co-related queris
drop table #series
create table #series
(
ids numeric(18,0) identity(1,1),
cln_id numeric(18,0),
ast_cls varchar(25),
val numeric(18,2),
val_dt datetime
)
insert into #series(cln_id,ast_cls,val,val_dt)
select * from
(
select 123 as cln_id ,'AB' as ast_cls, 3435 as val,'2010-01-01' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-02' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-03' as dat
union
select 123 as cln_id,'AC', 677 as val,'2010-01-04' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-05' as dat
union
select 123 as cln_id,'AC', null as val,'2010-01-06' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-07' as dat
union
select 123 as cln_id,'BD', 700 as val,'2010-01-08' as dat
) e order by dat
--update b set b.val= a.bnmk_rt
select *
from #series b,
(
SELECT (select min(a.val)
from #series a
where a.ast_cls = bnmk.ast_cls
and a.val is not null
and a.val_dt < bnmk.val_dt) bnmk_rt, *
FROM #series bnmk
where bnmk.val is null
) a
where a.ast_cls = b.ast_cls
and a.cln_id = b.cln_id
and a.ids= b.ids
as i want update blank amount field with previous available amount field.
May 7, 2010 at 7:15 am
the amount should be updated with previous amount present for client
May 8, 2010 at 4:57 am
dear team kindly revert
May 8, 2010 at 10:16 am
Dear Sir,
Please provide me a way to achieve this.
I have a table which have 4 field.
Field may be client id, asset code, amount field and date field
Sample data is given below
create table #series
(
cln_id numeric(18,0),
ast_cls varchar(25),
val numeric(18,2),
val_dt datetime
)
insert into #series(cln_id,ast_cls,val,val_dt)
select * from
(
select 123 as cln_id ,'AB' as ast_cls, 200 as val,'2009-12-29' as dat
UNION
select 123 as cln_id ,'AB' as ast_cls, NULL as val,'2009-12-30' as dat
UNION
select 123 as cln_id ,'AB' as ast_cls, NULL as val,'2009-12-31' as dat
UNION
select 123 as cln_id ,'AB' as ast_cls, 400 as val,'2010-01-01' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-02' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-03' as dat
union
select 123 as cln_id,'AB', 677 as val,'2010-01-04' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-05' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-06' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-07' as dat
union
select 123 as cln_id,'BD', 700 as val,'2010-01-08' as dat
) e order by dat
drop table #min_dtls
select cln_id, ast_cls, val_dt, min(val) as val
into #min_dtls
from #series
where val is not null
group by cln_id, ast_cls, val_dt
select * from #series
update #series
set val = oflow.val
-- select oflow.val, nv.*, oflow.*
from #series nv
inner join
(
--retrieve folio level corpus outflow details
select nv.cln_id, nv.ast_cls, nv.val_dt, max(hdr.val_dt) as val_dt1,
min(hdr.val) as val
from #series nv
left join #min_dtls hdr
on nv.cln_id = hdr.cln_id
and nv.ast_cls = hdr.ast_cls
and hdr.val_dt <= nv.val_dt
group by nv.cln_id, nv.ast_cls, nv.val_dt
) oflow on oflow.cln_id = nv.cln_id
and oflow.ast_cls = nv.ast_cls
and oflow.val_dt = nv.val_dt
and oflow.val_dt1 <> nv.val_dt
I have Null value in mat amount field for certain amount field, now i want to update the amount field, with the amount which is just previous for the all null amount for the next occurence.
Thankx
May 8, 2010 at 1:05 pm
Simple (now that we have some readily consumable data ;-)). Code with your data setup follows. Read the following article and pay attention to the rules because if you don't, BOOM!
http://www.sqlservercentral.com/articles/T-SQL/68467/
drop table #series
--===== Creation of data in #Series remains the same
create table #series
(
cln_id numeric(18,0),
ast_cls varchar(25),
val numeric(18,2),
val_dt datetime
)
insert into #series(cln_id,ast_cls,val,val_dt)
select * from
(
select 123 as cln_id ,'AB' as ast_cls, 200 as val,'2009-12-29' as dat
UNION
select 123 as cln_id ,'AB' as ast_cls, NULL as val,'2009-12-30' as dat
UNION
select 123 as cln_id ,'AB' as ast_cls, NULL as val,'2009-12-31' as dat
UNION
select 123 as cln_id ,'AB' as ast_cls, 400 as val,'2010-01-01' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-02' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-03' as dat
union
select 123 as cln_id,'AB', 677 as val,'2010-01-04' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-05' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-06' as dat
union
select 123 as cln_id,'AB', null as val,'2010-01-07' as dat
union
select 123 as cln_id,'BD', 700 as val,'2010-01-08' as dat
) e order by dat
drop table #min_dtls
select cln_id, ast_cls, val_dt, min(val) as val
into #min_dtls
from #series
--where val is not null --REMOVED THIS
group by cln_id, ast_cls, val_dt
--select * from #min_dtls
--===== Add this required clustered index
CREATE CLUSTERED INDEX IX_#min_dtls_CoverUpdate
ON #min_dtls (cln_id, ast_cls, val_dt)
--===== Declare some necessary working variables
DECLARE @PrevCLN INT,
@PrevAST VARCHAR(25),
@PrevVal NUMERIC(18,2)
--===== "Quirky" Update does the magic "data smear"
UPDATE #min_dtls
SET @PrevVal = val = CASE
WHEN cln_id = @PrevCLN
AND ast_cls = @PrevAST
AND val IS NULL
THEN @PrevVal
WHEN cln_id <> @PrevCLN
OR ast_cls <> @PrevAST
OR val IS NOT NULL
THEN val
END,
@PrevCLN = cln_id,
@PrevAST = ast_cls
FROM #min_dtls WITH(TABLOCKX)
OPTION (MAXDOP 1)
--===== Show the results
SELECT * FROM #min_dtls ORDER BY cln_id, ast_cls, val_dt
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2010 at 12:59 am
Thankx Jeff Sir, You made my job very easy with Quirky Update. Its a very nice artical.
Thankx once again for your great help as its giving me a lots of headache.
But there is a small problem if i am adding new column in my table and want to update data along with this column then its not working.
drop table #series
--===== Creation of data in #Series remains the same
create table #series
(
cln_id numeric(18,0),
ast_cls varchar(25),
bnmk_cd varchar(25),
val numeric(18,2),
val_dt datetime
)
insert into #series(cln_id,ast_cls,val,val_dt,bnmk_cd)
select * from
(
select 123 as cln_id ,'AB' as ast_cls, 100 as val,'2009-12-29' as dat, 4 bnmk_cd
UNION
select 123 as cln_id ,'AB' as ast_cls, NULL as val,'2009-12-30' as dat, 4
UNION
select 123 as cln_id ,'AB' as ast_cls, NULL as val,'2009-12-31' as dat, 2
UNION
select 123 as cln_id ,'AB' as ast_cls, 400 as val,'2010-01-01' as dat , 1
union
select 123 as cln_id,'AB', null as val,'2010-01-02' as dat , 1
union
select 123 as cln_id,'AB', null as val,'2010-01-03' as dat , 1
union
select 123 as cln_id,'AB', 677 as val,'2010-01-04' as dat , 1
union
select 123 as cln_id,'AB', null as val,'2010-01-05' as dat , 1
union
select 123 as cln_id,'AB', null as val,'2010-01-06' as dat ,4
union
select 123 as cln_id,'AB', null as val,'2010-01-07' as dat , 1
union
select 123 as cln_id,'BD', 700 as val,'2010-01-08' as dat ,2
) e order by dat
drop table #min_dtls
select cln_id, ast_cls, val_dt, bnmk_cd , min(val) as val
into #min_dtls
from #series
--where val is not null --REMOVED THIS
group by cln_id, ast_cls, val_dt , bnmk_cd
--select * from #series
--===== Add this required clustered index
CREATE CLUSTERED INDEX IX_#min_dtls_CoverUpdate
ON #min_dtls (cln_id, ast_cls, val_dt)
--===== Declare some necessary working variables
DECLARE @PrevCLN INT,
@PrevAST VARCHAR(25),
@PrevVal NUMERIC(18,2) ,
@PreBench VARCHAR(25)
--===== "Quirky" Update does the magic "data smear"
UPDATE #min_dtls
SET @PrevVal = val = CASE
WHEN cln_id = @PrevCLN
AND ast_cls = @PrevAST
AND bnmk_cd = @PreBench
AND val IS NULL
THEN @PrevVal
WHEN cln_id <> @PrevCLN
OR ast_cls <> @PrevAST OR bnmk_cd <> @PreBench
OR val IS NOT NULL
THEN val
END,
@PrevCLN = cln_id,
@PrevAST = ast_cls,
@PreBench = bnmk_cd
FROM #min_dtls WITH(TABLOCKX)
OPTION (MAXDOP 1)
--===== Show the results
SELECT * FROM #min_dtls ORDER BY cln_id, ast_cls, val_dt
--select * from #min_dtls
I will not able to update client 123 asset ab on date AB2010-01-06.
Kindly suggest
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy