Query Syntax

53K

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.

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

Sign up to Post

sum fields after being separated into new fields from query

I have a query:
That takes a field that looks like :
I have a field called
PICKCOUNTS   that looks like  

PICKCOUNTS
123-43-456-56-564-65-0
The query belows  separates the data like below
Exact     Function     DM       Match       OUT_OF_SYSTEM          Xtra            DO_NOT_COUNT
123        43                 456        56                 564                             65                    0

Now i need to sum all the new columns/fields

Exact:Function:dm:match
123+43+456+56

SELECT     
[AUTOMATCH]
      ,[POSTCOUNTS]
      ,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 1, '-') AS EXACT
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 2, '-') AS FUNCTIONAL
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 3, '-') AS DM
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 4, '-') AS MATCH
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 5, '-') AS OUT_OF_SYSTEM
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 6, '-') AS X
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 7, '-') AS DO_NOT_COUNT
      ,[MANUALMATCH]
      ,[EXCPETION_DIRECT]
      ,[AUDITEDBY]

FROM  [Metrics].[dbo].XREF_HEADER

Open in new window


FUNCTION

fUNCTION:
FUNCTION [dbo].[SEPARATES_COLUMNS](
@TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END 

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1 

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END

Open in new window





thanks
fordraiders
0
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

I'm trying to create what I expect would be a very simple query in Microsoft Access. I've added a screenshot that shows the query for reference. I have one criterion specified the references a date range on a form, which causes the query to select all matches records, which works fine. There are multiple other columns which are true\false that I would like to design the query to select if any one of those has a "true" value. I thought this would be very simple... basically if ANY of the records have a true value for ANY of the columns, then then that record would appear in the results, however all records where there wasn't AT LEAST one true value across all of the columns would be excluded.
0
here is my situation

i have 3 sql server tables
one is a customer table
one is an orderhistory table
and

one is a log table

the customer table has customerid along with name mailing address and email address

the orderhistory table has customer id and order total amount and order date

the log table has 2 fields
customerid
and insert date

I need to write an entry into the log table with the customerid for those customers that have placed an order over $50 in a given month that have an email address

this process is once a month on the 15


i need to check the log table before writing an entry to make sure customerid does not already exist

here is some sample data

customer table

customerid emailaddress
12345           jdoe@gmail.com




orderhistory table

customerid  order total orderdate

12345    40   11/20/2018
12345    60    02/19/2019
12345    70    04/14/2019

log table would have an entry
for customer 12345 since his 60 order on 02/19/2019

would have generated an entry

therefore
when the process ran on 4/15. i would not have wanted another entry written for customer 12345 written
even though there was an order over 50 placed on 4/14/2019

i was thinking of needing to combine a sql not in for the order amount rule and a not exists to determine if record already exists

looking for an example though

thanks!
0
How i Write a sql query to retrieve the loan number, state and city, customer first name for loans that are in the states of CA,TX,FL,NV,NM but exclude the following cities (Dallas, SanFrancisco, Oakland) and only return loans where customer first name begins with John.?
0
Our SQL cluster, for many months, has "‘Failed” status under Roles > Resource Group. But the cluster is working fine!

In the System event log, every hour, there is an Event ID 1069 error (Source: FailoverClustering): "Cluster resource 'SQL Server CEIP (MSSQL2016)' of type 'Generic Service' in clustered role 'RsrcGrp-MSSQL2016' failed." I have taken corrective steps given in MSKB: #883732, but it has made no difference.

On running Cluster Validation tests, we see only a Warning. It simply repeats what is in the event log.
BTW, this cluster is based on 2 VMs in VMware infrastructure.

How may I get rid of Failed state of Resource group.
SQL-Cluster-ResourceGrp-Failed.JPG
0
i have a query "select firstname, lastname, state from customers" nut I want to read like below

CA  john smith, mary smith
TX   john chan, mary chan
FL   John Khan, mary khan

Is it possible in MS SQL Server? Thanks
0
Hi!

I wish you could help me with the following question about SQL Server and a performance problem I have:


Old Server:

I have a closing and opening process on my system that takes about 11 minutes.  


My storage is:
3Par 7200.
Raid5 15K SAS ( 35 disks)
Block Size: 128KB

IOPS  can deliver as maximum for Reading and Writing in a native way:  IOPS 9950


 3par-7200.png

The server has the version of SQL Server 2008 R2 Enterprise Windows with 2008 R2 Enterprise. It has 2 Processors with 16 Cores but the SQL Service only recognizes 16 cores.  

I haven't been able to capture the IO statistics or specific time in the processes because this process has many cursors and makes the SQL Server crash. Neither have I been able to capture the execution plan by the cursors that the process has.

New Server:


The same process takes 8 minutes but writing IOPS rise from 800 to 2000. Reading IOPS decrease from 1000 to 400.

 3Par 8200 with the following features:

Raid5 SAS of 10K ( 10 disks)   IOPS 2568Max. For CPG FC_Raid5_SinAO
400 Gb SSD disk arrays ( 8 disks)  IOPS 100239 Max For CPG_SSD_Raid5
Block Size: 32KB

IOPS can deliver as maximum for Reading and Writing in a native way: IOPS 2568
IOPS with AO 30,000 Max Reading ( The writing processes depend on the Native IOPS of the CPG used in this case FC_Raid5_10K )

3par-8200.png
The server has the version of SQL Server 2016 Enterprise Windows with 2012 R2…
0
Hi,
I am importing data from a CSV file into SQL Server Express and one of the columns contains data format  £5,420 or  €5,420
Can the SQL table column be modified to accommodate data ? I would prefer it to be converted to format  5420 or  5420 if possible.
How can I go about it.
Thanks
Ian
0
I am analyzing  what is the best portable solution for a database....and it seems the question is between SQL LITE and MS SQL CE 4.0 sp1.

With that said I have a very large table in SQL Server.  So far it has created some 500 files of SQL for CE and it looks like LITE will be even more as the file sizes are smaller.

With that said It looks like Microsoft has pretty much abandoned CE compared to LITE.  

I will need to use this in a Winform c# app that will connect to one table...perform a very basic LINQ query.

so my question is 2 parts.  Which is the best.

And finally since this is creating so many files in my migration ...what is the best way to execute these files versus clicking on them and running them one at a time.

I would imagine they could be ran in batch with powershell or someothe tool.

Any guidance would be great!


Thanks,
0
Currently we have a DMZ with some external facing web servers that are only available from the outside world via port 443.  The SQL instance for the back end of this application is on a server that is in a trusted LAN with only holes poked in the firewall between the DMZ and the trusted LAN for Active Directory, SQL, DNS and some others used for a managed Antivirus client installed there.

I am under the impression that joining these web servers to the internal windows domain is less secure that if we didn't join them to the domain.  Right now I am testing a new web server that is not part of our domain and I am having trouble getting the web server to access the database on our sql server (integrated authentication) which is part of the domain.


I realize we can use sql authentication but I understand there are some risks involved there as well.  I have tried adding a local user to both servers as if both where part of a workgroup to no avail.  Any other thoughts here?


I was also contemplating doing a RODC in the DMZ (or possibly in the trusted lan and use firewall rules to direct all AD traffic to that server).  The final idea I had was setting up a new forest in the DMZ and establishing a trust relation ship between the new forest and the internal forest.


Any thoughts or experiences welcome.  Thanks!!
0
HTML5 and CSS3 Fundamentals
LVL 13
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

select
	DATE(quotes.create_date) as create_date,
	quotes.year,
    quotes.make,
    quotes.model,
    quotes.type,
    quotes.city_org,
    quotes.prov_org,
    quotes.city_dest,
    quotes.prov_dest,
	quotes.quote_amount,
	quotes.origin_shipping_type,
    quotes.dest_shipping_type,
    quotes.order_note,
    notes.note_text
from notes
join quotes on notes.quote_no=quotes.quote_no
where 
(
	quotes.quote_type = "EN"
	OR notes.note_text like "%non runner%"
	OR notes.note_text like "%nr%"
    )
AND notes.note_text like "(by:%"
AND notes.note_text not like "(by: Roger) T%"
AND notes.note_text not like "(by: Doug) T%"
AND notes.note_text not like "(by: Larry) T%"
AND notes.note_text not like "%Quote Source%"
AND DATE(quotes.create_date) >= '2017-01-01'

Open in new window


How can I concatinate the note_text so there is only a single row with concatinated notes instead of duplicate rows with different note text?

Sample output attached, I'm trying to get the notes (last column in attached image)  concatinated and only 1 row returned instead of 2 and the notes column should show "(by: Steve) non runner, (by: Steve) 3300" all in the same string.

I tried using concat and group_concate and it didn't work so well... need help
sample.JPG
0
I am using ASP.NET with an MVC 5 project and entity framework. I want to remove the required attribute that is created implicitly based on the SQL database field. I keep getting entity validation errors on the EMailAddress2 field in the form. I tried these two things among others.

1)  I added @{ Html.EnableClientValidation(false);} to the code in the create view for that div, but that did not resolve the issue.
2) I added DataAnnotationsModelValidatorProvider.AddImplicitRequiredAttributeForValueTypes = false; to the global.asax.

Create view code:

<h2>Create</h2>

@{ Html.EnableClientValidation(false);}
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>user</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.CustomerName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustomerName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustomerName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.AccountNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model 

Open in new window

0
Hello,
this is more advice than question...
I have data in SQL (sort of Active Directory Dump) where I have user, his manager & then Department
What I am looking for is to use JavaScript to create Org Chart.

The idea is to make changes in SQL & the chart should reflect it, I want it to be dynamic, in a way users can collapse & expand the branches & stuff like that....

Any guidance is highly appreciated.

Regards,
A
0
The following query is returning a null for the column description.

Please recommend any other way to get the value.
I think the html is getting in way or my xquery statement needs refining

Here is the entire example:

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
					DROP TABLE #T1

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
					DROP TABLE #T2

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL
					DROP TABLE #tmp

IF OBJECT_ID('tempdb..##T1') IS NOT NULL
					DROP TABLE ##T1
IF OBJECT_ID('tempdb..##T2') IS NOT NULL
					DROP TABLE ##T2
IF OBJECT_ID('tempdb..##T3') IS NOT NULL
					DROP TABLE ##T3
IF OBJECT_ID('tempdb..#tmpXML') IS NOT NULL
			 DROP TABLE #tmpXML
IF OBJECT_ID('tempdb..#tmpXML2') IS NOT NULL
			 DROP TABLE #tmpXML2


DECLARE @fileName varchar(100)
DECLARE @TableName varchar(Max)
DECLARE @FolderName varchar(100)


DECLARE @ReqID nvarchar(50)
DECLARE @ProjectDescription nvarchar(MAX)

DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML  XML

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
DECLARE @query2 NVARCHAR(MAX);

SET @TableName = 'BringDownCertificate';
SET @FolderName = 'BDCERT'

declare @fileData xml = '
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" 
xmlns:tns="http://tempuri.org/" xmlns:xhtml="http://www.w3.org/1999/xhtml" 
 xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" 
xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" 

Open in new window

0
Let's say I have two tables:  TableA and TableB - FieldA has some values similar in FieldB and I want to do a compare.
The problem with TableB and it's FieldB - the values are longer than what's found in TableA, FieldA.  

To explain further I provided a sample database:

Table A - FieldA
University191803ABC
UNIVERSITY1923032304
University192305H2307
UNIVERSITY19121F51
UL158

TableB - FieldB has:
UNIVERSITY19251
University191803
UNIVERSITY20101
UNIVERSITY192303
UL1580AH14L

I want a query listing with a join to show the following results:

(Table A, TableB combined)

FieldB, FieldA
University191803ABC, University191803
UNIVERSITY1923032304, UNIVERSITY192303
UL158, UL1580AH14L

These match because the first set of characters match- from the start of the value.  

(University191803 for the first one.  University192303 for the second row.)
The first two are 19 characters that match whereas the last example grabs the first 5 cause of the smaller value: UL158....

How would I go about creating such a query in Microsoft Access?
ee-joinleftdata.accdb
0
Server 2008 Standard -- SQL 2014 -- Need to recover two folders from RAID 5 (4 disks).    System failed to boot and rather than spend incredible amounts of time (beyond attempting to boot to 'last known' or attempt 'safe mode'), just popped in four new drives and reinstalled operating system and SQL2014.

However, I need a couple of folders off the broken array.   Any suggestions on how to get these rather than send the drives out?   It sure would be nice to get the server back online so staff can come back to work.  (sigh).
0
I have an Sql Server table Tb_Docs with RevID column. sample Values below
   _25_1_N_
 _2_1_N_
_175_N_
_A_N_

I need to get the substring as follows
   25
  2
 175
 A
0
Hi

I have an Excel VB.net Add-in project that interacts with an Azure SQL database using a connection string.
I want to control which tables a user can see and edit.
What is the best way to do this. Should it be set up inside the Azure  SQL database per user name
and then a different connection string given to various people?

Thanks
0
I am looking for a SQL server script that would do the following:

1) Uncheck the "Enforce password policy"  for ALL the Logins(sql server authentication) that have the
   "Enforce password policy" enabled
2) and logins that are mapped to one specific database
3) and is not a system login (like ''sa'')
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hi Experts,

I am receiving complaints from users from time to time that they are getting an error on a linked SQL view that could not be updated (see attached).

However the strange thing is that when I try doing that same change on my pc it works, moreover after a while (sometimes next day) the error doesn't occur on theirs either...

Any idea how to troubleshoot this?

Below is the SQL of the view in question.

SELECT     dbo.Skilled_Nursing_Visit_Note.ID, dbo.SNV_Printed_History.VendorsID, dbo.SNV_Printed_History.SNV_ID, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, dbo.Skilled_Nursing_Visit_Note.Visit_Date, dbo.Skilled_Nursing_Visit_Note.Shift_From_Hour, 
                      dbo.Skilled_Nursing_Visit_Note.Shift_To_Hour, dbo.Skilled_Nursing_Visit_Note.Date_Signed, dbo.Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, dbo.SNV_Printed_History.ReviewedBy, dbo.SNV_Printed_History.ReviewedDate, dbo.SNV_Printed_History.PrintedDate, 
                      dbo.SNV_Printed_History.PrintedBy, dbo.Skilled_Nursing_Visit_Note.Client_First_Name, dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Date_Of_Birth, dbo.Skilled_Nursing_Visit_Note.Shift_From_Minute, dbo.Skilled_Nursing_Visit_Note.Shift_To_Minute, 
                      

Open in new window

0
This is something that I've had trouble with over the years.   In the script below I need to move the date script in the where statement into the select.  so I want to the the last 365 days for po_count and line_count.  This way I see all suppliers even if they have a 0. The issue is the group by as I don't want to add date_created into the group by or it make a different line for each date and I want the sum so one line per supplier.

This is my case statment but requires me to put po_hdr.date_created into the group by which is not what I want.  

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_hdr.po_no) else 0 end AS po_count

Open in new window

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_line.po_line_uid) else 0 end AS po_count

Open in new window



SELECT DISTINCT p21_view_contacts.first_name + ' '
                + p21_view_contacts.last_name       AS buyer,
                p21_view_supplier.supplier_id,
                p21_view_supplier.supplier_name,
                Count(DISTINCT po_hdr.po_no)        AS po_count,
                Count(DISTINCT po_line.po_line_uid) AS line_count
FROM   p21_view_supplier
       INNER JOIN p21_view_contacts
               ON p21_view_supplier.buyer_id = p21_view_contacts.id
       INNER JOIN po_hdr
               ON p21_view_supplier.supplier_id = po_hdr.supplier_id
       INNER JOIN po_line
               ON po_hdr.po_no = po_line.po_no
WHERE  ( p21_view_supplier.delete_flag

Open in new window

0
i have directory websites with about 50+ rental owners. We basically store all of the their information in the MS SQL database included name and email.
Recently, we want to expand billing option on the website and allow potential customer to pay online e.g. john smiths(consumer) may purchase 1 day pass to access mary chan's house (rental owners).

My website works as middle man. so I hope to allow rental owner to submit their paypal email and some account infor of Paypal so I can send their information to Paypal API to complete the transaction.

My questions.

1. In my case, above plan makes senses? and Can paypal actually do that? If yes, can you drop me some good information.
2. Except Paypal, do you see any other vendors can do the same thing?

Thanks
0
Hi,

I am trying to retrieve loop a series of records as dates, the field eventDate is set to smalldatetime but I receive an error:

ex = {"Conversion from type 'Date' to type 'Long' is not valid."}

Open in new window



Public Function GetDisabledDates() As IEnumerable(Of DateTime)
        Dim disabledDates As New List(Of DateTime)()

'This works if added statically
        'disabledDates.Add(New DateTime(DateTime.Today.Year, DateTime.Today.Month, 7))

        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectConnectionString").ConnectionString)
        conn.Open()
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT * FROM [tbl_DisabledDates] WHERE [eventDate] >= @Date"
        cmd.CommandType = CommandType.Text

        cmd.Parameters.AddWithValue("@Date", Today)

        Dim reader As SqlDataReader = cmd.ExecuteReader()

        Try

            While reader.Read()
                disabledDates.Add(New DateTime(reader("EventDate")))
            End While

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        reader.Close()
        conn.Close()

        Return disabledDates

    End Function

Open in new window



Any help is appreciated.
0
Using SQL Server Management Studio v17.4

I have a SQL query that I need to export to my desktop using this command:

:OUT C:\Users\bedward1\Desktop\Encount3_test_RunDt_20190415.txt

I am in SQLCMD mode and I have the Query Options set to:
                Results\Text\
                       Output Format:  Custom delimiter
                       Custom Delimiter:  |

However, my result when I open them in Notepad are full of blank spaces.  Every row in the file takes up multiple rows due to large white spaces.

Can someone direct me to whatever settings I do not have correct?
0
Looking to count the number of SQL records until a certain value in one of the table columns is reached. Using Asp /VBscript. Not sure how to formulate the query.

What I'd like is to organise the results by the column "points" in descending order, grouped by the "city_id". I then need the number of records counted until a specific "member_id" is matched. Some "points" values could also be the same, which can cause an incorrect count. Similar points values should be ignored.

the db table is named "rankings" and is structured like this:

id  |  member_id  |  city_id  |  points
1   |      1      |   12500   |    2
3   |      2      |   12500   |    5
4   |     34     |     800     |    1
5   |     14     |   12500   |    14
6   |      6      |     600     |    12
7   |     11     |   12500   |    11
8   |     12     |   12500   |    5

For example, if I want to find the ranking for member_id "2", who happens to belong to city_id "12500", the correct final returned value from should be 3. This is because member_id "2" has the third highest points value in city_id "12500", even after taking into account the tie in points with member_id "12".

This below is all I can think of as I'm not a pro by any means and I know it's missing a lot!


member_id = 2
city_id = 12500
SELECT Count() as city_ranking FROM (SELECT * from rankings WHERE city_id='"&city_id&"' AND member_id <> '"&member_id&"' ORDER BY points DESC)
0

Query Syntax

53K

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.