Improve company productivity with a Business Account.Sign Up

x

Microsoft SQL Server

160K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

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

Sign up to Post

How can i take this recursive  CTE and convert it to a inline CTE for better performance?


			
	;WITH cte (StartDateUtc, EndDateUtc, OffhireDaysPerCalendarDay) AS (

					SELECT 
						@StartDateUtc,	
						CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, @StartDateUtc))),			
						CONVERT(NUMERIC(28,17), CONVERT(NUMERIC(28,17), (DATEDIFF(SECOND, @StartDateUtc , DATEADD(DAY, 1, CONVERT(DATE, @StartDateUtc)))) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay)

					UNION ALL

					SELECT 
						CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc))),
						CASE WHEN DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) = CONVERT(DATE, @EndDateUtc) THEN @EndDateUtc ELSE CONVERT(DATETIME, DATEADD(DAY, 2, CONVERT(DATE, StartDateUtc))) END,
						CASE WHEN DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) = CONVERT(DATE, @EndDateUtc) THEN CONVERT(NUMERIC(28,17), CONVERT(NUMERIC(28,17), (DATEDIFF(SECOND, DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)), @EndDateUtc)) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay) ELSE @OffhireDaysPerCalendarDay END						
					FROM cte
					WHERE DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) <= CONVERT(DATE, @EndDateUtc)
				)

Open in new window

0
What Kind of Coding Program is Right for You?
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Hi All,

I have SP like below :

USE [GPI_TRADING]
GO

/****** Object:  StoredProcedure [dbo].[ERV_SP_Update_Empty_Tax_No]    Script Date: 4/19/2018 4:06:51 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[ERV_SP_Delete_Stock_Detail_Roll]
      -- Add the parameters for the stored procedure here
      @NoTransaksi AS VARCHAR(30) = ''
      , @IDStock AS BIGINT = 0
        , @Year CHAR(4) = ''
      

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
 
 DELETE FROM TMSTOKBARANGDETIL2018
 WHERE NoTransaksi = @NoTransaksi
 AND IDStock = @IDStock

END

I want to use variable for TMSTOKBARANGDETIL2018 into TMSTOKBARANGDETIL@Year

How could I do this ?

Thank you.
0
Hi Experts,

I'm looking to write SQL statements affecting all tables of my database.
For example

A- Create a new field for each table Named DateExported (DateTime)
B- Set that DateExported field to today's date for all records in all tables.
C- Write some SQL like the following, "Delete * from ...(each table name here) Where DateExported is null"

How can these be accomplished the easiest way?

I recall doing that in excel a while back, by setting a formula and dragging down to all rows but dont remember exactly the formula.
I do have an excel sheet containing all my table names.

Thanks in advance
0
How do I remove the slashes '/' from the date field, here's what I have:

CONVERT(varchar, aam.orderStatusDateField, 101)       

and this is what prints:  '02/14/2018'  

I need to print :  '02142018'
0
Hi Experts,
This is my query:
SELECT * FROM WorkSchedules WHERE StartTime <= '1899-12-30 09:30:00.000'
How can I ignore the date and select records that are earlier than 09:30
I would like to achieve this because when a record is updated, it becomes 1900-01-01 09:30:00.000 in the SQL Server database, and my query won't return the expected rows.
I would appreciate your help.
0
Cloned a sql database server for testing.  Renamed the physical server name to dev.  The SQL Server still has the name of the live server, how do I rename it to Dev?  Microsoft Server 2008 R2 with SQL Server 2008 R2
0
Hello,

We would like to have an pivot-ouput, but it's coming from 3 tables

We have the following tablestructure (by design, we cannot change it)

UP (with 2 columns ID and Description)
1   FirstUp
2   SecondUp
...

SampleType (ID, Descr, ...)
10   SampleA
20   SampleB      
30   SampleC
...

SampleUp (ID_UP, ID_SAMPLE, TYPE)
1   10    W
2   10    W
1   20    W
2   30    W
....

We would like to have the following outcome with above example
                       FirstUp          SecondUp  ....
SampleA          1 (or yes)        1
SampleB          1                      0
SampleC          0                      1
SampleD          0                     0
...
0
sql.Format("SELECT A.DebitorenNr, A.Name1 AS Name,A.Ort, SUM(B.Einheitbez14wert)AS Gewicht,\
					  COUNT(B.ABNr) AS Auf\
					  FROM Auftragsbestätigungen AS A, Auftragsbestätigungspositionen AS B\
					  WHERE A.ABNr=B.ABNr AND\
					  A.FakturiertAktiv=%d AND A.storniertAktiv=%d AND\
			                  B.Einheitbez1wert<>%d AND B.Einheitbez1wert<>%d AND\
                                          B.Einheitbez1wert<>%d AND\
                                          B.KalkulationMetallwarenIDNr<>''\
					  GROUP BY A.DebitorenNr ,A.Name1, A.Ort\
					  ORDER BY CAST(A.DebitorenNr AS BIGINT)",0,0,50,97,0);

Open in new window


I am filling my list view with these data:
Holder_Name                 = m_ptrRs->GetCollect("Name");
Holder_Ort                      = m_ptrRs->GetCollect("Ort");
Holder_KundenNr           = m_ptrRs->GetCollect("DebitorenNr");
Holder_Gewicht              = m_ptrRs->GetCollect("Gewicht");
Holder_Auftraege            = m_ptrRs->GetCollect("Auf");
I now what to add a column with the last Order No = ABNr and the newest order date = ABdatum

Open in new window


Hello,
I am filling a list view with these data. I also would like to list the last (highest) ABNr. No. and the last (newest) order date for
each  Name that were saved in the sql table.


In the table = Auftragsbestätigungen is the order date = ABdatum (datetime)

Please help.
Thank you very much.


Best regards,
Thomas
0
I need to do a double pvit with counts

Select Origin, IndividualID, Cycle, StateCode from Individuals
There are 6 cycles and 56 "states"

The count will be on IndividualID with a PIVOT on the Cycle and the state codes

An example output would be...

Origin      Cycle1     Cycle2     Cycle3     Cycle3     Cycle4     Cycle5     Cycle6     AL     AZ     etc...
MyOrigin    3              30            19             21             3               5              6         22       54    etc....
0
I have control files that we use to upload data onto Oracle 9i server.  I need to rewrite these control files to T-SQL (we will be migrating to a SQL-Server). I've searched the web but haven't found any articles on creating control files in T-SQL.  

I've seen many articles on BULK INSERT but it does not apply to my situation.  The control files that we have do not include all the fields that the table has; also, the flat file containing the data to be loaded does not have the fields in the corresponding order as of table.

Can anyone point me in the right direction on writing a script file that will allow me to load the data in a specific order that is not based on the tables' field order?

Thank you
0
A proven path to a career in data science
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

need to run several Sql Server 2014   select queries and write the results to excel sheets

Example
select docname from TbDocs where plant = 'Plant1'   -   Write to Plant1.xlsx
select docname from TbDocs where plant = 'Plant2'   -   Write to Plant2.xlsx
select docname from TbDocs where plant = 'Plant3'   -   Write to Plant3.xlsx
.. has 20  plants total
I need this Each plant data going to separate excel files , due to some requiremnt
(NOTE:   It can be text files instead of excel files, If I can read it in excel later)
Can I have a batch sql script running and writing it to 20 Plant excel files in one shot?
0
Hello,

How can I modify the following script to set min and max memory identical :
DECLARE @MaxMem float
if @IfExecute=1 EXECUTE sp_executesql @SQLString ;

IF @@microsoftversion/0x1000000 IN (11,12,14)
      set @SQLString = N' Select @MaxMemOUT =physical_memory_kb/1048576.00+1 FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE)  ;' ;  -- 4193848KB or ~3.99GB of RAM for ''ADM-SQL14-1'' as an example
else
      set @SQLString = N' Select @MaxMemOUT =physical_memory_kb/1048576/1024.00 FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE)  ;' -- 4193848KB or ~3.99GB of RAM for ''ADM-SQL14-1'' as an example
Print @SQLString ;
SET @ParmDefinition = N'@MaxMemOUT float OUTPUT' ;

Thank you

Best regars
0
Hi,
What is the quickest way to transfer the following Excel data
to MYSQL. I have installed the MYSQL FOR EXCEL plugin.
Just under one million rows and 157 columns and size 138mb
Thanks
Ian
0
Hello all,

In SSMS, when you
Right Click "Edit All Rows"

When you click on a column, it will jump to the end of the text in that column.
Is there a way, to make it jump to the beginning of the column, instead of the end?
0
Hello :)

I'm having some difficulties in doing this statement .

Can anyone help me out? It's the first time I'm using access .

= DSum("[Quantity]", "[ MSP]", "[ ProductCode] LIKE 'EVWP*' AND "[Excluded]"=”FALSE”)

Cheers
0
Hello expert,

This query works
SELECT SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue)
from
ISWareHouse.dbo.CCMProtocolComponents
group by CCMPayerID

and returns a list of revenue by Payers

and this query works
SELECT ISOps.CCM.CCM_PAYER.PAYER_ID,
ISOps.CCM.CCM_PAYER.PAYER_NAME
from ISOps.CCM.CCM_PAYER

Returns Payer_IDs and PAYER_NAMES

Need to join the SELECT(SUM) query
with a PAyer_NAme join query on the PAyer_IDs

Have tried

SELECT SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue),
ISOps.CCM.CCM_PAYER.PAYER_NAME
from
ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID

returns
The multi-part identifier "ISOps.CCM.CCM_PAYER.PAYER_NAME" could not be bound. (sic)

Have also tried
SELECT ISOps.CCM.CCM_PAYER.PAYER_NAME, SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue),
from
ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID

Returns
Incorrect syntax near the keyword 'from'.

Tried just doing the join w/o the name display
SELECT SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue) as Rev
from
ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID

This runs with no error

I think the error in the 'cannot be …
0
I'm trying to write an SQL script to find the next to last record added to a table. The table in question (it's a table that tracks status changes to records that are in another table) has one row for every status change that is made to a record that is in the other table. So, there is a one to many relationship between the two tables. Also, every row in the status change table is date and time stamped.

For the sake of simplicity, let's call the status change table "TBL_B" and its "parent" table we can call "TBL_A". The 2 tables are related to one another through a single column (TBL_A.PONUM and TBL_B.PONUM).

What I am looking for is not the most recent record that was added to TBL_B for every record that is in TBL_A, but the next most recent. For example, assume that TBL_B has 4 rows in it for a single row in TBL_A as follows:

Row 1 was added on 1/1/2018
Row 2 was added on 2/1/2018
Row 3 was added on 3/1/2018
Row 4 was added on 4/1/2018

I want my query to return only Row 3

This is for a SQL Server DB

Any ideas / assistance / guidance is much appreciated
0
Hi Experts,

We are running a server with SQL server express which has the recovery model set to simple and the log file (.ldf file) for one of the database has been growing in file size significantly and appears to be growing a fair amount each day & eating into free disk space. Currently the log file is 17.6gb and the database file is 2.10GB.

Initial size (mb) of the database is 2161 and the log file is 18117
Autogrowth / Maxsize of the database is By 1mb, unlimited and the log file is by 100mb, limited to 2097152. Note we did have the log file set to autogrowth by 10% a few days ago but changed to 100mb hoping this would hel, however the log file still seems to be growing too much.

Under the general properties of the Database it says the last database log backup - None, the last database backup is today so not sure if this has something to do with the situation, since we are using the simple recovery model maybe that why it says the logs have never been backed up?

Any recommendations on something we can do to reduce the log file size?, ideally we dont want to impact performance of the database if this can be avoided?

Thanks
0
Hi Experts,

We have a server running MS Express SQL server, and databases are set with the simple recovery model and we are backing up three databases with a batch file script as follows:

SQlCmd -E -S SQLServerA -Q "BACKUP DATABASE [SSAdmin] TO DISK='C:\DB_Backups\SSAdmin.bak'"
SQlCmd -E -S SQLServerA -Q "BACKUP DATABASE [SST] TO DISK='C:\DB_Backups\SST.bak'"
SQlCmd -E -S SQLServerA -Q "BACKUP DATABASE [TAPlusRepository] TO DISK='C:\DB_Backups\Current\TAPlusRepository.bak'"

However we would like to keep 4 days worth of copies on disk of the databases (for all 3 DB's) and automatically remove the older backups. The intention is to set this up on a daily schedule in windows task manager. ideally appending the current date to each of the backup files names each time the script is run. Any recommendations on how this can be done?

Thanks
0
Build your data science skills into a career
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Hi Experts,

I'm looking to modify the below query to

A) include a yes/no field, if the table specified has a column named Emp% should be true and false otherwise.
B) add another column, if table specified has column DateEntered and there are records within last 30 days should be true and false otherwise.
SELECT      T.name TableName,i.Rows NumberOfRows
FROM        sys.tables T
JOIN        sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE       indid IN (0,1)
ORDER BY    i.Rows DESC,T.name

Open in new window

Thanks
0
Good afternoon, I have a question, is it correct to configure the SQL Server Databasemail to send mail, this violates the security of our server ?, if it were the case that another solution is had.

This commented since I want to do it so that I get emails when there is an error in several JOB, either maintenance or sending reports or internal processes of the company.

Thanks for the help.
0
I had a database I could not access until I set it using the following...

ALTER DATABASE emergencydemo SET EMERGENCY;
    GO
ALTER DATABASE emergencydemo SET SINGLE_USER;

How do I get it back to normal mode?
0
I am trying to write a query that will do the following.

I have a table with seperate sales order lines on it. Each line details the customer, product sold, the price sold at, and the date of the sale.

I am trying to establish for each product code, what the last price we sold it for was for each separate customer.

For example using my input below I would expect Product code ABC to return '10' for Brian, '20' for Gary, and '50 for Sam.

Below is complete set of results I would expect for all product codes.
0
I have a string that looks like this:
NOTE - 2 PIECES REQUIRED #02021336173R0 1.50 X 83.25 QTY=2

I need to find the position where there is a " X " and then get the number before this position.  So in this case I need to extract 1.50

How would I do this in t-sql?
0
Hi Experts,
Need help performing a DB backup thru SSMS 2008.
Tried right clicking on desired database and selecting backup
When running I get the attached error.
Did that successfully in the past, not sure whats currently missing here..
Thanks
Untitled.png
0

Microsoft SQL Server

160K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.