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

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
Tech or Treat!
LVL 10
Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

In my query I have this:

query
Which results in this:

2017-10-20_15-41-16.bmp
Obviously not correct. How to fix?
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
In this code how would I split it out so that it returns the first and last name?

SELECT('JIM, SMTIH K')

NOTE: not all of the last names will have the middle initial.
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
Hi. In the following query result I get dates in the [From] column that are before the date criteria (1 Oct 2017). What have I done wrong?

SELECT Max([vPerformanceHolesDelaysSurnames].[Date]) As [LatestDate], [vPerformanceHolesDelaysSurnames].[Shaft], [vPerformanceHolesDelaysSurnames].[Machine number], [vPerformanceHolesDelaysSurnames].[Mach Operator], Min([vPerformanceHolesDelaysSurnames].[Drilled From]) As [From], Max([vPerformanceHolesDelaysSurnames].[Drilled To]) As [To]
FROM [vPerformanceHolesDelaysSurnames]
WHERE ( [vPerformanceHolesDelaysSurnames].[Date] >= '01 Oct 2017' And [vPerformanceHolesDelaysSurnames].[Date] <= '17 Oct 2017' ) AND ( [vPerformanceHolesDelaysSurnames].[Shaft] = 'masimong air' )
 Group By [vPerformanceHolesDelaysSurnames].[Shaft], [vPerformanceHolesDelaysSurnames].[Machine number], [vPerformanceHolesDelaysSurnames].[Mach Operator]

Open in new window

0
New feature and membership benefit!
LVL 10
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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
Hi

In the SQL query below I get the following error and can't understand why: "Column vPerformanceHolesDelaysSurnames.Date" is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause

SELECT Max([vPerformanceHolesDelaysSurnames].[Date]) As [LatestDate], [vPerformanceHolesDelaysSurnames].[Shaft], [vPerformanceHolesDelaysSurnames].[Machine number]
FROM [vPerformanceHolesDelaysSurnames]
WHERE ( [vPerformanceHolesDelaysSurnames].[Foreman] = 'masimong air' )
 Group By [vPerformanceHolesDelaysSurnames].[Shaft], [vPerformanceHolesDelaysSurnames].[Machine number]
HAVING ( [vPerformanceHolesDelaysSurnames].[Date] >= '01 Oct 2017' And [vPerformanceHolesDelaysSurnames].[Date] <= '17 Oct 2017' )

Open in new window

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
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
I am relatively new to SQL Server, using Developer 14.

I have payment records stored in a table and I want to pull values for those that are fully paid and those that are partially paid.

The field 'FullOrPartialPayID ' =1 if the pay rec fully paid the balance.

I have two fields in my result set 'FullPayAmt' and 'PartialPayAmt'.  

If  'FullOrPartialPayID ' =1 I want the full amount of  field 'PayAmt' to be in 'FullPayAmt', otherwise it should be zero and 'PartialPayAmt' will get the full amount of 'PayAmt'.

Conversely  If  'FullOrPartialPayID ' <>1 I want to full amount of 'PayAmt' to be in 'PartialPayAmt' and 'FullPayAmt' to be zero.


If I was coding this in MS Access query builder I would have fields 'FullPayAmt' defined like this:

SELECT tblPaymentsYears.PayHdrID, tblPaymentsYears.PayTaxAuthID, tblPaymentsYears.[FullOrPartialPayID], tblPaymentsYears.[PayAmt],
IIf([FullorPartialPayID]=1,[PayAmt],0) AS FullPayAmt,
IIf([FullorPartialPayID]<>1,[PayAmt],0) AS PartialPayAmt
FROM tblPaymentsYears;

How can I code this in a SQL View?
0
Book1.txt is a Text (Tab delimited) (*txt) file.
Not sure why I am getting errors. I assume terminators are correct.
Just trying to write records to a SQL table.
Thought I would ask.




  BULK INSERT [DISPEXC].[WKLDStaging].[ocs]  
   FROM '\\dispexcDEVfiles\SQLDEVfiles\DISPEXC\Data\In\OCS\Book1.txt'  
   WITH  
      (  
         FIELDTERMINATOR ='\t',  
         ROWTERMINATOR ='\n'  
      );  


Msg 4866, Level 16, State 1, Line 25
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 25
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 25
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
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
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.

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
When i check DB options :
Auto Create Statistics: True
Auto Update Statistics: True

It means that if i created any indexes in this DB will automatically update statistics? am i correct?

When i used below query:
The results are:
auto_created  user_created
           0                    0
It should be 1 becasue detabase level is True.

USE TestDB
SELECT o.name, i.name AS [Index Name],  
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC; 

Open in new window


I got confusion when we make true at database level, it should automatically set true to index level?  am i correct
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

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.