Baffled! Cant figure out how to do this query. Is it even possible?

I have an "Issues" table for my technicians. An issue can be on "hold"
or "assigned".
I want to get a count for each tech with a column showing number of
issues on hold and a column for number of issues assigned. It would
look like this --

Tech Num_Assigned Num_On_Hold
Fred 3 10
Carol 6 7

I can get each column separately, but I want both in the same answer
table!
Is that too much to ask? :)You need to do a self join on the table. Without your table
definition, it would be something like this

SELECT TI1.Tech,
Num_Assigned = COUNT(TI1.TechID),
Num_On_Hold = COUNT(TI2.TechID)
FROM TechIssues TI1, TechIssues TI2
WHERE TI1.TechID = TI2.TechID
GROUP BY TI1.Tech

Now, the above assumes that all techs have issues assigned AND issues
on HOLD. You'd need to UNION a couple more of these to handle where
Techs have records assigned but not on hold and vice versa. But this
should get you started.

Hope it helps
Teresa Masino|||jonescv@.gw.ccsd.net wrote:
> I have an "Issues" table for my technicians. An issue can be on "hold"
> or "assigned".
> I want to get a count for each tech with a column showing number of
> issues on hold and a column for number of issues assigned. It would
> look like this --
> Tech Num_Assigned Num_On_Hold
> Fred 3 10
> Carol 6 7
>
> I can get each column separately, but I want both in the same answer
> table!
> Is that too much to ask? :)

Here's a guess:

SELECT tech,
COUNT(CASE WHEN status = 'assigned' THEN 1 END),
COUNT(CASE WHEN status = 'hold' THEN 1 END)
FROM your_table
GROUP BY tech ;

--
David Portas
SQL Server MVP
--

BAFFLED! Why won't this code work?!?!

Aaron and Tibor - Thank you for your replys.
Aaron, the tbl-vs-tblname issue and data type were silly mistakes -
thank you.
Tibor, so then making the entire table name a variable (rather than
just part of it) should solve the problem, right? The below code
attempts to make this change. Now getting the following error:
"Syntax error converting the varchar value 'tblIR_LB_' to a column of
data type smallint."
CREATE PROC CreateIRTables ( @.tblname varchar(50))
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
DECLARE @.tblname varchar(50)
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
SET @.tblname = 'tblIR_LB_' + @.i
EXECUTE CreateIRTables @.tblname
SET @.i = @.i + 1
ENDEXACT SAME PROBLEM, you've just moved it to a different place! You're
trying to add an integer to a string. You can't do this!

> SET @.tblname = 'tblIR_LB_' + @.i
Should be
SET @.tblname = 'tblIR_LB_' + CONVERT(VARCHAR(12),@.i)|||i think you are chasing a red herring, but I wil point out this error :)
SET @.tblname = 'tblIR_LB_' + @.i
^ @.I is a small int
SET @.tblname = 'tblIR_LB_' + CAST(@.i As varchar)
My suggestion to see what is happening .... return your @.SQL as an output
paramer and print it each time.
if it *was* giving you the error on tblIR_LB_2 then I assume tblIR_LB_1
worked? So, see what is happening by looking at the @.SQL. PRINT it as an
output variable and run the code directly.
Good luck.
John Scragg
"Butaambala" wrote:

> Aaron and Tibor - Thank you for your replys.
> Aaron, the tbl-vs-tblname issue and data type were silly mistakes -
> thank you.
> Tibor, so then making the entire table name a variable (rather than
> just part of it) should solve the problem, right? The below code
> attempts to make this change. Now getting the following error:
> "Syntax error converting the varchar value 'tblIR_LB_' to a column of
> data type smallint."
> CREATE PROC CreateIRTables ( @.tblname varchar(50))
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> DECLARE @.tblname varchar(50)
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> SET @.tblname = 'tblIR_LB_' + @.i
> EXECUTE CreateIRTables @.tblname
> SET @.i = @.i + 1
> END
>

BAFFLED! Why won't this code work?!?!

Hello,
The below code is intended to create a sp, then loop through a set of
values, each time running the sp with the new value. The CREATE PROC
executes fine, but I get an error when attempting the loop: "Invalid
column name 'tblIR_LB_2'."
Your help appreciated!
CREATE PROC sp_CreateIRTables ( @.tbl smallint)
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
EXECUTE sp_CreateIRTables @.i
SET @.i = @.i + 1
END> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
DON'T USE sp_ PREFIX!

> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
Is it @.tbl or @.tblname? Pick one.|||> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
@.tblname is, for some reason, a SMALLINT, not any kind of string.
First, I suggest not using this kind of unmanageable scheme.
But to solve your issue in the short term, you can convert to a character
type, e.g.
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + CONVERT(VARCHAR(12), @.tblname)
+ '] (|||You can't have a variable in the table name of a CREATE TABLE unless you use
dynamic SQL to create
the table. Why not create a temp table instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Butaambala" <brogers75@.yahoo.com> wrote in message
news:1131645127.077192.239150@.f14g2000cwb.googlegroups.com...
> Hello,
> The below code is intended to create a sp, then loop through a set of
> values, each time running the sp with the new value. The CREATE PROC
> executes fine, but I get an error when attempting the loop: "Invalid
> column name 'tblIR_LB_2'."
> Your help appreciated!
>
> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> EXECUTE sp_CreateIRTables @.i
> SET @.i = @.i + 1
> END
>

Baffled trying to display a parameter label

I have two parameters: year and month. The Month parameter includes All, 1, 2, ect..

I tried to display the parameter slected with the following expression:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(Parameters!DateShippedMonth.Label)) & ", " & Parameters!DateShippedYear.Label

I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.

Any ideas?

What error are you getting?

The MonthName takes an integer as MonthNumber. If you are using the parameter label, then I would assume that you are trying to pass a string to the MonthName function.

If you are indeed displaying a number 1,2, 3 etc in the month parameter, then try a CInt function around the label.

BobP

|||I tried both with and without CInt() in the MonthName function. If the parameter selected is a month I don't get an error, which is strange as I SHOULD get an error when I'm NOT using the CInt(). I only get an incorrect input string was not correct format when I add the MonthName function. If I run MonthName alone I get the error on the "All" parameter which is why I tried to pull out the "All" parameter with the IIf|||

It looks like it is validating the MonthName function on it's own, outside the context of the IIF.

So I did this to get it to work:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(iif(Parameters!DateShippedMonth.Label="All",1,Parameters!DateShippedMonth.Label))) & ", " & Parameters!DateShippedYear.Label

BobP

|||

Thanks. I'll try that. I already wrote some custom code to resolve the problem but this looks easier. Although I think I have to use custom code to translate a fiscal period of, say 4, which is really July of the previous year.

Thanks again!

|||

If you are reporting off of a Data Mart/Data Warehouse, your dimDate dimension should take care of that.

Other wise you can use the TSQL function DateName:

Quarter = DateName(q,@.CurDate)

BobP

|||I'm doing mdx queries from a cube and my dimDate month is in integers. If I change it to month names they don't sort correctly.

Baffled trying to display a parameter label

I have two parameters: year and month. The Month parameter includes All, 1, 2, ect..

I tried to display the parameter slected with the following expression:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(Parameters!DateShippedMonth.Label)) & ", " & Parameters!DateShippedYear.Label

I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.

Any ideas?

What error are you getting?

The MonthName takes an integer as MonthNumber. If you are using the parameter label, then I would assume that you are trying to pass a string to the MonthName function.

If you are indeed displaying a number 1,2, 3 etc in the month parameter, then try a CInt function around the label.

BobP

|||I tried both with and without CInt() in the MonthName function. If the parameter selected is a month I don't get an error, which is strange as I SHOULD get an error when I'm NOT using the CInt(). I only get an incorrect input string was not correct format when I add the MonthName function. If I run MonthName alone I get the error on the "All" parameter which is why I tried to pull out the "All" parameter with the IIf|||

It looks like it is validating the MonthName function on it's own, outside the context of the IIF.

So I did this to get it to work:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(iif(Parameters!DateShippedMonth.Label="All",1,Parameters!DateShippedMonth.Label))) & ", " & Parameters!DateShippedYear.Label

BobP

|||

Thanks. I'll try that. I already wrote some custom code to resolve the problem but this looks easier. Although I think I have to use custom code to translate a fiscal period of, say 4, which is really July of the previous year.

Thanks again!

|||

If you are reporting off of a Data Mart/Data Warehouse, your dimDate dimension should take care of that.

Other wise you can use the TSQL function DateName:

Quarter = DateName(q,@.CurDate)

BobP

|||I'm doing mdx queries from a cube and my dimDate month is in integers. If I change it to month names they don't sort correctly.sql

baffled .. no data returned to web browser

Hi.
I have a parameter driven report that has been built and deployed
succesfully with no errors.
After execution of the report I am just left with the format of my
report in my web browser i.e. no data is displayed.
However ...when I run the exact same SP from query analyzer I receive
the correct result set.
In the executionlog table the executioon of the report is marked as
successfull and that a certain amount of bytes have been returned to
the client...
The amount of pages are also displayed indicating to me that it knows
it has received data'
Anyone have any ideas'
LCan you export the report from the web browser? And if so, how does it
look there?

Baffled

I have a table called lab_results_import, and one of the fields is called
qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 150
0
records. 134 of these records have an empty cell in the Excel spreadsheet i
n
the qualifier_id column. The ss imports fine, but I am baffled by this quer
y
that I am running on the table. The query is
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = '')
which returns 134 records, BUT
SELECT *, qualifier_id
FROM lab_results_import
WHERE (qualifier_id = ' ')
also returns 134 records, and regardless of how many spaces are in my
(qualifier_id = '') where clause, the query returns 134 records.
Anybody know what is going on, and/or how to search for a field containing a
certain number of spaces.
Thanks a lot.
ArcherWhat data type is this column (qualifier_id)? Is it nullable?
ML|||>> Anybody know what is going on, and/or how to search for a field
It is a known behavior where the trailing blanks of are being trimmed off
for comparison. The workaround is to use a character which is not in your
column like '~' or '*' like:
SELECT *
FROM tbl
WHERE '~' + col + '~' = '~' + SPACE( 10 ) + '~' ;
Note that such padding in general, will avoid the usage of any existing
indexes in the column, though.
Anith|||Try,
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
'.' + c1 + '.' = '.' + space(3) + '.'
-- or
select
c1, datalength(c1)
from
(
select cast(space(0) as varchar(25))
union all
select space(1)
union all
select space(2)
union all
select space(3)
union all
select space(4)
) as t1(c1)
where
c1 = ' ' and datalength(c1) = 3
go
Joe Celko explained one time why sql server seems to ignore trailing spaces
when comparing varchar data type, but I could not find it.
AMB
"bagman3rd" wrote:

> I have a table called lab_results_import, and one of the fields is called
> qualifier_id. The table was empty, I imported 1 Excel spreadsheet with 1
500
> records. 134 of these records have an empty cell in the Excel spreadsheet
in
> the qualifier_id column. The ss imports fine, but I am baffled by this qu
ery
> that I am running on the table. The query is
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = '')
>
> which returns 134 records, BUT
> SELECT *, qualifier_id
> FROM lab_results_import
> WHERE (qualifier_id = ' ')
> also returns 134 records, and regardless of how many spaces are in my
> (qualifier_id = '') where clause, the query returns 134 records.
> Anybody know what is going on, and/or how to search for a field containing
a
> certain number of spaces.
> Thanks a lot.
> Archer|||varchar(10)
and nulls are allowed. I actually want the cells to be null and I am trying
to avoid having cells with emtpy spaces.
A
"ML" wrote:

> What data type is this column (qualifier_id)? Is it nullable?
>
> ML