Can any one explain, how to configure SQL server in IBM blue mix for asp .net web application.
lundi 31 août 2015
vendredi 28 août 2015
Transpose columns to rows SQL Server
How to dynamically transpose some columns to rows if the columns I want to convert to rows all start with a prefix of 'c' in the column name. I have a table as follows
DECLARE @t codes
(
Tax CHAR(5),
ptype CHAR(2),
c1 CHAR(1),
c2 char(1),
c3 char(1)
)
insert into @t (tax, ptype, c1, c2, c3) values ('AAAAA','10',Null, 1,2)
insert into @t (tax, ptype, c1, c2, c3) values ('BBBBB','21',3, 1,NULL)
insert into @t (tax, ptype, c1, c2, c3) values ('ZZZZZ','1',NULL, NULL, 2)
insert into @t (tax, ptype, c1, c2, c3) values ('CCCCC',NULL,1,3,4)
insert into @t (tax, ptype, c1, c2, c3) values ('YYYYY','4',NULL, NULL, NULL)
insert into @t (tax, ptype, c1, c2, c3) values ('DDDDD','8',2,5,6)
How do I output the below where ptype is not 'NULL' and when c1,c2,c3 are not 'NULL' with C1,C2,C3 values sorted ascending?
Tax ptype Columns value
----- ----- ------- -----
AAAAA 10 c2 1
AAAAA 10 c3 2
BBBBB 21 c2 1
BBBBB 21 c1 3
ZZZZZ 1 c3 2
DDDDD 8 c1 2
DDDDD 8 c2 5
DDDDD 8 c3 6
jeudi 27 août 2015
How to list rows which has the same value in the first column but different in the second? (SQL)
I have a Data table like this:
Name Grade
Jim 5
Jim 4
Charlie 3
I would like to get another Result table like this:
Name Grades
Jim 5,4
Charlie 3
How can I list or sort out the grades of a child in one column?
mercredi 26 août 2015
Adding new parameter to stored procedure
I've done some Googling, but I can't seem to find an answer to what I'm looking for. Maybe my search terms are off. Here is my situation:
I have a stored procedure in my database that currently takes in and utilizes 11 parameters (all working great). I need to add a new parameter to this for a new column I added. We always explicitly define our columns in code, so there was no issue adding a column to the end of the table. However, if I add a new parameter in my stored procedure to populate this new column, will it throw an error back to my C# code if it isn't supplied, or will it default to null (or some other value) for the parameter?
Example C# code to call SP:
public static void InsertMailLog(string messageId, DateTime sentOrReceivedDate,
string fromAddress, string toAddress, string subject, string receivedMessage, string tailNumber,
string messageType, string direction, string sentOrReceived, string distributionList, ILogger AppEventLog, string filename = null)
{
List<string> lstParameterValues = new List<string>();
try
{
lstParameterValues.Add(messageId ?? "");
lstParameterValues.Add(sentOrReceivedDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
lstParameterValues.Add(fromAddress ?? "");
lstParameterValues.Add(toAddress);
lstParameterValues.Add(subject ?? "");
lstParameterValues.Add(receivedMessage ?? "");
lstParameterValues.Add(tailNumber ?? "");
lstParameterValues.Add(messageType ?? "");
lstParameterValues.Add(direction ?? "");
lstParameterValues.Add(sentOrReceived ?? "");
lstParameterValues.Add(distributionList ?? "");
lstParameterValues.Add(filename ?? ""); //THIS IS NEW, but it has not been published yet as the SP hasn't been updated.
CommonDAL.ExecSpNonQuery("spMailLogInsert", lstParameterValues);
}
catch (Exception ex)
{
CommonBLL.LogError(ex, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, "Error", messageId, tailNumber, messageType, "", Settings.Default.ContentProvider, AppEventLog);
}
}
Example SP:
ALTER PROCEDURE [dbo].[spMailLogInsert]
@SdMessageId varchar(50),
@SentOrReceivedDate datetime,
@FromAddress varchar(100),
@ToAddress varchar(100),
@Subject varchar(255),
@Message varchar(MAX),
@TailNumber varchar(50),
@MessageType varchar(50),
@Direction varchar(50),
@SentOrReceived varchar(50),
@DistributionList varchar(50),
@Filename varchar(50) --THIS IS NEW
AS
SET NOCOUNT ON
INSERT MailLog (
SdMessageId,
SentOrReceivedDate,
FromAddress,
ToAddress,
[Subject],
[Message],
TailNumber,
MessageType,
Direction,
SentOrReceived,
DistributionList,
Filename --THIS IS NEW
) VALUES (
@SdMessageId,
@SentOrReceivedDate,
@FromAddress,
@ToAddress,
@Subject,
@Message,
@TailNumber,
@MessageType,
@Direction,
@SentOrReceived,
@DistributionList,
@Filename --THIS IS NEW
)
I completely understand that this is a terrible use of a stored procedure. I should be using Entity Framework, but it's already written, and I have a project to update the entire project to use EF in the DAL at a later date (This is very old code). My question is, if I add the new parameter "Filename" to the stored procedure BEFORE the new C# code above gets published, will I get an error, or will the SP simply default to NULL? Or, if someone has a better way to default this to NULL or empty string, if it isn't supplied, I'm all ears.
.Net datetime with milliseconds sql server using bulkcopy thows exception
I have this code, which throws exception.
SqlConnection con;
con = new SqlConnection(connectionStr);
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = @"select [ID], [VERSION_ID], '08/26/2015 09:33:24:717 AM' as [Added_Dt], [Loc_ID] as Column1
from dbo.Source where Added_Dt between CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM') and CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM')";
cmd.CommandTimeout = con.ConnectionTimeout;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy(destconnectionStr, SqlBulkCopyOptions.UseInternalTransaction);
bcp.BatchSize = (int)Global.BatchWriteThreshold;
bcp.DestinationTableName = destinationTableName;
bcp.NotifyAfter = (int)Global.BatchWriteThreshold;
bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.WriteToServer(rdr);
destination table(SQL Server 2005): ID (int, not null), VERSION_ID (int, not null),Added_Dt(DATETIME,not null),Column1(varchar2(50) ,not null)
This throws exception if used with SqlBulkCopy but runs without any issues when using ado.net in single updates or directly from SQL Management Studio. I found that the removal of milliseconds part makes the bulkcopy run without exceptions but then I want the milliseconds to be there. How to resolve the issue?
What would be best way to show N number of column as per no of days in month
i want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID
and i want to just calculate no of days in month based on year and month name supplied by user.
one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column. i am not sure am i thinking in right direction. anyone can come up with suggestion or code sample to achieve it. thanks
returning multiple results set from union all query
I have got this SP to check whether the user is having any licenses that are stored in different tables..
I am getting results into dataset and from that dataset i am getting individual results with the count and if the count greater than zero then that user is having licenses.
This is the SP
ALTER PROCEDURE [dbo].[UserCheck]
(
@activatedBy varchar(30),
@brand varchar(20)
)
AS
BEGIN
DECLARE @acctId as BIGINT
SELECT @acctId = pk_acct_id from accounts with(nolock) where email = @activatedBy and brand = @brand
IF LEN(@acctId) > 1
BEGIN
SELECT count(*) from dbo.links with(nolock) where one = @acctId
union all
SELECT COUNT(*)FROM waveactivationinfo with(nolock) where Activated_by = @acctId
union all
SELECT COUNT(*) FROM ABCActivationInfo with(nolock) WHERE Activated_by = @acctId
union all
SELECT COUNT(*) FROM CSE_ActivationInfo with(nolock) WHERE activated_by = @acctId
union all
SELECT COUNT(*) FROM Connect_ActivationInfo with(nolock) WHERE activated_by = @acctId
union all
SELECT COUNT(*) FROM LicActivationInfo with(nolock) WHERE Activated_by = @acctId
END
END
GO
and then in DAL I am catching that results into dataset like this
public DataSet UserCheck(string strEmailID, string strBrand)
{
DataSet ds = new DataSet();
List<SqlParameter> ParaList = new List<SqlParameter>();
ParaList.Add(new SqlParameter("@activatedBy", strEmailID));
ParaList.Add(new SqlParameter("@brand", strBrand));
ds = SqlHelper.ExecuteDataset(new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString), CommandType.StoredProcedure, "UserCheck", Convert.ToInt32(Utility.GetConfigValue("Connection_TimeOut")), ParaList.ToArray());
return ds;
}
I am retrieving that dataset in code behind like this ...
DataSet ds = userDeactivate.UserCheck(txtEmailID.Text.Trim(), brandType);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
osCount = Int32.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());
waveCount=Int32.Parse(ds.Tables[0].Rows[1].ItemArray[0].ToString());
aCount = Int32.Parse(ds.Tables[0].Rows[2].ItemArray[0].ToString());
PassCount = Int32.Parse(ds.Tables[0].Rows[3].ItemArray[0].ToString());
quickCount = Int32.Parse(ds.Tables[0].Rows[4].ItemArray[0].ToString());
vmcCount = Int32.Parse(ds.Tables[0].Rows[5].ItemArray[0].ToString());
}
}
I am thinking that this will not be a good way to check whether the user is having licenses .. Is there any alternatives for this
Is there any way to simply return the codes from SP for each result set .. if i want to get all counts from all queries do i need to modify any code in DAL ...
mardi 25 août 2015
Calculate financial year start and end date based on year entered SQL Server
I have report, which takes YEAR as one parameter and I wanted to calculate the start and end of the financial year. Here is how I'm trying:
CREATE PROCEDURE [dbo].[sp_name]
@StartDate as datetime,
@Enddate as datetime,
@year as varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
@StartDate = year(dateadd(q, -1, cast(cast(@year AS char) + '/01/' + cast(@year AS char) AS datetime))) = @year
Is this the correct way to do this?
I need financial start date as 1-July-2014 to 30-June-2015, if year entered as 2015.Please note that, this I need internally to be calculated in script. If I'm doing something wrong, how can I correct this to get desired results?
2005 SQL Job fails to execute when manually selected from SSMS
Just odd problem... Situation, my department uses SSMS 2012 to connect to multiple SQL servers, in multiple versions (2005, 2008, 2012)
A few weeks ago we had security patches sent to our PC's, and now a very strange issue has started, affecting all five of us....
When we connect to the 2005 SQL servers with SSMS, we can see the Job Agent. We can view the properties and History with no issues at all. We can edit various settings of the job. However, when we try to right click a job and execute it with "Start Job at Step" (which has worked for years!) we now get an error.
Microsoft.SqlServer.Smo ----------------------- Start failed for Job 'DashBoard_Execute_Measure_Items'. ADDITIONAL INFORMATION: |> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) |> A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64) |> The specified network name is no longer available
The odd part is that we really are still connected, I can open queries, edit things, save them, etc..
Just cannot run the job manually. The jobs do continue to run correctly when called by the Agent on schedule. AND if we do a remote desktop login to the server, pull up SSMS on the remote desktop, then click the same job and manually run, it works just fine!
This happens for all members of my department, and for all jobs on 2005 servers. we can manually run jobs on 2008 & 2012 servers just like always.
I now have several dents in the sheetrock next to my computer from trying to figure this one out.
Any ideas?
T-SQL 2005: Add Date and Time
I have two fields which are both Datetime type:
Date = '2011-1-01 00:00:00.000'
Time = '1900-01-01 3:31:19.000'
The '1900-01-01' is the default value so I am interested only in the time part (3:31:19.000).
All what I want is to combine Date and time together. So, for this example i want a query which would give me the result:
Combined = '2011-1-01 3:31:19.000'
I am using T-SQL 2005.
count hours in same column
i'm trying to query a table that contains employee records of presence. It records the employee id and datetime of the chipping as follows:
id datetime
3 2015-07-01 06:58:00.000
3 2015-07-01 12:01:00.000
3 2015-07-01 12:57:00.000
3 2015-07-01 19:17:00.000
3 2015-07-02 06:55:00.000
3 2015-07-02 14:05:00.000
15 2015-07-01 07:50:00.000
15 2015-07-01 12:01:00.000
15 2015-07-01 12:50:00.000
15 2015-07-01 18:04:00.000
i was trying to produce a query that gets
id date entrance exit 2entrance 2exit
3 2015-07-01 06:58 12:01 12:57 19:17
3 2015-07-02 06:55 14:05 00:00 00:00
15 2015-07-01 07:50 12:01 12:50 18:04
but the final result i'm looking is
id d1, d2, d3.. d31
3 11 7 12 6
15 9 0 6 12
(this is the number of worked hours, day by day, for a full month)
to get this table i was thinking to use excel with previous results, but i'd apreciate if someone pointed me in the right direction.
Thanks for your help
Add columns to query based on previous queries
I have two tables:
+-----------+
| Customer |
+-----------+
| ID | Name |
+----+------+
| 1 | Jack |
+----+------+
| 2 | John |
+----+------+
+----------------------------------------+
| Bill |
+----------------------------------------+
| ID | Customer_ID | date | amount |
+----+-------------+------------+--------+
| 1 | 1 | 01.01.2015 | 10$ |
+----+-------------+------------+--------+
| 2 | 1 | 01.01.2014 | 20$ |
+----+-------------+------------+--------+
| 3 | 2 | 01.01.2015 | 5$ |
+----+-------------+------------+--------+
| 4 | 2 | 01.02.2015 | 50$ |
+----+-------------+------------+--------+
| 5 | 2 | 01.01.2014 | 15$ |
+----+-------------+------------+--------+
I need to know the sum of all the bills a customer got in a year.
That's pretty easy:
SELECT
SUM(Bill.amount), Customer.Name
FROM
Customer
INNER JOIN
Bill ON Customer.ID = Bill.Customer_ID
WHERE
Bill.date BETWEEN #20150101# AND #20151231#
GROUP BY
Customer.Name
The difficult part is that i need to display the results of that query for multiple years in a single table like this:
+-------------------------------------------+
| sales to customer |
+-------------------------------------------+
| Customer_ID | Customer_Name | 2015 | 2014 |
+-------------+---------------+------+------+
| 1 | 1 | 10$ | 20$ |
+-------------+---------------+------+------+
| 2 | 1 | 55$ | 20$ |
+-------------+---------------+------+------+
I'm using SQL Server 2005.
I'm very grateful for every answer.
sincerly Andahari
lundi 24 août 2015
How do I remove a quote(") from SQL string before or after inputting it into a column?
I have split a name value in one column to get the first and last name to supply in another column. Sometimes I get a quote(") in the front of or beginning of someones name. How do I remove that quote?
I've tried pragmatically but it kicks back error at the set commands for @fname and @lname.
DECLARE @fname VARCHAR(100)
DECLARE @lname VARCHAR(100)
DECLARE @recnum as INT
DECLARE @i as int
SELECT @recnum = count(*) FROM dbo.medcor_weeklywire
SET @i = 1
WHILE @i <=@recnum
BEGIN
SELECT @fname = LTRIM(jw_employee_fname) where [sysid] = @i
SELECT @lname = LTRIM(jw_employee_lname) where [sysid] = @i
SET @fname = SELECT REPLACE(@fname,'"','')
SET @lname = SELECT REPLACE(@lname,'"','')
update [medcor_weeklywire]
SET [jw_employee_fname] = @fname, [jw_employee_lname] = @lname
END
T-SQL - Remove All Duplicates Except Most Recent (SQL Server 2005)
This problem is beginning to drive me crazy and I have been unsuccessful at locating an easily adaptable answer for what I'm trying to do. The basic idea is that I have a T-SQL function that will pull all records inserted into a main table within the last 60 minutes and insert them into a table variable. I've then got some more code that will filter that set into another table variable to be returned.
In this set I'm expecting some records to have multiple occurrences but they will have a unique date time. I would like to delete every record that has greater than or equal to 3 occurrences, but keep the one with the most recent datetime value. So far I've set up a CTE that manages to delete all the records with a count greater than or equal to 3, but this does not leave the record with the most recent occurrence.
WITH cte AS (
SELECT ColA, ColB, /*DateTimeColumn,*/ ColC, ROW_NUMBER() OVER (PARTITION BY ColA, ColB, /*DateTimeColumn,*/ ColC ORDER BY ColA /*,DateTimeColumn*/) AS r_count
FROM @table_variable_2
)
DELETE
FROM cte
WHERE r_count >= 3 -- I think I need an AND NOT IN subquery here
Any advice would be greatly appreciated!
Update column of different table based on manupulated result of another table
I have a table1 like below having more than 400k records.Below I'm just showing example, how it look likes. I want to write some query to itterate throgh each records and find its corresponding date; if all dates are present for the account ID , then I have to update another table2 "Yes". And, if any one of the date is null (for any account ID) then I have to update table2 flag as "No".This is my source table1.
Table1
Account ID | Date
----------- |------------
1 | 12-03-2015
1 | 11-03-2015
1 | 11-04-2015
1 | 01-03-2015
2 | 06-03-2015
2 | 11-03-2015
2 | Null
2 | 01-03-2015
This is how the table2 result will look like (after query execution)
Table2
Account ID | Flag
-----------|------
1 | Yes
2 | No
2ndly, if after few days, Date of Account ID is changed from Null to an actual date (say 07-07-2015), then Table2 "Account ID 2" value should change from "No" to "Yes". So after few days the Table2 should look like this
Account ID | Flag
-----------|------
1 | Yes
2 | Yes
Hope I have explained it correctly.I'm thinking to use cursor, however, I'm not sure how to use this and will cursor really solve this problem? Is there any other ways to achieve this? Checked on net, however, not able to get suitable solution, please help?
dimanche 23 août 2015
How to get script for creazione tables without constraints?
I'm looking for a tool or some way to generate tables scripts without constraint for a SQL server 2005 DB. My goal is make a snapshot of DB having one script file for each table with create and insert statements. I want to create a new DB just using these script files, but in order ti run them, the table scripts must not have any constraints.
Thanks Dom
selecting values from multiple records in one row but different columns in SQL
I have a table as follows
Ident type id value
A CP 1 1000
A CP 2 EXIST
A GVI 1 100
A GVI 2 NOT EXIST
I need to view them as follows
Ident type value( id=1) value(ld=2)
A CP 1000 Exist
A GVI 100 NOT EXIST
Any idea how to do it?
SQL Server conditional query
I have a table for attendance entries looking like this :
I need a query to export the following format :
which present the Check-in and Check-out timings using British/French format (103)
I tried the following query :
SELECT UserID,
(Select Min(checktime) from [FingerPrint].[dbo].[CHECKINOUT] I where CONVERT(VARCHAR(10),i.checktime,111) = CONVERT(VARCHAR(10),p.checktime,111) and i.userid = p.userid ),
(Select Max(checktime) from [FingerPrint].[dbo].[CHECKINOUT] I where CONVERT(VARCHAR(10),i.checktime,111) = CONVERT(VARCHAR(10),p.checktime,111) and i.userid = p.userid)
FROM [FingerPrint].[dbo].[CHECKINOUT] p
GROUP BY p.checktime,p.UserID
Basically I need a query to select the minimum time (check-in) and maximum time (check-out) for each day using the export format above, yet when there is no value for check-in and check-out, then query should return (null) for time.
samedi 22 août 2015
How to setup SQL SERVER 2012 Configurations
Installed SQL SERVER 2012 for the very first time. Anybody help to set up windows or Sql Server authentication to use because it shows server it not accessible. Thank you
vendredi 21 août 2015
Backup Principle database in SQL Server
we have set up database mirroring SQL 2005. can i take full backup principle database ? does it break data mirroring if i will take full backup of Principle database ?
jeudi 20 août 2015
Grant permissions to user for backup/restore in SQL Server
Can anyone tell me what permissions must i give to a user inside SQL Server 2005 so that this user can backup and restore a database without given him sysadmin server role ?
Inner Join dataset error in VB.net 2010
i'm trying to fill a dataset with this code
Dim objConexion As SqlConnection
objConexion = New SqlConnection
objConexion.ConnectionString = "Server=PILLAN\LATIN1;Database=Control;User Id=sa;Password=***;"
'
' Crear adaptador
Dim Da As New SqlDataAdapter("SELECT TOP (5) CONVERT(Varchar(13), FloatTableCaldera.DateAndTime, 103) AS Fecha, CONVERT(varchar(13), FloatTableCaldera.DateAndTime, 108) AS Hora, FloatTableCaldera.Val AS Valor, FloatTableCaldera.Millitm AS Militm, Equivalencia.Equivalencia as Indicador FROM dbo.FloatTableCaldera INNER JOIN Equivalencia ON FloatTableCaldera.TagIndex = Equivalencia.TagIndex ORDER BY FloatTableCaldera.DateAndTime DESC", objConexion)
'
' Crear conjunto de datos
Dim ds As New DataSet
objConexion.Open()
'
'utilizar el adaptador para llenar el dataset con una tabla
Da.Fill(ds)
objConexion.Close()
When i try to run in VS 2010 return this error.
Invalid object name 'FloatTableCaldera'.
But In Sql Server Managment i run the same sql without problem. Please anyone can help me because i don't see the error.
"Cannot convert varchar to numeric" or "string or binary data would be truncated"
I am trying to do an insert and I am receiving the error when trying to add in an additional clause on my where statement using SS#. The table I am copying from is all varchar columns and it works properly until I add in the line to check that the SS#'s match. I understand getting the convert error so I tried using the convert function in the subquery and then I get the truncate error.
--Grab the max record_serial_number from DESTINATION
declare @rsnOwner numeric
set @rsnOwner = (select max(RECORD_SERIAL_NUMBER)
from DESTINATION)
--Insert values from TEMP to DESTINATION
insert into DESTINATION
(
RECORD_SERIAL_NUMBER,
AGENT_NUMBER,
SS_NO,
LAST_NAME_OWNER,
FIRST_NAME_OWNER,
TITLE,
BIRTHDAY,
HOME_STREET_ADDRESS,
HOME_TOWN,
HOME_STATE,
HOME_ZIP_CODE,
HOME_PHONE,
BANKRUPT,
MODIFY_DATE,
MODIFY_TIME,
USER_ID,
SHARE_PCT,
CELL_PHONE,
CONTACT_TYPE,
CONTACT_DESC,
CH_APPROVED,
CH_DATE,
ADDRESS_VERIFY,
VERIFY_DATE
)
Select
@rsnOwner + ROW_NUMBER() over (order by agent_number),
convert(numeric, AGENT_NUMBER),
convert(numeric, replace(ss_num,'-','')), --remove dashes and convert
CONTACT_LAST_NAME,
CONTACT_FIRST_NAME,
CONTACT_TITLE,
case when isdate(DOB)=1 then convert(datetime, '19'+right(DOB, 2)+left(DOB, 2)+substring(DOB,3,2)) else null end, --rearrange the 6 digit date to 8 digit in YYYYMMDD format
ADDRESS1,
CITY,
STATE,
case when isnumeric(ZIP_CODE)=1 then convert(numeric, ZIP_CODE) else null end,
case when isnumeric (CONTACT_PHONE_NUM)=1 then replace(convert(numeric, CONTACT_PHONE_NUM),'-','') else null end, --remove dashes and convert
')', --BANKRUPT
convert(datetime, GetDate()) as MODIFY_DATE,
replace(Convert (varchar(8), GetDate(), 108),':',''),
'dbo_update', --USERID
cast(PERCENT_OWNERSHIP as numeric (5,2)) as PERCENT_OWNERSHIP,
case when isnumeric(CONTACT_CELL_NUM)=1 then replace(convert(numeric, CONTACT_CELL_NUM),'-','') else null end, --remove dashes and convert
TYPE,
TYPE_DESC,
CH_APPROVED,
case when isdate(CH_DATE)=1 then convert(datetime, '20'+right(CH_DATE, 2)+left(CH_DATE, 2)+substring(CH_DATE,3,2)) else null end, --rearrange the 6 digit date to 8 digit in YYYYMMDD format
ADD_VERIFICATION,
case when isdate(ADD_VERIFICATION_DATE)=1 then convert(datetime, '20'+right(ADD_VERIFICATION_DATE, 2)+left(ADD_VERIFICATION_DATE, 2)+substring(ADD_VERIFICATION_DATE,3,2)) else null end --rearrange the 6 digit date to 8 digit in YYYYMMDD format
from TEMP as tmp
where NOT EXISTS(select NULL
from DESTINATION as dest
where tmp.AGENT_NUMBER = dest.AGENT_NUMBER
and
convert(numeric, replace(SS_NUM,'-','')) = dest.SS_NO
)
The tables do not have primary keys and it is a many to many relationship using either agent_number or SS# but the two of them together is unique. I also tried creating a variable for SS_NUM so I can use it in both the select and the where clause but apparently I can't do that and would have to create variables for everything in my select.
SPROC that returns unique calculated INT for each call
I'm implementing in my application an event logging system to save some event types from my code, so I've created a table to store the log type and an Incremental ID:
|LogType|CurrentId|
|info | 1 |
|error | 5 |
And also a table to save the concrete log record
|LogType|IdLog|Message |
|info |1 |Process started|
|error |5 |some error |
So, every time I need to save a new record I call a SPROC to calculate the new id for the log type, basically: newId = (currentId + 1). But I am facing an issue with that calculation because if multiple processes calls the SPROC at the same time the "generated Id" is the same, so I'm getting log records with the same Id, and every record must be Id-unique.
This is my SPROC:
ALTER PROCEDURE [dbo].[usp_GetLogId]
@LogType VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
DECLARE @IdCreated VARCHAR(MAX)
IF EXISTS (SELECT * FROM TBL_ApplicationLogId WHERE LogType = @LogType)
BEGIN
DECLARE @CurrentId BIGINT
SET @CurrentId = (SELECT CurrentId FROM TBL_ApplicationLogId WHERE LogType = @LogType)
DECLARE @NewId BIGINT
SET @NewId = (@CurrentId + 1)
UPDATE TBL_ApplicationLogId
SET CurrentId = @NewId
WHERE LogType = @LogType
SET @IdCreated = CONVERT(VARCHAR, @NewId)
END
ELSE
BEGIN
INSERT INTO TBL_ApplicationLogId VALUES(@LogType, 0)
EXEC @IdCreated = usp_GetLogId @LogType
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(MAX)
SET @ErrorMessage = ERROR_MESSAGE()
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
RAISERROR (@ErrorMessage, 16, 1)
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
SELECT @IdCreated
END
I would appreciate your help to fix the sproc to return an unique id on every call.
mardi 18 août 2015
Convert string to integer and update into other field
I have a table that has the following structure
[zip] = <zip, nvarchar(4),>
[cityZipID] = <cityZipID, int,>
In the zip column there is a string containing 4 digits and this is a number between 1000 an 1239 gut stored as a string.
For some reason I need to calculate an other value out of this so I need to convert the string into an integer and store it into an other column called cityZipID. I want to do this using SQL Server Management Studio because it has to convert about 32000 lines so I cannot easily do it by hand.
I tried the following but get only an error message when trying to execute it
UPDATE [MyTestData].[dbo].[Addresses]
SET [cityZipID] = ((int)[zip])/10 -100
WHERE [city] = 'Wien'
The column of cityZipID is null in the moment and should be filled with numbers for the districts like the plzl for the first district is 1010 the 12th district is 1120 So the calculation would result in 1120 / 10 = 112 -100 = 12 and this would be the wanted result.
Any help would be appreciated.
Jonny
Access SQL Server via terminal services to load data warehouse
I have access to a SQL Server database only via terminal services. Can I connect to this as a load source for a local data warehouse?
Cheers,
Using SELECT with a display condition
SELECT DISTINCT Invoice.InvNo, Invoice.OrderNo, Part.PartNo, orders.orddate AS Order_Date, Invoice.InvDate AS Bill_Date, MiscChg.Descr, MiscChg.RegFee, Invoice.InvAmt, Orders.ClaimNo, Firm.FirmName AS Ordering_Firm, oppatty.attyid(WHERE oppatty.attyfor = 13), Location.Name1 AS Location
The bolded section is the part I'm having trouble with. I know what I have isn't right, but it demonstrates what I would like to accomplish. In the oppatty table, there could be several items listed. I want it to only display "AttyID for the entry that has an ATTYFOR = 13".
Hope this make sense, thanks
Jack
dimanche 16 août 2015
skip records based on columns condition
Hi I have one querstion in sql server
table name : Emp
Id |Pid |Firstname| LastName | Level
1 |101 | Ram |Kumar | 3
1 |100 | Ravi |Kumar | 2
2 |101 | Jaid |Balu | 10
1 |100 | Hari | Babu | 5
1 |103 | nani | Jai |44
1 |103 | Nani | Balu |10
3 |103 |bani |lalu |20
Here need to retrive unique records based on id and Pid columns and duplicate records need to skip. Finally I want output like below
Id |Pid |Firstname| LastName | Level
1 |101 | Ram |Kumar | 3
2 |101 | Jaid |Balu | 10
3 |103 |bani |lalu |20
I found duplicate records based on below query
select id,pid,count(*) from emp group by id,pid having count(*)>=2
this query get duplicated records 2 that records need to skip to retrive output
please tell me how to wirte query to achive this task in sql server .
Why the following SQL-Server query gives 12 months data and not 14 months data
I have the following query which gives me data for 12 months. Originally the query was for 14 days and I changed the day to month in datediff method to get 14 months data but I am getting only 12 months data. Can any one please check and see why? The output table is attached below.
ALTER procedure [dbo].[spGetplayingTimeOftheplayersPerMonth_Updated_6_August_lastMonths]
@email nvarchar(50)
AS
Begin
Set Nocount On;
Declare @MinDate Date
,@MaxDate Date
,@LastXMonths Int
,@ForLast14Date Date
Select @LastXMonths = -14
Select @ForLast14Date = players.last_update from players where players.email=@email
--Select @MaxDate = DateADD(DAY, 14, @ForLast14Date)
Select @MaxDate = DateADD(Month, 15, Dateadd(Month, -14, @ForLast14Date))
Declare @test Table
(
activity Varchar(100)
,date Date
,TimePerDay Decimal(5,2)
)
;WITH CTE AS
(
SELECT players.email, players.last_update, Activities.activity, activities.starttime, activities.endtime, Activities.duration as [Totaltime] from players
inner join Movesplayers ON players.Id=Movesplayers.parent_id
inner join Storylines ON Movesplayers.id=Storylines.movesuser_id
inner join Segments ON Storylines.id=Segments.storyline_id
inner join Activities ON Segments.id=Activities.segment_id
--WHERE activities.activity='cricket' and (players.email=@email)
WHERE activities.activity='playing' and (players.email=@email)
GROUP BY players.email, activities.activity, activities.duration, activities.starttime, activities.endtime, players.last_update
)
Insert Into @test(activity,date,TimePerDay)
Select activity
,Cast(starttime as date) As date
,SUM(datediff(second, starttime, endtime))/60.0 As TimePerDay
From cte With (Nolock)
--where starttime >= dateadd(day, @LastXDays, last_update)
where starttime >= dateadd(Month, @LastXMonths, Dateadd(Month, -14, @ForLast14Date))
group by activity
,cast(starttime as date)
--Select @MaxDate = Getdate()
Select @MaxDate = @MaxDate
,@MinDate = dateadd(Month, (@LastXMonths + 1), @MaxDate)
;With AllDates As
(
Select @MinDate As xDate
Union All
Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)
Select 'playing' As activity
--,ad.xDate
,min(ad.xDate) As xDate
,Isnull(sum(t.TimePerDay),0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
GROUP BY datepart(Month, ad.xDate)
--ORDER BY YEAR(datepart(Month, ad.xDate)) DESC, MONTH(datepart(Month, ad.xDate)) DESC, DAY(datepart(Month, ad.xDate))
ORDER BY MIN(ad.xDate)
option (maxrecursion 0)
END
vendredi 14 août 2015
Need SQL server join Query
I have three tables TBL_SUBJECT TBL_SEMESTER TBL_SUBJECT_SEMESTER_MAPPING I am having "subjectId", with this id, I want to get All the subjects of the semester to which that subject belongs.
need Query with joins SQL server..
Error converting data type varchar to numeric and varchar to datetime
Error converting data type varchar to numeric
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
declare @rsnMain numeric, @rsnAcnt numeric, @rsnRel numeric;
set @rsnMain = (select max(record_serial_number)
from sdg_aaismain)
set @rsnAcnt = (select max(record_serial_number)
from sdg_aaisacnt)
set @rsnRel = (select max(record_serial_number)
from sdg_aaisrel)
insert into sdg_aaismain
(RECORD_SERIAL_NUMBER,
AGENT_NUMBER,
AGENT_STATUS_CODE,
AGENT_STATUS_DESP,
TRADE_NAME,
GROUP_TYPE_1,
MADE_ON_BEHALF,
MADE_ON_BEHALF_DESP,
LICENSE_RENEWAL_CODE,
LICENSE_RENEW_DESP,
ACTUAL_NO_STREET,
ACTUAL_TOWN,
ACTUAL_STATE,
ZIP_CODE_PREFIX,
ZIP_CODE_SUFFIX,
CITY_CODE,
DISTRICT_CODE,
COUNTY_CODE,
MUNICIPALITY_CODE,
ACTUAL_DISTRICT,
ACTUAL_COUNTY,
ACTUAL_MUNICIPALITY,
CONTACT_LAST_NAME,
CONTACT_FIRST_NAME,
STORE_OPEN_HOURS#1,
STORE_OPEN_HOURS#2,
STORE_OPEN_HOURS#3,
STORE_OPEN_HOURS#4,
STORE_OPEN_HOURS#5,
STORE_OPEN_HOURS#6,
STORE_OPEN_HOURS#7,
STORE_CLOSE_HOURS#1,
STORE_CLOSE_HOURS#2,
STORE_CLOSE_HOURS#3,
STORE_CLOSE_HOURS#4,
STORE_CLOSE_HOURS#5,
STORE_CLOSE_HOURS#6,
STORE_CLOSE_HOURS#7,
MAILING_ADDRESS,
MAILING_CITY,
MAILING_STATE,
MAILING_ZIP_CODE_PREFIX,
MAILING_ZIP_CODE_SUFFIX,
CREATE_DATE,
MODIFY_DATE,
MODIFY_TIME,
USER_ID,
AGENT_TYPE,
NAIC_CODE,
CONTRACT_TYPE,
LONGITUDE,
LATITUDE,
CORP_NAME,
DSR,
FEDTAX_NAME,
FEDTAX_ID,
TIN_TYPE,
NAME_CNTL,
ES_GROUPTYPE,
ADDRESS2,
--CORPORATION_NUMBER)
INSTALL_DATE)
select
@rsnMain + ROW_NUMBER() over (order by agent_number),
AGENT_NUMBER,
AGENT_STATUS_CODE,
AGENT_STATUS_DESC,
TRADE_NAME,
cast(right(CHAIN_PARENT_RETAIL_NO,3) as numeric(3,0)) as GROUP_TYPE_1,
MADE_ON_BEHALF,
MOB_DESC,
LICENSE_RENEWAL_CODE,
RENEWAL_STATUS_DESC,
ACTUAL_NO_STREET,
ACTUAL_TOWN, ACTUAL_STATE,
cast(ZIP_PREFIX as numeric (5,0)) as ZIP_PREFIX,
cast(ZIP_SUFFIX as numeric (4,0)) as ZIP_SUFFIX,
CITY_CODE,
cast(DISTRICT_CODE as numeric (3,0)) as DISTRICT_CODE,
cast(COUNTY_CODE as numeric(3,0)) as COUNTY_CODE,
MUNICIPALITY_CODE,
ACTUAL_DISTRICT,
ACTUAL_COUNTY,
ACTUAL_MUNICIPALITY,
CONTACT_LAST_NAME,
CONTACT_FIRST_NAME,
STORE_OPEN_HOURS#1,
STORE_OPEN_HOURS#2,
STORE_OPEN_HOURS#3,
STORE_OPEN_HOURS#4,
STORE_OPEN_HOURS#5,
STORE_OPEN_HOURS#6,
STORE_OPEN_HOURS#7,
STORE_CLOSE_HOURS#1,
STORE_CLOSE_HOURS#2,
STORE_CLOSE_HOURS#3,
STORE_CLOSE_HOURS#4,
STORE_CLOSE_HOURS#5,
STORE_CLOSE_HOURS#6,
STORE_CLOSE_HOURS#7,
MAILING_ADDRESS,
MAILING_CITY,
MAILING_STATE,
cast(MAIL_ZIP_PREFIX as numeric (5,0)) as MAIL_ZIP_PREFIX,
cast(MAIL_ZIP_SUFFIX as numeric (4,0)) as MAIL_ZIP_SUFFIX,
convert(datetime, CREATE_DATE) as CREATE_DATE,
convert(datetime, GetDate()) as MODIFY_DATE,
replace(Convert (varchar(8), GetDate(), 108),':','') as MODIFY_TIME,
'DAILY UPDATE',
AGENT_TYPE,
NAIC_CODE,
cast(CONTRACT_TYPE as numeric (1,0)) as CONTRACT_TYPE,
LONGITUDE,
LATITUDE,
CORP_NAME,
DSR,
FEDTAX_NAME,
FEDTAX_ID,
TIN_TYPE,
NAME_CONTROL,
cast(ES_GROUPTYPE as numeric (6,0)) as ES_GROUPTYPE,
ADDRESS2,
--cast(CORPORATION_NUMBER as numeric (11,0)),
convert(datetime, INSTALL_date)
from sdg_tmp_aaismain as tmp
where NOT EXISTS(select NULL
from sdg_aaismain as dest
where tmp.agent_number = dest.agent_number
)
The above code is a sample of a longer update statement. I have 2 columns giving me the "Error converting data type varchar to numeric" error. This is because there is character data for some records.
The out of range error is occurring for 4 different varchar columns with dates which all have '00000000' as a value for some of the rows.
How do I go about excluding the character data and the zeroes from my select query?
Thanks, Scott
jeudi 13 août 2015
how to check the datatypes in two diff tables in different server
I have the same table in PROD and TEST ( test has no data) and when i am trying to import the data from prod to test it gives me an error, i want to make sure if the data types are similar in both tables.. is there a way we could find that out.
Thanks
"Insert trigger" - update column after insert
CREATE TRIGGER [dbo].[TR_dbo_GlLoan_LoanNumber] ON [dbo].[GlLoan] AFTER INSERT
AS
BEGIN
if @@ROWCOUNT = 0
return
SET NOCOUNT ON;
declare @count int;
set @count= (select max(loannumber) from glloan where branchid=(select branchid from inserted) and CompanyId=(select CompanyId from inserted))
update glloan set loannumber=@count+1 where id=(select id from inserted)
END
GO
Is there any chance where loan number can be duplicated for a given branch and company?
mardi 11 août 2015
refresh table data adpter in .net2.0 using sqlserver 2012 in backend
I am working on old .net 2.0 client app. It uses table data adapters and since it was 1st developed several years ago the database has been upgraded to Sql server 2012 from Sql server 2005.
Now I need to make changes to the database and modify new column. I have made the db change. But I am not sure how to refresh the table data adapter for this change. When I try to configure I get the error:
'The server version is not supported. You must have Microsoft sql server 2005 Beta 2 or later.'
I am using visual studio 2005 for this project.
Any pointers to resolving this will be appreciated.
Copy Data and increment PK in destination table
I have a temp table with data that needs to be split into 3 other tables. Each of those tables has a primary key that is not shared with each other or with the temp table. Here is a small sampling:
Table 1
RSN AGENT STATUS STATUS DECRIPTION
0 280151 51 Terminated
1 86 57 C/O Comp Agent Dele
2 94 57 C/O Comp Agent Dele
3 108 51 Terminated
Table 2
RSN AGENT CITY
1 10 Englewood
2 123 Jackson
3 35 Eatontown
4 86 Trenton
Table 3
RSN AGT SIGN NO_EMP START_DATE
0 241008 Y 1 2002-10-31 00:00:00.000
1 86 Y 0 2002-10-24 09:51:10.247
2 94 Y 0 2002-10-24 09:51:10.247
3 108 Y 0 2002-10-24 09:51:10.247
I need to check each table to see if the data in the temp table exists and if it does not I want to insert those rows with a RSN# starting with the max number in that table. So if I have 5000 records in the first table and I am adding 5000 new rows they will be numbered 5001 through 10000.
I then need to check to see if any columns have changed for matching rows and update them.
Thanks in advance for your assistance.
Scott
lundi 10 août 2015
Whats wrong with the following date sort query
I am using the following query to sort the output. It works in some cases but not all. Any suggestion why? any alternate please.
Select
'playing' As activity,
max(ad.xDate) As xDate,
Isnull(sum(t.TimePerDay), 0) As TimePerDay
From
AllDates As ad With (Nolock)
Left Join
@test As t On ad.xDate = t.date
Group By
datepart(wk, ad.xDate)
Order By
YEAR(datepart(wk, ad.xDate)) DESC, MONTH(datepart(wk, ad.xDate)) DESC, DAY(datepart(wk, ad.xDate))
Select only distinct columns
i have table T and want select only distinct columns (without correlation between fields)
F1 F2 F3
1 A AA
1 B BB
2 B CC
2 C DD
2 C EE
3 C EE
desired output
F1 F2 F3
1 A AA
2 B BB
3 C CC
null null DD
null null EE
i try
select T.F1, T1.F2 from
(select distinct F1, row_num() rn from T) T
left join (select distinct F2, row_num() rn from T) T1 on T.rn=T1.rn1
left join etc...
can it be simpler?
samedi 8 août 2015
Getting sql query error
I am running the below query in my code, but i am getting error as
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ')'.
Here is my query
"Select Type_desc, Type_Abbr from type_mst_a where Type_code = 'AC' and NOT Type_Abbr = 'PEC' " +
"and type_abbr NOT IN (select category_id from EMP_ATTACHED_DOCUMENTS where " +
"pk1_value = '" + txtEmpCode.Text + "')for xml raw,elements)) Categories"
Please suggest what is wrong here
Retrieving some specific records from SQL Server 2005 database table
I have a table in database
EmployeeID TeamLeadID
2 1
3 2
4 3
5 NULL
1 NULL
6 1
7 2
8 3
Now what I want is to retrieve all the TeamLeadsID (i.e all the upper hierarchy) for a given EmployeeID
-
For
EmployeeID = 2I should get ans 1 (because we don't haveTeamleadIDforEmployeeId = 1) -
For
EmployeeID = 4I should get 3,2 and 1 (because 4->Teamlead is 3, 3 -> Teamlead is 2, 2 -> TeamLead is 1) -
Likewise for
EmployeeID = 7I should get 2 and 1 only
while for 5 & 1 it should be Null as clearly seen from the table itself
vendredi 7 août 2015
Extract string between period delimiter
I need to extract P4534 from the string below in SQL. The length between those two periods can change. So I need to account for longer strings between those two periods as well when doing a substring. How can I start at a different position?
<?xml version="1.0" encoding="utf-8"?><mydata>9.9.P4534.2.3</mydata>
How to modify the following code to ignore rows with 0 and shows the output weekly
My following query gives me the output shown in figure 1. I want to change it so that it gives the output in figure 2. I want to show the weekwise (even if the value is 0) Here is the query. Please help.
SELECT @MaxDate = @MaxDate
,@MinDate = dateadd(week, (@LastXWeeks + 1), @MaxDate);
WITH AllDates
AS (
SELECT @MinDate AS xDate
UNION ALL
SELECT Dateadd(day, 1, xDate)
FROM AllDates AS ad
WHERE ad.xDate < @MaxDate
)
SELECT 'playing' AS activity
,ad.xDate
,Isnull(t.TimePerDay, 0) AS TimePerDay
FROM AllDates AS ad WITH (NOLOCK)
LEFT JOIN @test AS t ON ad.xDate = t.DATE
How to convert the following query to give sum per week
This is part of a long query which compute the total time per day. I want to change it to give me the total time per week. Any idea? To check the long query click Here
INSERT INTO @test (
activity
,DATE
,TimePerDay
)
SELECT activity
,Cast(starttime AS DATE) AS DATE
,SUM(datediff(second, starttime, endtime)) / 60.0 AS TimePerDay
FROM cte WITH (NOLOCK)
WHERE starttime >= dateadd(week, @LastXWeeks, last_update)
GROUP BY activity
,cast(starttime AS DATE)
jeudi 6 août 2015
Custom ordering in Max()
Suppose I have a table 'things' which has a column 'ranking'. There are 5 records with the values First, Second, Third, Fourth and Fifth in the ranking column.
I want to be able to use
select Max(ranking) from things
to return 'Fifth', instead of its default 'Third'.
Also, I would want Fourth to be considered greater than Third, etc. etc.
How can I accomplish this?
SQL Server 2005
Migration from SQL Server and Problems in applications and Database connection URL of application
I am working on the project where we are migrating from SQL Server 2000 to 2005 and also migrating from 2005 to 2012 or 2014. There are 30 databases that supports more than 45 applications. Some of the applications are Windows desktop and some are web based application.
My question is if I change the server than there will be problem in connectionUrl and I have to change the connectionURL of all the applications in the code, right?
Is there any way that I do not have to change the connectionURL in the code of all the application.?
What do you prefer?
PS: Most of the application are in C#, ASP.net
How to change the query to give last 15 weeks of data instead of last 15 days from the SQL-server
The following query gives playing time of the users from the database on daily basis for the last 15 days. It adds 0 if no game is played. Now I want to get the data of playing time on weekly basis and 0 if no game is played in the whole week. So I want the query to give the last 15 weeks of data.
Here is the daily query.
CREATE procedure [dbo].[spGetPlayingTimeOfthepeoplesPerDay]
@email nvarchar(50)
AS
Begin
Set Nocount On;
Declare @MinDate Date, @MaxDate Date, @LastXDays Int
Select @LastXDays = -15
Select @MaxDate = peoples.l_update from peoples where peoples.email=@email
Declare @test Table
(
quantity Varchar(100)
,date Date
,TimePerDay Decimal(5,2)
)
;WITH CTE AS
(
SELECT peoples.email, peoples.l_update, act.quantity, act.starttime, act.endtime, act.duration as [Totaltime] from peoples
inner join MPeoples ON peoples.Id=MPeoples.parent_id
inner join slines ON MPeoples.id=slines.movesuser_id
inner join seg ON slines.id=seg.sline_id
inner join act ON seg.id=act.seg_id
WHERE act.quantity='playing' and (peoples.email=@email)
GROUP BY peoples.email, act.quantity, act.duration,
act.starttime, act.endtime, peoples.l_update
)
Insert Into @test(quantity,date,TimePerDay)
Select quantity
,Cast(starttime as date) As date
,SUM(datediff(second, starttime, endtime))/60.0 As TimePerDay
From cte With (Nolock)
where starttime >= dateadd(day, @LastXDays, l_update)
group by quantity
,cast(starttime as date)
Select @MaxDate = @MaxDate
,@MinDate = dateadd(day, (@LastXDays + 1), @MaxDate)
;With AllDates As
(
Select @MinDate As xDate
Union All
Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)
Select 'playing' As quantity
,ad.xDate
,Isnull(t.TimePerDay,0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date
END
Incorrect syntax near 'SETS'. - SQL 2005
My code:
Group BY
GROUPING SETS
(
(wmsSponsorEntityName),
(wmsSponsorEntityPrimaryRegion),
(wmsProjectNameSWP) ,
()
)
I'm doing this because those three fields repeat and the other two:
w.[P2010]
w.wugCounty
differ for some wmsProjectNameSWP
wmsSponsorEntityName = ABERNATHY
wmsSponsorEntityPrimaryRegion = P
wmsProjectNameSWP = ProjectX
w.wugCounty = HARRIS and w.wugCounty = JEFFERSON
w.[P2010] = 200 and 345 (differs for each county)
But I receive an error of Incorrect syntax near 'SETS'.
Any guidance would be appreciated.
Displaying a message when something is inserted into a table
In my application I want a message to be displayed when something new is inserted into my table, what's the best way of doing this? the only thing I can think of right now is having a timer repeat a query every few minutes to check if there are any new records.
MS SQL 2005: Problems with alias in subquery
Ok, so I'm a SQL noob and the way I'm trying to get this done is probably not the best - and as of now it doesn't even work, so here goes:
What I have is a table full of calibration data. The devices are identified by a serialnumber (column Serial), each device can have multiple calibration runs identified by the RunID. There are a lot of different things which get calibrated, and those values are all stored in the CalValue column. To identify which row contains what calibration, the column CalID exists. What I'm trying to get is the CalValue of three different CalIDs when they all differ from some standard values. As a device can have multiple runs, I'm only interested in the most recent one.
To illustrate that:
# Serial # RunID # CalValue # CalID #
# 1 # 0 # 0.5 # 13 #
# 1 # 0 # 0.8 # 24 #
# 1 # 0 # 0.2 # 35 #
# 1 # 1 # 0.5 # 13 #
# 1 # 1 # 0.3 # 24 #
# 1 # 1 # 0.6 # 35 #
# 2 # 0 # 0.0 # 13 #
# 2 # 0 # 0.0 # 24 #
# 2 # 0 # 0.0 # 35 #
# 2 # 1 # 0.6 # 13 #
# 2 # 1 # 0.7 # 24 #
# 2 # 1 # 0.8 # 35 #
# 2 # 2 # 0.0 # 13 #
# 2 # 2 # 0.0 # 24 #
# 2 # 2 # 0.0 # 35 #
What I ideally want to get is this:
# Serial # CalValue.ID=13 # CalValue.ID=24 # CalValue.ID=35 #
# 1 # 0.5 # 0.3 # 0.6 #
# 2 # 0.6 # 0.7 # 0.8 #
The values for Serial 1 were selected because the last non-default values were stored in RunID = 1 rows. Serial 2 had also multiple runs, where the first and the third run only gave some standard values, so the values from the second run are selected.
So what I'm trying to do is to join tables, where I filtered out the standard values and then pick out only the values with the highest RunID. I've tried multiple things, ultimatively running into "The column CalValue was specified multiple times". I have not a real clue if my solution would actually work otherwise, but here is my approach:
WITH subq3 AS (
SELECT subq0.Serial AS Serial, subq0.RunID AS RunID, subq0.CalValue AS TRth0, subq1.CalValue AS TRth1, subq2.CalValue AS TRth2
FROM CalibrationData AS subq0
INNER JOIN CalibrationData AS subq1 ON (subq0.Serial = subq1.Serial AND subq0.RunID = subq1.RunID AND ((subq1.CalID=24) AND (subq1.CalValue<>$0.0 And subq1.CalValue<>$0.03)))
INNER JOIN CalibrationData AS subq2 ON (subq0.Serial = subq2.Serial AND subq0.RunID = subq2.RunID AND ((subq2.CalID=35) AND (subq2.CalValue<>$0.0)))
WHERE ((subq0.CalID=13) AND (subq0.CalValue<>$0.0 And subq0.CalValue<>$-400.0))
)
SELECT t1.Serial, t1.TRth0, t1.TRth1, t1.TRth2
FROM subq3 t1
LEFT OUTER JOIN subq3 t2
ON ((t1.Serial = t2.Serial) AND (t1.RunID < t2.RunID))
WHERE t2.Serial IS NULL AND t1.Serial < 90000000
ORDER BY t1.Serial ASC
Because of this question I've also built my example in SQLFiddle, and there it works just as I imagined it would. So my problem is actually with the specific features of the server. We have a MS SQL 2005 Server, which seems to have a problem with the alias in the subq3 statement.
Any suggestions how I can get around that "The column CalValue was specified multiple times"?
mercredi 5 août 2015
Break ranges into a row from two columns
I have a table as follows
CREATE TABLE [dbo].[CODE](
[BEG] [varchar](10) NOT NULL,
[END] [varchar](10) NOT NULL,
[EFF_BEG] [smalldatetime] NOT NULL
) ON [PRIMARY]
Insert into CODE select 'H0411', 'H0413', '01/01/2015'
Insert into CODE select 'J090', 'J093', '01/01/2015'
I want to get the list of all codes within the BEG and END like below
Instance
H0411
H0412
H0413
J090
J091
J092
J093
i have something like below but it wont work on varchar column
WITH number_cte (n)
AS (
SELECT n
FROM (
SELECT 0 n
) m
UNION ALL
SELECT n + 1 n
FROM number_cte
WHERE n < 2400
)
SELECT BEG+n instance
,EFF_BEG
FROM CODE
JOIN number_cte ON BEG + n BETWEEN BEG
AND END
WHERE BEG + n - 100 * floor((BEG + n) / 100) BETWEEN 0
AND 59
ORDER BY 1
OPTION (MAXRECURSION 2401)
Any solution to this? Thanks
Calculated fields from queries in CTE are quite slow, how to optimize
I have a query with calculated fields which involves looking up a dataset within a CTE for each of them, but it's quite slow when I get to a couple of these fields.
Here's an idea:
;WITH TRNCTE AS
(
SELECT TRN.PORT_N, TRN.TRADE_DATE, TRN.TRANS_TYPE, TRN.TRANS_SUB_CODE, TRN.SEC_TYPE, TRN.SETTLE_DATE
FROM TRNS_RPT TRN
WHERE TRN.TRADEDT >= '2014-01-01' AND TRN.TRADEDT <= '2014-12-31'
)
SELECT
C.CLIENT_NAME,
C.PORT_N,
C.PHONE_NUMBER,
CASE
WHEN EXISTS(SELECT TOP 1 1 FROM TRNCTE WHERE PORT_N = C.PORT_N AND MONTH(SETTLE_DATE) = 12) THEN 'DECEMBER TRANSACTION'
ELSE 'NOT DECEMBER TRANSACTION'
END AS ALIAS1
FROM CLIENTS C
WHERE EXISTS(SELECT TOP 1 1 FROM TRNCTE WHERE PORT_N = C.PORT_N)
If I had many of these calculated fields, the query can take up to 10 minutes to execute. Gathering the data in the CTE takes about 15 seconds for around 1,000,000 records.
I don't really need JOINS since I'm not really using the data that a JOIN would do, I only want to check for the existence of records in TRNS_RPT with certains criterias and set alias fields to certain values whether I find such records or not.
Can you help me optimize this ? Thanks
Sql configuration for Deployment C# Win form application in multiple System
I have Deployed My Win Form application in 5 system But among them 3 system works properly but one gets slow at loading data and some time It tells Application not Responding,what i will do? I am using SQL Server 2005 which is located in a remote location. I have pinged,checked system resources where all system are same,network is fine,SQL is dynamic listening mode,Disabled the firewall and so on.
lundi 3 août 2015
Adding or subtracting the value of a field in a SELECT
Here's a query I have:
SELECT
C.CLIENTNUMBER,
(SELECT SUM(B.FEES) WHERE B.TYPE IN ('1', '2')) AS TOTALFEES
FROM CLIENTS C
INNER JOIN BILLS B ON B.CLIENTNUMBER = C.CLIENTNUMBER
GROUP BY C.CLIENTNUMBER
This is pretty straightforward, but here's the rub. If B.TYPE is either 1 or 2, I want to add B.FEES to TOTALFEES. But if it's 3, I want to subtract B.FEES from TOTALFEES.
Any ideas on how to do this ? Thanks
How to recover SQL Server ownership when NT account is not valid anymore
I have a local SQL Server Express 2005, for which I don't know the SA password. I always connected with my NT login and I used that when I created a DB I want to access now.
My company split and I am a member of a new domain now. There are no connections with the old domain anymore. My account resides in the new user domain and is a local administrator on the computer where SQL Server runs.
I can access the Server through the Management Studio, but not my user DB anymore. I am not recognized as a Server Admin anymore either. Obviously, local NT administrators are not automatically mapped to be SQL Admins.
Is there a way to do that at this stage? Can I somehow recover access to the SA level of access?
Thanks in advance.
How to give change working of having function dynamicaly on executing an sql statement?
I'm having a Sql code like as follows
Select a.ItemCode, a.ItemDesc
From fn_BOM_Material_Master('A', @AsOnDate, @RptDate, @BranchID, @CompID)a
Left Outer Join fn_INV_AsOnDate_Stock(@StockDate, @AsOnDate, @RptDate, @BranchID, @CompID, @Finyear)b
On a.ItemCode=b.ItemCode and b.WarehouseCode<>'WAP'
and a.BranchID=b.BranchID and a.CompID=b.COmpID
Where a.ItemNatureCode = 'F' and a.BranchID = @BranchID and a.CompID = @CompID
Group by a.ItemCode, a.ItemDesc
Having sum(b.CBQty)<=0
Here the problem is that im passing an "@ShowZeroStock" value as as bit if the "@ShowZeroStock" value is '1' then Having should not be validated or (i.e: All values from the table should be returned including zero)
So How to change the query based on passed bit value "@ShowZeroStock"
I can Use "If else " condition at the top and remove having in else part, but for a lengthy query i can't do the same.
how to connect to a same local database used in domain database, when domain database is offline.(C# asp.net)
I am new to C#. I am doing an application for an institute registration,fees Payment etc. In my project i am using both windows and web application of C#.When i click a button from my windows form an asp page is opened in a browser. The rest of project is done as website. My question is,I am using a domain server as my database but when there is no current(offline) i want to use the same database in local system.Is there any option to do this? Please help me.........Thanks in advance. I am using C# version 2008 and MS SQL 2005.
SQL Server 2005 next lowest date greater than today
I’m looking to find the next lowest date, greater than today in a select statement. I’ve simplified the data down as an example.
Example data: table_1
name order_no order_date Due_date Run_no
customer1 abc1 01/04/2015 02/05/2015 1
customer2 def2 02/04/2015 02/05/2015 2
customer3 ghi1 03/04/2015 02/05/2015 3
customer2 def3 04/04/2015 04/05/2015 2
customer2 def4 05/04/2015 05/05/2015 2
and example query:
select
name,
order_no,
order_date,
Due_date,
Run_no
from
table_1
where
run_no = '2'
group by
name, order_no, Order_date, Due_date, Run_no
having
MIN(due_date) > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
so if today was 01/05/2015 i would expect the result to be:
name order_no order_date Due_date Run_no
customer2 def2 02/04/2015 02/05/2015 2
but what i actually get is:
name order_no order_date Due_date Run_no
customer2 def2 02/04/2015 02/05/2015 2
customer2 def3 04/04/2015 04/05/2015 2
customer2 def4 05/04/2015 05/05/2015 2
This has to be something to do with the Having clause doesn't it? Thanks
samedi 1 août 2015
DISTINCT in SQL Server on a single column
I am trying to run a SQL query in SQL Server 2005.
select distinct
emp_code, SSN, name
from
dbo.employee
where
(City = 'Abc')
or
(Country = 'India')
and Joining_date between getdate()-60 and getdate()
which is returning 76 rows.
But if instead I use
select distinct
emp_code
from
dbo.employee
where
(City = 'Abc')
or
(Country = 'India')
and Joining_date between getdate()-60 and getdate()
it returns 73 records.
I understand that in first query SQL Server is returning where all 3 columns are distinct.
How should I write the query if I want only distinct emp_code ?
I have tried using group by emp_code but it is throwing an error
Please help!
Inscription à :
Commentaires (Atom)