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