I have one table NewsEntities which contains one to many relationship.ie. One NewsId can have multiple EntityId.
Consider,NewsId=1 has two entities with EntityId 1 and EntityId 2 .there multiple newsid which can same or different EntityId .
Now I want to find those news in which EntityId=1 is Present but EntityId=2 is absent
I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
Our backup system has worked ok for us to date. We can restore back to either full saves or up to a certain log (we take log backups on the hour). We've never had to, but wanted to test restoring to a point-in-time with the backup data.
What the system does is generates .mdf and .ldf files, which is essentially a full backup say in the middle of the night. It then creates .bak files for the log backups based on the backup set you want to restore.
I can detach the database and apply the .mdf and .ldf and re-attach the database, but to apply the .bak files I need to get the database into a (recovering) state. I can't seem to do that. Otherwise when I try to apply the .bak files the system says: The log or differential backup cannot be restored because no files are ready to rollforward.
How to apply a .mdf and then apply the .bak files?
how to apply service pack when the databases are in replication?
View 2 Replies View RelatedI have a table (named table1) with 20million rows. It takes around 11 minutes to apply the primary key to this table. There are some tables with over 100 million rows so based on the previous time if my calculations are correct it will take close to an hour apply this primary key for tables with around 100 million rows.
My current solution is to create another table (named table2) with no indexs or primary keys. Pump over only like 5 days worth of data, then apply the primary key. Then have a script that will eventually populate table2 with the rest of the data gradually. When I say gradually I mean like insert like every 100k per hour or something. Keep in mind this table2 is heavily updated with new records.
Hi,
I am using nhibernate in my application.I am getting error when i try to insert date into our databse like
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
But in my databse this coulmn set as default value getdate().How to let the application in nhibernate to use default value which is set in databse.Could you please give me an odea on this.
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @PRODUCT)
GO
I m trying to execute
SQL Query in Excel vba,
Below is a query
SELECT stu_id, stu_
name, stu_class
FROM stu_table
WHERE stu_name IN
('JACK', 'WILLIAM') AND
stu_class IN ( 10,12)
But it doesn't work.does it possible to use multiple IN operator for different column ?
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
[Code] ....
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedA column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
I have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
I'm planning an upgrade to SQL Server 2012 and noticed the following.
We have a query that includes a duplicate column name in the SELECT statement. This query happens to work in a db we have that is set to SQL 2000 (80) compatibility level, but the same exact query fails when I set the db to SQL 2008 (100) compatibility level.
The error is (ColumnName is just an example):
Ambiguous column name 'ColumnName'
I have a question regarding indexes.
If i have cluster and NC index on same column,does it degrade performance on DML statements ? any advantage on select statements.
Is it good to have both indexes on same column ?
encrypting a column in a sql server database. I have userid and password columns that I think should have protection on them. My thought was to encrypt these 2 columns. What happens if someone needs to know the actual values of a userid and password? Is encryption the right way to secure this data?
View 2 Replies View RelatedThis should be a really simple, straight forward query, but apparently it's not. The error I get is "Invalid column name 'A'. I've put it in quotes and not in quotes and I left spaces around the equal sign and I've eliminated them. What's wrong with the SELECT statement below?
SELECT * from firms WHERE firm_id= 100002
AND active_firm = 1 AND firm_id != 100092
AND trust_id = A;
The identity int column in my sql2012 db is not incrementing properly.
I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:
1, 2, 3, 4, 5, 1004, 1005
This is happening on a random number of tables at very random times...
We have identity functionality for integer , Similarly i want for characters .i.e.
EX:
ID Name Sal
CD101 A 1000
CD102 B 2000
CD103 C 3000
CD104 D 4000
CD105 E 5000
I want output like above, I wont provide Value for ID column. It it should take automatically while inserting data into table.(like identity column)? Is it possible in SQL Server?
I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index.
I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
I have a table like below
create table #temp
(
valid_id int not null,
valid_no varchar(50)
)
10001
20002
30011
40012
i need to select the valid no without '0' lie 1,2,11,12, how do i?
We have a table with 10 columns each column is of datatype int and can accept nulls, when I do a save is it better to have 0 inserted in to the column or just insert null?
View 9 Replies View RelatedI have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.
UIDParam_ID Parameter Param_Threshold
1P1 Parameter1 NULL
4P2 Parameter2 NULL
6P3 Parameter3 NULL
7P4 Parameter4 NULL
8P5 Parameter5 NULL
9P6 Parameter6 NULL
The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.
UIDSiteIDP1P2P3P4P5P6P7P8
1721177503107NULLNULLNULL
27227129537710NULLNULLNULL
372897125851113NULLNULLNULL
4726881252997NULLNULLNULL
5727867502612NULLNULLNULL
673071312567107NULLNULLNULL
77214127535911NULLNULLNULL
I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.
UIDSiteIDParameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7 Parameter8
17211
27227
37289
47268
57278
67307
77214
I have attached the screenshot of the data for reference. I am not that much aware of sql queries.
[URL] .....
We have a large table with many columns and many indexes. One poorly performing query is having to do a key lookup when the where clause includes a particular column with no covering index.
Are you generally better off adding a new index or adding the column to an existing index ( included columns )Column: LAST_STATE_RESPONSE_CODE
The Query Processor estimates that implementing the following index could improve the query cost by 88.9332%.
*/
/*
USE [ database name]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SERVICE_REQUEST] ([BUSINESS_PROCESS_STATUS],[[color=#F00]LAST_STATE_RESPONSE_CODE[size="3"][/size][/color]],[CONCRETE_TYPE])
INCLUDE ([LIENHOLDER_PERFORMING_LIEN_FILING_ID],[MAKE],[YEAR],[MANUFACTURER_ID],[CLIENT_ID])
GO
Is there any possibility to trace, most used column in every table on the SQL server?
The intention is, a query which suggests some columns to be indexed; Based on the usage of the column by the queries that run on server.
Is it always the best practice to have the partition column also as the column for clustered index?
View 2 Replies View RelatedI am trying to select leveling; I have table like this;
FACC_WID FACC_BKEY FACC_CODE FACC_DESC FACC_CODE_DESC FACC_DESC_CODE FACC_H1_L5_CODE FACC_H1_L5_DESC FACC_H1_L5_CODE_DESC FACC_H1_L5_DESC_CODE FACC_H1_L4_CODE FACC_H1_L4_DESC FACC_H1_L4_CODE_DESC FACC_H1_L4_DESC_CODE FACC_H1_L3_CODE
[Code] ...
Now I need to make 5 levels like this:
1
10
100
1001
100100
When column FACC_CODE have 1 char then its a level one so I put this in FACC_H1_L1_CODE..
I this possible in (SSIS)?
i have written a query I was SO proud of but THEN was told to separate the values out of one column. simple way to do this without having to rewrite the whole query? Will I need to include a Case statement?
Basically the column contains several types of vacation codes which will need to be renamed to 2. So there is leave 1, leave 2, and leave 3 and sick. I will need to rename all leave 1, 2, and 3 to just vacation and leave sick as sick.