We help IT Professionals succeed at work.

Microsoft SQL Server

164K

Solutions

50K

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.

this code from "slightwv" expert seems work only for the following table

select guid, linkguid, loannumber, lientype, case when linkguid=prev_guid then prev_code else programcode end new_programcode
from (
   select guid, linkguid, loannumber, lientype, programcode,
      lag(guid) over(order by loannumber asc) prev_guid,
      lag(programcode) over(order by loannumber asc) prev_code
   from junk
) x;

Example:

Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    C30

Result
Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    Bond <-- should change to 'Bond'


if the table like the following then the above code won't work.

Example:

Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    C30
{4ertw}  {w234r}       342342342     First Lien          C15
{w234r} {4ertw}        567834566     Second Lien     C30

Result
Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    Bond <-- should change to 'Bond'
{4ertw}  {w234r}       …
0
We have SharePoint Foundation 2013 running on SQL Server 2008 R2 Express, on Server 2012 R2 Standard.  We want to upgrade the SQL Server to a supported version.

1. Which is the latest version that is supported by SharePoint Foundation 2013?
2. Is it a straightforward upgrade of the SQL?  If not, what steps must be taken?
0
I need some help for the SQL Query. I have a loan table (1st lien and 2nd lien). If the loan program is 'Bond' for the 1st lien then I need to change 2nd lien loan program to 'Bond'.

Example:

Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    C30

Result
Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    Bond <-- should change to 'Bond'
0
I have the following formula which works ok

select * from MembersDetails
[Membership Main} in ["Junior Intmd", "Junior U12", "Junior U18", "Social TP", "Tennis", "Tennis 18 25", "Tennis Day", "Tennis Ovr65", "Social"]
where [Renewal Date] > '2020-04-02 00:00:00.000'
and [Payment Date]   > '2020-02-02 00:00:00.000'
order by [Membership Main]


I want the [Payment Date] to show between fixed dates ie 2020-02-01 00:00:00 and 2020-04-02 00:00:00
0
I need to display average labor hours per PN and work performed by month. How would I do that using the example data attached?
EXAMPLE.csv
0
I have a client that runs a number of reports on SSRS.  These reports have been created and tested over the past two years, and have been running great for the last 6 months without problem.

Enter Covid19 and everyone working from home.  Users are now logging into the network via RDS and running the same reports, but many of them are now returning this error message after entering parameters SSRS Error message
The IT guys tell me that they have not done anything to the server, and when I log into the server I am able to run all of the Visual Studio reports without error.
Strangely, when I log into RDS and open the SSRS reports menu, the same way that the other users are doing, I don't get this error.

I do not know how to enable remote errors to get more info about the error.

Would appreciate any recommendations anyone might have.
0
Hi All,

I try below code.


declare @Barangcode VARCHAR(100) = '''CREPE'', ''CREPE 1018 1380'''

  DECLARE @SQL VARCHAR(MAX) = '
  SELECT BARANGCODE FROM TMBARANG
  WHERE BARANGCODE IN
  (
  SELECT ''' + REPLACE(@BarangCode, '''', '') + '''
  )  


PRINT @SQL
EXECUTE (@SQL)

What I want to achieve is show data from TMBARANG that has data 'CREPE' and CREPE 1018 1380 like below code :
 
 SELECT BARANGCODE FROM TMBARANG
  WHERE BARANGCODE IN ('CREPE', 'CREPE 1018 1380')

How could I do it ?

Thank you.
0
I have a number of stored procedures I'd like to search for a particular string.

When I search using syscomments text it doesn't find anything.

However I know for sure it exists in one of the stored procedures, but this particular procedure has over 12,000 characters and the string doesn't appear until over 9,000 characters.

Has anyone any scripts to suggestions for how to definitively search my stored procedures? I have ApexSQL Search but that doesn't seem to actually search the text of a stored procedure and I also have RedGate SQL Search but I only just installed that and it isn't working - I have a support ticket open with them.
0
I have a SSRS report and the cells in one column (MarketPrice) can be a number or text "NA". Is there a way in SSRS to have numbers in raw excel show as numbers even though another cell value in that column is text (NA)? Right now the raw excel dump shows the "Number Stored as Text" green triangle for numbers.Column with Number and Text values.
Thanks in advance.

Rob M.
0
Hello:

I have tried several different ways of combining my two CTEs within one "UNION ALL" statement but am having no luck.

How do I modify my syntax below to accomplish this?

Thank you!

Software Engineer

; with wip as 
(
select NULL as [LOTSEQNBR], FG.BatchNo as [FGBATCH], FG.FormulaId as [FG], FG.[Labor Cost] as [FGLABOR], FG.[OverHead Cost] as [FGOH], 
FG.[Material Cost] as [FGMAT], FG.[Labor Cost] + FG.[OverHead Cost] + FG.[Material Cost] as [FGCOST], FG.[TotalFGWeightYielded] as [FGWEIGHT], 
FG.[Labor Cost]/FG.[TotalFGWeightYielded] as [LABORLB],
FG.[OverHead Cost]/FG.[TotalFGWeightYielded] as [OHLB],
FG.[Material Cost]/FG.[TotalFGWeightYielded] as [MATLB],
NULL as [FORMULA], NULL as [FORMULAMAT], NULL AS [FORMULALABOR], NULL AS [FORMULAOH], NULL AS [Component], NULL as [LOT], NULL as [LOTQTY], 
NULL as [LOTMATUNITCOST], NULL as [LOTMATWIPCOST]
from BM_View_Dashboard_ProductionCostAnalysis FG
INNER JOIN BM010115 BOM ON FG.FormulaId = BOM.PPN_I
where FG.FormulaId = '506' and FG.BatchNo = '50418G12D' and BOM.SUBCAT_I = 1)
select [LOTSEQNBR], [FGBATCH], [FG], [FGLABOR], [FGOH], [FGMAT], [FGCOST], [FGWEIGHT], [LABORLB], [OHLB], [MATLB], [FORMULA], [FORMULAMAT], [FORMULALABOR], 
[FORMULAOH], [Component], [LOT], [LOTQTY], [LOTMATUNITCOST], [LOTMATWIPCOST],
[LABORLB]*[LOTQTY] as [LOTLABORWIPCOST],
[OHLB]*[LOTQTY] as [LOTOHWIPCOST]
from wip
; with wiplot as 
(
select * from (
select DISTINCT LOT.SLTSQNUM AS [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL 

Open in new window

0
Hello all;
Windows 2016 Server CORE
Installing = SQL Server 2019

Windows Installer was not running - started it

I took the configuration.ini file from the SQL Server setup I did earlier.
I edited it to work with Server Core.
Here is the following edit.
;SQL Server 2019 Configuration File
[OPTIONS]

; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, 
; you acknowledge that you have read and understood the terms of use. 

IACCEPTSQLSERVERLICENSETERMS=true

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="InstallFailoverCluster"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. 
; This parameter is required when installing the SQL Server Database Engine (SQL), or Analysis Services (AS). 

INSTANCENAME="SQL2019"

; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. Remove ; to switch on mixed mode
SECURITYMODE="SQL"

; Specify the root installation directory for shared components.  
; This directory remains unchanged after shared components are already installed. 

INSTALLSHAREDDIR="D:\SQLShared64"

; Specify the root installation directory for the WOW64 shared components.  
; This directory remains unchanged after WOW64 shared components are already installed. 

INSTALLSHAREDWOWDIR="D:\SQLSharedx86"

; Specify the Instance ID for the SQL 

Open in new window

0
Hi Experts,

I want to update student status if the date is joindate is more than 180 days old from todays date.

Update Students Set IsNew = 0
Where joindate - todaysdate > 180days	
	

Open in new window


Thanks in advance!
0
Hi

I have to build Excel VBA code in an Excel spreadsheet that looks in another closed Excel file and finds the value at the intersection point between  the word " Sundries" which appears on a random row in column A and the word "Household" which appears in a random column in row 1.

What Excel VBA  code would I use to do this?

Thanks
0
perHello All;

I have just run into a situation where I cannot find any information on it.
Installation of SQL Server 2019 WITHOUT installing Data Quality Services
If I uncheck it, it unchecks EVERYTHING.
So, the question is...
How do you install the SQL Server Database Engine, without installing the Data Quality Services

The reason:
I am installing the "SQL Server Failover Cluster" on the 2016 Desk Experience Server.
After this, I will be installing "Add node to a SQL Server failover cluster" on the 2016 CORE Server.

From my experience, adding the NODE fails every time I have tried to install it on the core.
A person posted but without proof that the Data Quality Services does not run on Server Core.
(This is my thread about the issue)

I am building my SQL Servers Clusters from scratch, and want to get the Core servers running SQL Server database engine.
And whatever I install on the Main SQL Cluster, will filter down into the NODE installations.
So I need to be cautious about what gets installed to make sure everything is going to go smoothly on this build.
These are the typical features that get installed on the NODE's
SQLEngine, SQLEngine\Replication, SQLEngine\FullText, SQLEngine\DQ
So please, if anyone has any information on this TA or the other TA, please let me know.

Thanks.
Wayne
0
I have a SQL table with the following fields:

Customer ID |Fiscal Year | September Amount | October Amount| November Amount ....

I need to change the format so I can produce an SSRS report in the following format by customer:


 Months down the left hand column and Fiscal Years across the top. For example:

 Customer: 1

                         Fiscal Year1 |  Fiscal Year 2| Fiscal Year 3|
September        100.00       |     200.00       | 150.00         |
October              250.00      |     300.00       | 180.00         |
November
December
etc.

For some reason every approach I take leads to a dead end.

Thanks!
0
I have a datetime field that's in UTC. I need to convert it to local time.

Now, we have clients in the US that have different time zones. Clients in Europe. In Australia, etc.

I've been looking at examples and I found this one as an example : Example

Would this take care of converting UTC to correct local time?
0
I am trying to figure out an SQL query to get aggregate calculations from a data table.  The data table is a simple list of dates and data (real).  I am simply trying to get the AVG, MAX, and MIN of the top 100 data points in descending order.  There are several thousand data points in the table.  The Select statement I have used that does not work is:

SELECT TOP 100 Cur_Date, AVG(DataMean) AS DMEAN, MAX(DataMean) AS DMAX, MIN(DataMean) AS DMIN  FROM DataTable WHERE PartNumber = ? ORDER BY Cur_Date DESC.

Can anyone help?

Thanks,
0
I have a table with about 5 million records.
I would like to use MS Excel's Get External Data to retrieve records for a specific day (only a few thousand records).
The challenge is that Get External Data wants to return the entire table.
How do I create a query with Get External Data and pass a date parameter so that only the records for that day are returned.

Thanks.
0
Hello Experts,

How do I get the top 20 by Charges for each Store Name ?

I know this is just easy but I cannot figure it out.

See example attached.

Thank you for all your help.
GetTheTop20ChargesByStoreName.xlsx
0
I have an ssrs report (sql server 2014) that takes county in as a parameter (10 possible counties)

I have a file share set up for each of the counties

Would like to write a single data driven subscription that will write the output for each county to the file share dedicated to the county

is this possible?

All the searching Ive done seem to be for emails
0
I am new to writing API's, and was wondering where the best place to start was with writing a RESTful API for a locally hosted SQL2017 server. I have written an app using angular, and wish to access my data that is located on the local network (installed on a windows 10 machine) through a API.
I already have a VB.net project that connects to the database (each practice has a local server and program that connects through the local network).  I am writing a app to give more functionality to the clients, and need to access this data from the newly written app (using ionics and angular, writen in visual studio code).   As I am new to API's, I was wondering where the best place was to start, or any good examples on how to build a basic api.  I would also like to know if I can integrate this into the existing VB.net project, as the project and sql server are already at clients.
0
Are there any way to add number days skipping Saturday and Sunday?

For example, if I add 10 days to 2020/4/1, the result is 2020/4/11. It's appreciate if I can know any good way to add days considering Saturday and Sunday.
I'm using SQL Server 2017

SELECT DATEADD(DAY, 10, '2020/4/1');

I want to get an result of 2020/4/14

2020/4/1 Wed
2020/4/2  Thr
2020/4/3  Fri
2020/4/4  Sat <- skip
2020/4/5  Sun <- skip
2020/4/6  Mon
2020/4/7  Tue
2020/4/8  Wed
2020/4/9  Thr
2020/4/10  Fri
2020/4/11  Sat <- skip
2020/4/12  Sun <- skip
2020/4/13  Mon
2020/4/14  Tue
0
Hi Expert,
I am having a big problem, The SolarWindsOrion database went to suspect mode after windows update. And we have tried all option to recover the database. No luck.

Tried repair option and it failed. Tried last full backup restore and the got the below error even resource governor is disabled.

Msg 41379, Level 16, State 0, Line 2
Restore operation failed for database 'SolarWindsOrion' due to insufficient memory in the resource pool 'default'. Close other applications to increase the available memory, ensure that both SQL Server memory configuration and resource pool memory limits are correct and try again. See 'http://go.microsoft.com/fwlink/?LinkID=507574' for more information.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'SolarWindsOrion'. CREATE DATABASE is aborted.

Any idea?
0
When running the following query, it never completes and pins the SQL Server at 100%. Found that if I comment out this below, it works fine:

AND (WOS.TAT_STOP_DATE_CUST_V2 IS NULL OR (DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())<=36)) --AND WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE AND WOS.TAT_STOP_DATE_CUST_V2<=GETDATE()))

I cannot find a problem with the data in these columns.

SELECT
	data_query.*,
	SUM(PROFIT.REVENUE) REV,
	SUM(PROFIT.COGS_LABOR+PROFIT.COGS_PARTS+PROFIT.COGS_SUBCONTRACTING+PROFIT.COGS_REWORKS+PROFIT.COGS_SRU_EXCH+PROFIT.COGS_MISC) COGS, 
	SUM(PROFIT.REVENUE) - sum(PROFIT.COGS_LABOR+PROFIT.COGS_PARTS+PROFIT.COGS_SUBCONTRACTING+PROFIT.COGS_REWORKS+PROFIT.COGS_SRU_EXCH+PROFIT.COGS_MISC) MARGIN_WO
		
FROM (

	SELECT WOS.WO_NUMBER,
		WOS.WO_TYPE,
		WOS.ACCT_COMPANY,
		WOS.DEPT_NAME,
		CASE WHEN WON_AUTO_KEY=2 THEN 'GSTE'
			WHEN WON_AUTO_KEY=7 THEN 'OTHER/NOT CLASSIFIED' --'AF WH - DORAL 2'
			WHEN WON_AUTO_KEY=9 THEN 'DORAL 1'
			WHEN WON_AUTO_KEY=10 THEN 'MIAMI'
			WHEN WON_AUTO_KEY=11 THEN 'DORAL 2'
			WHEN WON_AUTO_KEY=12 THEN 'ATLANTA'
			WHEN WON_AUTO_KEY=13 THEN 'PHOENIX'
			WHEN WON_AUTO_KEY=14 THEN 'LOUISVILLE'
			WHEN WON_AUTO_KEY=32 THEN 'OTHER/NOT CLASSIFIED' --'BIC POOL - DORAL 2'
			ELSE  'OTHER/NOT CLASSIFIED'
		END LOCATION,
		WOS.SHOP_TYPE,
		WOS.MFG_CODE,
		ISNULL(TECHNO.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "TECHNOLOGY",
		ISNULL(PROD_GROUP.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "PRODUCT GROUP",
		

Open in new window

0
Hi experts,

I'm using SQL Server 2019.

I have a table called Employees that looks like this:

TableEmployees.PNG
I have another table called PromotedEmployees that looks like this:
This table holds some employee names and their new title.

PromotedEmployees.PNG
This is the sql script to create the Employees table:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 

INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (2, N'Fuller', N'Andrew', N'Vice President, Sales')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (3, N'Leverling', N'Janet', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (4, N'Peacock', N'Margaret', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (5, N'Buchanan', N'Steven', N'Sales Manager')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (6, N'Suyama', N'Michael', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], 

Open in new window

0

Microsoft SQL Server

164K

Solutions

50K

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.