SQL Queries Doubts

[nenuinthe]

[quote author=Bairagi From Bapatla link=topic=194499.msg2371759#msg2371759 date=1305838928]
ila adagatam em bhagola baa... antha pedha board etti SQL queries ante nu malli db endi aduguthunav....

anyways SQL baaaaaa.....
[/quote]bongu  @3$%...annitlo unnadi SQL antaru....okko dantlo okko command untadi...anduke e database annanu  @3$%

[I am back]

[quote author=Bairagi From Bapatla link=topic=194499.msg2371759#msg2371759 date=1305838928]
ila adagatam em bhagola baa... antha pedha board etti SQL queries ante nu malli db endi aduguthunav....

anyways SQL baaaaaa.....
[/quote]

[img]http://s.chakpak.com/se_images/9483_250_200_true/ram-gopal-varma.jpg[/img]

[Bairagi From Bapatla]

evarikaina answers vachaya vasthe pls share cheyandi....

[ramu]

[quote author=Bairagi From Bapatla link=topic=194499.msg2372115#msg2372115 date=1305846016]
evarikaina answers vachaya vasthe pls share cheyandi....
[/quote] third part ayipoyindha... inka waiting aa....  sCo_^Y sCo_^Y sCo_^Y

[Bairagi From Bapatla]

[quote author=ramu link=topic=194499.msg2372327#msg2372327 date=1305850307]
third part ayipoyindha... inka waiting aa....  sCo_^Y sCo_^Y sCo_^Y
[/quote]

waiting baa...

[ramu]

[quote author=Bairagi From Bapatla link=topic=194499.msg2372330#msg2372330 date=1305850347]
waiting baa...
[/quote] different ways lo cheyyochu.... naku telisindhi vestunna.... chesthuvunte  we can manipulate by trying in diff ways.... but table set up antha vundali...


get the member id who has multiple admissions by querying count(member_id) group by member id on the specified table.
select all the records from the Beacon_CDC_MBRS_HO SP_CLMS whose member id in the above list.

create a column in the table which holds the difference between admission date and discharge date.  calculate the diff between the dates by using DATEDIFF function(chk google).
After every thing is done... select all the records from the table which has diff of dates(days ) between o and 30 and insert them into the final table.

[Bairagi From Bapatla]

[quote author=ramu link=topic=194499.msg2372413#msg2372413 date=1305851783]
different ways lo cheyyochu.... naku telisindhi vestunna.... chesthuvunte  we can manipulate by trying in diff ways.... but table set up antha vundali...


get the member id who has multiple admissions by querying count(member_id) group by member id on the specified table.
select all the records from the Beacon_CDC_MBRS_HO SP_CLMS whose member id in the above list.

create a column in the table which holds the difference between admission date and discharge date.  calculate the diff between the dates by using DATEDIFF function(chk google).
After every thing is done... select all the records from the table which has diff of dates(days ) between o and 30 and insert them into the final table.
[/quote]
baa ikkada konchem weak.... nu konchem query roopam lo vesthe adhi run chesi manipulate nenu chestha....

[ramu]

[quote author=Bairagi From Bapatla link=topic=194499.msg2372477#msg2372477 date=1305852553]
baa ikkada konchem weak.... nu konchem query roopam lo vesthe adhi run chesi manipulate nenu chestha....
[/quote] first  rendu complete ayyaka cheppu... step by step try  cheddam....  S%Hi S%Hi

[Bairagi From Bapatla]

[quote author=ramu link=topic=194499.msg2372604#msg2372604 date=1305854956]
first  rendu complete ayyaka cheppu... step by step try  cheddam....  S%Hi S%Hi
[/quote]
1st rendu ayipoyayi baa...

[FIDEL JAMBA]

[b]3rd one(members with there admission date, discharge date and readmission date who got admitted within 30 days of previous discharge):[/b]I am using a table [b]diabetis[/b](output of your second step)

table config (memberid, adm_dt, dis_dt)
table values
4321 1/1/2005 12:00:00 AM 1/5/2005 12:00:00 AM
4321 2/8/2005 12:00:00 AM 2/10/2005 12:00:00 AM
4321 2/15/2005 12:00:00 AM 2/18/2005 12:00:00 AM
4322 2/11/2005 12:00:00 AM 2/17/2005 12:00:00 AM
4322 4/1/2005 12:00:00 AM 4/5/2005 12:00:00 AM
4323 3/17/2005 12:00:00 AM 3/25/2005 12:00:00 AM
4323 3/30/2005 12:00:00 AM 4/5/2005 12:00:00 AM
4323 4/11/2005 12:00:00 AM 4/14/2005 12:00:00 AM
4323 6/1/2005 12:00:00 AM 6/5/2005 12:00:00 AM
4324 1/1/2005 12:00:00 AM 1/5/2005 12:00:00 AM
4325 1/8/2005 12:00:00 AM 1/13/2005 12:00:00 AM
4326 7/1/2005 12:00:00 AM 7/3/2005 12:00:00 AM
4326 8/11/2005 12:00:00 AM 8/12/2005 12:00:00 AM
4327 7/1/2005 12:00:00 AM 7/5/2005 12:00:00 AM
4327 8/17/2005 12:00:00 AM 8/21/2005 12:00:00 AM
4327 9/7/2005 12:00:00 AM 9/10/2005 12:00:00 AM

Now i create 3 temp tables

-- to hold repeated members
[color=green]create table ##temp1(memberid int, numberofoccurences int)
insert into ##temp1
select memberid,  count(*) 
from dbo.diabetis
group by memberid
having COUNT(*) > 1[/color] values in ##table1

memberid and numberofoccurences
4321 3
4322 2
4323 4
4326 2
4327 3

--to hold details of all the repeated members
[color=green]create table ##temp2(id_clm int identity(1,1),memberid int, adm_dt datetime, dis_dt datetime)
insert ##temp2(memberid, adm_dt, dis_dt)
select dt.memberid, dt.adm_dt, dt.dis_dt
from dbo.diabetis dt join ##temp1 tmp
on dt.memberid = tmp.memberid
values in ##temp2[/color]1 4321 2005-01-01 00:00:00.000 2005-01-05 00:00:00.000
2 4321 2005-02-08 00:00:00.000 2005-02-10 00:00:00.000
3 4321 2005-02-15 00:00:00.000 2005-02-18 00:00:00.000
4 4322 2005-02-11 00:00:00.000 2005-02-17 00:00:00.000
5 4322 2005-04-01 00:00:00.000 2005-04-05 00:00:00.000
6 4323 2005-03-17 00:00:00.000 2005-03-25 00:00:00.000
7 4323 2005-03-30 00:00:00.000 2005-04-05 00:00:00.000
8 4323 2005-04-11 00:00:00.000 2005-04-14 00:00:00.000
9 4323 2005-06-01 00:00:00.000 2005-06-05 00:00:00.000
10 4326 2005-07-01 00:00:00.000 2005-07-03 00:00:00.000
11 4326 2005-08-11 00:00:00.000 2005-08-12 00:00:00.000
12 4327 2005-07-01 00:00:00.000 2005-07-05 00:00:00.000
13 4327 2005-08-17 00:00:00.000 2005-08-21 00:00:00.000
14 4327 2005-09-07 00:00:00.000 2005-09-10 00:00:00.000

--to hold your final result
create table ##temp3(memberid int, adm_dt datetime, dis_dt datetime, re_adm_dt datetime)

LOGIC:
[color=green]DECLARE @memberid int
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT memberid
FROM ##temp1


OPEN c1


FETCH NEXT FROM c1
INTO @memberid


WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @min_id_clm INT
DECLARE @max_id_clm INT
SELECT  @min_id_clm = MIN(id_clm) FROM ##temp2
WHERE memberid = @memberid
SELECT  @max_id_clm = MAX(id_clm) FROM ##temp2
WHERE memberid = @memberid

WHILE (@min_id_clm <= @max_id_clm)
BEGIN
INSERT INTO ##temp3
SELECT t1.memberid, t1.adm_dt, t1.dis_dt, t2.adm_dt AS 'readm_dt'
FROM ##temp2 t1 JOIN ##temp2 t2 ON t1.memberid = t2.memberid
WHERE DATEDIFF(dd,t1.dis_dt, t2.adm_dt) <= 30
AND t1.id_clm = @min_id_clm
AND t2.id_clm = @min_id_clm +1

SET @min_id_clm = @min_id_clm + 1
END

FETCH NEXT FROM c1
INTO @memberid

END

CLOSE c1
DEALLOCATE c1[/color]

final result set from ##temp3(members with there admission date, discharge date and readmission date who got admitted within 30 days of previous discharge)

memberid    adm_dt                                      discharge_dt                          readmit_dt
4321 2005-02-08 00:00:00.000 2005-02-10 00:00:00.000 2005-02-15 00:00:00.000
4323 2005-03-17 00:00:00.000 2005-03-25 00:00:00.000 2005-03-30 00:00:00.000
4323 2005-03-30 00:00:00.000 2005-04-05 00:00:00.000 2005-04-11 00:00:00.000
4327 2005-08-17 00:00:00.000 2005-08-21 00:00:00.000 2005-09-07 00:00:00.000

[Ranga_The_Donga]

kani cursor use chesthe...performance affect avuthundhi ga.... sCo_^Y sCo_^Y sCo_^Y

[FIDEL JAMBA]

yaa he can replace it with a while loop
[quote author=INFATUATION link=topic=194499.msg2373714#msg2373714 date=1305900332]
kani cursor use chesthe...performance affect avuthundhi ga.... sCo_^Y sCo_^Y sCo_^Y
[/quote]

[manoghna]

[quote author=INFATUATION link=topic=194499.msg2373714#msg2373714 date=1305900332]
kani cursor use chesthe...performance affect avuthundhi ga.... sCo_^Y sCo_^Y sCo_^Y
[/quote] @gr33d kani a query alane chestharu anukunta,,  s%H# s%H#

[FIDEL JAMBA]

you can do it many ways..but i like cursors..if it is really slowing down your process replcae it with a loop

[FIDEL JAMBA]

problem workout kaaledhaa sCo_^Y