I've got a set of records that has a varchar data type with most of the info being numeric, i.e.
002563256
025636982
000025632
99% of the data is similar to this but there are a few oddbals with alpha characters:
a2532222
is there a way to put something like an 'is numeric' in the where clause?
I have a query however i am getting the following error message “Msg 447, Level 16, State 0, Line 1 Expression type numeric is invalid for COLLATE clause.“
This is my query
SELECT
sjo.ID,
sjo.MID,
sjo.Trade_Association_Name,
da1.Account_Name As Trade_Association_Name,
substring(do.[MM-CHN-AGENT],2,12) as Mass_Agent_Chain_No,
[Code] ....
And Dan.Stg_Jitter_Opp2 table consists of the following
ColumnNameData Type
Idvarchar(50)
Mid numeric(18, 0)
RecordTypeIDvarchar(50)
Trade_Association_Name varchar(50)
And [FDMS].[SalesForce].[DailyAccounts]table consists of the following
ColumnNameData Type
Idint
Account_Idvarchar(18)
account_Name varchar(150)
mid_externalvarchar(15)
Mid_internalvarchar(15)
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
how i convert varchar sal field to numeric in query
select sum(sal) from emp1
error:the sum or average aggregate operation cannot take a varchar data type as an argument.
I am trying to run a query that checks a column to see if its numeric. If it is, it should cast as a float, if its not numeric it should return 0.
select case when (isnumeric(Ref)=1) THEN select (cast(Ref,float) )
else (0) as outputfrom #table
but its returning an error.
i have dat like this
ab0001
a001
abc001
I need the following output
ab
a
abc
Hi guys....
View 10 Replies View Related
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)
50.00
0.50
0.00
0.00
To be displayed in the below format
1.25
0.75
0
0
1
I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query or SSRS expression.
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae
Here is the complete code:
SqlCeConnection _conn = null;
_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");
_conn.Open();
// DOES NOT WORK *** This statement does not return the record (it exist)
string _sql = "SELECT * FROM Product where RegDescr='0123456' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";
// works correctly *** as long as multiple records are returned
string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
// Same results using ExecuteResultSet or ExecuteReader
//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
_rdr = _cmd.ExecuteReader();
listBox1.Items.Add("In the while loop");
while (_rdr.Read())
{
listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));
}
listBox1.Items.Add("Done");
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row.
Example
Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application.
Thoughts?
Thanks,Ad.
Hi everyone, I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.
View 2 Replies View RelatedQuestion..
In a MSSQL SELECT Statement e.g
SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'
i have these values in the table
table1
fname lname
mike jackson
roy mires
table2
district_name district_number
South 123
Daggerty 7845
duffel 7224
rubble 7545
Now the query is dynamic (the letter is that the like clause is run against)
When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..
I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.
Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything
Am i doing something wrong?
any help would be greatly appreciated
Hi all,
In Oracle 'ROWNUM' can be used with any variables.
eg : select sno from test1 where rownum < variable1 ( say variable1 is a local variable )
Is there any equivalent for the above in SQL Server ?
Hint :
If 'select sno from test where rownum < 10' in Oracle, then SQL Server equivalent is 'select top 9 sno from test'.
The same way I need the equivalent for the above.
Thanks,
Sam
Hi folks,
How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause:
select * from course where (name like 'sci%' or name like 'eng%')
Can I use regular expressions to achieve that?
Thanks!
-Parul
Hi,
I am writing MDX query to retrive a set of data based on selected range of date.
I have written a MDX query but it is not filtering .
My code:
SELECT NON EMPTY { [Measures].[Fact Table Count] } ON COLUMNS, NON EMPTY topcount({ ([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS ) } ,1000)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56] ) ON COLUMNS FROM [Cube Analysis]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
This code should display only data with selected range of date but it displaying all data.
Can any one give a solution to filter the data based on Date using WHERE clause.
Thank you.
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column?
Do I use 'Having' or temp tables or Views?
Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob
FROM Transactions
SELECT Count(Ticker) as Trades_Lng
FROM Transactions
WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng
FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
I have a column in the database that stored moduleId that are seperated by '|' (pipes). For Example: '527|343|454'
I need to add a where clause to a query that pulls the data based on a
ModuleId. For Example: select * from table where 527 in [column above]
Does anyone know how I can do this in a query? Normally I could
use an IN statement, ex: select * from table where 527 in (527,343,454)
How can I get the column in that format?
Thanks for the help in advance,
KM
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface.
I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("outIDlist").Value', @outIDlist
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ...
FROM ...
WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure.
But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);).
The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
Alan
I got SQL Query error with this sql statement....
Code:
sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM "
sSQL = sSQL & sView
sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"
The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?
Hello,
I've put together the following query, but it has been unsuccessful running it so far.
Code:
select * from Contractors.dbo.Contacts
where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor]
where [Name] like '%of%')
I receive the following error when I run it in Query Analyzer:
Code:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!
Alex
This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ
View 2 Replies View RelatedOK ... I am using UPS Worldship that issues an ODBC query to my MS2Kserver ... Worldship can query either a table or a view and retreiveshipping info for a supplied orderid.I need to create a DB table that will track the orderids requestedfrom Worldship so that I can stop doubleships. That is to set up afunction to allow the info to be sent only once to worldship.I need to execute a stored procedure to write to a table and enforcebiz logic.So .. I've created a view that Worldship can execute an ODBC queryagainst (v_upsPull) ... in which I guess the query issued will belike: SELECT * FROM v_upsPull WHERE orderid = 123456The view is:CREATE VIEW dbo.v_upsPullASSELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]','exec sp_ups_pull')When the ODBC query calls the view the sp_ups_pull store procedurer isexecuted.However ... I do not have access to the original Where clause in theODBC query in the stored procedurer.Is there a way I can get access to the ODBC Where clause and pass itinto the stored procedurer?If not is there some other way I can create a DB table and run aselect against it ... based on the Worldship query?
View 3 Replies View RelatedHiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
View 7 Replies View RelatedHi everybody,
I have a problem. My provider(ISP) is supporting SQL Native Client driver and my forum supplier is only supporting SQLOLEDB. I am trying to access our sql2005 DB located at our ISP.
I have changed this line:
strCon = "Provider=SQLOLEDB;Connection Timeout=90;" & strCon
To:
strCon = "Driver={SQL Native Client};Connection Timeout=90;" & strCon
Now I can access the database, but when I am trying to loging I get this error:
Server Error in Forum Application
An error has occured while writing to the database.
Please contact the forum administrator.
Support Error Code:- err_SQLServer_loginUser()_update_USR_Code
File Name:- functions_login.asp
Error details:-
Microsoft OLE DB Provider for ODBC Drivers
Query cannot be updated because the FROM clause is not a single simple table name.
What can I do?? I am stuck in between and need a solution.....
Regards Gerry!
Table:ColumnsUsersList:UserID, UserName, Country
I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order
So the query first return all the users from Pakistan and the the users from rest of the world in ascending order.
Forexample,
1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan
then the query returns.
3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
Hello,
I have to update a query that was given to me so that it displays only items that were created in a certain month and certain year (which I prompt the user for). I hard-coded a month and year to test (2008 year, 2 month). My results are including other months I'm not asking for.
I have marked my problems areas with /* Problem 1 */ and /* Problem 2 */ (same where clause n 2 locations).|What am I doing wrong? Doesn't matter what year or month I enter, I am always getting extra data1st 3 columns of results (I removed the calculated fields)
2008 1 Incoming2008 1 Both2008 2 Outgoing2008 2 Incoming2008 2 BothI can't for the life of me figure out how to fix this up.
query below...
================================
DECLARE @TheYear integerDECLARE @TheMonth integer
SET @TheYear = 2008SET @TheMonth = 2
SELECT * FROM ( SELECT year(startime) as yearstart, month(startime) as monthstart, directioncodename
, count(CASE
WHEN new_issuecategoryname is null
THEN activitycountvalue
END ) as nullcall
, count(CASE
WHEN new_issuecategoryname='ACDelco Comment'
THEN activitycountvalue
END ) as acdelcocommentcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco General Inquiry'
THEN activitycountvalue
END ) as acdgeneralinquirycall
, count(CASE
WHEN
new_issuecategoryname='ACDelco PPD'
THEN activitycountvalue
END ) as acdppdcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Promotion'
THEN activitycountvalue
END ) as acdpromotioncall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Promotion - Calendar'
THEN activitycountvalue
END ) as acdpromotioncalendarcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Program Enquiry'
THEN activitycountvalue
END ) as tssprogramenquirycall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Promotion'
THEN activitycountvalue
END ) as tsspromotioncall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Training'
THEN activitycountvalue
END ) as acdtrainingcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Benefits'
THEN activitycountvalue
END ) as tssbenefitscall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Other'
THEN activitycountvalue
END ) as acdothercall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TAC Number'
THEN activitycountvalue
END ) as acdtacnumbercall
, count(CASE
WHEN
new_issuecategoryname='ACDelco PPD Promotion'
THEN activitycountvalue
END ) as acdppdpromotioncall
, count(CASE
WHEN
new_issuecategoryname='RealRewards - ISC'
THEN activitycountvalue
END ) as realrewardsisccall
, count(CASE
WHEN
new_issuecategoryname='RealRewards - CounterPerson'
THEN activitycountvalue
END ) as realrewardscounterpersonscall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Event'
THEN activitycountvalue
END ) as acdelcoeventcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Lead'
THEN activitycountvalue
END ) as acdtssleadcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Other'
THEN activitycountvalue
END ) as acdtssothercall
, count(CASE
WHEN new_issuecategoryname is not null
and new_issuecategoryname!='ACDelco Comment'
and new_issuecategoryname!='ACDelco General Inquiry'
and new_issuecategoryname!='ACDelco PPD'
and new_issuecategoryname!='ACDelco Promotion'
and new_issuecategoryname!='ACDelco Promotion - Calendar'
and new_issuecategoryname!='ACDelco TSS Program Enquiry'
and new_issuecategoryname!='ACDelco TSS Promotion'
and new_issuecategoryname!='ACDelco Training'
and new_issuecategoryname!='ACDelco TSS Benefits'
and new_issuecategoryname!='ACDelco Other'
and new_issuecategoryname!='ACDelco TAC Number'
and new_issuecategoryname!='ACDelco PPD Promotion'
and new_issuecategoryname!='RealRewards - ISC'
and new_issuecategoryname!='RealRewards - CounterPerson'
and new_issuecategoryname!='ACDelco Event'
and new_issuecategoryname!='ACDelco TSS Lead'
and new_issuecategoryname!='ACDelco TSS Other'
THEN activitycountvalue
END ) as othercall
,count(activitycountvalue) as totalcall
FROM (
select
startime =
CASE
WHEN filteredphonecall.new_cmgstartdatetime is not null
THEN filteredphonecall.new_cmgstartdatetime
WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null
THEN filteredphonecall.actualstart
ELSE
filteredphonecall.createdon
END
, 1 as activitycountvalue
, new_issuecategoryname
, new_issuecategory
, 'phone call' as activitytypecodename
, filteredphonecall.new_languagename
, filteredphonecall.directioncodename
from /* PROBLEM 1 */ filteredphonecall
WHERE ( ( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) )
OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND (Month(filteredphonecall.actualstart) = @TheMonth) )
OR ( filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth) ) )
)as phoneactivities
GROUP BY year(startime), month(startime), directioncodename
UNION ALL
/* KATHY1 */
SELECT year(startime) as yearstart
, month(startime) as monthstart
,'Both' as directioncodename
, count(CASE
WHEN new_issuecategoryname is null
THEN activitycountvalue
END ) as nullcall
, count(CASE
WHEN new_issuecategoryname='ACDelco Comment'
THEN activitycountvalue
END ) as acdelcocommentcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco General Inquiry'
THEN activitycountvalue
END ) as acdgeneralinquirycall
, count(CASE
WHEN
new_issuecategoryname='ACDelco PPD'
THEN activitycountvalue
END ) as acdppdcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Promotion'
THEN activitycountvalue
END ) as acdpromotioncall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Promotion - Calendar'
THEN activitycountvalue
END ) as acdpromotioncalendarcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Program Enquiry'
THEN activitycountvalue
END ) as tssprogramenquirycall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Promotion'
THEN activitycountvalue
END ) as tsspromotioncall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Training'
THEN activitycountvalue
END ) as acdtrainingcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Benefits'
THEN activitycountvalue
END ) as tssbenefitscall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Other'
THEN activitycountvalue
END ) as acdothercall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TAC Number'
THEN activitycountvalue
END ) as acdtacnumbercall
, count(CASE
WHEN
new_issuecategoryname='ACDelco PPD Promotion'
THEN activitycountvalue
END ) as acdppdpromotioncall
, count(CASE
WHEN
new_issuecategoryname='RealRewards - ISC'
THEN activitycountvalue
END ) as realrewardsisccall
, count(CASE
WHEN
new_issuecategoryname='RealRewards - CounterPerson'
THEN activitycountvalue
END ) as realrewardscounterpersonscall
, count(CASE
WHEN
new_issuecategoryname='ACDelco Event'
THEN activitycountvalue
END ) as acdelcoeventcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Lead'
THEN activitycountvalue
END ) as acdtssleadcall
, count(CASE
WHEN
new_issuecategoryname='ACDelco TSS Other'
THEN activitycountvalue
END ) as acdtssothercall
, count(CASE
WHEN new_issuecategoryname is not null
and new_issuecategoryname!='ACDelco Comment'
and new_issuecategoryname!='ACDelco General Inquiry'
and new_issuecategoryname!='ACDelco PPD'
and new_issuecategoryname!='ACDelco Promotion'
and new_issuecategoryname!='ACDelco Promotion - Calendar'
and new_issuecategoryname!='ACDelco TSS Program Enquiry'
and new_issuecategoryname!='ACDelco TSS Promotion'
and new_issuecategoryname!='ACDelco Training'
and new_issuecategoryname!='ACDelco TSS Benefits'
and new_issuecategoryname!='ACDelco Other'
and new_issuecategoryname!='ACDelco TAC Number'
and new_issuecategoryname!='ACDelco PPD Promotion'
and new_issuecategoryname!='RealRewards - ISC'
and new_issuecategoryname!='RealRewards - CounterPerson'
and new_issuecategoryname!='ACDelco Event'
and new_issuecategoryname!='ACDelco TSS Lead'
and new_issuecategoryname!='ACDelco TSS Other'
THEN activitycountvalue
END ) as othercall
,count(activitycountvalue) as totalcall
FROM (
select
startime =
CASE
WHEN filteredphonecall.new_cmgstartdatetime is not null
THEN filteredphonecall.new_cmgstartdatetime
WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null
THEN filteredphonecall.actualstart
ELSE
filteredphonecall.createdon
END
, 1 as activitycountvalue
, new_issuecategoryname
, new_issuecategory
, 'phone call' as activitytypecodename
, filteredphonecall.new_languagename
, filteredphonecall.directioncodename
from filteredphonecall
/* PROBLEM 2 */ filteredphonecall
WHERE ( ( filteredphonecall.new_cmgstartdatetime is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) )
OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.new_cmgstartdatetime) = @TheYear) AND (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth) ) OR ( filteredphonecall.actualstart is not null AND (Year(filteredphonecall.actualstart) = @TheYear) AND (Month(filteredphonecall.actualstart) = @TheMonth) )
OR ( filteredphonecall.createdon is not null AND (Year(filteredphonecall.createdon) = @TheYear) AND (Month(filteredphonecall.createdon) = @TheMonth) ) )
)as phoneactivities
GROUP BY year(startime), month(startime)
) as orderedresults
order by yearstart, monthstart, directioncodename DESC;
I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,
employee working which Project and what project have assign to him, what is his assign project or contract no,
One employee working more then one project, retrieve information one employee how many projects are working,
What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.
I use this query.
select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'
above query retrieve no data,
how can use group by clause in the above query ?
group by apprecords.appcontract
group by clause give error.
above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.
Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on…
Group by apprecords.appposition
……….
Contract Table (basic data entry contract table)
-------------------------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate
Employee Table (basic data entry employee table)
---------------------------------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType
Approved Records Table (in this table all information about
the employee and his approved
position and contract )
------------------------------------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract
Assign Project Table (in this table all information about the
employee his assign the project)
--------------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus
Regards.
MATEEN