MyTable that i want to select the data from
if exists(select * from MyTable where ActChildID_FK = @actid_fk)
begin
declare @parentid int,@fnname nvarchar(100)
select @parentid = ActParentID_FK from MYTabe where ActChildID_FK = @actid_fk
END
the problem is that when the ActChildID_FK = 300374 it returns 2 values for the ActParentID_FK , i need to be able to receive only one value and i do not want to change any of the values in my table. Thanks in advance for any appreciated help.
I have a table with an identity column (say Column1) and an integer column Column2 (nullable)
While inserting a row into this table, if the value passed to the Column2 is null, copy the Column1 value. If not null value is passed as argument for column2, retain the given value.
Only after the every insert, this check and update should be done.
But as per the instruction given to me, should not use default constraint and trigger (after insert).
This is for audit purpose. The record from this table will be moved/switched from one table to another table. The volume and transaction is very high (billions of records).
Can you help me on this?
Thanks, Baskaran
I'm currently trying to write a SQL Trigger that will be used in a 2005 SQL Server. This trigger is made to be fired after an insert and an update querry.
The insert part works fine, but there is a different process for the update, since I want to compare the old and new values. Here is the part that is not correctly working :
DECLARE @temp nvarchar(200)
SET @temp = (
SELECT SPS_VALEUR
FROM DELETED
WHERE ID_SPSC_COLONNE = 'CAT-EFORM-ACA-19-8'
)
The error i get when i try to save my trigger is :
Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
The strange thing is that at the very begining of my code i do this querry, and it works fine :
DECLARE @idTableauModifie nvarchar(200)
SET @idTableauModifie = (
SELECT SUBSTRING( ID_SPSC_COLONNE,1, 16)
FROM INSERTED
)
SPS_VALEUR and ID_SPSC_COLONNE have also the same type (nvarchar), and the INSERTED and DELETED tables are not empty.
I looked for other solutions on this site and others but none are working. I tried to do casts, convertions, an inner join on an empty table having varchars types and even a substring(SPS_VALEUR, 1, 200).
Maybe I am doing something wrong, but if anyone has encountered the same problem, please help me.
Thanks
I used the below query to extract the weekday by providing datetime that works fine but I need to extract weekday from a Millisecond column
If TIMEVAL is DATETIME datatype then below one works
SELECT DATEPART(dw,TIMEVAL) FROM Student;
If TIMEVAL is in Big Int (Millisecond) then the same query doesn't work
So, how can I get the WEEKDAY by using DATEPART for a column which contains MILLISECOND?
I have a table where I have to check the values between column Value1 and value2. If value1 is less than value2, update the status 'Low' in Status column, if greater then update status to 'High'.
I have tried with this query
UPDATE table
SET Status = If(value1 <= value2) Then 'Low' else 'High'
Please guide to a solution.
I am having trouble connecting remotely to the a DB instance in a computer with sql server 2005.
I have checked the settings in sql configuration manager: IPALL have the port number "1466" set in "TCP Port" that I set
sql server + browser added to firewall allowed list
allowed both port "1434 + 1466" in windows firewall inbound rules
Forwarded both port 1434 + 1466 TCP/UDP
set IP Address to static IP address, same in cmd > ipconfig
I can connect with the local IP address with SQL authentication in "SQL Server Management Studio Express" ( "ipaddress/instancename, 1466"
but when I replaced the IP with the IP I get at Whatismyip.com, in the format of "ipaddress/instancename, 1466" I get error: 0 and cannot connect
try to ping the whatismyip.com IP address locally, it worked
but when I ping it from another computer remotely 4 request => 4 timeouts, 100% loss
Does this have anything to do with router firewall?
I have deatached the database, after this i have backup a later version of the database backup. Now i can access to the database through the MSQL server 2005 with no problem, but the program that needs to connect the database fires an SQL exception, it says that can't open the database. I am afraid i have done something wrong while the backup because no changes have been madden.
The client asked me to analize a migration for the current MSQL server 2005 to a virtualized enviroment with a MSQL server 2012 and i need some advice for this purpouse ¿Which is the best Virtual Enviroment for this purpouse? ¿What are the most important points to keep in mind when storing database in a virtualized enviroment?
I just need an approach, i assume this question may take hours of analysis. thx in advance
I want to access data from sql server 2005 from my local machine i.e from different ip address.
I tried to access using 'Ip/Instance' eg "10.10.10.10/Myinstance" and with userid and password but not able to access it.
getting error --enter image description here
I read earlier that we need to register local Ip in 'sql server configuration manager', but i forget it.
or If you have solution please tell me.
thanks and regards
sagar
I'm wonder if there is any way for removing CHAR( 13 ) and CHAR( 10 ) at the end of the variable. I got this :
SELECT @TEXT
@Text is a very long line with a lot of CHAR( 13 ) and CHAR( 10 ). I just need to remove it when those CHARS are at the end of the line. Is it posible?
Thank you in advance.
I want to Add Counter in this query any idea ?
SELECT
STUFF((
SELECT ' , vol' + counter +' : ' + t2.ViaLocValue
FROM Book_ViaLocat t2
WHERE t2.BookId = t1.BookId
FOR XML PATH (''))
,1,2,'') AS Names
FROM Book_ViaLocat t1
WHERE t1.BookId = '167698'
GROUP BY t1.BookId;
result like this vol 1 : java2 , vol 2 : java3, vol 3 : java4
I want to create a storedprocedure where I want to check if I add a Bin no and if exist in the table it should give me validation message otherwise it should work
I tried like below but it is not working
ALTER PROCEDURE [dbo].[sp_P_WMS_Stock_Adj_Val_Proc]
(@Bin_no nvarchar(max)) AS BEGIN
IF (@Bin_no=)
BEGIN
RAISERROR('Bin no already exist', 16, 1)
RETURN
END
ELSE
BEGIN
select distinct Location_Name + '-' + convert(varchar, mkey)
from WMS_Storage_Bin where status='Confirmed' and location_name=@Bin_no
END END
I am using sql server 2005
I'll try to make it as simple as I can:
I have a table that registers whatever a USER (employee) is doing (it can be starting his job, registering new products, delivering products and ending his job). The most common scenario is when the user starts his job, do whatever he needs to do during the day, and then ends his job on the same day he started, so these are the info that matter to me now (start and end).
This query:
SELECT DISTINCT
DATEPART(DAY,TimeStamp) [Day],
DATEPART(MONTH,TimeStamp) [Month],
DATEPART(YEAR,TimeStamp) [Year],
login [USER],
CASE WHEN TipoOcorrencia = 'IniciarDia' THEN 'YES' END [Started],
CASE WHEN TipoOcorrencia = 'FinalizacaoDia' THEN 'YES' END [Ended]
FROM CHAMADO ch
WHERE
TimeStamp >= '20151001'
AND TipoOcorrencia = 'IniciarDia' OR TipoOcorrencia = 'FinalizacaoDia'
GROUP BY
DATEPART(DAY,TimeStamp),
DATEPART(MONTH,TimeStamp),
DATEPART(YEAR,TimeStamp),
login,
TipoOcorrencia
ORDER BY [USER], [Year],[Month],[Day]
returns something like this (but with more users):
You can see that this user has Started and Ended his job day on the same date in three different days (10/7, 10/8 and 10/9).
By correctly using PIVOT I believe that is possible to obtain the following expected result:
Obviously my problem is that I, despite trying really hard, could not make PIVOT work. And I need help...
Thanks in advance!
I am trying to get an employee manager's first and last name IF the employee has a manager ( Some employee's do not ex CEO etc ). Currently it returns the employee name for manager and if there is no ManagerID in contact it wont return any values
Here is my general table structure for the tables I'm trying to access:
EmpID
EmployeeNumber
StartDate
isManager
Status ( full time / part time )
ContactID
ContactID
Fname
Lname
ManagerID
DeptID
Name
DeptHistID
DeptID
PosTitle
StartDate
EndDate
ModifiedDate
And here is the query I have been manipulating:
SELECT
dh.StartDate, dh.PositionTitle, d.Name, e.EmployeeNumber, e.Classification, e.Status, c1.FirstName, c1.LastName, c1.SIN, c1.DateOfBirth, c1.PhoneNumber, c1.EmailAddress, c1.AddressLine1, c1.AddressLine2, c1.PostalCode, c1.City, c1.Province, (c2.FirstName+ ' ' + c2.LastName) AS Manager
FROM Person.Contact c1
JOIN HumanResources.Employee e ON c1.ContactID = e.ContactID
JOIN HumanResources.EmployeeDepartmentHistory dh ON e.EmpID = dh.EmpID
JOIN HumanResources.Department d ON dh.DepartmentID = d.DepartmentID
JOIN Person.Contact c2 ON c2.ManagerID = e.EmpID
WHERE
e.EmpID = @empID
I have a data set where I would like to filter out rows that meet a specific condition, however it's proving a lot more trickier than I thought it would do.
Essentially I want to do something along the lines of WHERE (column1 NOT LIKE 'Z%' and column2 != '2') with the view that the query will only return rows in which a specific row doesn't have the value like 'Z%' in column1 and doesn't have the value '2' in column2.
For example:
Full data set
column1 | column2
-----------------
ACAL | 2
-----------------
ZVBBU3 | 2
-----------------
FSKE2 | X
Data set that I want
column1 | column2
-----------------
ACAL | 2
-----------------
FSKE2 | X
However, it doesn't do that. So I thought there may be a programming approach like WHERE (!(column1 LIKE 'Z% and column2 = '2')) where ! will return the opposite of the criteria specified, but this throws a syntax error. Is there any way of doing what I want to achieve?
I have a query:
SELECT Substring(warehouse_id, 1, Len(warehouse_id) - 1)Warehouse_Id FROM (SELECT (SELECT Cast(ur.warehouse_id AS VARCHAR) + ','
FROM wms_user_rights ur
WHERE ur.delete_flag = 'N'
AND end_date >= Getdate()
FOR xml path('')) Warehouse_Id)mm
It displays this result:
Warehouse_id
------------
11,12
If I don't use that substring, it give this result:
Warehouse_id
---------------
11
12
So, my question is:
How do I create a function that will handle all this process that can be handled in one function?
Something like this would be simple:
SELECT FuncName(ur.warehouse_id) FROM wms_user_rights ur
I am using sql server 2005
Currently I tried with SubString but I want a Simple Function so that I can use it multiple times
I want to create a Stored procedure that will check 5 things from a table and they are as follows:
After checking this it will return the exact warehouse_id from the table.
The table name is WMS_User_Rights in which Mkey is unique. Please suggest what to do
Below is my Script for WMS_User_Rights table
CREATE TABLE [dbo].[WMS_User_Rights](
[Mkey] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Warehouse_Id] [numeric](10, 0) NOT NULL,
[Admin] [char](1) NOT NULL,
[User_Id] [numeric](10, 0) NOT NULL,
[Start_date] [datetime] NULL,
[End_date] [datetime] NULL,
[Tran_type] [varchar](10) NULL,
[Created_By] [numeric](10, 0) NULL,
[Created_date] [datetime] NULL,
[Last_Updated_By] [numeric](10, 0) NULL,
[Last_Updated_date] [datetime] NULL,
[Delete_Flag] [char](1) NOT NULL,
[Attribute1] [varchar](250) NULL,
[Attribute2] [varchar](250) NULL,
[Attribute3] [varchar](250) NULL,
[Attribute4] [varchar](250) NULL,
[Attribute5] [varchar](250) NULL,
[Attribute6] [varchar](250) NULL,
[Attribute7] [varchar](250) NULL,
[Attribute8] [varchar](250) NULL,
[Attribute9] [varchar](250) NULL,
[Attribute10] [varchar](250) NULL,
[Attribute11] [varchar](250) NULL,
[Attribute12] [varchar](250) NULL,
[Attribute13] [varchar](250) NULL,
[Attribute14] [varchar](250) NULL,
[Attribute15] [varchar](250) NULL
) ON [PRIMARY]
In the following query I am trying to get specifically a managers name using the 'not so looked up to' subquery. I am only using this subquery as I do not know how to specify another where clause for the 'ManagerID'
I understand that joins should be used in place of these queries as they operate row by row and not as a set but I don't see another way of doing this. Currently this query will not execute as I get the famous "subquery has returned multiple rows" Any help would be appreciated.
SELECT hrdh.StartDate, hrdh.PositionTitle, HumanResources.Department.Name,
HumanResources.Employee.EmployeeNumber, HumanResources.Employee.Classification, HumanResources.Employee.Status,
HumanResources.Employee.ManagerID AS ManagerID,
Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.SIN,
Person.Contact.DateOfBirth, Person.Contact.PhoneNumber, Person.Contact.EmailAddress, Person.Contact.AddressLine1,
Person.Contact.AddressLine2, Person.Contact.PostalCode, Person.Contact.City, Person.Contact.Province,
(SELECT (Person.Contact.FirstName+ ' ' +Person.Contact.LastName) AS manager
FROM Person.Contact
INNER JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
WHERE HumanResources.Employee.ManagerID = ManagerID) AS manager
FROM HumanResources.EmployeeDepartmentHistory hrdh, HumanResources.Employee, Person.Contact, HumanResources.Department
WHERE hrdh.EmpID = @empID
I am working on a custom application that allows users to insert / update / delete / retrieve data from a database.
Stored procedures I use affects a few tables so I use a transaction and I want to be able to lock these tables to prevent unintended things from happening when multiple users are inserting , deleting, updating, or retrieving (CRUD) data.
My question is what would be the best isolation level to use here, I have read the MSDN on isolation levels and tried to make sense of it as well as searching around, If someone who has been here done that could comment quickly that would be greatly appreciated.
Is possible change format date by specific pattern ? I need to made a function which has a two parameters. First is date and second is pattern. I need convert more date variants. Goal this function is change US and European date format.
For example i need convert
EU: dd:MM:yyyy hh:mm:ss to US: MM:dd:yyyy hh:mm:ss
On another page i need change
EU: dd/MM/yyyy to US: MM/dd/yyyy
And i have a several next variant to convert
And i want to made a similar function
Formater(euDate, pattern)
BEGIN
....
RETURN usDate
My production server is unfortunately SQL server 2005 and doesn't support function FORMAT(). And function CONVERT() doesn't support some variant of date, which i need convert. So in my current solution i parse EU date at individualy parts (@day = day(@euDate), @month, @year, ...) and join them in new string . And i compare it with input parameter in pattern and return CASE which is equal like pattern. I want to this function make general and simplier.
Thank you for Your advice.
I have a audit table in SQL that simply contains some narrative, the datestamp and a linkid that links narratives to a particular object. When reading this table I would search by object id and then order the narratives by date to understand what is happeneing.
Obviously this approach does not work programmatically so I wanted to know how I could build logic to identify specific events.
For example a single object may have multiple narratives that say 'Received new host ...'. And it may have multiple narratives that say 'Matched against object xxx...'.
I would like to partition (?) this table in a way that I can see what the 1st 'Matched against ...' time after the nth 'Receive new ...' record was.
Is this possible?
TradeRef - unique PK and irrelevant
TradeId - the grouped trade ID which is what I will be using
AuditDate
AuditNarrative
The following is an example of the data for one particular trade Id. I have shown the date and the narrative so you can work out what is happening. To link this back to my question - I want to find out the 1st match that happened after the 2nd 'Received new host contract' but ignore where the end of the narrative is PHONE_CONFIRMED. So simply reading through the records the record I want is the 'Matched event...' record on 29/09/15 15:09:23. Please note that this is a simple example and there are other trades with many more match events and new host contracts coming in!
13/07/15 14:49:00 Received new host contract
13/07/15 15:22:39 Moved to Priority 'Host Unmatched' Queue
13/07/15 19:17:44 Received new counterparty contract
13/07/15 19:21:04 Automated perfect match C123
13/07/15 19:21:04 Automated perfect match H123
13/07/15 19:21:05 Match Event generated for Host Trade H123 and Counterparty Trade C123 C123
12/08/15 15:36:18 Automated unmatch request
12/08/15 15:36:18 Unmatched Event generated for Host trade: H123 and Counterparty Trade: C123
12/08/15 15:36:18 Automated unmatch request
12/08/15 15:36:18 Processed Cancellation
12/08/15 15:42:25 Moved to Priority 'Host Unmatched' Queue
12/08/15 16:00:44 Cpty cancelled their trade stoday. Emailed James Newman to advise
12/08/15 16:23:23 Received cancelled host contract
12/08/15 16:23:23 Processed Cancellation
12/08/15 16:24:04 Received new host contract
12/08/15 16:57:24 Moved to Priority 'Host Unmatched' Queue
17/08/15 21:57:58 Matched Event generated for Host Trade H123 PHONE_CONFIRMED
17/08/15 21:57:58 Manual VCO x
17/08/15 21:57:58 previously mathced Contact:x Contact No:x Contact Date:x Our Phone No: x
17/08/15 22:40:14 Manual unmatched
17/08/15 22:40:14 Unmatched Event generated for Host trade: H123 and Counterparty Trade: PHONE_CONFIRMED
17/08/15 22:42:21 Moved to Priority 'Host Unmatched' Queue
10/09/15 22:01:53 Matched Event generated for Host Trade H123 PHONE_CONFIRMED
10/09/15 22:01:53 Manual VCO x
10/09/15 22:01:53 Affirmations affirmed via email Contact:x Contact No:x Contact Date:x Our Phone No: x
29/09/15 15:09:23 Matched Event generated for Host Trade H123 confirm received
29/09/15 15:09:23 Manual counterparty sight paired confirm received
Ι have 3 columns in my table: col1, col2 and col3.
col2 is having (1, 0, 2, 3, 4, 0, 1, 3)
col3 is having (1234,1023,1025,1032,1234,1013,1325,1332)
Ι have to get col1 values as
(1234,1234,1234,1234,1234,1013,1013,1013).
Τhe logic is i have to get col3 value for col1 untill the same number repeated in column two.
Please give me any suggestion to solve this.
I have written a query that returns the data from SQL Server 2005, but I need a prompt for users (Excel) to select date range.
Looking for a quick help. In Sql, one of the table has a column(ntext). I need to find the count of spelling mistakes in the field. Association of MS Word dictionary would be great, however, can work with any other dictionary as well.
Any help on this would be great.
I want know the trick to create method which perform the Insert operation on the database based on n number of parameters
here is pseudocode definition
Insertdata(tablename,Col_name,Col_values)
add data to db
output: can be normal sql query string or insert operation on Database;
I have two servers; server A host SQL Server 2000 and server B ( a remote server) host SQL Server 2005. I replicate data between these two servers using transactional replication. I recently migrated (side by side) the SQL server 2000 to SQL Server 2008 hosted on a virtual server. I would like to mimic the replication I already have between server A abd B. Can someone tell me the best practice of carrying out this task? Essentially, what I want to do is to set up a transactional replication between the SQL Server 2008 hosted on a virtual server and SQL Server 2005 hosted server B ( a remote server) . Do I have to create all the publications and subscriptions from scratch ? Thanks for your contribution in advance.
I have a varchar column in sql server 2005 which stores time as 1420 but I want this to be shown as 14:20 or 02:20 PM in report. Please help..
I have a simple query with two tables. massfehler_hardware has 50K rows and no indexes. f_produkt has 38M rows with a clustered unique index on account_number, item_number, contract_number, [and some other columns]
Following query takes 11 seconds:
select count(distinct a.serial_number)
from massfehler_hardware a
join f_produkt f
on a.account_number = f.account_number
where f.product_code = 'VOD'
With two additional join conditions, it gets 5x slower:
select count(distinct a.serial_number)
from massfehler_hardware a
join f_produkt f
on a.account_number = f.account_number
and a.service_address_id = f.service_address_id
and a.outlet_location = f.outlet_code
where f.product_code = 'VOD'
Why is the impact of additional join conditions so extreme? Joining with only account_number already limits the number of results to approx 46K, so the additional join conditions (service_address_id and outlet_location) should only run on those 46K rows, and shouldn't increase the costs so much. What do I miss?
Strangely enough, when I compare the execution plans, the estimated relative cost of the faster running query is bigger! (57%)
Here are the two execution plans respectively:
Faster running query:
Slower running query:
I have two SQL2005 tables:
Table 1: LandingPages
Table 2: Products
The LandingPages table manages the heading, description and other details that are displayed on each of about 200 targetted landing pages. Each record also has a set of default Criteria fields that specify what products should be shown on each individual landing page.
So, a landing page about widgets with red tops and blue bottoms would have a record thus:
Each landing page record has a unique ID, called LPID.
I want a stored procedure that takes the LPID, reads the criteria and then returns a recordset of matching products.
What is the best way to achieve this for performance?
I have tried reading the various criteria into lots of variables, then doing a select using the variables in the WHERE of the SELECT.
I am trying to see if a CTE can be used / is better than my current method, but I'm not sure I can use the CTE within the WHERE of my SELECT.
-- Extra note: certain options can be changed by the user, such as maximum price and sorting.
Can anyone help? Thanks.
I have a table with columns PRNumber, PRLevel, Levelstatus and TransDate
Executed the following case expression,
Firstcase:
select PRNumber,PRLevel,Levelstatus,Trasdate,
case when Levelstatus = 'PUR'
then prlevel - 1
end as [PR_level]
from dbo.tblpr_approvalstatus where prnumber = '000005'
PRNumber PRLevel Levelstatus Trasdate PR_Level 000005 1 DM 2015-06-15 10:07:33.467 NULL 000005 2 PRE 2015-06-15 11:09:08.980 NULL 000005 4 IMM 2015-06-19 12:02:56.600 NULL 000005 5 PUR 2015-06-19 14:37:43.650 4 000005 3 FIN 2015-06-18 21:07:57.933 NULL
When Levelstatus is equal to PUR, I get the PrLevel 5 from case expression, I want to fetch the Trasdate of previous value of PRlevel in the case expression like as below
Secondcase:
select case when PRlevel = 4
then trasdate
end as [Transaction_date]
from tblpr_approvalstatus where prnumber = '000005'
I want to combine first case and second case expression as a single expression to fetch the Trasdate column values when Levelstatus = ‘PUR’ and previous value of PRLevel.
The columns PRLevel, Levelstatus and TransDate are having different values for each PRNumber.
I have a scenario where I have to match the system time, but I have to match the current time with a pre-set saved datetime variable in sql server.
DECLARE @SyncDate AS DATETIME
DECLARE @SystemDate AS DATETIME
SELECT @SyncDate = dbo.TimeTable.auto_time
FROM dbo.TimeTable
'1900-01-01 03:00
SELECT @SystemDate = GETDATE()
I have to replace the @SyncDate with current date and already saved time, i.e:
2015-11-16 03:00
Any body can help?
I have the following table, where I have aggregated the OrderNumber, Vendor, Item Description, Rate, Discount, Start and End Date. My end result needs to be one line item per Vendor PO with the Billed Weeks, Current Billed to Date and Forecast cost. I have been struggling with this for a while and cannot seem to find a way to do this.
Here is what the table currently looks like:
vendor OrderNo OrderDate Item Description Count rate unitdiscnt dayinweek startDate EndDate Billed Weeks JO SERVICES N2764 2015-04-29 5000 BASEPLATE 4 1.250 0.00 0.900 2015-04-29 2015-06-10 7 JO SERVICES N2764 2015-04-29 4100 PIGTAIL 24 0.250 0.00 0.900 2015-04-29 2015-06-10 7 JO SERVICES N2764 2015-04-29 4100 PIGTAIL 4 0.250 0.00 0.900 2015-05-06 2015-06-10 6 TIM JOEANNE N2760 2015-04-29 4290 STEEL CASTOR 8 6.000 0.00 0.900 2015-04-29 2015-06-10 7
In this case the two records of PigTail Should be a single line item, the Start and End Date does not have to appear in the table but the count with be 24+4, and the Actual Billed to Date will be From the Start Date until today's date (calculated in Billing Weeks * Count * rate - Discount amount). The forecast Date would be anything after today's date billed until the end date.
As an aside, I also need to create a graph that will give me the overall cost month by month From the beginning of the year until the end of the year.
How would I go about doing these both. I have been struggling with this for a couple of days and any direction would be extremely helpful and appreciated.
I'm trying to use the below code to bring though a count of all lines in the last 12 month period for the period and plant, please see the output below. So for example with the below output, rather than the 12 months column currently containing the total for the period, I want the count using a period between 201001-201101 (Please note, my example was only for the dataset below and the 12 months column needs to adapt for each period).
Period Plant Stock Special MonthTotal 12Months
201101 0EA0 27 0 27 27
201101 0EB0 35 2 37 37
The issue I'm having is that rather than bring through the last 12 month count, my code is merely bringing through the count for the current period. Please can someone assist?
select
convert(varchar(6),dateadd(mm,0,P.Dt),112) as Period,P.Plant,
Sum(Case When Left(Upper(Material),2) = 'ZZ' then 1 else 0 end) as Stock,
Sum(Case When Left(Upper(Material),2) <> 'ZZ' then 1 else 0 end) as Special
,Count(*) as MonthTotal,Sum(Case When
convert(varchar(6),dateadd(mm,0,P.Dt),112)
Between
convert(varchar(6),dateadd(mm,-12,P.Dt),112)
And
convert(varchar(6),dateadd(mm,0,P.Dt),112) Then 1 else 0 End
)as [12Months]
from
iesaonline.dbo.DS_POs as P where
Plant IN(
Select Client From METRICS.DBO.CO_001_Plants_090_Final
where CustGrp = 'Hovis'
)
Group by
P.Plant,convert(varchar(6),dateadd(mm,0,P.Dt),112)
order by
convert(varchar(6),dateadd(mm,0,Dt),112),Plant
I'm trying to add a column to the below code which brings though a count of all lines in the last 12 month period for the period and plant, please see the output below.
Period Plant Stock Special MonthTotal 12Months
201101 0EA0 27 0 27 27
201101 0EB0 35 2 37 37
The issue I'm having is that rather than bring through the last 12 month count, my code is merely bringing through the count for the current period. Please can someone assist?
select
convert(varchar(6),dateadd(mm,0,P.Dt),112) as Period,P.Plant,
Sum(Case When Left(Upper(Material),2) = 'ZZ' then 1 else 0 end) as Stock,
Sum(Case When Left(Upper(Material),2) <> 'ZZ' then 1 else 0 end) as Special
,Count(*) as MonthTotal,Sum(Case When
convert(varchar(6),dateadd(mm,0,P.Dt),112)
Between
convert(varchar(6),dateadd(mm,-12,P.Dt),112)
And
convert(varchar(6),dateadd(mm,0,P.Dt),112) Then 1 else 0 End
)as [12Months]
from
iesaonline.dbo.DS_POs as P where
Plant IN(
Select Client From METRICS.DBO.CO_001_Plants_090_Final
where CustGrp = 'Hovis'
)
Group by
P.Plant,convert(varchar(6),dateadd(mm,0,P.Dt),112)
order by
convert(varchar(6),dateadd(mm,0,Dt),112),Plant
I would like to only have one record returned for each VariableName, and it needs to be the highest revision number. I have tried GROUP BY and DISTINCT, but nothing seems to work.
I have this SQL Code
SELECT Docs.DocID
,Filename
,VName
,VText
,RevNo
FROM Docs
INNER JOIN VarVal ON Docs.DocID = VarVal.DocID
INNER JOIN Vars ON Vars.VarID = VarVal.VarID
WHERE Docs.DocID in (
select DocID
from Docs
INNER JOIN VarVal on Docs.DocID = VarVal.DocID
INNER JOIN Var on Var.VarID = VarVal.VarID
where VarName = '07'
and ValueText = '801016'
)
AND Docs.Deleted = 0
AND Var.IsDeleted = 0
ORDER BY VName
That produces this result
how can I verify that "Feature Pack for Microsoft SQL Server 2005 (December 2008 version - English)" is (or is not) installed on a computer? I need to know if the following features from that Feature Pack are installed:
(System is running SQL Server 2008 R2 on Windows Server 2008 R2)
Regards, Pelle
I know that this is very popular question and I found an answer to it, but not optimal.
So the task is - I need to know which of the employees are on vacation at the moment if start day of the vacation is bigger or equal than today and end date of the vacation is bigger or equal than today.
The problem is that dates of start and end of vacation are datetime so I can't compare it with getDate(). Varchar conversion of dates doesn't works also.
I solved the task by this way:
WHERE
(
YEAR (vac.e_dateTo) = YEAR (getDate()) AND
MONTH (vac.e_dateTo) = MONTH (getDate()) AND
DAY (vac.e_dateTo) >= DAY (getDate()) AND
DAY (vac.e_dateFrom) <= DAY (getDate())
)
but it seems to me that it can be done with cast/convert but I don't underestand how. Will be greatfull for any advise.
I have took a server now，I can't modify the source code.the service is to slowly.
IBM server windows 2008 sql server 2005 mem 16G CPU 8*1.2 300 people visit
There are more than fifty table database，The database each table has more than one hundred fields
How do I do to improve the performance of service？
Use two servers or other ways thanks
I am in the midst of upgrading to SQL Server 2012 from 2005 and came across a syntax error that I need help with. The syntax error is actually in a database that was built in 2000 so the compatibility issue cannot be fixed in the settings.
Select PPB.*,
SUBSTRING(STUFF((select license_id as k --*
FROM LMACatscan..license AS L
WHERE L.archived = 0
AND ((L.ppb_id IN (SELECT ppb_id FROM LMACatscan..ppb WHERE ppb_id = PPB.ppb_id))
OR (L.ppb_id_2 IN (SELECT ppb_id FROM LMACatscan..ppb WHERE ppb_id = PPB.ppb_id)))
FOR XML PATH('')),1,0,''),2,999999)
FROM LMACatscan..ppb AS PPB
WHERE state_code > ''
The results from 2005 are: 
I am looking for something like this.
find n consecutive free numbers but not exactly this.
As a part of performance tuning, i am looking for a better solution which will return only first 10 records when i click on the page index of a datagrid. The total record count will be more than 1 lakh but i dont want to process the whole set each time.
ie. When i click the pageindex "1" it should return records 1-10 whereas for "2"- 11-20 so on..
Hello today I have problem with database diagram when I to create new database diagram error had occurred "Attempted to read or write protected memory. This is often an indication that other memory is corrupt.". Can you help me to solve this problem?
Can anyone tell me what's causing this error? In SQL Server 2005, I am getting the error "Incorrect syntax near 'order'". I have isolated the error to the OVER clause (the error goes away when I remove the order by in the over clause). I have reviewed the documentation and searched for a solution but can't find what the problem is.
SELECT bill.[cust_id] , MONTH(bill.[del_date]), YEAR(bill.[del_date]),
SUM(SUM(ISNULL(bill.[sum_count], 0))) OVER (PARTITION BY bill.[cust_id]
ORDER BY YEAR(bill.[del_date]), MONTH(bill.[del_date])) AS QtyProcessed
FROM EnvelopeBilling bill
WHERE bill.[cust_id]= 1721
AND bill.[del_date] BETWEEN '5/1/2015' AND '10/31/2015'
GROUP BY bill.[cust_id], MONTH(bill.[del_date]), YEAR(bill.[del_date])
ORDER BY bill.[cust_id], YEAR(bill.[del_date]), MONTH(bill.[del_date])
Thanks for your help!!
I had seen the below thread which explains how the password validated in C#.
I want to do the same in sql stored procedure.
select * from Person.Password
how to validate the password against the values of password salt and password hash? which algorithm its using to encrypt the password?
I'm trying to install SQL 2005 via command line with an ini file.
The application i'm running with SQL 2005 requires a set SAPWD, however, this password does not meet our password policy.
I know there's a way to create the user with "CREATE LOGIN" and "CHECK_POLICY=OFF". But is there a way to make the setup.ini file ignore security policy as well?
i have to create a Application to send automatic sms to parents mobile no. when students punched IN or OUT from RFID attendance machine . please provide any solution or suggestions.
I am new to Microsoft SQL Server and am not sure how to connect via internet with PHP to retrieve some data from a table in a database instance in MS SQL Server in the server.
The main server is connected via hamachi with a few other outlets that will periodically update some data to the main server database
the "Server" is actually a desktop computer running on win 8 that is on 24/7
I got the server hostname ( which is the pc name/something ) and created a login account + password with Microsoft SQL Server Management Studio that can access the database I want locally.
Bu I have no idea how the http:// url looks like or how to connect to the DB via internet with PHP ...
or is it possible to connect with just the IP?
Do I need to do Port Forwarding + install PHP, apache, SQL FTP etc and setup the server?
Im trying to retrieve the month difference of two dates but it seems like i cant find a way to get the accurate months.
Here are the queries i tried so far :
SELECT DATEDIFF(month,convert(datetime, '11/05/2015'), convert(datetime, '12/06/2015')) - 1
This will result to 0 which is wrong and when i used another date :
SELECT DATEDIFF(month,convert(datetime, '12/31/2015'), convert(datetime, '01/01/2016')) - 1
This would yield to 1.
Leap year must also be considered.
I am trying to export a SQL Server 2005 database using ESF Database Migration Toolkit to .CSV, which I can then - hopefully - manipulate enough to create an Import file for importing into WordPress.
However, the current site is in Korean language and shall remain in Korean on the new server running MySQL.
After exporting all of the tables, I am finding columns where I would expect to find "some korean string" to contain "T???? ?????? ?????" - no Korean words in sight! I have tried exporting using ANSI, OEM and UNICODE options in ESF, each provides the same result.
Anyone?
I have string something like ACGI SHIPPING (3471) and i need to get 3471 number out of it.
If there is no number just print 0. in sqlserver
I like to request your help. I can get the results seperated but now i want to create a query which has it perfect for a external person. my explanation:
I have a statistics database with in this database a table when some records comes in and each records has several columns with values etc...
Now one of these columns is called "MT" MT Column can have only one of the following values per records: A,B,C,D,E The records also have a columne called TotalAmount which indicate a size of a value outside the database. This TotalAmount column is numeric without decimals and can have a value between 1 and 10.000. And the last part is the records it self, the table has X amount of records.
So Basicly i need to create a query which seperates each MT value and calculates the amount of records per MT and the sum of TotalAmount.
This is on SQL Server 2005.
Many thanks for your assistance!
I have a script that works fine in SQL Server 2008R2 but it couldn't work in SQL Server 2005. Is there any way that can work for both version?
Thanks!
declare @SQL nvarchar(max) = ''
select @SQL = @SQL + 'ALTER LOGIN ' + QUOTENAME(DisabledName) + ' DISABLE;'
from [SysAdmin].[dbo].[DisabledAccountHistory]
where DisabledName in (select name
from sys.server_principals
where is_disabled = 0)
group by DisabledName
exec sp_executesql @SQL
I need to fetch the second record for each employee that had events (access to building) in 3 consecutive days and only the oldest result for each employee.
Time is not taking into consideration.
My table look like this:
EMPID EVENTIME
4 2015-08-05 13:34:54.000
4 2015-08-19 16:29:32.000
4 2015-08-21 16:30:35.000
4 2015-08-24 13:51:25.000
4 2015-08-24 16:32:39.000
4 2015-08-26 13:48:32.000
4 2015-08-26 16:29:58.000
4 2015-08-27 16:30:07.000
4 2015-08-28 14:00:02.000
4 2015-08-28 16:29:09.000
19 2015-08-10 07:27:10.000
19 2015-08-10 15:18:51.000
19 2015-08-11 07:33:12.000
19 2015-08-11 07:33:16.000
19 2015-08-11 10:19:56.000
19 2015-08-11 15:49:12.000
19 2015-08-12 07:21:06.000
19 2015-08-12 10:37:53.000
19 2015-08-12 12:48:12.000
19 2015-08-12 14:11:25.000
19 2015-08-12 15:01:18.000
19 2015-08-13 07:25:38.000
21 2015-08-03 10:07:00.000
21 2015-08-10 08:00:41.000
And the desired results would look like this:
EMPID EVENTIME
4 2015-08-27 //first record that had an event 1 day before and 1 day after
19 2015-08-11 //first record that had an event 1 day before and 1 day after
Since access is controlled by proximity cards, and these are tested and then a few days later delivered to new employees, then the first record won't work, and the first record of these 3 consecutive days won't work either since cards are delivered in the afternoon missing events from the morning. That's why I need a query for the second record of the first 3 consecutive days.
This will give me a start date for each employee that I can use for reports.
I'm using mssql 2005.
Thank you!
Here is what I have.
SELECT sh.[No_], scl.[Date], scl.[Comment]
from [Service Header] sh left join [Service Comment Line] scl
on sh.[No_] = scl.[No_]
where sh.[No_] = 'SVO-16657'
It returns this:
NO. DATE COMMENT
SVO-16657 2015-10-02 00:00:00.000 PART IS READY FOR PICK UP.
SVO-16657 2015-10-15 00:00:00.000 CONVERSION KITS SHIPPED 10/19/15.
SVO-16657 2015-10-21 00:00:00.000 ALL OTHER MATERIAL SHIPPED 10/23/15.
SVO-16657 2015-10-02 00:00:00.000 READY FOR PICK UP.
SVO-16657 2015-10-15 00:00:00.000 CONVERSION KITS SHIPPING 10/16/15.
SVO-16657 2015-10-21 00:00:00.000 ALL OTHER MATERIAL SHIPPED.
I want it to read
SVO-16657 10/2/2015 PART IS READY FOR PICK UP | 10/15/2015 CONVERSION KITS SHIPPED 10/19/15 | 10/21/2015 ALL OTHER MATERIAL SHIPPED 10/23/15...AND SO ON
In an interview the below questions asked and couldn't provide an appropriate answer.
Question 1: if i have a table with employee id and manager i want to display it in the below format with employee name and manager.( the employee id 1 have manager null. You need to use self join only)
Question 2: if i have same result got for inner join ,right outer join and left outer join what type of content that table have?
Can anyone help me to find out answer for this?
I'm trying to pull one field from a view in my database. I determined there is data for this one instance, I correctly set up the SQLDataReader (I believe), the debugger verifies that I have rows in the DataReader, yet when I try to read I get an error.
Here's the code:
public string[] getReasons(string Accession) {
string[] reasonList = new string[0];
SqlParameter accNumber = new SqlParameter();
accNumber.SqlDbType = System.Data.SqlDbType.VarChar;
accNumber.ParameterName = "@Accession";
accNumber.Value = Accession;
string selectText = "select reason from pendingList where accession = @Accession";
SqlCommand selectStmt = new SqlCommand(selectText,toPending);
selectStmt.Parameters.Add(accNumber);
if (selectStmt.Connection.State == System.Data.ConnectionState.Closed) {
selectStmt.Connection.Open();
}
SqlDataReader pendList = selectStmt.ExecuteReader();
while (pendList.Read()) {
reasonList[reasonList.Length] = pendList["reason"].toString();
}
pendList.Close();
return reasonList;
}
I call getReasons('RAM4658980'). I've verified that the following SQL query
select reason
from pendingList
where accession = 'RAM4658980'
returns exactly one row. The pendList variable looks like this:
I'm not sure why I get "Enumeration yielded no results"; and at the reasonList[reasonList.Length] = pendList["reason"].toString(); step, I naturally get the "Invalid attempt to read ..." error. What am I missing?
I am extracting some wildcards from a string type column using certain keywords but for certain keywords in my list i am getting some false positives which I do not want in my output. Some of the keywords in my wildcard select is 'old', 'older' and 'age'
select * from DESCRIPTIONS..LONG
where (DESCR like '% old %'
or DESCR like '% older %'
or DESCR like '% age %'
or DESCR like '%old%'
or DESCR like '%older%'
or DESCR like '%age%')
I want to extract only rows that contain these absolute words but I end up returning strings that include 'management', 'image', 'cold', 'colder' etc. I could remove these true negatives by not looking for the below
DESCR like '%old%'
or DESCR like '%older%'
or DESCR like '%age%'
but in that process I am excluding rows that have special characters like period, comma, slash etc. which are true positives E.g. i would miss strings ending in 'age.' or 'old.' or 'older,' or 'age' when it is the last word in the string without a trailing space.
How do I exclude true negatives and false positives and only get all true positives?
here is a complete list of my keywords separated by a comma.
keywords: newborn, newborns, infant, infants, year, years, child, children, adult, adults, pediatric, old, older, young, younger, age
Thanks
I have string (VARCHAR(255)) that contains only zeros or ones.
I need to search all positions and return them as comma separated string. I've build two queries using solutions from http://ift.tt/1N84Dxq
Here is my code so far:
DECLARE @TERM VARCHAR(5);
SET @TERM = '1';
DECLARE @STRING VARCHAR(255);
SET @STRING = '101011011000000000000000000000000000000000000000';
DECLARE @RESULT VARCHAR(100);
SET @RESULT = '';
SELECT
@RESULT = @RESULT + CAST(X.pos AS VARCHAR(10)) + ','
FROM
( SELECT
pos = Number - LEN(@TERM)
FROM
( SELECT
Number
,Item = LTRIM(RTRIM(SUBSTRING(@STRING, Number, CHARINDEX(@TERM, @STRING + @TERM, Number) - Number)))
FROM
( SELECT ROW_NUMBER () OVER (ORDER BY [object_id]) FROM sys.all_objects
) AS n ( Number )
WHERE
Number > 1
AND Number <= CONVERT(INT, LEN(@STRING))
AND SUBSTRING(@TERM + @STRING, Number, LEN(@TERM)) = @TERM
) AS y
) X;
SELECT
SUBSTRING(@RESULT, 0, LEN(@RESULT));
DECLARE @POS INT;
DECLARE @OLD_POS INT;
DECLARE @POSITIONS VARCHAR(100);
SELECT
@POSITIONS = '';
SELECT
@OLD_POS = 0;
SELECT
@POS = PATINDEX('%1%', @STRING);
WHILE @POS > 0
AND @OLD_POS <> @POS
BEGIN
SELECT
@POSITIONS = @POSITIONS + CAST(@POS AS VARCHAR(2)) + ',';
SELECT
@OLD_POS = @POS;
SELECT
@POS = PATINDEX('%1%', SUBSTRING(@STRING, @POS + 1, LEN(@STRING))) + @POS;
END;
SELECT
LEFT(@POSITIONS, LEN(@POSITIONS) - 1);
I'm wondering if this can be done faster/better? I'm searching only for single character positions and I have only two characters that can occur in my string (0 and 1).
I've build two functions using this code, and run them for 1000 records and got same results in same time, so I can't tell which one is better.
for single record second part gives CPU and reads equals to 0 in Profiler, where first piece of code give me CPU=16 and reads=17.
I need to get result that looks like this: 1,3,5,6,8,9 (when multiple occurrences), 3 for single occurence, NONE if there are no ones.
Let's Assume I have DataTable /SQL Table that represents employee information for example
which contains firstname,lastname,age,company,yearsofexperience,Degree
I want to combine information based on firstname,lastname,age
company,yearsofexperience,Degree must be concat into corresponding cell
firstname lastname age company yearsofexperience Degree
john muller 21 IBM 4years MBA
jan tonny 22, MSoft 1years MS
martin tata 21 apple 2years PHD
john Muller 21 sony 3years MBA
james muller 21 IBM 4years PHD
jan tonny 22 Telsa 1years BS
martin tata 21 sun 2years MBA
james Muller 21 TCS 3years BS
Please find me way to remove the duplicate rows and make other data concat in particular column
For example in from above example I want combine the information present in the similar other 3 entries
firstname lastname age company yearsofexperience Degree
john muller 21 IBM,sony, 4years,3years, MBA,MBA
jan tonny 22, MSoft,Telsa 1years,1years MS,BS
martin tata 21 apple,sun 2years,2years PHD,MBA
james muller 21 IBM,TCS 4years,3years PHD,BS
Right I am looking for what is best ways to implement this
Its good approach if I Split the Tables in to 2 different tables? may be based on Primary key match. we can concat other entries ?
Please help me out thanks(+1) in advance
Don't ask me why, but I want to have a column where Identity is stored as varchar. Is it possible to assign this during creation or do I need to Scope Identity and Update?
Normally I would do this:
INSERT INTO [User]
(
-- id -- this column value is auto-generated,
useridcr,
dtcr,
varcharid
)
VALUES
(
25,
'2015-01-30 00:00:00.000'
''
)
SET @id = SCOPE_IDENTITY()
UPDATE [User]
SET
varcharid = @id
WHERE id = @id
But is it possible to know the value of new identity before it's created? So I could use the value in the insert statement.
Thank you