Database

CodingYoshi wrote:

Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it

INSERT INTO 
dbo].[Table1]
(
   [col1]
   , [col2]
   , [AccountCode]
   , [col4]
   , [col5]
   , [colDate]
)
SELECT 
   [col1]
   , AccountCode
   , '12345' 
   , '12345'
   , 100
   , '01/16/2009'
FROM #TempTable

SET IDENTITY_INSERT [Table1] ON
INSERT...
SET IDENTITY_INSERT [Table1] OFF

Unsy wrote:

Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception

SELECT 
    Field1
   ,Field2
   ,SUM(
        SUM(CASE WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 END))
        /
        SUM(CASE WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 TotalPaid END))
       )
FROM
    Table1
GROUP BY 
    Field1
   ,Field2

Unsy wrote:

Please excuse the SQL code if it is full of errors as I am 250 miles from home

SELECT
    Field1
   ,Field2
   ,SUM( CASE Divisor
            WHEN 0 THEN 0
            ELSE Amount / Divisor
         END)
FROM (
   SELECT Field1,
          Field2,
          SUM(CASE 
                 WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 
                 THEN 0 
                 ELSE 1 
              END
          ) amount,
          SUM(CASE 
                 WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 
                 THEN 0 
                 ELSE 1 
              END
          ) divisor
   FROM
       Table1
   GROUP BY
       Field1
      ,Field2) alias
GROUP BY
    Field1
   ,Field2

Aman786Singh wrote:

i ran sp_configure command on my both servers. the values are - 1

SET REMOTE_PROC_TRANSACTIONS ON

... tJ.cdSubjectCode LIKE (CASE @ciSubjectId
                              WHEN '' THEN tJ.cdSubjectCode 
                              ELSE '%' + @ciSubjectId + '%'
                           END)...

declare @ciSubjectId varchar(50);
SET @ciSubjectId = 'table';
SELECT *
FROM sysobjects
WHERE
     --tJ.cdSubjectCode 
     name LIKE (CASE @ciSubjectId                              
                WHEN '' THEN name
                ELSE '%' + @ciSubjectId + '%'
                END)

dptalt wrote:

Syntax error converting the varchar value '4,25' to a column of data type int

tLD.idClientDeptNumb IN (
   CASE 
     WHEN @ciDept <> '' 
         THEN CONVERT(REAL, REPLACE(@ciDept, ',', '.')) 
      ELSE tLD.idClientDeptNumb 
   END...