Query Syntax

55K

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.

I had this question after viewing Filter sql on date and time.

I am querying data that is stored to a table every 10 seconds.

I have a working query statement that returns all "Cost_per_1000" records that are less than two years old:
Select t_stamp, Cost_per_1000 From std_10_Sec
WHERE t_stamp > (DATEADD(YY,-2,GETDATE()))

Open in new window


Unfortunately, this query results in >6,000,000 rows returned.  I need to reduce each hour of Cost_per_1000 data to a single row, ignoring any NULL or 0-value rows in the process.

An example of the returned t_stamp data looks like: "2019-03-13 03:41:32.487"
0
Hi Experts!

Need help with the following linq query:

            //For each City, For each person, if the JobTitle is EITHER Knight, Cook, Mechanic we want to take all the records for the Person for that City,

            //For each City, For each person, if there's a duplicate JobTitle with the same address then just take one

Below is a mock up unit test with test data.


using Microsoft.VisualStudio.TestTools.UnitTesting;

using System;

using System.Collections.Generic;

using System.Linq;

 

namespace UnitTestProject2

{

 

    internal class JobHistory

    {

        public int PerId;

        public string JobTitle;

        public string Address;

        public string City;

        public Nullable<System.DateTime> CreatedOn;

    }

 

    [TestClass]

    public class UnitTest2

    {

        [TestMethod]

        public void Extract_PersonByJobTitleByCriterias_Success()

        {

            // Arrange

            List<JobHistory> jobHistories = new List<JobHistory>()

            {

                new JobHistory() {PerId = 1, JobTitle = "Knight", Address = "123 Street", City = "Sapporo", CreatedOn = new DateTime(2011, 6, 1)},

                new JobHistory() {PerId = 1, JobTitle = "Cook", Address = "234 Street", City = "Sapporo", CreatedOn = new DateTime(2012, 6, 1)},

                new JobHistory() {PerId = 1, JobTitle = "Mechanic", Address = "234 Street", City = "Sapporo", CreatedOn = new DateTime(2012, 12, 1)},

 

              

Open in new window

0
Hi,
       This sql works
With CTE as (select Seq from DocumentInfo_DocType where Department  like CONCAT('%',<Department2>, '%'))select Document_Type from DocumentInfo_DocType d inner join CTE on d.seq = CTE.seq and Document_Type is not null order by Document_Type

I was asked to change it so that if a user leaves the Department2 field blank on the form the query will check for the null and if null, set it to use the value of "Universal" as the Department2.

My try with inserting the Case statement is below but it will not work. What am I doing wrong?

With CTE as (select Seq from DocumentInfo_DocType where Department  like CONCAT('%',(case when (<Department2> is null) then 'Universal' else  <Department2> end), '%') select Document_Type from DocumentInfo_DocType d inner join CTE on d.seq = CTE.seq and Document_Type is not null order by Document_Type
0
I need to update or insert data into a field without removing the existing data that is already in the field. Need to add the data after a line feed or carriage return. The example below shows what I wish to do. Add Note: FMV: $12588 after any existing data already there.

Ex.

Do not buy this PN

Note: FMV: $12588
0
Hello -  

I am troubleshooting why a report is generating a duplicate record.  So the person's sql I am looking at is as follows.  Please note I find the bolded sql rather confusing.  How can the bolded portion be written in a manner that is more straightforward. Please explain your answer.


 SELECT  'LN' ,
 'Test' ,
 '2020-02-12' ,
 'V' ,
 A.REG_TEMP ,
 A.POSITION_NBR ,
 A.POSN_STATUS ,
 A.DESCR ,
 A.DEPTID ,
 B.DESCR,
 A.SAL_ADMIN_PLAN,
 CASE WHEN C.EMPLID IS NULL THEN ' ' ELSE C.EMPLID END AS EMPLID2 /* Current Incumbent EMPLID */
 ,0
 /* Last Incumbent EMPLID */
 ,D.REPORTS_TO
 ,''
 ,''
 /* Current INCUMBENT NAME */
 ,''
 /* Last INCUMBENT NAME */
 ,''
 ,''
 ,''
 ,GETDATE()
 ,CASE WHEN A.GRADE IS NULL THEN ' ' ELSE A.GRADE END
 [b]FROM 
 ( SELECT DISTINCT D2.SETID ,A2.DEPTID ,A2.POSITION_NBR ,A2.DESCR ,
 A2.POSN_STATUS ,A2.STATUS_DT ,A2.BUSINESS_UNIT ,
 A2.JOBCODE ,A2.COMPANY ,A2.LOCATION ,A2.FULL_PART_TIME ,
 A2.REG_TEMP ,A2.CONFIDENTIAL_POSN ,A2.REPORTS_TO ,
 A2.MAX_HEAD_COUNT ,A2.DESCRLONG ,A2.MANAGER_LEVEL ,A2.SAL_ADMIN_PLAN ,A2.STEP ,A2.GRADE 
 FROM PS_POSITION_DATA A2 , PS_DEPT_TBL D2 
 WHERE D2.SETID=( SELECT Z2.SETID FROM PS_SET_CNTRL_REC Z2 
 WHERE Z2.SETCNTRLVALUE=A2.BUSINESS_UNIT AND Z2.RECNAME='DEPT_TBL') 
 AND D2.DEPTID=A2.DEPTID 
 AND D2.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PS_DEPT_TBL E2 
 WHERE E2.DEPTID = D2.DEPTID AND E2.SETID=D2.SETID AND E2.EFFDT<='2020-02-12')
 AND A2.EFF_STATUS = 'A' 
 AND 

Open in new window

0
Is there a dynamic way to get the Monday of every week regardless of what day you execute a query?

What I am trying to do is get a range of time to always give me Monday of every week and go back 4 weeks. Any help would be greatly appreciated.

I have this now in my where clause for a date
between DATE_SUB((current_date() - INTERVAL WEEKDAY(NOW()) + 2 DAY), INTERVAL 27 DAY) and current_date() - INTERVAL WEEKDAY(NOW()) + 2 DAY

Open in new window


However, I know that there is a better way to do this because if I run this tomorrow it will be off.
0
Hello Experts,
Any suggestions on the SP where the date is getting inserted as 1900-01-01 when it is suppose to be blank. Thanks in advance.

USE [xyz]
GO
10:57:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER PROCEDURE [dbo].[xyz_InsertUpdate_MOT] (
  
  @Registration				NVARCHAR(255)	= NULL
, @RefNo					NVARCHAR(255)	= NULL
, @RefMOT				    NVARCHAR(255)	=NULL
, @ReportedBy			    NVARCHAR(255)   =NULL
, @Garage				    NVARCHAR(255)
, @Notes				    NVARCHAR(max)	= NULL
, @Vehicle				    NVARCHAR(200)	= NULL
, @VehColor				    NVARCHAR(200)	= NULL
, @Make						NVARCHAR(50)	= NULL
, @Model				    NVARCHAR(50)	= NULL
, @IsNotActive 				NVARCHAR(2)		= NULL	
, @IsDeleted                NVARCHAR(2)		= NULL	
, @MOTRef1				    NVARCHAR(255)
, @MOTRef2				    NVARCHAR(255)
, @MOTRef3				    NVARCHAR(255)
, @Milage				    NVARCHAR(255)
, @MilageRef1				NVARCHAR(255)
, @MilageRef2				NVARCHAR(255)
, @MilageRef3				NVARCHAR(255)
, @AccountName				NVARCHAR(200)	= NULL
, @MachineName				NVARCHAR(200)	= NULL
, @NewID					INT						OUTPUT
) 

AS 

BEGIN

CREATE TABLE #action (
		action varchar(10)
		)



-- UserID will be used to look for the user in the XYZ_Users table.
DECLARE @UserID INT
-- Check to see if we have either an AccountName or Machine name and then check the users table !

IF @AccountName IS NOT NULL
BEGIN
	SELECT @UserID = ID
	FROM XYZ_Users
	WHERE AccountName = @AccountName

	IF @UserID IS NULL
	BEGIN
		INSERT INTO XYZ_Users (AccountName) 

Open in new window

0
I have a Access table (tblExcelProductivity) that includes FullName, FieldOffice, Mnth , and Goal. I would like to use an update query to update the Maximum Goal number with 0 for each like record based on FullName, FieldOffice and Mnth. For example below, Mary has 2 records in Arlington in the month of DEC with Goals of 45 and 67.2. In this case 0 would be updated 67.2 the max for the 2 records.




Image

qrySumAgedCase1.xlsx
0
I'm using the following code and receive:

Image
UPDATE tblExcelProductivity INNER JOIN tblAdjDupGoals ON (tblExcelProductivity.FieldOffice = tblAdjDupGoals.FieldOffice) AND (tblExcelProductivity.FullName = tblAdjDupGoals.FullName) AND (tblExcelProductivity.Mnth = tblAdjDupGoals.Mnth) SET tblExcelProductivity.Goal = 0
WHERE (((tblExcelProductivity.Goal)=Max([tblExcelProductivity]![Goal])));

Open in new window

0
Hi:

If you have a SQL view in Excel and if the Excel spreadsheet is the data source for Power BI, is it possible to refresh the dataset without having to refresh the actual spreadsheet and if so how?

Thanks!

Software Engineer


John
0
I have  two fields I want to update using CASE WHEN Syntax.
The fields are PipUPC and TUPC. I don't know how to set the fields with the ELSE END syntax using the CASE WHEN.
Essentially what I need is:
1) check the PipUPC and if it is null, update  update with the List_UPC  value  from ActionList2020  table ELSE keep the  current value in the cell.
2)Check the TUPC and if null, update with the List_UPC  value  from ActionList2020  table ELSE current value of the in the cell.



Update
.[dbo].[UpdateProcessSales] 

Set PipUPC=
(

CASE WHEN PipUPC is NULL then t2.List_UPC,
 WHEN TUPC is NULL then UPC=t2.List_UPC
ELSE END,
)
from
[dbo].[UpdateProcessSales] t1
inner join
[dbo].[ActionLIst2020] t2
on t1.PNumber=t2.Price_Code

Open in new window

0
I have two images residing on the same VM Ware Workstation Pro host. I can ping both machines from each other. However when I go into SSMS and try to connect one to another I get the message that the server cannot be found. What do I need to do to conect these two SQL instances. I need to create a linked server from one to another. Right now there is no domain. Is it a question of setting the DNS addresses?
0
I have 3 Application Servers and One SQL Database Server. One Application server cannot connect with SQL DB.
Whereas other two Application servers are connected and working fine.
Please suggest how to troubleshoot the issue.
0
Hello Experts,

I know this is just simple to experts but I am struggling to figure this out.

I want to get the @EndDate if the report will run any @TodayDate between First Date and Last Date of any quarters and default @EndDate to "Last Date of any quarters."

@BeginDate will default to Beginning of Year.

Example 1:
Today's Date = 02/20/2020
First Day -1st Quarter  = 1/1/2020
Last Day -1st Quarter  = 3/31/2020
@Begin Date = 1/1/2020
@EndDate = 3/31/2020

Example 2:
Today's Date = 04/05/2020
First Day – 2nd Quarter  = 4/1/2020
Last Day - 2nd Quarter  = 6/30/2020
@Begin Date = 1/1/2020
@EndDate = 6/30/2020

Example 3:
Today's Date = 09/25/2020
First Day – 3rd Quarter  = 7/1/2020
Last Day - 3rd Quarter  = 9/30/2020
@Begin Date = 1/1/2020
@EndDate = 9/30/2020

Example 4:
Today's Date = 10/10/2020
First Day - 4th Quarter  = 10/1/2020
Last Day - 4th Quarter  = 12/31/2020
@Begin Date = 1/1/2020
@EndDate = 12/31/2020


Date Variables:
Declare @TodayDate Date
Set @TodayDate = getdate()
Declare @FirstDayOf1stQtrCurrentYr
Set @FirstDayOf1stQtrCurrentYr = DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf2ndQtrCurrentYr
Set @FirstDayOf2ndQtrCurrentYr = DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf3rdQtrCurrentYr
Set @FirstDayOf3rdQtrCurrentYr = DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf4thQtrCurrentYr
Set @FirstDayOf4thQtrCurrentYr = …
0
Hello, all.

OK, working with SQL Server 2019, and it works wonderfully with accented characters.
Dvořák Hall
It displays to the page just fine.
However, when I try to read from a URL QueryString, it's not working.
I get
Dvo
Instead of the full name.

I have at the top of my main page.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Response.ContentType = "text/html"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
%>
 

Open in new window


But it still will not get the URL: /Page/Dvořák_Hall
And display it correctly to the page, to use against the database query.

Any suggestions?
Wayne
0
Hi,

Please see https://www.experts-exchange.com/questions/29173210/SSRS-sort-on-datediff.html

Is there also a way to do a plain sql query (not ssrs)which puts the date diff in a separate (new) column and shows it it days,hours,minutes?

J
0
I have an Access table (tblAgedCaseGoal) with 10 data elements including 'Appeal Number', 'AID' and 'Appeal Assign Date'. How can I remove duplicate 'Appeal Number's where the "Appeal Assign Date' is the oldest date. The remaining 'Appeal Number' should be the latest Appeal Number based on the Assign Date.
0
Hello all;

The query I am working with, I need a subquery to send back multiple records.
I know I have done this before in the past, but cannot find the code or remember what was done.

This is my query. (Of course, it gives this error: Subquery returned more than 1 value.)

SELECT   

(SELECT     distinct(Artists.ArtistName)
FROM            Albums INNER JOIN
                         Artists ON Albums.ArtistID = Artists.ArtistID INNER JOIN
                         Tracks ON Albums.AlbumID = Tracks.AlbumID INNER JOIN
                         Studios ON Tracks.TrackID = Studios.TrackID
WHERE        (Studios.StudioID = Studio.StudioID)) as Artists,


Studio.StName, ArtistPics.PicsPath
FROM            ArtistPics RIGHT OUTER JOIN
                         Studio ON ArtistPics.StudioID = Studio.StudioID
WHERE        (Studio.StName = 'Studio_308')

Open in new window


I need to return all the ArtistName's with a comma between them.

Thanks for any assistance on this one.
Wayne
0
i'm using sql server 2019.

I have a table that looks like this:

Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesReport](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] ON 
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (1, N'Davolio', N'Nancy')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (2, N'Fuller', N'Andrew')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (3, N'Leverling', N'Janet')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (4, N'Peacock', N'Margaret')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (5, N'Buchanan', N'Steven')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (6, N'Suyama', N'Michael')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (7, N'King', N'Robert')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (8, N'Callahan', N'Laura')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (9, N'Dodsworth', N'Anne')
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] OFF
GO

Open in new window



So far i have this query:

DECLARE @IsCurrentMonth VARCHAR(100), @LastName VARCHAR(100), @FirstName VARCHAR(100)
SET @LastName = 'Fuller'
SET @FirstName = 'Laura'
SET @IsCurrentMonth = 'Yes'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[EmployeesReport]

Open in new window



for the WHERE clause I want to use an CASE statement or if else statement.


What i need is the following,

if the parameter called @IsCurrentMonth equals 'Yes' i want the where clause to look like this. Filter by LastName.

-- if @IsCurrentMonth equals Yes use this where clause
WHERE [LastName] = @LastName



if the parameter called @IsCurrentMonth equals 'No' i want to the where clause to look like this. Filter by FirstName.

-- if @IsCurrentMonth equals No use this where clause
WHERE [FirstName] = @FirstName


I know I can just put the whole query inside an if statement, but I know what to apply the CASE statement to the WHERE clause.

What's the best syntax for this?
0
Getting Error, while connecting through SQL Management Studio 18.3.1 from client to server MS SQL 2012 R2. Both the servers have os windows server 2012 R2. Enabled TLS 1.2 and 1.1 In the client-server. PFA error screenshots.
SQL-Error-1.jpeg
SQL-Error-2.jpeg
0
Hello All,

I need to return the first tracking number from a table per order (SOP Number) but I keep getting all the tracking numbers. The lowest DEX_ROW_ID per order is the tracking number I need.

Any help?

This is what I have so far:


SELECT DISTINCT
	SalesLineItems.[SOP Type] ,
       SalesLineItems.[SOP Number] ,
       SalesLineItems.[Actual Ship Date] ,
       SOP10107.Tracking_Number,
	   (SELECT TOP 1 dbo.SOP10107.DEX_ROW_ID) AS ID
  FROM
       SalesLineItems LEFT OUTER JOIN SOP10107
       ON SalesLineItems.[SOP Number] = SOP10107.SOPNUMBE

Open in new window



Results

Order      9877      2020-01-17 00:00:00.000      777xxxxx8696                                   3386856
Order      9877      2020-01-17 00:00:00.000      777xxxxx9420                                   3386857


Thanks,
0
hi,

any offical, practical guide to migrate Oracle and MS SQL to Azure MS SQL ?

Azure do not support Oracle DB yet, right?
0
I am adjusting a VB6 statement in which the application is making a request from the SQL database and the value contains quotation marks in it.  The result is an error:  Incorrect syntax near 'value'  

The requesting statement from VB6 is:

select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Text1 & "'"

If Text1 contains quotation marks in it then the error is produced.  How can I avoid this?
0
Hi Experts,

We have a report based on Nurses table.

Then we have a table named Skilled_nursing_Visit_Notes (Nurse_Table.Nurse_UserName = Skilled_Nursing_Visit_note.Nurse_User_ID_num_SNV) where nurses enter visits and notes for patients.

Under each visit note there is a column named Browser which auto saves the browser version used for that entry.
We also have a calculated field (formula) in the Skilled_nursing_Visit_Notes  table named OutdatedBrowser set as follows.
case when ([@field:Browser] like 'Chrome%' and [@field:Browser]>='Chrome 78.0') or ([@field:Browser] like 'Safari%' and CAST((Right([@field:Browser],(Len([@field:Browser])-Charindex(' ', [@field:Browser], 0)))) as FLOAT) >= 13) or ([@field:Browser] like 'Firefox%' and [@field:Browser]>='Firefox 71.0') or ([@field:Browser] like 'InternetExplorer%' and [@field:Browser]>='InternetExplorer 11.0') then 'No' Else 'Yes' end

Open in new window


Now we would like to add to the nurses report the following.

1- Ability to filter for nurses with Outdated browser (According to latest entry in Skilled_Nursing_Visit_Notes table determined by Date_Signed field which is a timestamp field).
2- View on the report the browser field.

Thanks
0
I am getting a error must declare the scalar variable "@qualityControl "

I think it's because in c# i have it as a checkmark box and in sql it is a bit.  how do I arrange the Parameter? see code.

using (SqlConnection SqlConn = new SqlConnection
                     ("Data Source = gcm10; Initial Catalog = Vendor; Integrated Security = True"))
            {
                string query = "Insert into vendor_approval(name, add1, add2, city, state, zipcode, phone_number, fax_number, contact_name, product_vendor, cert_quality, quality_assurance, product_sample, quality_control, specified_inspection, certificate_conformance, inspection_testing, supplier_testing, quality_system)" +
                    "values (@name, @add1, @add2, @city, @state, @zipcode, @phone_number, @fax_number, @contact_name, @product_vendor, @cert_quality, @Quality_Assurance, @Product_Sample, @qualityControl, @specified_inspection, @certificate_conformance, @inspection_testing, @supplier_testing, @quality_Testing)";
                SqlCommand cmd = new SqlCommand(query, SqlConn);
                cmd.Connection.Open();
                cmd.Parameters.AddWithValue("@name", Ven_Name.Text);
                cmd.Parameters.AddWithValue("@add1", Add_1.Text);
                cmd.Parameters.AddWithValue("@add2", Add_2.Text);
                cmd.Parameters.AddWithValue("@city", City.Text);
                cmd.Parameters.AddWithValue("@state", State_list.Text);
                …
0

Query Syntax

55K

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.