MyCodeSnippets: 2014

Monday, October 27, 2014

Search a file for a string and update text using powershell

"Starting"
# Put the directory where the photos are located here
$Directory = "C:\Users\jmm\Desktop\deploy"
# filename to search for
$FileName = get-childitem $Directory -recurse -Include ac360.js
# Counter for testing
#$g = 0
foreach ($file in $FileName){
$filename = $file.BaseName
$Find_This = "var maxLen = obj.maxlength;"
If (Get-Content $file | Select-String -Pattern $Find_This){
$indx = Select-String $Find_This $file | ForEach-Object {$_.LineNumber}
#array of file lines is 0 based.
$indxone = $indx - 1
"Found at line $indxone with line content " + (Get-Content $file)[$indxone]
# Need to check line previous to the line we are changing to make sure the file wasn't already changed
# since we are adding stuff after the line and not before.
$indxtwo = $indx - 2
# If it finds what it thinks it should find
If ((Get-Content $file)[$indxtwo] | select-string -Pattern "var obj = acEvent.srcElement; "){
"Found at $indxtwo " + (Get-Content $file)[$indxone] + "Changed at $indxone " + (Get-Content $file)[$indxone]
# replace the line and save it back to the same file
(get-content $file) | foreach-object {$_ -replace $Find_This, "if (typeof obj.maxLength == 'undefined'){`r`n obj.maxLength = 4000;`r`n }`r`n var maxLen = obj.maxLength; "} | set-content $file
}else{
"Doesn't Qualify : " + $indxtwo + " : " + (Get-Content $file)[$indxtwo]
}
}
#$g++
}
"Done"

Tuesday, October 14, 2014

Date Conversion

,CASE
WHEN ISNUMERIC([SITE VISIT]) = 1
THEN CONVERT(SMALLDATETIME, (CAST([Site Visit] AS INT)), 101)
ELSE
CASE WHEN ISDATE([SITE VISIT]) = 1
THEN CONVERT(DATETIME, ([Site Visit]), 101)
ELSE '' END
END AS date_time

Monday, August 18, 2014

A better javascript object enumeration function (sorting, table formatting)

Wednesday, August 6, 2014

Add Identity, Change Column Types, Etc..

Alter Column
alter table [dbo].[GIS_311Registrations]
alter column [StreetNumber] varchar(MAX) null

Delete Column
alter table GIS_Primary drop column [Best Guess Vacancy Rank 3]

Add Identity
ALTER TABLE GIS_VPRVacant DROP COLUMN ID
ALTER TABLE GIS_VPRVacant ADD ID INT IDENTITY(1,1)

Insert Into
INSERT INTO Table
SELECT Something
FROM Someplace

Change Column Name
EXEC sp_rename 'GIS_Primary.City Owned Vacant', 'CtyOwnVac', 'COLUMN';

Friday, July 25, 2014

Excel Import to SQL Increase Cell Size Scan via Registry

http://forums.asp.net/t/1578417.aspx?REGEDIT+TypeGuessRows+Windows+7

I know it's been forever since your post, but I had this issue as well and found out that if you're running Windows 7 64-bit, the location has changed:

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Wednesday, July 16, 2014

Rank and counting within subgroups

This was done for the inspector Report.

So this is in the select statement:

,G.[Total Number of Inspections on Permit]
,rank() over (PARTITION BY [b1_alt_id] ORDER BY COALESCE([G6ACTION].[G6_COMPL_DD],[G6ACTION].[G6_ACT_DD],G6_ACT_NUM) DESC, G6_ACT_NUM DESC) AS 'Order Number'

And here is the outer apply for the total count within a given record:

OUTER APPLY (SELECT COUNT(DISTINCT [G6_ACT_NUM]) AS 'Total Number of Inspections on Permit'
FROM [G6ACTION] g6a
WHERE g6a."SERV_PROV_CODE" = B1P."SERV_PROV_CODE"
AND g6a."B1_PER_ID1" = B1P."B1_PER_ID1"
AND g6a."B1_PER_ID2" = B1P."B1_PER_ID2"
AND g6a."B1_PER_ID3" = B1P."B1_PER_ID3" AND G6A.[REC_STATUS] = 'A' AND COALESCE(g6a.[G6_ACT_DD],g6a.[G6_COMPL_DD],'') BETWEEN @startdate
AND DATEADD(d, 1, @enddate)
and (
(ISNULL(@InspectorName, '') = '')
OR (
ISNULL([GA_FNAME], '') + CASE
WHEN ISNULL([GA_MNAME], '') = ''
THEN ' '
ELSE ' ' + [GA_MNAME] + ' '
END + ISNULL([GA_LNAME], '') = @InspectorName
)
) ) AS G

Thursday, July 10, 2014

Closed Conditions

AND ISNULL([B1PERMIT].[B1_APPL_STATUS], '') <> 'CofO Issued'
AND ISNULL([B1PERMIT].[B1_APPL_STATUS], '') <> 'Closed'

AND ISNULL(B1P1.b1_appl_status,'') not in ('cancelled','Classified Abandon','Closed','CC Issued','Classify Abandoned','CofO Issued','Duplicate','Duplicate Record','Void')

Wednesday, June 25, 2014

SSRS Custom Report Items

Create the DLL

[https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CCoQFjAA&url=http%3A//go.microsoft.com/?linkid=7729280&ei=kPeqU6roHtDgsASR7YGoCA&usg=AFQjCNGiitNJ-d11Tj28gSXiRagdju4fcQ&sig2=WH9JhYuw7C5fcP7kO76RFA&bvm=bv.69620078,d.cWc Download VS2008 ](Visual Basic Version) or pick up the installer launcher from S:\Accela\Custom Report Items. Install it. In Windows 7 and later it seems to create a shortcut at the top rather than in the folder area on the start menu so look there.
The project source for this (probably just use the one we have since you will not need to set up permissions for it in Reporting Services as it is already there) is in the location mentioned above. Extract and open.
- If you are starting from scratch you will need to do three things to get the dll to work with windows and reporting services
  - Sign it from the Project properties tab labeled signing
  - Assert any special privledges you might be using such as system.net or system.data ( IE.
    Dim myWebPermission As New WebPermission(PermissionState.Unrestricted)
    myWebPermission.Demand()
  - You need to set up AllowPartiallyTrustedCallers on the DLL. (I can't find this in the code but I know I put it there ?)
Create a new shared public function and build your DLL.

Export to the Report Server

Copy the DLL to the report server somewhere such as the desktop.
If you have neccessary .net framework installed. Ex ; .Net 4.0 or .Net 3.5, then you can just copy Gacutil.exe from any of the machine and to the new machine.
1) Open CMD as adminstrator in new server.
2) Traverse to the folder where you copied the Gacutil.exe. For eg - C:\program files.(in my case).
3) Type the below in the cmd prompt and install.

[file system location]\gacutil.exe /I [file system location]\dllname
Copy the DLL to the Report Server Folders (3 places) at something like
- C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
- C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportServer\bin
- C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportManager\Bin

Edit the Report Server Configuration Files

The files are at
- C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportServer\rssrvpolicy.config
- C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportManager\rsmgrpolicy.config
- C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSPreviewPolicy.config
First edit the RSPreviewPolicy.config (on the machine you are testing on, not necessarily the report server. Wherever your Visual Studio is installed upon which you are doing the testing from development. I believe the DLL needs to be gacutil'd to both systems (report server and development machine) but maybe not) to test changes in developement before changing the config files on the actual server. Add something along the lines of what's below to the end of the file, changing the dll name to yours. It should be at the bottom of all codegroup sections and there should be two extra </codegroup> remarks below your section as it is a submember of other codegroups.
```
<CodeGroup
           class="UnionCodeGroup"
           version="1"
           PermissionSetName="FullTrust"
           Name="COSReportingDLL"
           Description="COSReportingDLL. ">
<IMembershipCondition
           class="UrlMembershipCondition"
           version="1"
           Url="C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\COSReportingDLL.dll"
           />
</CodeGroup>
</CodeGroup>
</CodeGroup>
</PolicyLevel>
```

In the section such as below (if it doesn't exist, add under the first codegroup item towards the upper center of the file) set FullTrust in the Permission Name

<CodeGroup
          class="UnionCodeGroup"
          version="1"
          PermissionSetName="FullTrust"
          Name="Report_Expressions_Default_Permissions"
          Description="This code group grants default permissions for code in report expressions and Code element. ">
 <IMembershipCondition
       class="StrongNameMembershipCondition"
       version="1"                                          
       PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100512C8E872E28569E733BCB123794DAB55111A0570B3B3D4DE3794153DEA5EFB7C3FEA9F2D8236CFF320C4FD0EAD5F677880BF6C181F296C751C5F6E65B04D3834C02F792FEE0FE452915D44AFE74A0C27E0D8E4B8D04EC52A8E281E01FF47E7D694E6C7275A09AFCBFD8CC82705A06B20FD6EF61EBBA6873E29C8C0F2CAEDDA2"
 />
</CodeGroup></

Supposedly there is a way to do this with caspol and adding your own security groups and then adding the dll with full permissions to that security group and then adding that security group to the report server configuration files but I couldn't get it to work. If need be, start with caspol and see where it takes you.

Test in Development Environment

Open Visual Studio for reporting and open your report.
Import the custom DLL by right clicking outside the report body and click 'Report Properties'.
Under 'References' on the left and then 'add or remove assemblies' browse to your custom dll. I pulled it from private assemblies but I don't think the report server cares where it is since it is in trusted assemblies pool and is always pulled from the system?
Go back to your report and use the custom assembly by calling the full name (IE: =COSReportingDLL.GetImage.GetImageFromWebToByteArray(Fields!Expr1.Value))

THE END

Thursday, June 19, 2014

Change to FN_GET_LICPROF_INFO

ELSE IF UPPER(@NameFormat) = 'FL'
BEGIN
IF @C_fname <> ''
SET @VSTR = @C_fname

IF @C_lname <> ''
BEGIN
IF @VSTR <> ''
SET @VSTR = @VSTR + ' ' + @C_lname
ELSE
SET @VSTR = @C_lname
END

IF UPPER(@Case) = 'U'
SET @VSTR = UPPER(@VSTR)
ELSE IF UPPER(@Case) = 'I'
SET @VSTR = DBO.FN_GET_INITCAP('', @VSTR)
ELSE
SET @VSTR = @VSTR
END
ELSE
BEGIN
IF @C_fname <> ''
SET @VSTR = @C_fname

IF @C_mname <> ''
BEGIN
IF @VSTR <> ''
SET @VSTR = @VSTR + ' ' + @C_mname
ELSE
SET @VSTR = @C_mname
END

IF @C_lname <> ''
BEGIN
IF @VSTR <> ''
SET @VSTR = @VSTR + ' ' + @C_lname
ELSE
SET @VSTR = @C_lname
END

IF UPPER(@Case) = 'U'
SET @VSTR = UPPER(coalesce(@VSTR,@C_Bname,@C_FullName))
ELSE IF UPPER(@Case) = 'I'
SET @VSTR = DBO.FN_GET_INITCAP('', coalesce(@VSTR,@C_Bname,@C_FullName))
ELSE
SET @VSTR = coalesce(@VSTR,@C_Bname,@C_FullName)
END

Wednesday, May 14, 2014

Standard WHEREs

INSPECTIONS

"G6ACTION"."G6_COMPL_DD" IS NOT NULL
AND "G6ACTION"."G6_STATUS_DD" IS NOT NULL AND "G6ACTION".REC_STATUS = 'A'
AND B1_ALT_ID NOT LIKE '%vncy%'
AND B1PERMIT."REC_STATUS" = 'A' AND (
(
B1PERMIT.b1_appl_status <> 'cancelled'
AND B1PERMIT.b1_appl_status <> 'Classified Abandon'
)
OR (B1PERMIT.b1_appl_status IS NULL)
)
AND (
(
isnull(B1PERMIT.B1_APPL_CLASS,'') NOT IN (
'INCOMPLETE EST'
,'INCOMPLETE TMP'
)
)
OR (B1PERMIT.B1_APPL_CLASS IS NULL)
)
AND "G6ACTION"."G6_STATUS" NOT IN ('DPWR','Pending','Scheduled','Rescheduled','Cancelled')
AND "G6ACTION"."INSP_GROUP" != 'AATRAINING'
AND (b1_per_group = 'building' OR b1_per_group = 'Enforcement')
AND b1_per_sub_type != 'health'
AND "G6ACTION"."GA_FNAME" + ' ' + "G6ACTION"."GA_LNAME" != 'Steve Desilets'
AND "G6ACTION"."GA_FNAME" + ' ' + "G6ACTION"."GA_LNAME" != 'David Cotter'
AND "G6ACTION"."GA_FNAME" + ' ' + "G6ACTION"."GA_LNAME" != 'Admin Admin'
AND G6ACTION.G6_COMPL_DD >= @StartDate
AND G6ACTION.G6_COMPL_DD < DATEADD(day,1,@EndDate)

AND B1P.b1_alt_id NOT LIKE '%tmp%'

Friday, April 11, 2014

EMSE Snippets

Apply An Address Condtion (You have to create any conditions used in scripts in the system or you won't have access to be able to shut them off when they are no longer needed)

true ^ addAddressCondition(null, "Address", "Not Satisfied", "Vacant or Foreclosing","Address is either Vacant or in the process of Foreclosure. From " + capId,"Lock")

Tuesday, April 8, 2014

Outer Applies For Various Fields

MOST RECENT INSPECTION DETAILS

OUTER APPLY (
SELECT TOP 1 G6_ACT_TYP
,GA_LNAME
,G6_STATUS
,ISNULL(GA_FNAME, '') + CASE
WHEN ISNULL(GA_MNAME, '') = ''
THEN ''
ELSE CASE
WHEN ISNULL(GA_FNAME, '') = ''
THEN ISNULL(GA_MNAME, '')
ELSE ' ' + ISNULL(GA_MNAME, '')
END
END + CASE
WHEN ISNULL(GA_LNAME, '') = ''
THEN ''
ELSE CASE
WHEN ISNULL(GA_FNAME, '') + ISNULL(GA_MNAME, '') = ''
THEN ISNULL(GA_LNAME, '')
ELSE ' ' + ISNULL(GA_LNAME, '')
END
END AS 'Inspector Name'
,CONVERT(SMALLDATETIME, CASE
WHEN G6_STATUS = 'SCHEDULED'
THEN G6_ACT_DD
ELSE G6_COMPL_DD
END + 0, 101) AS 'MOST RECENT INSPECTION DATE'
,G6_ACT_NUM
,6_ACT_DES
,REC_FUL_NAM
FROM G6ACTION
WHERE REC_STATUS = 'A'
AND B1PERMIT.SERV_PROV_CODE = G6ACTION.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = G6ACTION.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
ORDER BY CONVERT(SMALLDATETIME, CASE
WHEN G6_STATUS = 'SCHEDULED'
THEN G6_ACT_DD
ELSE G6_COMPL_DD
END + 0, 101) DESC
) AS C1

ALL INSPECTORS WHO ACTED ON RECORD

OUTER APPLY (
SELECT Inspectors = STUFF((
SELECT DISTINCT ',' + ISNULL([GA_FNAME], '') + CASE
WHEN ISNULL([GA_MNAME], '') = ''
THEN ' '
ELSE ' ' + [GA_MNAME] + ' '
END + ISNULL([GA_LNAME], '')
FROM G6ACTION
WHERE B1PERMIT.SERV_PROV_CODE = G6ACTION.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = G6ACTION.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
FOR XML PATH('')
), 1, 1, '')

) AS B1

GET ASSIGNEE

OUTER APPLY (
SELECT ISNULL([G3STAFFS].GA_FNAME, '') + CASE
WHEN ISNULL([G3STAFFS].GA_MNAME, '') = ''
THEN ''
ELSE CASE
WHEN ISNULL([G3STAFFS].GA_FNAME, '') = ''
THEN ISNULL([G3STAFFS].GA_MNAME, '')
ELSE ' ' + ISNULL([G3STAFFS].GA_MNAME, '')
END
END + CASE
WHEN ISNULL([G3STAFFS].GA_LNAME, '') = ''
THEN ''
ELSE CASE
WHEN ISNULL([G3STAFFS].GA_FNAME, '') + ISNULL([G3STAFFS].GA_MNAME, '') = ''
THEN ISNULL([G3STAFFS].GA_LNAME, '')
ELSE ' ' + ISNULL([G3STAFFS].GA_LNAME, '')
END
END AS 'Assignee Name'

FROM BPERMIT_DETAIL INNER JOIN [G3STAFFS] ON
BPERMIT_DETAIL.[B1_ASGN_STAFF] = [G3STAFFS].[USER_NAME]
WHERE B1PERMIT.SERV_PROV_CODE = BPERMIT_DETAIL.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = BPERMIT_DETAIL.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = BPERMIT_DETAIL.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = BPERMIT_DETAIL.B1_PER_ID3

) AS A3 NEIGHBORHOOD

OUTER APPLY ( SELECT TOP 1 L1_ATTRIBUTE_VALUE AS 'Neighborhood' FROM ACCELAPROD.dbo.B3PARCEL b3pa LEFT OUTER JOIN ACCELAPROD.dbo.L3APO_ATTRIBUTE ON L3APO_ATTRIBUTE.L1_APO_NBR = [ACCELAPROD].dbo.FN_GET_PARCEL_NBR (B1P."SERV_PROV_CODE", B1P."B1_PER_ID1", B1P."B1_PER_ID2", B1P."B1_PER_ID3") WHERE L1_ATTRIBUTE_NAME = 'planning neighborhood' ) AS A

Friday, April 4, 2014

Query Results as List

OUTER APPLY( SELECT Inspectors = STUFF((
SELECT DISTINCT ',' + ISNULL([GA_FNAME], '') + CASE
WHEN ISNULL([GA_MNAME], '') = ''
THEN ' '
ELSE ' ' + [GA_MNAME] + ' '
END + ISNULL([GA_LNAME], '')
FROM G6ACTION
WHERE B1PERMIT.SERV_PROV_CODE = G6ACTION.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = G6ACTION.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
FOR XML PATH('')),1,1,'')
) AS B1

Tuesday, March 25, 2014

TSQL Testing

http://sqlmag.com/blog/everybody-needs-test-harness

Thursday, March 13, 2014

Search Entire Database

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

And in case the site ever disappears -

CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN

 -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
 -- Purpose: To search all columns of all tables for a given search string
 -- Written by: Narayana Vyas Kondreddi
 -- Site: http://vyaskn.tripod.com
 -- Tested on: SQL Server 7.0 and SQL Server 2000
 -- Date modified: 28th July 2002 22:50 GMT


 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName = 
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )
 
   IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO #Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END 
 END

 SELECT ColumnName, ColumnValue FROM #Results
END

Tuesday, January 21, 2014

Multiple CTE's

DECLARE @begindate datetime, @enddate datetime
set @begindate = '2014-1-1'
set @enddate = '2014-1-21'
;WITH CTE (b1_alt_id, b1_alt_id_count)
AS (SELECT distinct b1_alt_id, COUNT(B1_ALT_ID) AS 'b1_alt_id_count'
FROM B1PERMIT b1p
LEFT OUTER JOIN dbo.G6ACTION g6a ON b1p.SERV_PROV_CODE = g6a.SERV_PROV_CODE
AND b1p.B1_PER_ID1 = g6a.B1_PER_ID1
AND b1p.B1_PER_ID2 = g6a.B1_PER_ID2
AND b1p.B1_PER_ID3 = g6a.B1_PER_ID3
WHERE CONVERT(DATETIME, b1_file_dd) >= @begindate
AND CONVERT(DATETIME, b1_file_dd) < DATEADD(day, 1, @EndDate)
AND isnull(b1p.B1_APPL_CLASS, 'na') <> 'INCOMPLETE EST'
AND isnull(b1p.B1_APPL_CLASS, 'na') <> 'INCOMPLETE TMP'
AND b1p.REC_STATUS = 'A'
AND b1p.b1_appl_status <> 'cancelled'
GROUP BY b1_alt_id
),
CTE2 (b1_alt_id, b1_alt_id_count)
AS (SELECT distinct b1_alt_id, COUNT(B1_ALT_ID) AS 'b1_alt_id_count'
FROM B1PERMIT b1p
LEFT OUTER JOIN dbo.G6ACTION g6a ON b1p.SERV_PROV_CODE = g6a.SERV_PROV_CODE
AND b1p.B1_PER_ID1 = g6a.B1_PER_ID1
AND b1p.B1_PER_ID2 = g6a.B1_PER_ID2
AND b1p.B1_PER_ID3 = g6a.B1_PER_ID3
WHERE CONVERT(DATETIME, b1_file_dd) >= @begindate
AND CONVERT(DATETIME, b1_file_dd) < DATEADD(day, 1, @EndDate)
AND isnull(b1p.B1_APPL_CLASS, 'na') <> 'INCOMPLETE EST'
AND isnull(b1p.B1_APPL_CLASS, 'na') <> 'INCOMPLETE TMP'
AND b1p.REC_STATUS = 'A'
AND b1p.b1_appl_status <> 'cancelled'
AND isnull(datediff(d,b1p.B1_FILE_DD, g6a."G6_COMPL_DD" ) - (datediff(wk,b1p.B1_FILE_DD, g6a."G6_COMPL_DD" ) * 2),9999) <= 10
GROUP BY b1_alt_id
)
SELECT DISTINCT 'Heading' as Heading
, B1_ALT_ID
, b1_per_group
, b1_per_type
, B1_PER_SUB_TYPE
, B1_FILE_DD
, dbo.fn_get_task_status(B1PERMIT.SERV_PROV_CODE, B1PERMIT.B1_PER_ID1, B1PERMIT.B1_PER_ID2, B1PERMIT.B1_PER_ID3, 'Complaint Intake', null) AS 'Workflow Status' , "G6_COMPL_DD"
, "G6_STATUS"
, "INSP_GROUP" , cast(isnull(datediff(d,B1PERMIT.B1_FILE_DD, "G6ACTION"."G6_COMPL_DD" ) - (datediff(wk,B1PERMIT.B1_FILE_DD, "G6ACTION"."G6_COMPL_DD" ) * 2),9999) as int) AS 'Date Difference'
,(SELECT b1_alt_id_count
FROM CTE
WHERE B1PERMIT.b1_alt_id = cte.b1_alt_id) AS 'b1_alt_id_count'
,(SELECT b1_alt_id_count
FROM CTE2
WHERE B1PERMIT.b1_alt_id = cte2.b1_alt_id) AS 'b1_alt_id_count_lt_10' FROM dbo.B1PERMIT B1PERMIT
LEFT OUTER JOIN dbo.G6ACTION ON B1PERMIT.SERV_PROV_CODE = dbo.G6ACTION.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = dbo.G6ACTION.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = dbo.G6ACTION.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = dbo.G6ACTION.B1_PER_ID3
WHERE CONVERT(DATETIME, b1_file_dd) >= @begindate
AND CONVERT(DATETIME, b1_file_dd) < DATEADD(day, 1, @EndDate)
AND isnull(B1PERMIT.B1_APPL_CLASS, 'na') <> 'INCOMPLETE EST'
AND isnull(B1PERMIT.B1_APPL_CLASS, 'na') <> 'INCOMPLETE TMP'
AND B1PERMIT.REC_STATUS = 'A'
AND B1PERMIT.b1_appl_status <> 'cancelled'
AND isnull(datediff(d,B1PERMIT.B1_FILE_DD, "G6ACTION"."G6_COMPL_DD" ) - (datediff(wk,B1PERMIT.B1_FILE_DD, "G6ACTION"."G6_COMPL_DD" ) * 2),9999) <= 10
AND dbo.fn_get_task_status(B1PERMIT.SERV_PROV_CODE, B1PERMIT.B1_PER_ID1, B1PERMIT.B1_PER_ID2, B1PERMIT.B1_PER_ID3, 'Complaint Intake', null) = 'Emergency'

MyCodeSnippets