Query Syntax

51K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

Dear Experts,

 I need help to create a view from the following table, the view contain 2 columns ID & sum of Hold time which is difference between each Hold_RELEASE & HOLD



ORDERID  |  STATE                   |  Timestamp
1                |  HOLD                    | 12-Nov-13 06:03:46
1                |  HOLD_RELEASE   | 18-Nov-13 04:13:33
1                |  HOLD                    | 19-Dec-13 03:12:34
1                |  HOLD_RELEASE   | 25-Dec-13 12:04:55
2                | HOLD                     | 12-Oct-13 06:22:33
2                | HOLD_RELEASE    | 23-Nov-13 08:12:11


Thanks
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hello Experts,

I'm just getting started learning SQL and Python.

Compiling sql queries and python queries is pretty tough going.

I was wondering if someone could let me know if there are any GUI tools out there they could recommend to help write sql and python queries?

Thanks

Carlton
0
Raw_data.xlsxHello Expert,

I have a table with following record details.
SR_NO ROOM_NO	   FLOOR_NO	TIME_830AM_930AM	FLOOR1	TIME_930AM_1030AM	FLOOR2	TIME_1030AM_1130AM	FLOOR3	TIME_1130AM_0130AM	FLOOR4[/b]
        1	        216              	2ND	                ME       	           H1	                       NULL	           NULL	              BE	                             G1	                   NULL	                    NULL
        2	        216	                2ND                       	ME	                   H1	                       GE	                      I1	             NULL	                           NULL                      NULL		            NULL
        3	        216	                3RD   	              NULL         	NULL	                       EE	                     E1	             NULL                            NULL                         FE	                      F1
        4	        216	                3RD	                      NULL	        NULL	                      NULL	           NULL	              AA	                             A1	                   NULL	                    NULL
        5	     217	                1ST	                      NULL	        NULL	                       BB	                     B1	             NULL	                           NULL	                   NULL	                    NULL

Open in new window

Looking for O/p like this!

SR_NO ROOM_NO	   FLOOR_NO	TIME_830AM_930AM	FLOOR1	TIME_930AM_1030AM	FLOOR2	TIME_1030AM_1130AM	FLOOR3	TIME_1130AM_0130AM	FLOOR4[/b]
        1	     216              	2ND	                ME       	           H1	                       NULL	           NULL	              BE	                             G1	                   NULL	                    NULL
        2	     216	                2ND                       	ME	                   H1	                       GE	                      I1	             NULL	                           NULL                      NULL		            NULL
        3	     216	                3RD   	              NULL         	NULL	                       EE	                     E1	               AA                                  A1                           FE	                      F1

Open in new window

Thanks in Advance.
Looking-for-Output.xlsx
0
I have a failed SQL backup and have no clue why this is happening? Can anyone help with this?

Failed:(-1073548784) Executing the query "BACKUP DATABASE [JDE910] TO  DISK = N'\\\\x.x.x.x..." failed with the following error: "Cannot open backup device '\\\\x.x.x.x\\JDE Images\\SQL Backup\\Daily\\JDE910_backup_2018_07_14_220001_8461617.bak'. Operating system error 1311(There are currently no logon servers available to service the logon request.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


X.X.X.X are the IP address of the webserver jde
x.x.x.x is the ip address of the NAS device where the backup has to be moved
0
Hello ,

I have some queries  regrading NEW WSUS server setup , Please provide your suggestion on below mentioned points.

1.  Which DB should use WID or SQL Express ?
2. Is it recommended to push Services pack to  WSUS client machine like (win8/10 ),As services pack comes with hefty file size &  it may impact network perform if same time all machine try to get update from WSUS?
3. In case of downstream wsus server , Do we need to have DB for respective downstream server ?

Please help me out with these queries .

Thanks
0
So i have a user schema like this:

var user_schema = new Schema({
   username:{type:String,required: true, unique : true, trim: true},
   college:{type:String,required: true},
   password:{type:String,required: true, trim: true},
   email:{type:String,required: true, unique : true, trim: true},
   phone:{type:Number,required: true, unique : true, trim: true},
   dp:{type:String},
   tag:{type:String},
   description:{type:String},
   friends:[{type:String}],
   pending:[{type:String}],
   skills:{type:String},
   bucket:[{type:String}]
  });

Open in new window

and my objective is, to search the all the documents in the collection to get people based on the following conditions:

1. They should not be in the users' "friends" array.
2. They should not be in the users' "pending" array.
3. They should have the same "tag" (a string value) as the user.

So, basically I have to compare the users' fields ("friends","pending" and "tags"), with fields of all documents in the whole collection.

How do I do it, using mongoose (nodejs mongodb library)
0
I'm running Power Bi Reporting Service 2018 Version 1.2.6648.38132

The problem I am running into is when running scheduled subscriptions, only 1 subscription can be run at a time.

When starting the subscription manually through the SQL Agent (while the other one is running), the status of the subscription goes directly to: The report server has encountered a configuration error.

I notice that the SQL Agent is starting the job, but it does not seem to trigger the Reporting Service to run the subscription. (Image attached)

Do you have any suggestions?



See the error message in the attached file.
ssrs---schedules.jpg
SQL-Agent-vs-SSRS-Job.jpg
0
I am running System Center Configuraiton Manager 2016 (SCCM) on a Server 2016 Hyper-V virtual server.

When the server boots up I always have to go into the Windows services and manually start the SQL Server CEIP service (MSSQLSERVER), SQL Server Agent (MSSQLSERVER), SQL Server Reporting Service, (MSSQLSERVER), & CONFIGURATION_MANAGER_UPDATE services since these services don't start automatically.

I have already created a batch file that will start these services when it is run and I would prefer not to change the startup type on these services from automatic to automatic (delayed start) unless absolutely necessary.

What can I do to fix this issue so these services will automatically start and won't have to be manually started every time the server boots up?
0
I m trying to Install SQL 2008r2 on newly bulit Windows Server20088r2. It requires dotnetframework 3.5
However I added roles & features for dotnet.
It still requires
0
Hi,

im curious if there is a way to do the following in PHP but within a stored procedure with SQL on the fly

$managers = array(
	0 => array(
		"id"	=> 1,
		"name"  => "manager",
		"email"	=> "email"
	)
	...
)

foreach($managers AS $d){
	//send mail
	mail(...);
	
	//Insert into messages table
	INSERT INTO...

}

Open in new window


Essentially, i would want to SELECT the manager from a table and then immediately insert it a message into the massage table

for example:

1. select manager 1
2. insert message into message table for manager 1
3. repeat for manager 2
,...so on

I would handle sending the email using php

I KNOW i can do this in a few steps such as
1. Select all the managers and organize them into an array to loop over
2. loop over the managers and create an array of emails as well as sql statements i can create a transaction with and submit the inserts all at once
3. complete loop
4. send the transaction to sql
5. send the email

but i am curious if i can do it one for one within a stored procedure

Thanks in advance
0
Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I want to make sure I am understanding the Group By completely in MS Access. Basically I am importing CSV files into Access and I want to Group Customers by several fields. The same customer could have several shipping addresses. Therefore I want to make a record in Access for each shipping address of the customers and if there is several duplicate Shipping Address in the CSV file, I want Access to Group them. The fields I am trying to Group on is:
Company Name
First Name
Last Name
Address Line 1
Address Line 2
City
State
Zip Code
Country
Email address

Basically if any one of these field values is different from the previous record. I want access to show a record in the query results and if it is the EXACT same all the way down the line of fields. I want Access to group all of the similar ones and just show one record.

I have attached a photo of my query grid below and thank you for the help:
Group-By-Access.png
0
TABLE1
store_ID
Relation_ID
Store_Open
Store_Close
Trans_Date


TABLE2
Relation_ID
Relation_Name

TABLE3
Store_ID
Store_Name

TABLE1 is transactional, table 2 is static as is table 3
I need to find all the stores and their names from TABLE3 where the stores opened in 2016. Also all the names where they closed in 2017. But only for a Relation_Name = "MyStore"
I have a query that, without using the transaction dates, gives results, but I'm sure its wrong.  I need to only use the Max record previous to the end of 2016.
(I know the date syntax can change from platform to platform - this is for DB2, or oracle)

The query that almost works is:
Select distinct(A.Store_ID)
,A.Store_Name
from TABLE3 A
join TABLE2 B on A.Relation_ID= B.Relation_ID
join TABLE3 C on A.Store_ID = B.Store_ID
where B.Relation_Name = "MyStore"
and C.Store_Open < '01/01/2017'
and C.Store_Close > '01/01/2017'

How do I only use the Max records of TABLE1 in 2016?
0
I used to use a software called QueryCell.  It allowed you to easily take columns from excel and easily create update and insert statements.  Unfortunately this doesn't work with current versions of excel.  Can anyone tell me if they know of something similar?

Thanks


 Screenshot of QueryCell
0
Hi

This is how query looks like:
First query:

select FullName, SystemUserId from SystemUser
ORDER BY FullName

This query will show 100x systemuserID now to the next one:

update UserEntityUISettingsBase
set RecentlyViewedXml = null
where OwnerId = 'SystemuserID'

ok here comes the question, how do I do this easiest? Declare a var for 100 systemuserID? how do i do this so the 100x systemuserID updates easiest.

Thanks
0
Hi

Could do with some help with creating SQL code that replicates the CHI-Inverse Function from Excel.

=CHIINV(0.5+95/200,300)

I'm stuck with the CHIINV part that uses Right-Tailed Probability of the Chi-Squared Distribution.

Some forums mention CHISQ.INV.RT but struggling to find any SQL Examples or a Table somewhere with all Chi combinations...
0
We are randomly having issues with our barracuda backup of our SQL 2014 DB's. It will run for weeks and then get this error for several days. Sometimes a reboot works, other times it does not.
here is the application event id info:
 System

  - Provider

   [ Name]  SQLWRITER
 
  - EventID 24583

   [ Qualifiers]  0
 
   Level 2
 
   Task 0
 
   Keywords 0x80000000000000
 
  - TimeCreated

   [ SystemTime]  2018-07-13T02:03:27.000000000Z
 
   EventRecordID 79638946
 
   Channel Application
 
   Computer SQL2014.AbleHC.local
 
   Security
 

- EventData

   ICommandText::Execute
   0x80040e14
   SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000, Native Error: 3271 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: A nonrecoverable I/O error occurred on file "{9259D979-BF0D-4316-AAC5-29F290E39625}5:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).
0
In oracle how can I replace the chinese character from a given string
string = '020?Domestic CT outgoing?20测试叙事测试叙事 测试叙事测试叙事测试叙事 ?21测试叙事测试叙事)测试叙事测试叙事测试叙事?31测试叙事测试叙事 测试叙事?32测试叙事'

I want the Output as
020?Domestic CT outgoing?20:?21:?31:?32

Please help me
0
I'm passing dates in the select statement and getting error 'Conversion failed when converting the varchar value '10/02/2002'

What conversion formula should I use?
0
Hi Experts,

Is there any way to compare 2 timestamp and pull records from DB2 using query.

 Query wil be having input file which contains basic timestamp .We have to compare this timestamp with the timestamp in process_date column and have to pull all records which happend after this basic timestamp.

How to compare timestamps and pull records ...

Any help regarding this would be helpful.

Thanks in Advance.
0
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

I have sql server 2012, where I Stored times zone for users, so that I can convert the datetimes to their timezones before showing it
 I stores date in UTC format.

I need to know how can I convert UTC dates to our customer's time zone lile CST or Indian Standard time.
0
Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
0
I'm new to mysql and need some help. I have populated a multi-select drop down with my first query. The dropdown populated correctly and I want to use the value or values from the dropdown to get results from a second query. I will try to explain some more.

I have a multi select dropdown named students that is populated with Names and values that are numbers. I want to select one, two or all and use those values in a second query. The dropdown will have values like 390, 234, 5421, 461. If you look at the query below you will see the student_id = 461 that value is what I want to get from the dropdown. This query works if I place in the student_id.

SELECT *, student_id, SUM(handled) FROM stats WHERE student_id = 461

From there I
echo "Handled: " . $row["SUM(handled)"]. "";
and that works just fine.

Thanks for any help.
0
I need help with pivoting my rows into columns. In my dataset, i need to return exactly 13 months for every Provider / Company combination and return the total Charges and Payments . Any assistance is deeply appreciated.

SET NOCOUNT ON;

DECLARE @Datevar DATETIME
      , @StartDate DATETIME
      , @EndDate DATETIME
      , @Counter INT;

SET @Datevar = ISNULL('1/01/2014', '1/1/1900');

SET @Counter = 12;
SET @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @Datevar)); -- puts us at end of current month
SET @StartDate = DATEADD(MONTH, -12, @StartDate);  -- sets the date 12 months from end of last month of entry
SET @EndDate = DATEADD(MONTH, @Counter, @StartDate);

PRINT @StartDate
PRINT @EndDate

CREATE TABLE #output (
    ProviderName     VARCHAR(100)
  , DoctorFacilityId INT
  , Company          VARCHAR(100)
  , CompanyId        INT
  , Charges          MONEY
  , Payments         MONEY
  , [Month]          INT
  , [Year]           INT
  );

INSERT INTO #output
    (
	 ProviderName
    , DoctorFacilityId
    , Company
    , CompanyId
    , Charges
    , [Month]
    , [Year]
    )

  SELECT
    Dr.Listname AS ProviderName
  , Dr.DoctorFacilityId
  , Comp.ListName AS Company
  , Pv.CompanyId
  , Pvp.TotalFee AS Charges
  , DATEPART(MONTH, B.entry) AS [Month]
  , DATEPART(YEAR, B.entry) AS [Year]

  FROM PatientVisit Pv
    INNER JOIN DoctorFacility Dr ON Pv.DoctorId = Dr.DoctorFacilityId
    INNER JOIN DoctorFacility Comp ON Pv.CompanyId = Comp.DoctorFacilityId
    

Open in new window

0
Hi Experts,
I need a help on build a report by using the SQL.
I have a two tables
1) Document table
2) Fields table

The relation between these tables are
documentid in both the tables..

Basically I need the SQL to get the report as shown in the spread sheet. ( Attached excel sheet)

Thank you
Regards,
KumarTest.xlsx
0
Hello Experts,
I am trying to display a table rows horizontally by TKT_Id.  I have two tables, TKT_DETAILS and FIELD_DETAILS.  Please see the details below.

TKT_DETAILS
TKT_Id
Field_Id
Field_Value

There are about 750 different fields numbered as 1 thru 750.  Each row contains only 1 field.  There are 750 rows for every single record vertically.

FIELD_DETAILS
Field_Id
Field_Name

This table contains Field Names of the every Field_Ids.

Because it is very difficult to read vertically, I am trying to create a view to show them all 750 fields in a single row as below.

Tkt_Id, Field_Name_1,  Field_Name_2,  Field_Name_3,  Field_Name_4,  Field_Name_5...  Field_Name_750
    1             001                     002                     003                     004                      005            ...             750

Plesae let me know how to do it without using PIVOT.  Thank you in advance for your time and help!
0

Query Syntax

51K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.