Solved

How to populate an array and compare the data

Posted on 2008-10-09
9
1,514 Views
Last Modified: 2012-05-05
Hi all,

Im wondering how/where (withing the custom code?) im able to fill an array with data.  What im doing is retrieving postal codes from clients and stripping the first 3 characters and trying to put those values into an array.  With this array i will then compare each value against a string value (will be a list of all Alberta's postal codes) and keep a count so i can then display the number of clients from that certain area of Alberta.

eg.  The array will contain thousands of postal codes (T2W, T0E, T0L, etc...) and then compare it to a string (such as T2W) and i would like it to return the total number within the array that are T2W.

Im just trying to figure out how im able to create the array within SSRS and populate it, then pass the array to another method or procedure that does the string comparison and returns a count value.

This may not be explained very well but any help is greatly appreciated.

Thanks!
0
Comment
Question by:agilebiz
  • 5
  • 4
9 Comments
 
LVL 1

Expert Comment

by:timberbt
ID: 22680252
If you're really just looking to get a list or a count, why not do it all in SQL?   Could you provide a sample table structure that you're pulling from?   Should be a pretty trivial query after that if I understand correctly.
0
 
LVL 1

Expert Comment

by:timberbt
ID: 22680324
Something like this maybe....
DECLARE @SearchString VARCHAR(3)

SELECT @SearchString='T2W'
 

DECLARE @PostalCodes TABLE

(

	Code VARCHAR(6)

	)

	

INSERT @PostalCodes VALUES ('T0E123')

INSERT @PostalCodes VALUES ('T0E124')

INSERT @PostalCodes VALUES ('T2W123')

INSERT @PostalCodes VALUES ('T0L123')

INSERT @PostalCodes VALUES ('T0E125')

INSERT @PostalCodes VALUES ('T2Wzzz')

INSERT @PostalCodes VALUES ('T0L456')
 

--List matching codes

SELECT * FROM @PostalCodes

WHERE

	LEFT(Code,3) = @SearchString
 

--Get a count of matching codes

SELECT COUNT(1) AS [MatchingCount] FROM @PostalCodes

WHERE

	LEFT(Code,3) = @SearchString

Open in new window

0
 

Author Comment

by:agilebiz
ID: 22680425
My search string will be every postal code in alberta.  Basically the company wants to see how many clients came from each area of Alberta.  There are 150 differnt postal codes in Alberta so ill need to iterate through each and spit out the number of clients per area in alberta.  Attached is my select statement.  

Basically what they got me doing is retreiving the number of clients who contacted this company via certain referral methods (ie. Yellow pages, Friend, etc)  In my report i have it displaying the different types of referral sources as well as the different postal codes of the clients who contacted them (with out duplicates).  Where im having troubles is displaying the total number of referrals per postal location.

What im looking for:

Referral Source         Location              Total Referrals
Yellow Pages               T2W                           5
                                      L0E                            2
etc..
Friends                        T7E                              1
etc..
etc..
SELECT     dbo.tlkpReferralSource.refPK, dbo.tlkpReferralSource.refDescription, dbo.tblBankruptsAddress.addPostalCode, dbo.tblBankrupts.banBankruptPK

FROM         dbo.tblBankrupts INNER JOIN

                      dbo.tlkpReferralSource ON dbo.tblBankrupts.banReferralSource = dbo.tlkpReferralSource.refDescription INNER JOIN

                      dbo.tblBankruptsDates ON dbo.tblBankrupts.banBankruptPK = dbo.tblBankruptsDates.datBankruptPK INNER JOIN

                      dbo.tblBankruptsAddress ON dbo.tblBankrupts.banBankruptPK = dbo.tblBankruptsAddress.addBankruptPK

WHERE     (dbo.tblBankruptsDates.datInsolvency >= @startDate) AND (dbo.tblBankruptsDates.datInsolvency <= @endDate)

GROUP BY dbo.tlkpReferralSource.refPK, dbo.tlkpReferralSource.refDescription, dbo.tblBankruptsAddress.addPostalCode, 

                      dbo.tblBankrupts.banBankruptPK

ORDER BY dbo.tlkpReferralSource.refPK

Open in new window

0
 

Author Comment

by:agilebiz
ID: 22680440
Excuse my inability to really explain what im asking for, i have very limited knowledge in SSRS.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Accepted Solution

by:
timberbt earned 500 total points
ID: 22680462
Okay so you hae multiples... this is showing just locations, but I still say SQL is the right place to handle this...

Something with output more like this...
DECLARE @SearchString VARCHAR(3)

SELECT  @SearchString = 'T2W'

 

DECLARE @PostalCodes TABLE ( Code VARCHAR(6) )

        

INSERT  @PostalCodes

VALUES  ( 'T0E123' )

INSERT  @PostalCodes

VALUES  ( 'T0E124' )

INSERT  @PostalCodes

VALUES  ( 'T2W123' )

INSERT  @PostalCodes

VALUES  ( 'T0L123' )

INSERT  @PostalCodes

VALUES  ( 'T0E125' )

INSERT  @PostalCodes

VALUES  ( 'T2Wzzz' )

INSERT  @PostalCodes

VALUES  ( 'T0L456' )
 

SELECT  b.code,

        COUNT(1) [MatchingCount]

FROM    @PostalCodes a

        JOIN ( SELECT DISTINCT

                        LEFT(Code, 3) AS [Code]

               FROM     @PostalCodes

             ) b ON LEFT(a.Code, 3) = b.Code

GROUP BY b.Code

Open in new window

0
 

Author Comment

by:agilebiz
ID: 22688871
I will give this a shot and get back to you. Thanks
0
 
LVL 1

Expert Comment

by:timberbt
ID: 22689032
No problem.  It looks like you'll have to work a couple tables into that join, but doing the subselect with the distinct filter on the first 3 character of the postal code sounds like what you need.   Enjoy!

0
 
LVL 1

Expert Comment

by:timberbt
ID: 22831097
All done and working?
0
 

Author Comment

by:agilebiz
ID: 22850263
Sorry timberbt, we have decided to go with a more graphical representation of the data using mappoint, although this was helpful, Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now