SQL Help & Support | Experts Exchange

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"

7 Comments

LVL 6

Question by

allanau20

1d agoSolvedPrivate

Linq Query Group By City, and Then Person

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

…

3 Comments

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

4 Comments

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

3 Comments

Question by

Brock

3d agoSolvedPrivate

Simplifying a sql query

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

…

2 Comments

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.

6 Comments

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

…

9 Comments

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.

qrySumAgedCase1.xlsx

11 Comments

I'm using the following code and receive:

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

1 Comment

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

4 Comments

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

14 Comments

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?

6 Comments

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.

12 Comments

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 = …

3 Comments

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

7 Comments

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

3 Comments

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.

44 Comments

LVL 31

Question by

Wayne Barron

2020-02-18Solved

SQL Server 2019 return multiple records from Subquery.

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

8 Comments

i'm using sql server 2019.

I have a table that looks like this:

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?

1 Comment

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

1 Comment

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,

17 Comments

LVL 1

Question by

marrowyung

2020-02-18Solved

Oracle and MS SQL to Azure.

hi,

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

Azure do not support Oracle DB yet, right?

15 Comments

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?

14 Comments

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);
…

1 Comment

Hi Team,

Iam running Oracle 11g r2 database , Iam getting the below error when I try to login into the database. Iam new to Oracle DBA .

I tried logging in as below and run the below command

sqlplus / as sysdba

sql> alter database orcl open;

I get the below error

rror starting at line 1 in command:
alter database orcl open
Error report:
SQL Error: ORA-00355: change numbers out of order
ORA-00353: log corruption near block 9972 change 57969482 time 01/28/2020 17:08:22
ORA-00312: online log 1 thread 1: 'E:\APP\oracomp\ORADATA\ORCL\REDO01.LOG'
00355. 00000 - "change numbers out of order"
*Cause: A change number found in the redo log is lower than a previously
encountered change number. The log is corrupted in some way. The
corruption may be at the earlier change or at this one.
*Action: Do recovery with a good version of the log or do time based
recovery up to the indicated time.

Kindly let me know what is the solution for this error and cause of the error. Any help is really appreciated

5 Comments

SQL

Related Topics

SQL

Related Topics