So I am trying to write a query to remove the records where the Customer has only one OverdueFee. (I have a Loan Table and a Customer Table which both contain the same column CustomerID which in the Customer table is seen as a Primary Key AUTOINCREMENT) So far my query has DELETE FROM Loan WHERE EXISTS (SELECT * FROM Customer WHERE Customer.CustomerID = Loan.CustomerID Loan and Customer are two separate tables. Loan contains LoanID, CustomerID, InventoryItemID etc.... and the Customer Table contains CustomerID as well as others that dont really matter in this case. The OverdueFee is a column within the Loan Table with the REAL data type
I am wanting to remove all records when the Customer has only ONE overduefee in the Loan table so im guessing I have to somehow JOIN both the two tables by the CustomerID? Tad stuck so any help would be appreciated

Hi experts, How to put a condition in a certain field (datetime). I have column named, theDateIn which has a data "2017-09-24 08:02:39.000", "2017-09-24 07:50:44.000". I'm trying to retrieve by using this code but it didn't work.

Select TheDescription from eCho where theDateIn Between '2017-09-24' And '2017-09-24' order by Theicnt asc

Please note that the code will work if i make  '2017-09-25' or september 25, in the end part but my existing data doesn't have 25th of september all are 24th only. I also notice that it also work  If i remove the time in the database. Which part of the code should need to add or change? thank you!

Hi experts,
   I have oracle 10g table for _Files which has a _filename field
    sample entries can be
      f1.xlsx
      file2.doc
      fl.h
      testfile.abcxyz

   I need a query that gives only the extensions . in this case the result should give
      .xlsx
      .doc
     .h
    .abcxyz

 Your help is appreciated

Is there a better way to write the final output and not use sub-queries?

 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
create table #temp(
	fullName varchar(100) null,
	IP_PP varchar (100) null,
	payment int null
)

insert into #temp values
('Tom', 'IP', 1),
('Tom', 'PP', 4),
('Tom', 'PP', 47),
('Ed', 'PP', 10),
('Ed', 'PP', 20),
('Ed', 'IP', 20),
('Ed', 'IP', 30)

select 
	fullName,
	(select sum(payment) from #temp b where a.fullName = b.fullName and b.IP_PP = 'pp') PP,
	(select sum(payment) from #temp b where a.fullName = b.fullName and b.IP_PP = 'IP') IP
 from #temp a
 group by fullName

Select all Open in new window

Is it possible to schedule a job to run every hour, but at a specific time every hour?

For example, i would want to run the job at 1:02, 2:02, 3:02, 4:02 etc...

Right now, the SQL scheduler wizard only allows me to select the top of every hour for a specific time-frame.

I have an Oracle database that I want to extract data from with four columns called U_TIME, KEY, VALUE and QUALITY.  I need the data for 334 entries using the KEY column. File #1 should contain the last 6 hours of information from the list of 8 digit KEY's.  The second file should contain the KEY where the field named QUALITY has a value of 256 but queries data for the last 45 days.  VALUE is a float and QUALITY is an integer.  
The KEY looks like this:

03031024, 03031027, 03031030, 03039002, 03039004, 03040003, 03040005, 03041002

and must have the leading 0:


The output files should look like:

File #1
9/1/2017      1:00      03019002      12      0
9/1/2017      2:00      03019002      14      0
9/1/2017      3:00      03019002      16      0
9/1/2017      4:00      03019002      25      0
9/1/2017      5:00      03019002      10      0
9/1/2017      6:00      03019002      22      0
9/1/2017      1:00      03019025      34      0
9/1/2017      2:00      03019025      20      0
9/1/2017      3:00      03019025      11      0
9/1/2017      4:00      03019025      52      0
9/1/2017      5:00      03019025      26      0
9/1/2017      6:00      03019025      39      0
.
.
.                        

File #2
9/11/2017      4:00      03019002      0      256
9/14/2017      2:00      03068025      0      256
9/27/2017      12:00      03052045      0      256
.
.
.

I am looking for hosting server recommendations.

Our project will involve:

ASP.NET framework
SQL Server database
Xamarin mobile app

Load Balancer
Firewall

Recommendations?

Thanks!

Hello:

In SQL, I created a Windows Authenticated login for myself.  I gave that login access to only one SQL database.

Yet, when I use Microsoft Query in Excel, I get a login error message when trying to access this database.  The only way "around this" is to check the box for "Use Trusted Connection".  Why is that?

Secondly, even when using this checkbox, I get access to all of the SQL databases in the instance--not just the one database that I gave myself access to.  Why?

In any case, my Team and I ultimately used a SQL authenticated-login for Excel's Microsoft Query.  But, doesn't doing so represent a security loophole?

Thanks!

John

Access 2010 query to sql linked tables.

SELECT dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id, 
dbo_t_nsc_trackcode_assigned_DataEntry.ID_Racfid, [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME] AS Name, 
dbo_t_nsc_trackcode_assigned_DataEntry.Opened_Date, dbo_t_nsc_trackcode_assigned_DataEntry.Closed_Date, 
dbo_t_nsc_trackcode_assigned_DataEntry.Col_Id_Template, 
dbo_t_nsc_trackcode_assigned_DataEntry.Activity_Task, Workdays([Opened_date],[Closed_Date]) AS [Working days], 
[Service_Level_Hours]/24 AS STATED_SLA_HOURS, 
dbo_t_nsc_support_type_column_template_v2.Standard_In_Minutes

FROM (dbo_t_nsc_trackcode_assigned_DataEntry INNER JOIN dbo_t_nsc_support_type_column_template_v2 ON 
dbo_t_nsc_trackcode_assigned_DataEntry.Col_Id_Template = dbo_t_nsc_support_type_column_template_v2.Column_ID) 
INNER JOIN dbo_employee_directory ON dbo_t_nsc_trackcode_assigned_DataEntry.ID_Racfid = dbo_employee_directory.user_id
WHERE (((dbo_t_nsc_trackcode_assigned_DataEntry.Closed_Date) Is Not Null))
ORDER BY dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id DESC;

Select all Open in new window

What I need:
For this field in the query:
[Service_Level_Hours]/24 AS STATED_SLA_HOURS,

If the value is  "1", i need it displayed as "0" if possible.


Thanks
fordraiders

I have been asked to generate Reports from lots of tables in DB2.

I want to learn to solve writing queries from simple to complex through lots of problems and suggested the solution.

I have this query that works great. As soon as I try to add Group By pat.PATNUM it breaks and no data displays. Is there a different way to use Group By with mssql?  I put in Group By pat.PATNUM right before the ORDER BY

SELECT info.Suite, info.PID, info.NID, info.TTF, info.Discharged,, pat.TASK,info.PATIENTID, pat.PATNUM, pat.SCHDT FROM admissions.info as info INNER JOIN admissions.patientinfo as pat ON info.PATIENTID=pat.PATNUM WHERE info.Suite IN (7,8,9,10,11,12,13,14,15) AND info.Discharged IS NULL  AND pat.DNU !='1' AND pat.TASK !='Z CO SURGEON' ORDER BY info.Suite ASC

Select all Open in new window

Please show example to populate a text box with last I'd from a sql table(e.g. Employee)

Hi,
I have an insert trigger that uses a CLR function to retrieve values to insert in.

If I try the insert outside the trigger, it runs fine or retrieves data using the CLR function.

But when I create a trigger with the CLR function within, get a timeout error.

Why won't it work within a table trigger?  And works just fine by itself?

Permissions issue?

Thanks

Error getting -
Msg 6522, Level 16, State 1,
System.Net.WebException:  The operation has timed out

Database - Sql Server 2008