Hi experts,
I have the following table:

Date      Time      mVal      hVal
10/08/2014      00:05      1000      1500
11/08/2014      02:00      2000      2300
12/08/2014      06:00      2500      3000
13/08/2014      14:00      1500      3300
14/08/2014      20:00      1800      2800
01/02/2015      00:05      1000      1500
02/02/2015      02:00      2000      2300
03/02/2015      06:00      2500      3000
04/02/2015      14:00      1500      3300
05/02/2015      20:00      1800      2800
05/08/2016      10:00        800       1200
06/08/2016      12:00      1300      2200
07/08/2016      13:30      1800      3100
08/08/2016      15:00      1900      3500
09/08/2016      18:00      2200      2900

I am trying to write a query to get following data:

For each year, report the minimum value and the maximum value for the that year and show the date and time associated with the respective min and maximum values.

I am currently working in SQL Server 2014 but I would also like to be able to do same in Oracle 11g/12g

Hope this is description is simple enough.

Thanks

My apology our experts , I closed the the question about a code to re link  access to sql server back end on different network wrongly without proper testing. The code below could not work see the errors below:

(1) I have put the code in a module and tried to call it on a login form as follow ( Call AttachSqlServer), this was called on the on-click event, it gave me an error as argument not optional
(2) The following part of the called is also not compiling:

Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer
   End Function


I the issue is on the call function

Below is how I have replaced the parameters:


Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal PassWord As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server or Azure SQL.
' 2016-04-24. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef
   
    Dim strConnect      As String
    Dim strName         As String
   
    On Error GoTo Err_AttachSqlServer
   
    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, PassWord)
   
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
     …

Hi there!

I would like to create a string that

1. Begins with two-digit present year, in our case, 18.

This works:   substring(CAST(DATEPART(yyyy, GetDate()) as CHAR(4)),3,2)

2. Continues with two-digit present month, in our case, 08.

This does not work:  CAST(DATEPART(mm, GetDate()) as CHAR(2))     (It gives 8, when it should give 08)

3. Continues with a (say) ten-digit random string.

This seems to work:  CAST(10000000000*RAND() as bigint)   , say 1231231234  for example

4. CONCATENATE everything, in that order, in our case:   18081231231234

5. Store this in a field defined as nvarchar(50)

Could you please help me create the string ?

Thanks,
fskilnik.

I am having an issue with my SQL 2012 server running an integrity check. When i try to run the job as an SA I get a message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed." I know that SA is a SysAdmin so I don't know what the issue is.

This all started on Wednesday where it looks like an windows update come down on the SQL server and installed while the backup job was running. I restarted the server but it was during work hours so I couldn't re-run the job so I figured it would run last night and finish the job. Unfortunately, there wasn't a job scheduled to run till tonight so I decided to run the integrity check to make sure it won't fail tonight and I am getting the error above.

What can I do. How can I fix this?

I have a SQL statement that works when I enter it manually in SQL Management Studio, but fails when I run it in a C# program. The statement is:

select CaseHistory.CaseNo, CaseInvoice.InvoiceNo, CaseHistory.Code, Paid = cast(Paid as decimal(10,2)), VoucherDate, 
PaymentNo, CaseDebtCollection.RegisteredDate, Debtor.CountryCode, Client.Country from CaseHistory
inner join CaseDebtCollection on CaseDebtCollection.CaseNo = CaseHistory.CaseNo
inner join dbo.Debtor on dbo.CaseDebtCollection.DebtorNo = dbo.Debtor.DebtorNo
inner join dbo.Client on dbo.CaseDebtCollection.ClientNo = Client.ClientNo
inner join CaseInvoice on CaseInvoice.CaseNO=CaseHistory.CaseNo
    LEFT JOIN TempCHUnik ON (TempCHUnik.Unik = CaseHistory.Unik)
WHERE (CaseHistory.Code='301' or CaseHistory.Code='400') and
ClosingDate = '' and CaseDebtCollection.Workflow not in ('0012','0015','0016') and CaseInvoice.InvoiceLineNo=1 and TempCHUnik.Unik IS NULL

Select all Open in new window

When running it in a C# program I get this error in the log:
Invalid object name 'TempCHUnik'.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
The table TempCHUnik is there both before and after, so this is very strange.
I use this code segment to get data from the SQL Server, and it works perfectly with a different sql statement:

      cmd = new SqlCommand(sql, con);

                DataTable table = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(table);
                con.Open();

                dr = cmd.ExecuteReader();
                dr.Read();

Select all Open in new window

Hi,

The below SQL query works for me, but I have one issue.  Which is that if it happens to find a second or third "contact" (this part of the join:  "LEFT JOIN contact ON de_rowid = co_rowid_debtor AND co_done_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)" ) it will return duplicated data.

SELECT DISTINCT bl_rowid
,de_cl_account
,CASE WHEN contact.co_description IN ('@tvarDirectDebitClaim', '@tvarBPAYClaim') THEN 'CLPD' 
WHEN contact.co_type = '22' THEN 'SMS1' 
WHEN contact.co_description = 'Copy' THEN 'CONC' 
WHEN contact.co_description = '@tvarTransVar' THEN 'TRAN' 
WHEN contact.co_description = 'HARD' THEN 'FINH' 
WHEN contact.co_description = '@tvarInfo' THEN 'INFO' 
WHEN contact.co_description = 'MRT' THEN 'RTS' 
WHEN contact.co_type = '1' and co_user_1 = 'Invalid' THEN 'NOAN' 
WHEN contact.co_type = '1' and co_user_1 = '@tvarMD' THEN 'LM' 
WHEN contact.co_type = '1' and co_user_1 = '@tvarMUd' THEN 'DISC' 
ELSE 'SMS1' 
END as TASK
,bl_text
    FROM blobs
    JOIN debtor ON de_rowid = bl_rowid_debtor
    JOIN client ON cl_rowid = de_rowid_client
    LEFT JOIN contact ON de_rowid = co_rowid_debtor AND co_done_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)
    WHERE cl_number ='2005' and de_group_id < '1' AND bl_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)

Select all Open in new window

this is what it returns:
BL_ROWID      DE_CL_ACCOUNT      TASK      BL_TEXT
4961674      471146      INFO      NEW STATUS
…

I had this question after viewing After SQL 2016 Upgrade SA Account Keeps Getting Locked by Backup Jobs.

I am getting the exact same error... except there is no message in the logs about a failed login attempt  by the SA account. And what is even more interesting, the SA password was never changed...

I am administering an isolated SQL Server database, managing a specific task for my company. This database takes data from other sources and consolidates it for marketing.

Two years ago, we updated the server and migrated the database, using SQL 2012 Standard. The server is a Dell

Last month, with the assistance my our corporate DBA, we upgraded from 2012 to 2016 Standard SP1.

Since then, I have been getting these messages about the SA account being locked out... well, why would that do that when the SA account password has never been changed? I can log into SSMS using that password... I can run jobs using that password, as soon as the SQL Server agent tries to run it, the errors fly.

This morning, I have updated the maintenance jobs to use a direct connection to the server vs the "local connection" default. I will test that shortly.

This is one of two problems... the other is different and will be under a different title.

Thank you all in advance!

Andy

How to consume web API  in stored procedure I am using oracle 11g R1 DB  ? Please help with an example.

Hello.

I want to create a trigger on a table on INSERT. So when an INSERT attempts to happen on the table, a SELECT is first run and if the SELECT returns rows, the INSERT is cancelled but the transaction ls logged to another table.

The code that has the SELECT in, is as follows

BEGIN TRAN
IF (EXISTS (SELECT *
FROM DBO.TABLE1 AS A, DBO.TABLE2 AS B 
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.COUPONID = B.COUPONID
AND PREVBALANCE = 4 AND BALANCE = -2
AND ISSUEDLOCALDATE> '3-JUL-2018'))
BEGIN 
	SELECT @@TRANCOUNT,'UNABLE TO UPDATE TABLE 1 WITH THESE DETAILS - CHECK TABLE_ISSUE' 
	ROLLBACK
END

Select all Open in new window

The above works as expected.

I'm now trying to develop a trigger on the table in question:

USE [DB]
GO
/****** Object:  Trigger [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]    Script Date: 08/16/2018 09:41:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]   
ON [dbo].[WIDGET_COUPON]   
AFTER INSERT 
AS 

	DECLARE @inserted TABLE ([couponID] [bigint] NOT NULL,[organizationID] [bigint] NOT NULL, [orgLevelID] [bigint] NULL, [couponTypeID] [bigint] NULL, [accountID] [bigint] NULL,	[promotionID] [bigint] NULL,[customerID] [bigint] NULL,[hashCode] [int] NULL,[couponCode] [nvarchar](32) NULL DEFAULT ((0)),[serialNumber] [nvarchar](50) NOT NULL,[createdLocalDateTime] [datetime] NOT NULL,[createdHostDateTime] [datetime] NOT NULL,[createdLocationID] [int] NULL,[issuedLocalDateTime] [datetime] 

Select all Open in new window

…

Need help with a SQL Server select to join tow tables.  There are duplicate rows between the two tables so I want include the duplicate row from Table A.  A duplicate row is a the same CatNum and Attribute ID.  So join two tables and resolve duplicates by only including the duplicate row from table A.

TableA
CatNum        Attribute ID     Description
999                444                   Test
888                211                    Test 2

Table B
CatNum      Attribute ID
999               444
500               422

Expected Results
CatNum        Attribute ID     Description
999                444                   Test              
500                422
888                211                    Test 2

I have a project for our shipping department - I need to created in SQL query that will provide the following results;
1. group by style & color (example: style:123R12, color: BLK) and create an alpha sequence (A) which 123R12BLK = A

2. if style is the same but color is different (example: style:123R12, color: BLUE) it should be sequence (B) which 123R12BLUE = B and so on.

3. Sequence could go as far A thru ZZZ

I'm looking to optimize the execution plan for a specific SQL statement, so I'm looking at how to achieve this using SQL profiles.

What I'm looking to do is add an index hint to a SQL statement, but this would need to be on the basis of matching against the first half of the statement as the number of predicates following this will be subject to change.

Is it possible to set up a profile based on a pattern match like this? Or should I be looking at something else to achieve this?

Any help much appreciated.

This is a follow-on to this question - https://www.experts-exchange.com/questions/29113292/Need-To-Know-Syntax-To-Display-Attribute-On-Checkout-Page.html

I need to know how to make this show attributes dynamically to whatever is in the shopping cart on the checkout page.

Thanks,