how to join 2 tables sql using stored procedure?

Question

0.00/5 (No votes)

See more:

SQL

Alter Procedure Forms9(@mdvn int,@mphc varchar(4),@mhsc varchar(2), @fdate date, @tdate date)
as*
BEGIN 
declare @fedddate date
declare @tedddate date
declare @v1date date
declare @v2date date
declare @v3date date
set @v1date=dateadd(dd,-190,@fdate)
set @v2date=dateadd(dd,-92,@fdate)
set @v3date=dateadd(dd,-45,@fdate)
set @fedddate=dateadd(dd,-320,@fdate)
set @tedddate=dateadd(dd,30,@tdate)

SELECT DVN_CD,PHC_CD,HSC_CD,SUM(REGDT) AS REGDT,SUM(trims1)AS Trim1,sum(trims2) AS Trim2,
sum(ABORT) AS ABORT,sum(MTB) AS MTB,sum(LB)AS LB,sum(SB) AS SB,SUM(ANC1) as ANC1,SUM(ANC2) as ANC2,SUM(ANC3) as ANC3,
SUM(TT1) as TT1,sum(TTB2) as TT2,sum(IFA1) as IFA,sum(BP) as BP,sum(HB1) as HB FROM
(
  SELECT a.DVN_CD,a.PHC_CD,a.HSC_CD,
          CASE WHEN AN_Reg_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS REGDT,
          CASE WHEN DATEDIFF(DD,lmp,an_reg_date)<=90 AND AN_Reg_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS trims1,
          CASE WHEN DATEDIFF(DD,lmp,an_reg_date)>90  AND AN_Reg_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS trims2,
          CASE WHEN Preg_outcome=1 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS ABORT,
          CASE WHEN Preg_outcome=2 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS MTB,
          CASE WHEN Preg_outcome=3 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS LB,
          CASE WHEN Preg_outcome=4 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS SB,
          case when visit_date between @v1date and @tdate then 1 else 0 end as ANC1,
		  case when visit_date between @v2date and @tdate then 1 else 0 end as ANC2,
		  case when visit_date between @v3date and @tdate then 1 else 0 end as ANC3, 	 
		  case when TTB=1 and TTDate between @fdate and @tdate then 1 else 0 end as TT1,
		  case when TTB>=2 and TTDate between @fdate and @tdate then 1 else 0 end as TTB2,
		  case when IFA=100 and IFADate between @fdate and @tdate then 1 else 0  end as IFA1, 	 
		  case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0  end as BP,
		  case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0 end as HB1
      
   FROM Preg_cohort3 a join ANVisits3 b on a.DVN_CD=b.DVN_CD and a.PHC_CD=b.PHC_CD and a.HSC_CD=b.HSC_CD and a.DVN_CD=@mdvn and a.PHC_CD=@mphc and a.HSC_CD=@mhsc 
)a group by a.dvn_cd,a.phc_cd,a.hsc_cd
RETURN 
END

Call:

SQL

exec Forms9 42,'8301','01','2012/01/01','2012/01/31'

I got the output result ... But that result is not correct.. I think i made a mistake on that joining line.. Pls any one help me...
My original output was written below.. but i got some other big values getting this code..

dvn_cd phc_cd hsc_cd Regdt Trim1 Trim2 Abort MTB LB SB ANC1 ANC2 ANC3 TT1 TT2 IFA BP HB
42     8301    01     0      0     0     0    0  2   0 119  16	  0   0    0   0  0   1

Posted 10-Apr-14 20:46pm

Member 10463904

Updated 10-Apr-14 21:17pm

Maciej Los

v3

Add a Solution

Comments

AndrewCharlz 11-Apr-14 4:04am

Try this way
select sum(case when a.col = 'something' then 1 else 0 end ) as test from tbl

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2014-04-10T22:49:00

Iam Not Sure what output you want.
it seems your SP is very complex .If you could give me more detail and i can try to solve more.
but i think you need to change your query like this.
chk with this hope this will be help you to work out more on your query.

SQL

Alter Procedure Forms9(@mdvn int,@mphc varchar(4),@mhsc varchar(2), @fdate date, @tdate date)
as*
BEGIN 
declare @fedddate date
declare @tedddate date
declare @v1date date
declare @v2date date
declare @v3date date
set @v1date=dateadd(dd,-190,@fdate)
set @v2date=dateadd(dd,-92,@fdate)
set @v3date=dateadd(dd,-45,@fdate)
set @fedddate=dateadd(dd,-320,@fdate)
set @tedddate=dateadd(dd,30,@tdate)

SELECT DVN_CD,PHC_CD,HSC_CD,SUM(REGDT) AS REGDT,SUM(trims1)AS Trim1,sum(trims2) AS Trim2,
sum(ABORT) AS ABORT,sum(MTB) AS MTB,sum(LB)AS LB,sum(SB) AS SB,SUM(ANC1) as ANC1,SUM(ANC2) as ANC2,SUM(ANC3) as ANC3,
SUM(TT1) as TT1,sum(TTB2) as TT2,sum(IFA1) as IFA,sum(BP) as BP,sum(HB1) as HB FROM
(
  SELECT a.DVN_CD,a.PHC_CD,a.HSC_CD,
          CASE WHEN AN_Reg_Date is not null THEN 1 ELSE 0 END AS REGDT,
          CASE WHEN DATEDIFF(DD,lmp,an_reg_date)<=90 AND AN_Reg_Date is not null THEN 1 ELSE 0 END AS trims1,
          CASE WHEN DATEDIFF(DD,lmp,an_reg_date)>90  AND AN_Reg_Date is not null THEN 1 ELSE 0 END AS trims2,
          CASE WHEN Preg_outcome=1 AND Deli_Date is not null THEN 1 ELSE 0 END AS ABORT,
          CASE WHEN Preg_outcome=2 AND Deli_Date is not null THEN 1 ELSE 0 END AS MTB,
          CASE WHEN Preg_outcome=3 AND Deli_Date is not null THEN 1 ELSE 0 END AS LB,
          CASE WHEN Preg_outcome=4 AND Deli_Date is not null THEN 1 ELSE 0 END AS SB,
          case when visit_date is not null then 1 else 0 end as ANC1,
		  case when visit_date is not nullthen 1 else 0 end as ANC2,
		  case when visit_date is not null then 1 else 0 end as ANC3, 	 
		  case when TTB=1 and TTDate is not null then 1 else 0 end as TT1,
		  case when TTB>=2 and TTDate is not null then 1 else 0 end as TTB2,
		  case when IFA=100 and IFADate is not null then 1 else 0  end as IFA1, 	 
		  case when BP>='140/90' and ANEDDis not null then 1 else 0  end as BP,
		  case when HB<11 and ANEDD is not null then 1 else 0 end as HB1
      
   FROM Preg_cohort3 a 
   Inner join ANVisits3 b 
   on a.DVN_CD=b.DVN_CD and a.PHC_CD=b.PHC_CD and a.HSC_CD=b.HSC_CD 
   Where
    a.DVN_CD=@mdvn and a.PHC_CD=@mphc and a.HSC_CD=@mhsc 
    and  AN_Reg_Date BETWEEN @fdate AND @tdate 
)a group by a.dvn_cd,a.phc_cd,a.hsc_cd
RETURN 
END