SQL Server 2008 :: Query That Returns Only Rows That Meet Specific Condition?

Oct 22, 2015

Here's what my table looks like;

UniqID | Code |

1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC

I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.

View 9 Replies


ADVERTISEMENT

Function Appears To Affect All Rows Not Just Those That Meet Condition

Jan 31, 2007

Hi below sample data incoming from a source that cannot be changed. Please ignore the mishandling of zls. Obviously it is not insurmountable - I am just interested in why it is happening because I cannot explain it. DECLARE @t TABLE(the_data CHAR(73)) SET DATEFORMAT dmySET NOCOUNT ON INSERT INTO @tSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'0'+space(10)+'08 01 2002'+space(10)+'04 10 2002'UNION ALLSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'109 08 2004'+space(20)+'21 07 2005'UNION ALLSELECT ' 11 13026721XX198734 1'+space(10)+'0'+space(10)+'XXXXXXXXXX'+space(10)+ '09 01 2003' SELECT CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX',NULL) AS SMALLDATETIME) AS date_1_prob,CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX','') AS SMALLDATETIME) AS date_1_ok_ish,CAST(NULLIF(REPLACE(date1_text,' ','/'),'XXXXXXXXXX') AS SMALLDATETIME)AS date_1_fine, date1_textFROM--derived table - selecting relevant substring(SELECT LTRIM(RTRIM(SUBSTRING(the_data, 44, 10))) AS date1_textFROM @t)AS der_t date_1_prob date_1_ok_ish date_1_fine date1_text----------------------- ----------------------- ----------------------- ----------NULL 2002-01-08 00:00:00 2002-01-08 00:00:00 08 01 2002NULL 1900-01-01 00:00:00 1900-01-01 00:00:00 NULL 1900-01-01 00:00:00 NULL XXXXXXXXXX Can anyone explain the result in the first row first column? Thanks

View 8 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases.  Each test case can have multiple bugs associated to it.  I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription  BugID BugStatus
1                TestCase1                       1      Closed
2                TestCase1                       2      Open
3                TestCase2                      11     Closed
4                TestCase2                      12     Closed
5                TestCase2                      13     Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

SQL Server 2008 :: FEFO Query If Condition

Jan 28, 2015

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

[Code] ....

View 9 Replies View Related

Selecting Tables That Meet Certain Condition

Aug 5, 2004

Hi there,

I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.

So I just want to use a cursor on a table list that meet the criteria:

1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '


Example

declare @strqry varchar(1000)

declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable

while @@FETCH_STATUS = 0

begin

Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '

exec(@strqry)

fetch next from dailyYear into @DelTable

end

close dailyYear
deallocate dailyYear

Any help would be greatly appreciated!!

Thanks

S

View 3 Replies View Related

SQL Server 2008 :: Query To Delete All Backup Files Older Than A Specific Date?

Jul 29, 2015

I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.

EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'

View 2 Replies View Related

SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?

Jun 30, 2015

We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.

Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?

View 2 Replies View Related

SQL Server 2008 :: In Query - All Rows With Same Status

Oct 28, 2015

I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?

View 6 Replies View Related

Consequtive Rows That Meet Criteria

Oct 3, 2007



Hi

I have the following table in SQL Server 2000.

JobDate PayrollNo Variance JobNumber
1/1/2007 123456 -100 111111
1/2/2007 123456 -200 222222
1/3/2007 123456 100 333333
1/5/2007 123456 -150 555555
1/4/2007 543212 -100 444444
1/8/2007 543212 -500 666666
1/14/2007 543212 -192 777777

I need to show any values where the variance is negative for 3 or more consequtive jobs. So the rows in bold above would be returned.

I've found something here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022586&SiteID=1 But it's not exactly the same as that returns rows where the dates are consequtive and 2 rows have negative numbers.

Anyone got any suggestions on how to achieve this please?

Thanks

View 8 Replies View Related

SQL Server 2008 :: Query To Select Every 2 Rows Based On A Date?

Sep 6, 2015

I have a table like the following (with much more data, but the concept is the same) with Dates and Actions for People and a column called Action with beginning Dates and end dates.

(I attached a picture because I could not figure out how to Format it)

begin Date end Date Name

begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John

I need the query to be like this. The idea is to have the query grab the next 'END' not all Ends, which my attempts have done i.e. I get not just the closest end to the begin date, but ALL Ends with the same Person.

I Need it to look like this:

begin Date end Date Name

begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John

There can be different People so the query Needs to return the beginning and end rows for the Person in sequential order.I can't figure out how to select only the 'next' end. My query always gets 'end' values that have a 'begin'. I

View 8 Replies View Related

SQL 2012 :: Percentage Of Rows That Meet Multiple Criteria?

Jun 2, 2015

I am working on a project that was assigned to me that has to do with data in one of our SQL databases. I have the following query that takes information from a single table and averages test scores for each student.

--Group all scores from same student and average them together

with cte_names as
(
SELECT StudentID, MAX(StudentName) AS StudentName
FROM LDCScores
WHERE schoolYear='2014-2015' AND term = 3
GROUP BY StudentID

[code].....

I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.

View 7 Replies View Related

SQL Server 2008 :: Remove Rows Affected From Query In Send Mail

May 6, 2015

I need to remove "rows affected" text from results as shown below from posted Sp. I am using set nocount on but its not working as expected.

Create Procedure DailyCheckList
As
SET NOCOUNT ON
Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)
set @dt= convert(varchar(20),GETDATE(),107)

[Code].....

View 1 Replies View Related

Returns TABLE Based On A Condition

Jul 20, 2005

HI all,In SQL Server, i have a function which will return a table. likecreate function fn_test (@t int) returns table asreturn (select * from table)now i want the function to retun the table based on some condition like belowcreate function fn_test(@t int) returns table asif @t = 1 return (select * from table1)else return (select * from table2)It is not working for me. Please give me your suggesstions. It's very urgent.Thank you in advance....

View 1 Replies View Related

SQL Server 2008 :: Inner Join With Complex Condition

Mar 23, 2015

I have Two tables @master and @child

Master Table :

MasterID EntryNumber BranchId IsstockIn
1 1 1 1

2 1 1 0

Child Table:

CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4

My Query:

Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)

My Result:

EntryQty
10
20
-5
-4

Expected Result:

10
20

View 6 Replies View Related

SQL Server 2008 :: Adding Condition Within Inner Join

Jul 13, 2015

I am looking for a query where in I can have a conditional statement within inner join after ON statement as shown below.

Declare @roleid int
select @roleid = roleid from Role where Name ='Admin'
select empid,empName,deptName from employee em
inner department dm on CASE when @roleid>0 then 1=dm.RoleId else em.RoleId=dm.RoleId end

View 1 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

SQL Server 2008 :: Skip Code If Certain Condition Not Met In Stored Procedure

Mar 18, 2015

I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?

View 5 Replies View Related

Xp_AvailableMedia Returns 0 Rows In SQL Server 2005

May 2, 2007

Without systemAdmin role, xp_AvailableMedia can still run (after granting execute permission) but just return 0 rows.



It seems we have to give the user SystemAdmin role, in order to get the list of devices in SQL 2005, while in previous versions of SQL Server, the user granted the execute permission for xp_AvailableMedia will be able to get the list of devices.



Is this be design? Is there any way the a NON-SA user can retrieve device list? If yes, what extra permissions do we have grant?



Thanks in advance for your help.



Frank



View 3 Replies View Related

SQL Server 2008 :: Expression Of Non-Boolean Type Specified In Context Where Condition Is Expected

Apr 10, 2015

I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.

SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY

[code]...

what I need to change in the WHERE clause to rectify this error ?

View 3 Replies View Related

SQL Server 2008 :: Moving Into A Specific Filegroup

Jun 3, 2015

SQL 2008 R2

I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.

Scheme and function currently defined as:

CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])

CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)

I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.

How do i get it off of Primary completely, and onto RTFG1 to RTFG6?

I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.

View 0 Replies View Related

SQL Server 2008 :: SSIS Expression On Getting The Specific Files?

Feb 26, 2015

I am developing the SSIS and stuck on copying specific files.

1. We receive CSV file to our drive on a daily basis.

2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.

3. There are a lot of files in this directory.

[URL]

What we would like to do:

Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.

View 1 Replies View Related

SQL Server 2008 :: Specific Type Of Join For 3 Tables

Jul 3, 2015

I have 3 tables as per following:

orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5

ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015

product
ProductdescMinQty
Item1This is 110
Item2This is 220

I want to pull only the 1 line for minqty for an item as follows

OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015

View 2 Replies View Related

SQL Server 2008 :: Join Two Tables With Specific Data

Jul 15, 2015

I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.

Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.

However, no matter what I did I always got 99 records when I joined.

View 3 Replies View Related

SQL Server 2012 :: Import XML Returns 0 Rows Affected Failing

Nov 29, 2013

I am trying to do a bulk import of data from XML into sQL.

My query returns no errors but no data gets imported.

Here is my XML

?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>

[code]....

View 2 Replies View Related

SQL Server 2008 :: How To Load SSIS Package For Any Specific Date

May 18, 2015

I have a ssis package which runs daily. This ssis package has couple of execute sql tasks which load data for yesterday's transaction. Ex.

INSERT INTO Shipped (Div_Code, shipment_value, ship_l_id, shipped_qty, shipped_date, whse_code,

ord_id, ship_id, ship_l_ord_l_id, Created_date) select ord.DIV_CODE as div_code, ship.SHIPMENT_VALUE as shipment_value, ship_l.SHIP_L_ID as ship_l_id, ship_l.SHIPPED_QTY as shipped_qty, ship.SHIPPED_DATE as shipped_date, ship.WHSE_CODE as whse_code, ord.ORD_ID as ord_id, ship.SHIP_ID as ship_id, ship_l.ord_l_id as ship_l_ord_l_id, Getdate() as Created_date from SHIP ship, ORD ord, SHIP_L ship_l where ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))) and ship.WHSE_CODE='WPP' and ord.ORD_ID=ship.ORD_ID and ship.SHIP_ID=ship_l.SHIP_ID

All execute sql task has query like above query. and in some query we have date filter which loads data for yesterday. Ex. one query has ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). some other query has ord.trans_date=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). this package runs daily through sql server job, so It loads data for yesterday. Now If i want to run for any particular date, How could we achieve from ssis?

View 3 Replies View Related

SQL Server 2008 :: Load Data For Yesterday And Specific Date

May 31, 2015

I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load for any specific date, package should be set for yesterday's date dynamically. How to achieve this as I am new to SSIS.

View 2 Replies View Related

SQL Server 2008 :: How To Track Modification Date On Specific Table

Jul 13, 2015

I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date

I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.

View 5 Replies View Related

SQL Server 2008 :: Extended Events Capture A Specific Proc Parameter

Feb 19, 2015

I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.

View 4 Replies View Related

SQL Server 2012 :: List Catalog Reports With No Execution History Returns 0 Rows

Feb 13, 2015

comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:

SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4

However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:

SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])

I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).

select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'

View 4 Replies View Related

SQL Server 2008 :: Syntax Error When Trying To Change Mirroring For Dedicated Network Through Specific IP

Jun 10, 2015

We have a database in SQL Server 2008 R2 with mirroring and want that replication is done by dedicated network.We stop the endpoint and when we try to run the following command, syntax error occurred:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '192.168.1.14'.

What is the correct syntax of the command line below?

ALTER ENDPOINT Endpoint_Mirroring AS TCP (LISTENER_IP = '192.168.1.14')

View 2 Replies View Related

SQL Server 2008 :: Add Target Specific Extended Properties To Replication Subscriber Tables

Oct 6, 2015

I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?

View 0 Replies View Related

SQL Server 2014 :: Check If Two Rows Have A Different Value In A Specific Column

Sep 9, 2015

I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.

Like this:

ID IS NULL
1 1
2 1
3 0
1 0

The expected ID i get as a result of my query should be 1 in this case.

View 6 Replies View Related

Reporting Services :: How To Create Multi-valued Parameter For Only One Specific Condition

Jul 21, 2015

I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name  in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved