--SQL STORED PROCEDURE USED TO CONCATENATE STRINGS INTO A SINGLE RECORD IN A GROUP BY FASHION
--THIS WOULD BE SIMILIAR TO SUMMING A NUMBER ON A GROUP BY
--use the commented out line below to execute this procedure
--exec get2casenumbers 1
create PROCEDURE GET2CASENUMBERS
@option bit
AS
DECLARE @CASENUMBER VARCHAR(14)
DECLARE @COURTDATE DATETIME
DECLARE @COURTNAME VARCHAR(40)
DECLARE @CUSTID VARCHAR(40)
DECLARE @CUSTOMERIDNAME VARCHAR(40)
DECLARE @ADDRESS1_POSTAL VARCHAR(10)
DECLARE @ADDRESS1_STATE VARCHAR(10)
DECLARE @ADDRESS1_LINE1 VARCHAR(15)
DECLARE @CACITY VARCHAR(20)
DECLARE @COURTADDRESS VARCHAR(30)
DECLARE @MMDDYY VARCHAR(10)
DECLARE @HHMM VARCHAR(10)
DECLARE @LETTERSPRINTED VARCHAR(5)
DECLARE @OLDCASENUMBER char(14)
DECLARE @OLDCOURTDATE DATETIME
DECLARE @OLDCOURTNAME CHAR(40)
DECLARE @OLDCUSTID CHAR(40)
DECLARE @OLDCUSTOMERIDNAME VARCHAR(40)
DECLARE @OLDADDRESS1_POSTAL VARCHAR(10)
DECLARE @OLDADDRESS1_STATE VARCHAR(10)
DECLARE @OLDADDRESS1_LINE1 VARCHAR(15)
DECLARE @OLDCACITY VARCHAR(20)
DECLARE @OLDCOURTADDRESS VARCHAR(30)
DECLARE @OLDMMDDYY VARCHAR(10)
DECLARE @OLDHHMM VARCHAR(10)
DECLARE @OLDLETTERSPRINTED VARCHAR(5)
DECLARE @CD DATETIME
DECLARE @CN CHAR (50)
DECLARE @CID char(50)
DECLARE @COUNT INT
DECLARE @RE VARCHAR(100)
DECLARE @OLDCOUNT INT
DECLARE @FIRSTREC INT
SET @OLDCOUNT = 0
SET @COUNT = 1
SET @CASENUMBER = ''
SET @OLDCASENUMBER = ''
SET @OLDCOURTDATE = ''
SET @OLDCOURTNAME = ''
SET @OLDCUSTID = ''
SET @RE = ''
SET @FIRSTREC = 0
If @option = 0 Begin
DECLARE C1 CURSOR FOR
SELECT top 100 CASES.New_CourtDate,CASES.CustomerIdName,CASES.NEW_CASENUMBER, CASES.CUSTOMERID,
CASES.New_CourtIdName, CONTACTS.Address1_PostalCode,
CONTACTS.Address1_StateOrProvince,
CONTACTS.Address1_Line1,
CASES.New_caCity,
COURTS.New_CourtAddress1,
CONVERT(char(100),
CASES.New_CourtDate - 0.29166666666666666666666666666667, 101) AS mmddyyy,
LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
CASES.New_CourtDate - 0.29166666666666666666666666666667, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20),
CASES.New_CourtDate - 0.29166666666666666666666666666667, 22), 3)) AS hhmm,
CASES.New_LetterPrinted
FROM
<>_MSCRM.dbo.Incident AS CASES
inner join
<>_MSCRM.dbo.New_CourtExtensionBase AS COURTEXT
ON CASES.New_CourtId = COURTEXT.New_CourtId
inner join
<>_MSCRM.dbo.New_Court AS COURTS
ON CASES.New_CourtId = COURTS.New_CourtId
inner join
<>_MSCRM.dbo.Contact AS CONTACTS
ON cases.CustomerId = CONTACTS.ContactId
WHERE CASES.NEW_COURTDATE IS NOT NULL and CASES.new_letterprinted = 0
ORDER BY CASES.CUSTOMERID,CASES.NEW_COURTDATE,CASES.New_CourtIdName,
CONTACTS.Address1_StateOrProvince,
CONTACTS.Address1_City,
CONTACTS.Address1_Line1,
CASES.New_caZip,
CASES.New_caCity,
COURTS.New_CourtAddress1,
CASES.New_LetterPrinted
End
Else begin
DECLARE C1 CURSOR FOR
SELECT top 100 CASES.New_CourtDate,CASES.CustomerIdName,CASES.NEW_CASENUMBER, CASES.CUSTOMERID,
CASES.New_CourtIdName, CONTACTS.Address1_PostalCode,
CONTACTS.Address1_StateOrProvince,
CONTACTS.Address1_Line1,
CASES.New_caCity,
COURTS.New_CourtAddress1,
CONVERT(char(100),
CASES.New_CourtDate - 0.29166666666666666666666666666667, 101) AS mmddyyy,
LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
CASES.New_CourtDate - 0.29166666666666666666666666666667, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20),
CASES.New_CourtDate - 0.29166666666666666666666666666667, 22), 3)) AS hhmm,
CASES.New_LetterPrinted
FROM
<>_MSCRM.dbo.Incident AS CASES
inner join
<>_MSCRM.dbo.New_CourtExtensionBase AS COURTEXT
ON CASES.New_CourtId = COURTEXT.New_CourtId
inner join
<>_MSCRM.dbo.New_Court AS COURTS
ON CASES.New_CourtId = COURTS.New_CourtId
inner join
<>_MSCRM.dbo.Contact AS CONTACTS
ON cases.CustomerId = CONTACTS.ContactId
WHERE CASES.NEW_COURTDATE IS NOT NULL and CASES.new_juryrequestletterprinted = 0
ORDER BY CASES.CUSTOMERID,CASES.NEW_COURTDATE,CASES.New_CourtIdName,
CONTACTS.Address1_StateOrProvince,
CONTACTS.Address1_City,
CONTACTS.Address1_Line1,
CASES.New_caZip,
CASES.New_caCity,
COURTS.New_CourtAddress1,
CASES.New_LetterPrinted
End
OPEN C1
--FETCH NEXT FROM C1 INTO @CASENUMBER, @COURTDATE,@COURTNAME, @CUSTID
FETCH NEXT FROM C1
INTO @COURTDATE,
@CUSTOMERIDNAME,
@CASENUMBER,
@CUSTID,
@COURTNAME,
@ADDRESS1_POSTAL,
@ADDRESS1_STATE,
@ADDRESS1_LINE1,
@CACITY,
@COURTADDRESS,
@MMDDYY,
@HHMM,
@LETTERSPRINTED
CREATE TABLE #CASESTEMP2(COURTDATE DATETIME, CUSTID VARCHAR(50),
CASENUMBER VARCHAR(50), COURTNAME VARCHAR(50), RE VARCHAR(40),
TOTAL INT,CUSTOMERIDNAME VARCHAR(40),ADDRESS1_POSTAL VARCHAR(40),
ADDRESS1_STATE VARCHAR(40),ADDRESS1_LINE1 VARCHAR(40),
CACITY VARCHAR(40),COURTADDRESS VARCHAR(40),MMDDYY VARCHAR(40),
HHMM VARCHAR(40),LETTERSPRINTED VARCHAR(40))
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CASENUMBER IS NULL
SET @CASENUMBER = 'NULL'
IF @COURTDATE = @OLDCOURTDATE AND @OLDCUSTID = @CUSTID AND @COURTNAME = @OLDCOURTNAME
BEGIN
SET @RE = @OLDCASENUMBER + ':' + @CASENUMBER
SET @COUNT = @COUNT + 1
END
ELSE
BEGIN
IF @OLDCOUNT = 1
INSERT INTO #CASESTEMP2(COURTDATE, CUSTID, CASENUMBER,
COURTNAME, RE, TOTAL,CUSTOMERIDNAME,ADDRESS1_POSTAL,ADDRESS1_STATE,
ADDRESS1_LINE1,CACITY,COURTADDRESS,MMDDYY,HHMM,LETTERSPRINTED)
VALUES(@OLDCOURTDATE, @OLDCUSTID, @OLDCASENUMBER, @OLDCOURTNAME,
@OLDCASENUMBER, @OLDCOUNT,@OLDCUSTOMERIDNAME,@OLDADDRESS1_POSTAL,
@OLDADDRESS1_STATE,@OLDADDRESS1_LINE1,@OLDCACITY,
@OLDCOURTADDRESS,@OLDMMDDYY,@OLDHHMM,@OLDLETTERSPRINTED)
ELSE
BEGIN
IF @FIRSTREC != 0
BEGIN
INSERT INTO #CASESTEMP2(COURTDATE, CUSTID, CASENUMBER,
COURTNAME, RE, TOTAL,CUSTOMERIDNAME,ADDRESS1_POSTAL,ADDRESS1_STATE,
ADDRESS1_LINE1,CACITY,COURTADDRESS,MMDDYY,HHMM,LETTERSPRINTED)
VALUES(@OLDCOURTDATE, @OLDCUSTID, @OLDCASENUMBER, @OLDCOURTNAME,
@RE, @OLDCOUNT,@OLDCUSTOMERIDNAME,@OLDADDRESS1_POSTAL,
@OLDADDRESS1_STATE,@OLDADDRESS1_LINE1,@OLDCACITY,
@OLDCOURTADDRESS,@OLDMMDDYY,@OLDHHMM,@OLDLETTERSPRINTED)
SET @COUNT = 1
END
END
END
SET @FIRSTREC = 1
SET @OLDCASENUMBER = @CASENUMBER
SET @OLDCOURTDATE = @COURTDATE
SET @OLDCOURTNAME = @COURTNAME
SET @OLDCUSTID = @CUSTID
SET @OLDCUSTOMERIDNAME = @CUSTOMERIDNAME
SET @OLDADDRESS1_POSTAL = @ADDRESS1_POSTAL
SET @OLDADDRESS1_STATE = @ADDRESS1_STATE
SET @OLDADDRESS1_LINE1 = @ADDRESS1_LINE1
SET @OLDCACITY = @CACITY
SET @OLDCOURTADDRESS = @COURTADDRESS
SET @OLDMMDDYY = @MMDDYY
SET @OLDHHMM = @HHMM
SET @OLDLETTERSPRINTED = @LETTERSPRINTED
--FETCH NEXT FROM C1
--INTO @CASENUMBER, @COURTDATE,@COURTNAME, @CUSTID
FETCH NEXT FROM C1
INTO @COURTDATE,
@CUSTOMERIDNAME,
@CASENUMBER,
@CUSTID,
@COURTNAME,
@ADDRESS1_POSTAL,
@ADDRESS1_STATE,
@ADDRESS1_LINE1,
@CACITY,
@COURTADDRESS,
@MMDDYY,
@HHMM,
@LETTERSPRINTED
SET @OLDCOUNT = @COUNT
END
CLOSE C1
DEALLOCATE C1
INSERT INTO #CASESTEMP2(COURTDATE, CUSTID, CASENUMBER,
COURTNAME, RE, TOTAL,CUSTOMERIDNAME,ADDRESS1_POSTAL,ADDRESS1_STATE,
ADDRESS1_LINE1,CACITY,COURTADDRESS,MMDDYY,HHMM,LETTERSPRINTED)
VALUES(@OLDCOURTDATE, @OLDCUSTID, @OLDCASENUMBER, @OLDCOURTNAME,
@OLDCASENUMBER, @OLDCOUNT,@OLDCUSTOMERIDNAME,@OLDADDRESS1_POSTAL,
@OLDADDRESS1_STATE,@OLDADDRESS1_LINE1,@OLDCACITY,
@OLDCOURTADDRESS,@OLDMMDDYY,@OLDHHMM,@OLDLETTERSPRINTED)
SELECT * FROM #CASESTEMP2
DROP TABLE #CASESTEMP2
|