two different operation in different cases sql table function

Asked Feb 08 '18 at 16:45

Active Jun 14 '18 at 16:02

Viewed 43 times

I want to program a function that sees how much time the client spent of time without paying the seller. My problem is that I'm kind of confused because this is the function that already programmed:

ALTER function [dbo].[bal_cli](@date date, @diff1 int,@diff2 int) returns @table1
table 
(
  solde float,
  cpt char(20)
)as

begin

insert into @table1(solde,cpt)
SELECT         SUM(a.ECRDEBIT-a.ECRCREDIT) AS solde,a.CPTCODE
  FROM            dbo.ECRITURES AS a , dbo.ECRITURES AS b
  WHERE      (a.JORID = '7' or a.JORID = '10' or a.JORID = '22' or a.JORID = '15' or a.JORID = '16' or a.JORID ='5' or a.JORID = '38' or a.JORID = '45' or a.JORID = '12' or a.JORID = '17' or a.JORID = '44' or a.JORID = '18' or a.JORID = '8' or a.JORID = '9') AND (a.ECRISMARQUE <> 'O') AND (a.CPTCODE LIKE '3421%') and 
                           ( DATEDIFF(day, a.ECRDATEEFFET, @date) >= @diff1 and  DATEDIFF(day, a.ECRDATEEFFET, @date) < @diff2) and (YEAR(a.ECRDATEEFFET) = YEAR(@date)) 

  GROUP BY a.CPTCODE

return;
end;

My issue is that I want JUST for JORID to calculate the difference note with ECRDATEEFFET but with ECRLIBELLE.

edited Jun 14 '18 at 16:02

Jason Aller

3,541
28
38
38

asked Feb 08 '18 at 16:45

Azouz Rh

1

When you are writing the question, you have a preview just below the box where you are typing. You can check the formatting there before you post. – HoneyBadger Feb 08 '18 at 16:46
why is the b table never used??? – Franck Ngako Feb 08 '18 at 16:48
Start using 'modern' explicit `join` syntax – HoneyBadger Feb 08 '18 at 16:51
@FranckNgako sorry just forget the b table i was just thinking about a stupid solution – Azouz Rh Feb 08 '18 at 16:54
@HoneyBadger i didn't understand – Azouz Rh Feb 08 '18 at 16:56
@AzouzRh, never use comma's in your where clause (implicit join), rather use `JOIN`: `FROM dbo.ECRITURES AS a (LEFT|RIGHT|INNER) JOIN dbo.ECRITURES AS b ON a.ID = b.ID` (or whatever the join condition are). This is just general advice, not specific to your question. – HoneyBadger Feb 08 '18 at 16:58
@HoneyBadger ohhh okay thanks man ... – Azouz Rh Feb 08 '18 at 17:00

two different operation in different cases sql table function

0 Answers0