i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice?
Thanks.
select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
--and total > 2 (Wont recognise Total)
group by b.iownerid, a.icompanyid, a.vchcompanyname
order by 2 desc
Table A - data example Idno Entity Flag 1 1 y 2 1 n 3 1 y 4 1 y
Table B - data example Idno Entity 1 1 2 1 3 1
Table C - data example Idno Entity 4 1
I want to update the value flag in table A.Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y. 4th record where idno A not in table b and in Table C so flag will change from y to n. is it possible, or do i need to use the "fetch" method to compare record by record.
I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause.
What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this:
select * from tableA where tableA.col1 = 'something' and tableA.code in (select * from #codes) and....
However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause.
Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it?
Hello all, I've got some problem with the following query: select subscriber_id, sum(convert(int,n_inboundcalls)) as totalinbound from calldiversityratedaily where totalInbound < 10 group by subscriber_id
The error message provided is: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'totalInbound'.
I'm using MSSQL server 2000, version 8.00.194 and it appears that I can't use the name I give to the sum in the where clause. Could anyone help me please?
Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers. i.e I should get the CustomerIDs C2,C4,C6,C8
I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
From this I have to form the where clause like "Name like'3X5900' ".This will be dynamic , as the search may vary on the next call for search.
How I have to implement this dynamic query in the below mentioned code.I have add the dynamic query where i have mentioned as '---- Where clause has to be added '. Sometime the table input will be having all the value for search. How I can implement this in my query with good performance.
DECLARE @Where varchar(4000); Select @where = 'v.Name LIKE ''3X5900''' SELECT * from (SELECT Row_number()Over(Order By V.ProjectId) Rownum, v.Firstname,
Hi, hope someone can help or suggest something to help me with the issue.
I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up
parent_ID child_id Type projname ----------------------------------------------- 100 100 P parent_X_proj 100 25 C child_X_proj 100 29 C child_X_proj2 200 200 P parent_Z 300 300 P parent_Y etc................
this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project. so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.
I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.Â
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go  CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
I have to write a select query which need some logic to be implemented. Query is like select name,number,active, if active ="true" then select @days=days from tbl_shdsheet else @days='' end from tbl_emp In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet So how can i write queery for this.
Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
I wish to make a query with if condition Implemented in a database sql server 2008 R2.
I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place. The principle of my request is:
if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request). else if amount of movement (QTEMVT) <amount entered by the user then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions: the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0 by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.
Basically I want to set up an item management query based on batch number and expiration dates.
ps: QTEMVT = quantity of the item stored in my database NUMLOT = batch number items DATFABRIC = manufacturing date items DATPEREMP = expiry date items
Here is my request:
SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date) from #table) IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month <Something here to kill the query> ELSE SELECT email_address FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
declare @dte as datetime='2015-04-01' declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0) declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0)); DECLARE @query AS NVARCHAR(MAX);
[code]...
iam getting the ouput correctly my requirement is i want to write a condition here JOIN busdetails b ON m.bus_id = b.bus_id i want to write this statement as JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
The following query performs acceptably (2 seconds against 126,000,000 rows in the main table):
SELECT Count(*)
FROM
Message1_2_3 INNER JOIN
VDMVDO ON Message1_2_3.VDMVDO_ID = VDMVDO.VDMVDO_ID INNER JOIN
NMEA ON VDMVDO.NMEA_ID = NMEA.NMEA_ID
WHERE
NMEA.NMEA_ID BETWEEN 14000000 AND 14086000 AND
VDMVDO.RepeatIndicator = 0 AND
NMEA.SentenceFormatterID = 'VDM'
When we change the first condition from an Int column to a DateTime as in:
NMEA.TimeDate BETWEEN CONVERT(DATETIME, '2007-07-09 8:30:00', 102) AND CONVERT(DATETIME, '2007-07-09 9:30:00', 102)
the query performance falls to 14 seconds, even though both columns are indexed and a similar number of rows are found. When the select clause changes from a simple Count to a complex Max expression, response time falls to over a minute!
Any thoughs on optimizing the DateTime search would be greatly appreciated...
I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView it takes 14 seconds to completes if I want a subset of the result and I run the query with a WHERE clause: SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
I have a view V1 created as a plain UNION from 2 tables:
SELECT * FROM T1 UNION SELECT * FROM T2
If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned. However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.
Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?
SELECT Column_1, Column_2, Column_3, 10*Column_1 AS Column_4, 10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:
reportMonth - Just a non-queried list of months for the user to select reportYear - Generated using the following query from dataset "Years":
declare @curYear int set @curYear = 2000 declare @yearTable table (repYear int not null) while @curYear <= year(getdate()) begin insert into @yearTable(repYear) values (@curYear) set @curYear = @curYear + 1 end select * from @yearTable
This generated a list like:
2000 2001 2002 etc.
I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.
I have a date parameter for a report that is intended to search 'within the last X week/month...'. The SQL for this is basically WHERE [datefield] >= @dateparam AND [datefield] <= GETDATE().
The @dateparam is defined in the report as 'From query', the value field is 'thedate', and the label field is 'thetext', and the dataset is:
Date Query dataset
SELECT GETDATE()-7 as thedate, '1 Week' as thetext, 1 as theorder UNION SELECT GETDATE()-14 as thedate,'2 Weeks' as thetext, 2 as theorder UNION SELECT GETDATE()-21 as thedate,'3 Weeks' as thetext, 3 as theorder UNION SELECT GETDATE()-31 as thedate,'1 Month' as thetext, 4 as theorder UNION SELECT GETDATE()-62 as thedate,'2 Months' as thetext, 5 as theorder UNION SELECT GETDATE()-(31*3) as thedate,'3 Months' as thetext, 6 as theorder UNION SELECT GETDATE()-(31*6) as thedate,'6 Months' as thetext, 7 as theorder UNION SELECT GETDATE()-365 as thedate,'1 Year' as thetext, 8 as theorder ORDER BY 3 When I go to run the report and selecting a value for the date parameter, there is never any output, and the data selection combo box just gets reset to show <select a value>.
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",   otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",   otherwize return "NAME SUCCESSFULLY DELETED") I tried the following which doesn't work: =IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"), IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose, I have four data tables with the same structure but records may be different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I want to that two records by filtering all tables and sum of rates and want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100
(and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
