Counting Characters and Records
-
Hi Everyone
I hope you guys can help me. I'm writing software for an off-site document storage facility. They store files in boxes inside a warehouse.
My software is written in VB2005 (.net2.0) and runs on SOAP Webservices. OK, here goes:
Boxes are called containers and the files inside them gets indexed (physically typed and saved in a database)
1. When a user creates a new container, the container details are saved in a [containers] table.
2. Most important fields in this table is:
- containerid <auto incrementing unique integer>
- containercode <string>
- subcoid <integer>
- userid <integer>
- createdate <datetime>
3. Once a container has been created, I return the unique ID (containerid) and uses that for every file that gets indexed into this container.
4. These files are saved in a table called [indexdata] with the most important fields here being:
- containerid <joined from containers>
- indexid <auto incrementing unique integer>
- indexfield1 <nvarchar>
- indexfield2 <nvarchar>
- indexfield3 <nvarchar>
- indexfield4 <nvarchar>
- indexfield5 <nvarchar>
- indexfield6 <nvarchar>
- indexfield7 <nvarchar>
- dod <datetime>
- fileno <nvarchar>
5. There's another table, [subco] with the following fields:
- subcoid <auto incrementing unique integer>
- subconame <nvarchar>
6. There's another table, [users] with the following fields:
- userid <auto incrementing unique integer>
- fullname <nvarchar>
7. I would like to generate a report with the following info in a DataGridView for each container that meets a "where" clause:
containers.containercode, {number of files indexed for this container in the [indexdata]-table}, containers.createdate, subco.subconame, users.fullname, {total number of characters for everything typed in indexdata.indexfield1-indexdata.indexfield7}
The following code works great for giving me the container code and next to it the total number of records in indexdata linked to this containerid:
"SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(10)) AS [Number of Files] FROM containers c INNER JOIN indexdata i ON c.containerid = i.containerid GROUP BY c.containercode"
The following code works great as well for giving me the containercode and the number of characters inside the 7 indexfields in indexdata-table.
SELECT containercode,
SUM(LEN(containercode) + LEN(indexfield1) + LEN(indexfield3) + LEN(indexfield4) + LEN(indexfield5) + LEN(indexfield6) + LEN(indexfield7) + LEN(dod) + LEN(fileno))
FROM indexdata
INNER JOIN containers ON indexdata.containerid = containers.containerid
GROUP BY containercode
ORDER BY containercode
So I basically have the code to generate 2 different grids: containercode --> number of files and container code --> number of characters.
How do I combine them to get a grid looking like this:
CONTAINER CODE | NUMBER OF FILES | NUMBER OF CHARACTERS | CREATE DATE FOR CONTAINER | SUBCO NAME
In my where clause I would like to specify the following parameters:
"where containers.userid = xx and createdate > xx/xx/xxxx and createdate < xx/xx/xxxx and subcoid = xx"
I've asked so many people and no one seems to be able to help me - would be great if someone would give it a bash.
Thanks a lot
-
You are using [containters] and [indexdata] in both queries; and you are grouping by [containercode] on both queries. If I have understood your queries correctly, combining the two SELECT statements in to one should work to return the [number of characters] and the Container Data:
SELECT c.containercode AS [Container Code],
CAST(COUNT(c.containercode) AS varchar(10)) AS [Number of Files],
SUM(LEN(i.containercode) + LEN(i.indexfield1) + LEN(i.indexfield3) +
LEN(i.indexfield4) + LEN(i.indexfield5) + LEN(i.indexfield6) +
LEN(i.indexfield7) + LEN(i.dod) + LEN(i.fileno)) AS [Number of Characters],
c.CreateDate AS [Create Date For Container]
FROM containers c
INNER JOIN indexdata i
ON c.containerid = i.containerid
GROUP BY c.containercode
ORDER BY c.containercode
I can't see how your [containers] and [subco] tables can be joined though? You'd need a reference to the [containercode] somewhere in your [subco] table?
Ade
-
Adrian, thanks for your reply.
I modified your code a little bit and it works perfectly when it's like this:
SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(10)) AS [Number of Files], SUM(LEN(c.containercode)
+ LEN(i.indexfield1) + LEN(i.indexfield2) + LEN(i.indexfield3) + LEN(i.indexfield4) + LEN(i.indexfield5) + LEN(i.indexfield6) + LEN(i.indexfield7)
+ LEN(i.dod) + LEN(i.fileno)) AS [Number of Characters], c.createdate AS [Create Date For Container]
FROM containers c INNER JOIN
indexdata i ON c.containerid = i.containerid
GROUP BY c.containercode, c.createdate
ORDER BY c.containercode
About the join between [containers] and [subco]:
a) [subco] has a subcoid unique identifier field. (integer)
b) [containers] has a subcoid field that is assigned to a record when the container is created. (integer)
c) Am I wrong in joining [containers] and [subco] like that? So basically each container belongs to a SubCompany, there are many companies.
d) Your code works SO well, now all I need is to also return the [subco].subconame field from the [subco] table.
-
Sorry!
I missed the subcoid column totally in your [containers] table, that is correct:INNER JOIN subco s
ON c.subcoid = s.subcoid
I'll just take myself away and learn to read properly!
Ade
-
Haha - no dude you're frackin' clever.
Just one last thing, how do I incorporate the c.subcoid = s.subcoid into this:
SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(10)) AS [Number of Files], SUM(LEN(c.containercode)
+ LEN(i.indexfield1) + LEN(i.indexfield2) + LEN(i.indexfield3) + LEN(i.indexfield4) + LEN(i.indexfield5) + LEN(i.indexfield6) + LEN(i.indexfield7)
+ LEN(i.dod) + LEN(i.fileno)) AS [Number of Characters], c.createdate AS [Create Date For Container]
FROM containers c INNER JOIN
indexdata i ON c.containerid = i.containerid
GROUP BY c.containercode, c.createdate
ORDER BY c.containercode
So that I get the company name in subco.subconame displayed as well?
-
You'll need to add the field s.subconame to the SELECT part of your statement and then if you also add the INNER JOIN in my previous post so that your FROM statement takes the form of:
FROM table1 a
INNER JOIN table2 b
ON a.col = b.col
INNER JOIN table3 c
ON a.othercol = c.othercol
... you should be just about there!
Ade
-
It looks like this now and it works like a dream!
SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(14)) AS [Number of Files], SUM(LEN(c.containercode)
+ LEN(i.indexfield1) + LEN(i.indexfield2) + LEN(i.indexfield3) + LEN(i.indexfield4) + LEN(i.indexfield5) + LEN(i.indexfield6) + LEN(i.indexfield7)
+ LEN(i.dod) + LEN(i.fileno)) AS [Number of Characters], c.createdate AS [Create Date For Container], s.subconame AS [Company Name]
FROM containers c INNER JOIN
indexdata i ON c.containerid = i.containerid INNER JOIN
subco s ON c.subcoid = s.subcoid
WHERE (c.userid = 1)
GROUP BY c.containercode, c.createdate, s.subconame
ORDER BY c.createdate
THANK YOU THANK YOU THANK YOU!
And in the process I actually learned how to do this.
Have a nice day!
-
You're welcome buddy. The best thing about these forums is how much we can all learn from each other, there's also some healthy rivalry about too!
You'll also soon find that there is usually (if not always) more than one way to solve your problem.
Take it easy...
Ade
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply