Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Query Syntax

49K

Solutions

19K

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

Just taking a survey of other developers for best practices in maintaining your search filters and order-by while paging through multiple pages of search results.

Typical scenario.. user enters a few search filters and presses search.   Say, 500 results and showing 25 records per page.  The user changes the display order and clicks through to page 2, page 3, etc.  

What approach are people using to maintain the where-clause (search filters) and the order by so that each page, the same results are shown, but a different page.

Store SQL where clause in session variable?
Save filter criteria to the database?
Save search results to a temporary table and page through them?
Put search fields and order-by fields inside the search form and resubmit them each time?

Pros and cons for all approaches.
Just curious to change/improve my method.
0
How to Use the Help Bell
LVL 10
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

I have to create a csv file nightly that captures various data for our patients based on the visit type. Because the queries do not easily combine into one, I have 4 basic queries. I need to run the first one and use that unique patient identifier for subsequent queries but also save the output of each of those to a file or multiple files organized by patient.
Once it finishes the first patient it needs to loop through the remainder of patients that had that visit type for that day and go through all 4 queries as well.
If there were a logical way to combine these queries that would be great but I'm not really sure there is.  
I'm attaching a spreadsheet with the tables involved and the queries.
I'm not sure this can be done so any guidance in getting it to a point where it can, even if multiple steps or SSIS packages etc. is fine. Thanks
Queries_tables_rev2.xlsx
0
The code below creates a view TST_tsr3 based on the logic in the code; correctly producing the tsr_id and 'state_of_onion' fields within the view.    

I need to add a new condition under which tsr.tsr_id =  4 will fall.  

The logic to follow: because there is no record in t3 for tsr_id = 4, it should kick out as 'untrialed' in the 'state_of_onion'  field.

In reality, there are many thousands of records falling into each category.  The test case is presented to point out the lineup of tables that effect this single field within a many-fielded, multi-tabled view.

I have tried placing an additional 'case' after the 'else' following 'tossed'. Alternatively,  I have tried adding an additional 'case' immediately in front of the current 'count(1) function'.  In every case, I end up with a 'missing keyword' error.

 My thinking is that this code should produce the untrialed result for tsr 4 but I cannot get it to settle in within the current code.  It needs to be in the case statement under the 'tossed' code and above the code developing 'done'.

 CASE (SELECT COUNT (1)
                           FROM tsr s
                              JOIN t3
                             ON t3.tsr_id = s.tsr_id)
                       WHEN 0
                       THEN
                           'untrialed'

Open in new window


The following creates the view but does not handle the above condition:

CREATE OR REPLACE FORCE VIEW TST_tsr3
AS
    SELECT s.tsr_id,
         CASE 

Open in new window

0
I have two tables as:

Tbl1: Col1 Col2 Col3
Tbl2: Colx Col2 ColFlg

select T1.Col3, T2.Colx
from Tbl1 T1
left outer join Tbl2 T2
on T1.Col2 = T2.Col2
and T2.ColFlg = 'Y'

I have a problem with the last line's syntax. This filter is on all rows being returned by the whole SQL above. I would like it to be
only on Tbl2 and then if filter is satisfied, I would like the resultant rows returned from Tbl2 and then get included in the LEFT OUTER JOIN.

I know that in the old syntax (T2.ColFlg = 'Y'(+)) I can do it but I would like to do it using the LEFT OUTER JOIN type syntax.

Please let me know if I can answer any questions.

Thanks a lot.
0
I need an output file(s) that selects from a single Intake table/template in our EHR from a single most recent visit that equals @CurrentDate. However this single row return also needs to include all the meds, allergies and problems they have. Can this be done as a single output file? Once I have this I need to utilize it in an SSIS package to loop through all patients that meet the criteria.


I can include detail of the tables but for now just not sure how to even go about this. A UNION, or nested select statements? Not sure.  This can be done as separate files as well that breaks out meds, allergies etc. so that each one saves to a csv file with the unique patient identifier tying them together. Maybe a temp table could work as well.

Appreciate any help.
0
Hi,

We have a production Microsoft SQL Server 2014 - 12.0.2000.8 (X64)   Feb 20 2014 20:04:26   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)  and RTM version. Out of 4 GB ram sql server assigned 3 GB RAM. User complained about they are having some performance issues while they update their records and it takes several hours.

What i found that they kept their all data, log, tempdb and backup in a same drive. Each databases memory usage are very less. I asked them we need to separate drives to keep data and log file separately.

But SAN Admin replied that, Since we only have one slow SAN I'm not sure moving this data to different drive letters is going to matter if they are still on the same spindles on the same SAN. It may be time to buy a physical for this if better performance is needed.

Please advise me what else i can check and give them suggestions.

Thanks

Zahid
0
Dear All,
Please I want an sql script to select all records older than 24 hours
0
In an SQL SELECT statement, how would I get any value that contains a comma, to surround the entire value in quotes?
i.e.

Value is 123 Maple St., Apt 1
Would be "123 Maple St., Apt 1" in the result set
0
I have below SQL query to get onedrive version numbers details from users laptops based on collections. Is there any way to add file path in this query so that I can filter only below onedrive installation path and get correct result.
Onedrive.exe                    %localappdata%\Microsoft\onedrive\
GROOVE.EXE                     C:\Program Files (x86)\Microsoft Office\Office16

this is the SQL Query:

Declare @CollectionID Varchar(8)
Set @CollectionID = 'ORS0001A'

SELECT distinct
Vrs.Netbios_Name0,
Vrs.User_Name0,
Vrs.AD_Site_Name0,
Sf.FileVersion,
Sf.FileName,
Sf.FileVersion
FROM v_R_System Vrs
Inner Join v_GS_SoftwareFile Sf On Vrs.ResourceID = Sf.ResourceID
Inner join v_FullCollectionMembership Fcm on Vrs.ResourceID = Fcm.ResourceId
WHERE Sf.FileName like 'OneDrive.exe' or Sf.FileName like 'groove.exe'
and Fcm.CollectionID = @CollectionID
GROUP BY
Vrs.Netbios_Name0,
Vrs.User_Name0,
Vrs.AD_Site_Name0,
Sf.FileName,
Sf.FileVersion
ORDER BY Vrs.Netbios_Name0
0
As you see from the attached screenshot, I am trying to create queries base don the information in the columns. Unfortunately, the date value in the NEXT_DELIVERY_DATE field does not transfer into my query as a date. I know there a way to format it using TEXT within the formula. Any help will be greatly appreciated.screenshot
0
Enroll in October's Free Course of the Month
LVL 10
Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

I have a table called tblthirdparty with fields lineitemnumber, custom_01, Custom_02, Custom_03

I would like to update table tblservicedetail which has the same fields  (Lineitemnumber, custom_01, custom_02, Custom_03)  where lineitemnumber on both tables are equal.  

I would like to append the tblservicedetail table all those fields if lineitemnumber does not exist.  

lineitemnumber is a primary key in both tables.  The data types are the same in both tables.  

I am importing weekly a csv file that fills the tblthirdparty table I would then like to update my tblservicedetail with the data from the tblthirdparty.  When successful I would like to delete all records from tblthirdparty to get ready for the next weeks import.  

Please and thank you.
0
See attached files,

In addition this is what I have so far:

SELECT DISTINCT(a.USERNAME) AS 'User', SUM(DATEDIFF(minute, b.LOGINTIME, a.LOGOUTTIME)) AS 'Total Minutes'
FROM USERACTIVITYLOGS a
LEFT JOIN USERACTIVITYLOGS b
on a.USERNAME = b.USERNAME
WHERE a.LOGOUTTIME <= DATEDIFF(MINUTE, b.LOGINTIME, a.LOGOUTTIME)
OR b.LOGINTIME >= DATEDIFF(MINUTE, a.LOGINTIME, a.LOGOUTTIME)
GROUP BY a.USERNAME

This HAS TO BE ADDED:
The query should allow the input of 2 datetime parameters/variables from the user.
The SQL query should determine how much time each of the users is logged into the system between the 2 datetime stamps provided by the parameters/variables.
a1.PNG
a2.PNG
0
 select s.ID,
                     case is_stopped
               when 'Y'
               then
                   'stopped'
               else
                   case (select count (1)
                           from  t3
                                left join  t4
                                    on t3.id = t4.try_id
                          where     t3.spquerry_id = s.id
                                and try_place_id is null)
                       when 0
                       then
                           'done'
                       else
                           'notdone'
                   end
           end
               sr_place
      from tsr s;

Open in new window


The above returns the desired results except that an additional condition needs to be added.
I need to add to this code the possibility of there not being any
try_id for any particular s.id.  if there is no try_id for any s.id,
we need 'Not Tried', added to the sr_place field.  is_stopped is a field within the tsr table.

Any and all pointers and suggestions appreciated..
0
Greetings,

I have a remote SQL Server engine that I have to query and send the results to local csv files.  The tables are subject to change without notice and there are about 265 of them.  I have a sqlite db table of 256 tablenames with their queries I need.

because DTSX files require schema management in the input/output sections of the package, is there a way via script to engage the SQL Server Import/Export Wizard via script or other automated methods to generate dtsx packages based on the tablename and the source query set for it.

The reason why I'm suggesting DTSX is because I want to use the SQL Native driver to download the results.

Thanks
0
Hello,

We have a few clients who use some custom apps on SQL at their locations. Now, we want to have these DB's replicate to say a Cloud server - so we can have full live copy there for some analytics we'd like to run on them. How can i get this done?
0
We having 1 standalone DB. WE scheduled full backup for every Friday @ 10 Pm and log backup for every 15 mins.

But when i check DB properties it showing as 10/18/2017 PM. it supposed to be 10/13/2017. What is the logic behind this or when run this query it was showing last full backup was on 10/18/2017 PM.
SELECT sdb.name as DBName, 
    MAX(CASE WHEN bus.type = 'D' THEN bus.backup_finish_date ELSE NULL END) AS LastFullBackup,
    MAX(CASE WHEN bus.type = 'I' THEN bus.backup_finish_date ELSE NULL END) AS LastDifferential,
    MAX(CASE WHEN bus.type = 'L' THEN bus.backup_finish_date ELSE NULL END) AS LastLogBackup
FROM sys.sysdatabases sdb
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.name
ORDER BY LastLogBackup desc

Open in new window


backupfile.JPG
0
I have two tables (table1 and table2) in a single DB that backend some web pages. One of my pages shows some basic personal information so thate when I click on a persons name, it links to another page that shows that persons info. I carry the ID variable to the info page as to only get the info for that person (select * FROM table1 where ID = ID). This works fine but I want to grab more info for that person from table2 to show on that page. So I try this:

SELECT * FROM table2 LEFT JOIN table1 ON  table2.field3 = table1.field3 WHERE (table1.ID = ID)

Field3 in each table obviously have matching info. So I am trying to get data from table2 based on the ID that was carried over where field3 in table2 matches the value for field3 in table1.

Yes, I am a novice and no, I have no real DB or SQL experince. Thanks
0
So I have my table that is attached in the image, and as you can see I have my Primary key 'StudentID'
I am trying to draw and identify the dependencies for this table but am a tad stuck
Would there be A transitive dependency between Code (which is meant to be CourseCode) and CourseName (Code --> CourseName) ?
As well as a Partial one between StudentID --> Name, Address, Email, Degree
and maybe another Partial dependency involving StudentID with Name, CourseCode and Grade?

Just sort of wanting to know if I am on the right track and any help would be appreciated, I also must Normalize from 1NF to 2NF and finally 3NF and so far for 2NF I had StudentID -->Name, Degree, Code, Grade & Code --> StudentID --> Name, Address, Email

Sorry i may be completely offtrack and this may have been posted like gibberish so hopefully I was able to be clear enough.

Thank you
Untitled.png
0
I have the following mysql query which find duplicate urls in Magento 1. I would like to run an update query which will add a unique value onto the end of any duplicate urls ('value' in the query), probably just -1, -2, -3 etc for each duplicate it finds.

Could someone advise on how to do?

SELECT COUNT(DISTINCT entity_id) AS amount, `value`, entity_id FROM catalog_product_entity_varchar v WHERE EXISTS ( SELECT * FROM eav_attribute a WHERE attribute_code = "url_key" AND v.attribute_id = a.attribute_id AND EXISTS ( SELECT * FROM eav_entity_type e WHERE entity_type_code = "catalog_product" AND a.entity_type_id = e.entity_type_id ) ) GROUP BY v.VALUE HAVING `amount` > 1 ORDER BY `amount` DESC

Open in new window

0
Concerto's Cloud Advisory Services
LVL 4
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Expert Advice (Kent Olsen)
me to use for adding Frieght to FactInvoiceDetails table

CREATE TABLE DimItemType (
  id   integer IDENTITY,
  TypeName varchar(30)
);

And populate it with the possible entry types:

INSERT INTO DimItemType (TypeName) SELECT 'DATA'
INSERT INTO DimItemType (TypeName) SELECT 'FREIGHT'
INSERT INTO DimItemType (TypeName) SELECT 'SALES TAX'
etc...

Add the column ItemTypeID (integer) to the FactInvoiceLine table.
When an item is stored into FactInvoiceLine, set the ItemTypeID value according to the data type.

My questions is :

how to combine FactInvoiceDetails Table and DimItemType and i have created view to transfer to FactInvoiceheader Table through
SSIS. (OLEDB source)

01. Following View is correct? or any issue
02.Then how to add the ProductID for the FactInvoiceDetail Table, becuase when i'm adding extraline for
Frieght and Tax
CREATE VIEW DW_FactInvoiceDetails
AS

SELECT invhdr.invNumber,0 AS InvLine,0 AS Qty,invhdr.FREIGHT_AMOUNT AS price,2 AS ItemTypeID --FRIEGhT is 2
FROM Invoiceheader invhdr
WHERE FREIGHT_AMOUNT <>0 --May be - also possible

UNION ALL

SELECT invLine.invNumber,invLine.InvLine,invLine.Qty,invLine.price,1 AS ItemTypeID --DATA is 1
FROM InvoiceLine invLine


UNION ALL

SELECT invLine.invNumber,NULL AS InvLine,0,invLine.Sales_Tax AS price,3 AS ItemTypeID --SALES TAX is 3
FROM Invoiceheader invLine
WHERE invLine.Sales_Tax NOT IN(0)

GO

Open in new window

Any comments greatly appriciated.

Many thanks
0
I have the following SQL Server query.

I was hoping to get help with this on performance.  The most important thing I need it to be able to sort by the 1st column in the order by so that its ascending and does not put nulls first.

I am running this in SQL mode 100 so I cannot use the nulls first or nulls last approach...I also do not seem to be able to get a case to work..

Please help.


SELECT Distinct top 2000 tContact.nid as nID,
iSNULL(tContact.cFirstName, ' ') as cFirstName,
ISNULL(tContact.cMiddleNme, '') as cMiddleNme, 
ISNULL(tContact.cLastName,' ') as cLastName,tContact.cSuffix, tContact.cDepartment, 
tContact.cTitle, tContact.cEmail as cEmail,tContact.cEmailAlt, tContact.lActive, 
 tContact.cClassify, tContact.cDesignate, tContact.cContactCompany,
 tContact.cPhone, tContact.cPhoneExt,tContact.cCellPhone,tContact.cHomePhone, ISNULL(tContact.cEIN,' ') AS cEIN,
 tSubscr.nid as nidSubscr, tSubscr.cUserID, tSubscr.lActive as lActiveSubscr, tSubscr.lAdmin as lAdminSubscr, tSubscr.dLastLogin, 
 (select cPhone1 from CADoc_CRM..tClient where nID = tContact.nIDClient) as cClientPhone, 
 Case When dupes.nIdContact is null Then cast(0 as bit) Else cast(1 as bit) End as lContactIsDuplicated, 
 Case When coalesce(changeRequest.status,1) = 0 Then Cast(1 as bit) Else Cast(0 as bit) End as lChangeRequestPending 

 FROM CADoc_CRM..tContact tContact 
 left outer join CADoc_System..tSubscrXCRMCont tSXCC on tSXCC.cIdCRMCont = tContact.nid 
 left outer join 

Open in new window

0
Hi,

We have already existing maintenance plan (Full Db backup).
now i want automate like, any newly created DB should automatically added to existing maintenance plan?
how to do this?
0
Hi experts,
i am trying to make a join query in symfony 3 application. I have tested the query in sql and it worked as planned.
I have not been able to get it to work in symfony though. I have found examples on how to do this in symfony 2, but it did not work in my version.
Any help will be greatly appreciated.
0
Greetings Experts,

I'm trying to use an SMTP client my sql dev gave me to send e-mails from his front end app.  We need to be able to use the exchange 2013 server to relay so we can send from our exchange server work e-mails both internal and external.  When I put the gmail settings in, the program is successful.  So I proved the client works.

I created a Frontended transport receive connector,   Security: TLS checked off.  Anonymous user checked off.  
remote network settings - local IP of sql server
Network adapter binding = all available ip4, port 25

When I try to send mail from the sql server using the client I'm getting:
System security Authentication AuthenticationException: The remote Certificate is invalid according to the validation procedure.  

I checked and the DNS is working.  Ping the server with local name, localname.localdomain, external name.  All resolve.

My client has three ports you can send from, all with the same result as above: 587, 25, 465.  

If I change the security on the connector to Basic or integrated windows and un-check TLS, then port 587 and 465 reports the same error.  If I use 25 and TLS in unchecked I get:  Server does not support secure connections.

If I go to OWA using external name that is on certificate it is fine.
I have an internal cert for local server name, but if I use it then I get the cert error.  Can't put the .local domain on the ssl from GoDaddy.

Any ideas?  Nothing is in event log on sql or …
0
Hello all,

I need to build a table that has which cost center should be mapped different service codes.  

I have a list of almost 35K records that I need to map.  In a perfect world, I could just say 000001 to 000100 belongs to 5010 then 000101 to 000200 belong to 5020.  Well that does not work as I have some ranges within a range that belongs to a different cost center or in one section every other code changes to a different cost center really only 2 (000300 = 5040, 000301 = 7485, 000302 = 5040, 000303 = 7485 and so on.) I still would like to have some SQL code that would read in the first record say OK here is the start of my range and it goes to cost center xxxx then look at the next record it is the same cost center so it is the top of the range, go to the next one it is the same cost center so it is now the top of the range.  Finally, the cost center changes so it is the start of the new range for the new cost center and I do the same logic again.  Does this sound right?  I just need a little help getting started.

*** UPDATE ***
10/19/2017
I built the table manually but I would still like to create a script in SQL or VBA that will take my listing of codes and map a range out for each section.  The listing is around 25,000 lines and my range table is about 4,000 lines.  It took me a few hours to create it.  Creating the range in SQL is my preference, but I can do VBA as well.  Thanks again.


10/18/2017
I have started creating a VBA Function in Access to …
0

Query Syntax

49K

Solutions

19K

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.