Advice on SQL statement please.

I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.
Here's my qry:
Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType
Type Last12Mnths_200006 Prior12Mnths_199906
Airlines 1234.50 '
Concessions 73854.00 '
etc......Russell Verdun wrote:
> I have a query that generates the dataset below, based on the year being
> filtered I get the sum of an amount Group By the type. What I would like t
o
> do is use the exact qry using a differnet date, to generate a third column
> called Prior12Mnths. How would I use my qry to accomplish this task.
> I appreciate the help.
> Here's my qry:
> Select GroupType, Sum(SumRevAmt) as Last12Mnths
> from MyQRY
> Where Period = '200006'
> Group by GroupType
>
>
> Type Last12Mnths_200006 Prior12Mnths_199906
> Airlines 1234.50 '?
?
> Concessions 73854.00 '
> etc......
>
>
It's not clear from your example what this "other date" would be, so
I'll use a different example. Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:
SELECT
account,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account
Is that enough to get you started?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It's not clear from your example what this "other date" would be, so
> I'll use a different example. Say I have a table containing
> transactions, and each transaction consists of an account, a transaction
> date, and an amount:
> SELECT
> account,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
> ELSE 0) AS Last12Months,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
> THEN amount ELSE 0) AS Prev12Months
> FROM table
> GROUP BY account
> Is that enough to get you started?
>
Sorry, those CASE statements are missing ENDs...|||Hi Russell,
I believe you could do something like this:
Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType
I Dont know if thats the best way, but that is what first comes to
mind.
Paul T.|||>> I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.
CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);
In the vague pseudo-code you posted, only some kind of vague date can
be a key
The best trick for this kind of summary is to build a reporting range
table
CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));
INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );
SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;
Adjust the table as needed.

Advice on Partitioning and Processing

I'd appreciate some advice on the following:

I'm building a cube which is based on data in a SQL 2000 DB. There are around 1.2 billion rows of fact data in 26 tables. Each table uses approx 6 Gig. There are 4 Dimensions, 3 dimensions have less than a 1000 members with the 4th having 65000. For convenience I am creating 26 Cube partitions aligned to the DB tables, each partition will have around 50 million rows. Does this sound like a reasonable proposal? The reason I ask is that I am seeing very poor performance whilst calling ProcessData. I have processed various numbers of partitions in parallel and cannot achieve the same times that I can get on the existing AS2000 rig using the Parallel Processing Utility.

I have tested the data read by writing a .net application to execute the same DB queries in parallel that the partitions use (achieving 200 million rows in 20 minutes) and there are no issues related to network, disk or the DB server.

Processing seems to start well but around a minute in the Network utilisation drops down to 10 percent and CPU utilisation to 30%. It then goes back up to 100% for a short period and then drops again. This is repeated continually until processing is finished. At current speeds it will take 26 hours to complete as opposed to 10 hours on AS2000. The box is a 64-bit 4way hyperthreaded with 32 GB of RAM. AS2005 is Enterprise with SP1.

Any suggestions would be appreciated as I'm running out of ideas!

Rob.

You should check yourself against best practices you would find the project REAL implemetation http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

I would say the partition size is a bit big. You should try and partition some more for and split a single partition in two. But that doesnt look like a major problem.

Question for you: in AS2005 you processing partitions in parallel. How many? Is the pattern the same for any number of partitions?

What OLEDB provider you are using to read data from SQL Server?

The reading rates looks to be slow. See where the bottleneck is. In your test application, the fair comparison not only when you execute SQL query, but you also retrevie all the data returned by the query.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

advice on hosting website safely

Hello,

For one of my clients I have built 2 applications. The 1st one is a windows based application that is used for production and employee scheduling. The data for this application is stored in a SQL Express database. The 2nd one is a ASP.NET 2.0 site that contains the clients website and a portal page. The portal page uses forms authentication and redirects a customer to a page that shows the production schedule for this customer. So I have 2 kind of users: local (LAN) and web. Both using the same database.

For test purposes the site is currently hosted on the clients SBS 2003 server (this is the only server). However I don't think it is wise to host the public website on this server. What is the best configuration to host the public website safely? I read about putting a webserver in a DMZ. But I need a connection with the SQL Express server to retrieve the data for the portal section. As well for authenticating the customer and for retrieving the schedule data.

I'm currently using Windows Authentication on the SQL server. Can this still be used when the webserver is in the DMZ?

If I understand it correctly the webserver in the DMZ is not part of the local domain? Also could remote hosting be an option at all?

Any help and advice would be highly appreciated.

Remote hosting would be the 'safest' option. If the box is hacked, the hacker is NOT in your network. Siting in a DMZ 'could' be relatively safe -if you are certain that there are no 'holes' in the firewall.

The public website (application) may be best using Application Pooling security on the IIS box. You can connect to a SQL Server using an IP address instead of a serverName. Use the IP address and the Port in the connection string.

Since a server in a DMZ is not part of the local domain, you cannot use Windows Authentication on the SQL Server.

I suggest that if you were to visit the forums and blogs at www.ASP.NET, you will find folks with a lot of experience with this particular issue.

Advice on dimension-dependant KPI Goals

We have KPI goals that vary based on the country or state.

For instance for US Stores, the goal is 2, and for stores in NY the goal is 2.8

I can create an SQL table with KPI_Name, Country or State, and KPI_Goal. I can also generate a view that will provide the proper KPI goal for each store.

What is the best practice to implement this kind of KPI ? I just don't know where to start. The examples I've seen are all hardcoded, like CASE when Store = XXX then 2.8

I'd rather avoid hardcoding KPI goals in the cube design as I'm expecting users to adjust goals regularily.

Can you create KPI goal measure group on its own. This is quite common. Then when you build KPIs, for goal you select value from this measure group.|||Good idea. Why didn't I think of that.

Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

I am currently working on a PHP based website that needs to be able to draw
from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
took a lot of time and care creating a flexible and solid wrapper and am
deep into coding. The only problem is a noticed VARCHAR fields being drawn
from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functi...ield-length.php)

The only problem with this advice is Text fields seem to be limited to 16
characters in length, and I am having similar results in terms of truncation
with other character based fields that can store more than 255 characters.

I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
What are my options here? Has anybody worked around this or am I missing
something obvious?

JamesNo, text fields can handle text string up to 2-Gig. The 16 bytes refers to
the space used up by the string pointer inside the record. The problem with
text/ntext is that they're slow, and you can't use them in ORDER BY or GROUP
BY.

The superlame way of retrieving more than 255 chars from a MSSQL varchar is
to retrieve the field in multiple sections, using the SUBSTR() function,
then concatenate them in PHP:

SELECT SUBSTR(msg, 1, 255) AS msg_1, SUBSTR(msg, 256, 255) AS msg_2, ...

$msg = $row['msg_1'] . $row['msg_2'] . $row['msg_3'] ...

Uzytkownik "James" <jamesstarrittRemovethefollowingtoemailme@.hotmail.c om>
napisal w wiadomosci news:40285e29$1_1@.newspeer2.tds.net...
> I am currently working on a PHP based website that needs to be able to
draw
> from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS.
I
> took a lot of time and care creating a flexible and solid wrapper and am
> deep into coding. The only problem is a noticed VARCHAR fields being
drawn
> from SQL Server 2000 are being truncated to 255 characters.
> I searched around php.net and found the following :
> Note to Win32 Users: Due to a limitation in the underlying API used by PHP
> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functi...ield-length.php)
> The only problem with this advice is Text fields seem to be limited to 16
> characters in length, and I am having similar results in terms of
truncation
> with other character based fields that can store more than 255 characters.
> I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
> the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
> What are my options here? Has anybody worked around this or am I missing
> something obvious?
> James|||James (jamesstarrittRemovethefollowingtoemailme@.hotmail. com) writes:
> The only problem is a noticed VARCHAR fields being drawn
> from SQL Server 2000 are being truncated to 255 characters.
> I searched around php.net and found the following :
> Note to Win32 Users: Due to a limitation in the underlying API used by PHP
> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functi...ield-length.php)
> The only problem with this advice is Text fields seem to be limited to
> 16 characters in length, and I am having similar results in terms of
> truncation with other character based fields that can store more than
> 255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

However, text is fairly cumbersome and not really easy to use. I don't
know anything about PHP, but it's apparent that PHP uses DB-Library to
access SQL Server. And while I think this is a very nice API, Microsoft
does not think so, and has not developed DB-Library since the release of
SQL 6.5, which was seven years ago. The next version of SQL Server, slated
for release this year, will accept connections from DB-Library, but will
not come with files needed for development. You may not even get the DB-
Library run-time DLL:s, but have to find them elsewhere.

Thus, there are all reasons to look into alternative means of connections
for PHP to MS SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
> > Note to Win32 Users: Due to a limitation in the underlying API used by
PHP
> > (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> > need to store more data, use a TEXT field instead.
> > (http://www.php.net/manual/en/functi...ield-length.php)
> > The only problem with this advice is Text fields seem to be limited to
> > 16 characters in length, and I am having similar results in terms of
> > truncation with other character based fields that can store more than
> > 255 characters.
> As pointed out by Chung Leong, there is room for 2GB of data in text.
> The 16 bytes you see is just a pointer.
> Thus, there are all reasons to look into alternative means of connections
> for PHP to MS SQL Server.

I believe the Linux PHP builds use that alternate means and the Warning
(given only on one page and not the main PHP MS SQL Server driver page as it
should have been) -- I'll be testing that later on today.

Retrieving a field in chunks may not be such a big issue, it is rare that
this will happen frequently in the app however the possibility is there on
some 60% of the fields and I do need to be able to Group and Order By on the
majority of these fields. I may just have to not support the MS SQL Server
at this time -- the goal was an app that could run on Linux and Windows
webservers and connect to just about any DB alive - its a shame that one of
the staple db's is so poorly supported by the PHP project given that I am
tied to it. My work is with non-profits and we have to be able to utilize
the licensing they already have so demanding the use of particular software
will only drive the pricing up.

Strangley I had similar issues when working with ASP and SQL Server in the
past - fields would simply not show up sometimes if they where over 255
characters in length and it is a known and documented issue that affects
'certain databases' according to MS but I have only ever seen with the SQL
Server and MSDE - never Oracle, MySQL, Postgre or even Access -- its a shame
I like the SQL Server but it seems that everytime I get contracted to use it
with a web back end I run into problems that make it a royal pain in the ass
to work with.

Thanks for the feedback,

J|||James (jamesstarrittRemovethefollowingtoemailme@.hotmail. com) writes:
> Strangley I had similar issues when working with ASP and SQL Server in
> the past - fields would simply not show up sometimes if they where over
> 255 characters in length and it is a known and documented issue that
> affects 'certain databases' according to MS but I have only ever seen
> with the SQL Server and MSDE - never Oracle, MySQL, Postgre or even
> Access -- its a shame I like the SQL Server but it seems that everytime
> I get contracted to use it with a web back end I run into problems that
> make it a royal pain in the ass to work with.

I don't know more about ASP than I know about ASP, but I would expect
ASP today have any problems with longer varchar values.

Of course there was a time when SQL Server did not have anything better
than varchar(255) (and text). If you were accessing ASP from a machine
with a version of ODBC that did not support the new and improved types
in SQL7, then you would be in that boat. But that's long ago.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adversarial Vendors

I'm working with a vendors (Stockamp & Assoc.) We hired them for the Clinic Ontrac application (SQL Server based) and they highly customized it to work in our hospital. They left after implementing the software in 3 of our 46 clinics. As I add more clincs to the system I continuously run into issues. Stockamps response to the problems is : "The system was working fine when we left what have you done with it?"
I'm continually given contradictory solutions to problems, then being blamed for modifying their system.
What are others doing when working with vendors that assume this type of adversarial position?This is difficult to answer - can you be more specific. However, in my experiences - go up the chain until it is addressed and resolved. Also, if possible, make sure you are involved with the contract process (most companies make assumptions and end up getting burned).|||Originally posted by rnealejr
This is difficult to answer - can you be more specific. However, in my experiences - go up the chain until it is addressed and resolved. Also, if possible, make sure you are involved with the contract process (most companies make assumptions and end up getting burned).

Here is my latest example. They have a form that allows me to add new clinics and specifics for that clinic. I added 2 new clinics into a test system and it failed. After spending 8 hours running through the whole process of adding a clinic (many forms are used and they have drop down boxes that are very error prone) , checking my data entry and trying to understand the strange error I was receiving, I finally sent an email to their tech support with the error, what I had done and asked for recommendations.
It turns out the forms' data are sent through to a SQL variable that only holds 8000 characters. Stockamp & Associates had known of the problem for many months and had not notified us, posted an issue to a log ... I escalated the problem up their ladder and the response from the high ups is that I should have been monitoring the number of characters I put into their form. I asked for documentation or a utility for monitoring the characters and they have none. They fixed the problem and now the variable holds 16,000 characters. I now have 16 of 46 clinics clinics in the system so I'm less than half way done, so even though they have doubled the limitation I am certain to hit the limit again. To monitor I will now have to count characters in their cludgy forms and hope I don't accidently modify something in the dropdowns and pray that I don't lose count!

In another instance, I was testing adding a new clinic and had modified one of Stockamps' stored procedures in a test database to view dates beyond those available in their forms (as I was shown by their trainer). One of Stockamps' analysts is also working on fixing other bugs. I had no idea they were using my test database because I set up a different database for them to use. The next day when I was told they implemented some fixes into our live server. By 10AM the live server was no longer functioning. I started looking into the problem and found that the analyst had modified some lines of the same stored procedure, gotten them to work, then replaced the sproc on the live server with the entire sproc from the test server - which included my modification. I found the problem and asked that they fix it (I'm afraid to touch sprocs in the live server for fear of blame) and was told that I had created the problem and needed to fix it myself, and that I was not communicating modifications I was making.

In another instance Stockamps DBA told me I could run sp_spaceused and truncate any large history tables to recoop some space (the system grows over 100 mb/day and although they have a purge, I was told it didn't function properly so I was not to use it). I truncated some tables in my test database to see the results. Again Stockamp used my test database to test some of their fixes and they kept failing. I found the problem and realized that the truncation was causing the problem. Again I was told that the problem was caused by me and the modifications I was making to their system.

I acknowledge that my truncation caused the problem, but I was acting under Stockamps directions and when the problem arose I researched the problem and figured it out. I admit my fault and want to move on and keep working with them to get the system functional, but anytime something goes wrong I am blamed.|||Well part of the problem seems to be that they are unwilling to take blame/responsibility for anything...

The first step in resolving this is to get them to document everything they recommend to you. Get everything in writing and communicate back to them in writing. Then when an issue arises you can point to the communication that has taken place and show that they were at fault. Then they realy have no choice but to accept some responsibility (or look like total dickheads).|||I would start an email chain - letting the tech support know that you will email any recommendations from them for confirmation. Next, disable their ability to touch YOUR test database - that will eliminate both of you from overlapping code. Lastly, email their vp of sales/tech support with the list of issues - carbon copy the highest ranking official in your company that was responsible for the contract with the vendor(s) (but communicate with this person the problems and get their support). As far as the forms issue - that I would let your company know as a major flaw in the product. They should have validation on the forms based on their limitations - which should also be documented in their product. I would investigate that further.

Advanced Query

I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName

so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'.

I would imagine I would need a Declare @.Status nvarchar(15) -- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.

Add this as a column:

CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status

And remove AND (ev.personalScore IS NULL) from the WHERE clause.

Like:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName

Of course, that assumes that every employee can only have 0 or 1 records in the employeeeval table. If you want to test if they have 0 or >0 records, then it would be like this:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ISNULL(EvalCount,0)=0 THEN 'Not Started' ELSE 'In Progress' END AS Status

FROM Employee e

LEFT OUTER JOIN (SELECT COUNT(*) EvalCount,EmployeeID FROM EmployeeEval GROUP BY EmployeeID) ev ON e.EmployeeID = ev.EmployeeID

LEFT OUTER JOIN [User] u ON u.Department = e.DeptID

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName

|||

Gah, I think I finally understand what it is that you want. Questions I have:

Can there be more than 1 row in the EmployeeEval table for an employee?

It seems like someone (The employee) is starting an eval request, and you enter a row into the EmployeeEval table (With approved=0). The supervisor gets notified and they mark approved=1 when they pick up the request for eval. When the eval is completed, you then remove the row from EmployeeEval? Is that correct?

Can there ever be more than 1 row per employee in the eval table? What happens if an unapproved eval is in the table and they request again? Do you deny the request and not make another row? What if it's in progress (approved=1)? Deny and don't enter another row as well?

|||

thanks, but I had to place the approved column in place of the personalscore so it looks like this:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

As you stated above they can only have 0 or 1 records that's why I went w/the first one. Thanks for the help.

|||Everytime I get one thing working people keep requesting things I just don't have the experience to complete. I now need the ability w/the forementioned query this: I need to count all employees in each dept and if they match the # in the employeeeval table place in a simple text that says Reviews for Dept. 100% complete.

I've tried working ways of using the Count statement, but I'm lost again.|||

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

I'm starting to get a wee bit confused on what exactly you want back from the query. I'm having serious doubts that the above query is actually going to return what you want it to, but.. To add your last request you can do it two ways.

Add these to your FROM clause:

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)

Then add this to your columns:

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete

if you want the % to be accurate, or the following if you want it to 100% or some other message if not 100%:

CASE WHEN c1.cnt=c2.cnt THEN 'Reviews for Dept. 100% complete' ELSE 'Not done yet!' END As DeptComplete

And you should end up with something like:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||

The query is being used to create a dataset, which in turn is used to populate a crystal report. The users, however keep wanting new requests. So I'm forced to do this through my Dataset.

I really don't need a percentage brought back, I just need to know when the #'s match in the employeeeval table and employee table, and say either incomplete or complete.

When I ran your query it tells me 3 errors--

Server: Msg 174, Level 15, State 1, Line 3
The floor function requires 1 arguments.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'c1'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'c2'.

|||

Try:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||

nope now I'm getting the following:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

In the above however, I tried removing e.deptid and still got the same error. Where is it pulling Employee.DeptID from?

|||

Doh! My bad.

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeGROUP BY DeptID) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)GROUP BY DeptID) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||Thanks for being so patient with me, I have a few me questions. Can you break down a couple of things for me. What does the Cast(Floor portion of the code do? As well what does (SELECT COUNT(*) cnt,DeptID FROM Employee do? <--for this what does the cnt, DeptID do ? I imagine cnt is like a variable for C1 or C2 but I'm a little lost on it.

Another thing I would like to be able to do--If the number of employees in a specific dept = the number of employees in the Employeeeval table I would like to return something like --Reviews are 100% complete for this dept. I don't need the employee name or anything like what I brought back in the SELECT statement.--For this I realize I'll probably need a new procedure. So I'll work on that and if I need help I'm sure I can find help on here.|||

The CAST(FLOOR(c1/c2*100) AS varchar(3)) is two steps, the first FLOOR(c1/c2*100) takes c1 divides it by c2, multiplies it by 100, and then floor says to remove any fraction, always rounding down. (99.9 becomes 99, etc). Then once we have an integer (0-100), we then say take that integer and make it a varchar(3) which is a variable length string up to 3 bytes/characters long.

As for (SELECT COUNT(*) cnt,DeptID FROM Employee ...
I take the count of records where the DeptID is the same and create a derived table from the result. Specifically, I am calling the count of records 'cnt'. DeptID is the second column taken from Employee.

Think of it this way... If you do:

SELECT COUNT(*),DeptID FROM Employee GROUP BY DeptID and your employee table looks like:

DeptId/{other columns}
1/
1
2
2
3
3
3

Then the result of the query would be:
COUNT(*)/DeptID
2/1
2/2
3/3

Instead of allowing the first column name be "COUNT(*)" since it has no other real name, COUNT(*) cnt says that I want the count, but call the column 'cnt'.

That make any sense?

|||

Yes--I just wish I could totally grasp T-SQL--So I'm trying to do the portion of SQL I asked in my last post--is there a way to incorporate all depts in this? so I still show only employees who have ev.PersonalScore is NULL but show the All Departments --Kinda like this:

Dept 1 - Supervisor 1 - EmpName - EmployeeID - Status- DeptStatus

But if the dept has all employees w/value in PersonalScore and the counts match -- put Dept Status = 'Dept has all reviews complete' --See I get lost as to where I put this to try and attempt to make it work.

|||Ok so I got what I wanted for just the Dept. but I would like to incorporate it into my other query so I don't have to run a subreport--b/c that is a pain.

select distinct d.[description],
Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
from department d LEFT OUTER JOIN [user] u ON d.deptid = u.department
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)
ORDER BY d.[description]

I just wouldn't know how to place this in my SELECT for the previous query b/c I need to use the c1 & c2 with d.DeptID instead of Employee|||

Sure... I can help you, but since I'm not totally sure on what you want out of it, let me explain this part.

Take the last query you have, and run that in management studio, or query analyzer, etc. See the results you get back? You can call it a resultset, or you can call it a table. Technically, I believe it should be called a resultset, which is like a table, but it's ordered. In any case, remove the ORDER BY from it, and you should see that the results don't really change, except that now it's in no particular order anymore.

If you now think of that as being a "table" you can now select from, aggregate, join etc, this is how you can make some very complex queries possible. I can see how you might want to use something like this in different queries. You have two options now. You can take that whole query and put parenthesis around it, and use it just about anywhere you can use a table if you give it an alias. For example...

SELECT myalias.description, myalias.status
FROM (your query here) myalias
WHERE ...

You can also use it in joins like...

SELECT myalias.description,myalias.status
FROM (your query here) myalias
JOIN (some other table) myalias2 ON (some condition)
WHERE ...

You can also make your query a view, which basically saves it so you can reuse it easily like...

CREATE VIEW DepartmentStatusView AS
(your query here)

Then you can use it almost anywhere:
SELECT *
FROM DepartmentStatusView
WHERE ...

However, to be truly useful, you will need to include the DeptID in your query, since that is what you would most likely be using in a join condition, so lets go ahead and build something like that:

CREATE VIEW vw_DepartmentStatus AS
select d.*,Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
FROM department d
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)

Now, anytime you want the department status, you can simply join in vw_DepartmentStatus using the DeptID for the ON clause, and you can reference it. Infact, you can simply think of vw_DepartmentStatus as the departments table with an extra "Status" column at the end if that helps.

If you want to add the "Status" on any query that you had before, so long as you were using Department, you can simply change your reference from Department to vw_DepartmentStatus and now you can reference the Status column like going from:

SELECT (some fields)
FROM departments d
JOIN (some stuff)
WHERE (some conditions)

to:

SELECT (some fields),d.status
FROM vw_DepartmentStatus d
JOIN (some stuff)
WHERE (some conditions)

Note the only change was to add d.status as a column you wanted to return and the change from departments to vw_DepartmentStatus

Advanced Expression?

I'm creating a report that looks through detail records and want it to sum
amounts based on a record type. If FieldType = 1 to sum accounts in a range
otherwise to sum all records with that account. The result is an error
[BC30201] Expression expected. The formula I have so far is:
=iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
db.owner.table with (nolock) where Fields!account between
Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
I've also tried VB's Select/Case statement rather than IIF but it still
returns an error.
If this can't be done with expressions, can it be done someother way?
Thanks,
RickThis seems you might want to try creating a stored procedure and using the sp
as the basis of your report, not trying to calc that in a report cell. You
can't use a select statement from within the iif statement.
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick|||Mike,
Thanks for your reply. Are you referring to creating a stored procedure in
SQL or does RS have the ability to create stored procedures as well, like in
Report properties.custom code?
Since my post I came to the same conclusion you mention that what I'm trying
to do won't work in the reporting cell so I've been looking into creating a
function using custom code but as you mention, the select statement can't be
used within IIF. Can a stored procedure be called from IIF?
As a general custom code question, can C as well as VB code be used?
Thanks again!
Rick
"mike" wrote:
> This seems you might want to try creating a stored procedure and using the sp
> as the basis of your report, not trying to calc that in a report cell. You
> can't use a select statement from within the iif statement.
> "rickp3131" wrote:
> > I'm creating a report that looks through detail records and want it to sum
> > amounts based on a record type. If FieldType = 1 to sum accounts in a range
> > otherwise to sum all records with that account. The result is an error
> > [BC30201] Expression expected. The formula I have so far is:
> >
> > =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> > db.owner.table with (nolock) where Fields!account between
> > Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> >
> > I've also tried VB's Select/Case statement rather than IIF but it still
> > returns an error.
> >
> > If this can't be done with expressions, can it be done someother way?
> >
> > Thanks,
> > Rick|||You can try an SQL user defined function returning a table with summary
details, then use the IIF statement to get more specific, or you can use a
"Where" statement in your dataset
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick

ADO error: Invalid object name <object name>.

I tried to create a report that is based on a stored procedure and I get this error when I try to verify the SQL.

ADO error: Invalid object name '#MatPatAbsence'.

If I click on the RUN exclaimation mark, I get no data, but do get the column names.

#MatPatAbsence is a temporary table created in the stored procedure and deleted once used.

I have also created another similar report from a stored procedure that has temporary tables and all are working fine.

also this stored procedure runs fine in query analyzer.

Can any one help please?

paste your query, I will take a look

ADO error: incorrect syntax near keyword

Hello,
I'm trying to change a stored procedure to add another parameter
(qaType) and take one of two execution paths based on its value (=1 or <>
1). When I try to save the changes using VS2003.NET, I keep getting this
error:
ADO error: Incorrect syntax near the keyword 'AS'. I'm having trouble
spotting it, but I'm sure you guys will see it in a flash. Thanks!
*--Code begins here --*
ALTER PROCEDURE dbo.frmQASELECTQADetailDetails
@.qaID int, @.qaType int
AS
IF (@.qaType <> 1)
BEGIN
SELECT
dd.QADetailDetailID,
dd.QADetailID,
dd.DetailedFR,
dd.UnitQty,
dd.QtyPerUnit,
(dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
dd.MockUps,
dd.Spares,
dd.FabFallout,
dd.GFE,
dd.ITA,
dd.Other,
(((dd.UnitQty * dd.QtyPerUnit) + dd.MockUps + dd.Spares + dd.FabFallout) -
(dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
dd.Comments,
dd.ITAAmountPlanned,
dd.ITAAmountActual
FROM
tblQADetailDetail dd,
tblQADetail d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @.qaID
END
ELSE
BEGIN
SELECT
dd.QADetailDetailID,
dd.QADetailID,
dd.DetailedFR,
dd.UnitQty,
dd.QtyPerUnit,
(dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
dd.MockUps,
dd.Spares,
dd.FabFallout,
dd.GFE,
dd.ITA,
dd.Other,
(((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
dd.Comments,
dd.ITAAmountPlanned,
dd.ITAAmountActual
FROM
tblQADetailDetail dd,
tblQADetail d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @.qaID
END
________________________________________
____________________________________
___
Posted Via Uncensored-News.Com - Accounts Starting At $6.95 - http://www.uncensore
d-news.com
<><><><><><><> The Worlds Uncensored News Source <><><><><><><><>> (((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
> dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
It looks like you have an extra open parenthesis. Try
((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
Hope this helps.
Dan Guzman
SQL Server MVP
"Spurious Logic" <spurs> wrote in message
news:420955b0$1_2@.news4.uncensored-news.com...
> Hello,
> I'm trying to change a stored procedure to add another parameter
> (qaType) and take one of two execution paths based on its value (=1 or <>
> 1). When I try to save the changes using VS2003.NET, I keep getting this
> error:
> ADO error: Incorrect syntax near the keyword 'AS'. I'm having trouble
> spotting it, but I'm sure you guys will see it in a flash. Thanks!
> *--Code begins here --*
> ALTER PROCEDURE dbo.frmQASELECTQADetailDetails
> @.qaID int, @.qaType int
> AS
> IF (@.qaType <> 1)
> BEGIN
> SELECT
> dd.QADetailDetailID,
> dd.QADetailID,
> dd.DetailedFR,
> dd.UnitQty,
> dd.QtyPerUnit,
> (dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
> dd.MockUps,
> dd.Spares,
> dd.FabFallout,
> dd.GFE,
> dd.ITA,
> dd.Other,
> (((dd.UnitQty * dd.QtyPerUnit) + dd.MockUps + dd.Spares + dd.FabFallout) -
> (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
> dd.Comments,
> dd.ITAAmountPlanned,
> dd.ITAAmountActual
> FROM
> tblQADetailDetail dd,
> tblQADetail d
> WHERE
> dd.QADetailID = d.QADetailID AND
> d.QAID = @.qaID
> END
> ELSE
> BEGIN
> SELECT
> dd.QADetailDetailID,
> dd.QADetailID,
> dd.DetailedFR,
> dd.UnitQty,
> dd.QtyPerUnit,
> (dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
> dd.MockUps,
> dd.Spares,
> dd.FabFallout,
> dd.GFE,
> dd.ITA,
> dd.Other,
> (((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
> dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
> dd.Comments,
> dd.ITAAmountPlanned,
> dd.ITAAmountActual
> FROM
> tblQADetailDetail dd,
> tblQADetail d
> WHERE
> dd.QADetailID = d.QADetailID AND
> d.QAID = @.qaID
> END
>
> ________________________________________
__________________________________
_____
> Posted Via Uncensored-News.Com - Accounts Starting At $6.95 -
> http://www.uncensored-news.com
> <><><><><><><> The Worlds Uncensored News Source
> <><><><><><><><>
>