2018-10-22         Mavis

How to join a query result with an existing table to perform an UPDATE?

My query looks like this, and I am thinking it is because of the use of ConcatRelated(), which concatenates related fields:SELECT C.ID, ConcatRelated(["GameNames", "BaselineQuery", "CC.ID = " & [ID]) AS Games FROM BaselineQuery AS C GROUP BY C.IDThe Result Set will look like:ID GameNames 1 "A, "B", "C", D" 2 "A", "B" 3 "D", "E", "A", "B"My Games Table I want to UPDATE Looks likeID GameNames 1 2 3Where GameNames is currently blank and I want to use the ID of the query and the ID of the table to populate GameNames, so 1 in the Games table should be "A", ...

 sql                     1 answers                     22 view
 2018-10-22         Devin

SQL duplicate counting headache

I have a problem that I’m not sure how to overcome and would really appreciate some direction.Example dataDevice Project statusa111 101 Readya222 102 Finisheda333 103 Readya444 104 Readya555 105 Readya111 106 Starteda111 107 Readya555 108 StartedI’m looking for a way to count the number of devices at ‘Ready’ status. However, when it comes to ‘Ready’ I only want to count it once AND don’t count it if that device has a status of anything other than ‘Ready’ in another projectLogicIf ready in one or multiple projects ...

 sql                     4 answers                     23 view
 2018-10-22         Roderick

Invalid operation: syntax error at or near "Delta"

I have redshift scriptHere is code of part of script SELECT cog.organizationid, cog.clientid, t.MonthBilled, t.Month, t.Year, u.ClientCharges, cp.supervisorcontactid AS Principal, cc.fullname AS CSL, LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) AS PreviosMonthCharges, CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Yea...

 sql                     1 answers                     24 view
 2018-10-22         Lilith

How to get count for each day between certain times

I'm trying to get a total count for each day between 07:00 and 19:00 for the last 7 days. The below query only displays the count for the date 7 days back and not each individual day. Any help would be greatly appreciated. Thanks!DECLARE @Date AS DATETIME = DATEADD(HOUR, 7, CAST(CAST(DATEADD(DAY, -7, GETDATE()) AS DATE) AS DATETIME))DECLARE @Date2 AS DATETIME = DATEADD(HOUR, 19, CAST(CAST(DATEADD(DAY, -7, GETDATE()) AS DATE) AS DATETIME))SELECT CONVERT(NVARCHAR(20), DATE, 120) AS Report_Date, COUNT(DISTINCT GUID) AS ROW_COUNTFROM TABLE WITH (NOLOCK)WHERE DATEADD(MINUTE, +...

 sql                     3 answers                     27 view
 2018-10-22         Susie

Employee table - hierarchy with some caveats

I've got an issue with a sort of standard employee table. In my example I have done self joins to the source however I don't get the end structure that I would like.The hierarchy is a few levels up.In a simple form the code is like:select e.employee_name , e.teamlead_name , e1.teamlead_name 'manager_name' , e2.teamlead_name 'senior_manager_name' , e3.teamlead_name 'director_name'from employees eleft join employees e1 on e.teamlead_id = e1.employee_idleft join employees e2 on e1.teamlead_id = e2.employee_idleft join employees e3 on e2.teamlead_id...

 sql                     1 answers                     28 view
 2018-10-22         Novia

Creating a column loop within a table loop to calculate fill rate for all fields in a database

I have 2 databases with over 400 tables for which I have to calculate fill rates. I have a loop for calculating fill rates for all columns in a table, but how can I do it for all tables in a database?? Please help!I am using SQL Server.Here is the code for looping through all columns in a table for calculating fill rate-DECLARE @Table NVARCHAR(max) = 'dbo.[TableName]' ,@MetaTable NVARCHAR(128) = '#TempTable' ,@ColumnName NVARCHAR(128) ,@Iterator INT = 1 ,@SQL1 NVARCHAR(MAX)SELECT c.NAME ,c.COLUMN_ID ,ROW_NUMBER() OVER (ORDER BY COLUMN_ID) AS RNINTO #ColsFROM SYS.C...

 sql                     1 answers                     29 view
 2018-10-22         Lauren

Check if field exists in CosmosDB JSON with SQL - nodeJS

I am using Azure CosmosDB to store documents (JSON).I am trying to query all documents that contain the field "abc", and not return the documents that do not have the field "abc". For example, return the first object below and not the second{ "abc": "123"}{ "jkl": "098"}I am trying to use the following code:client.queryDocuments(collectionUrl,`SELECT r.id, r.authToken.instagram,r.userName FROM root r WHERE r.abc`)I assumed the above would check if abc exists similar to if (r.abc) {}I have tried using WHERE r.abc IS NOT NULL Thanks in advance All you need to do is c...

 sql                     1 answers                     41 view
 2018-10-22         Leo

Extract Month, Year (Netezza SQL)

My data type is a date formatted as "YYYY-MON-DD" and I would like to extract the month and year to be formatted as "MON YYYY" while keeping the data type as date so that I will be able to use it with the ADD_MONTHS function. Is there a way to do so? I extract the date from the data field called date_process.This is what I thought of but it doesnt seem to be working.SELECT TO_DATE(TO_CHAR(PROCESS_DATE,'YYYY-MON'), 'MON YYYY') AS PERIOD,Thank you. Dates are stored in an internal format, not as a string.If you want to see the value in a particular format, then you need to ...

 sql                     1 answers                     85 view
 2018-10-22         Rae

Search for references of a view in MSSMS

We have a huge database, let's call it TestDB for convenience and I'm supposed to look if anything within that database references a view, VTest.I have absolutely no experience with SQL and all I could find referred to looking for things in table columns, which isn't exactly helping a whole lot.How would one go about solving this, aside from manually looking through every single file. I have used this script for searching through SPs for years, there is probably a better way, but it works:SELECT DB_NAME() [DB_Name], SCHEMA_NAME(p.[schema_id]) [Schema], p...

 sql                     1 answers                     19 view
 2018-10-22         Bonnie

select count of cities having population greater than 410000

i am new to sql . i have in a table data like district city state populationd1 c1 s1 2000d2 c1 s1 10000d3 c1 s1 400000d1 c2 s2 500000I want to have count of cities having population greater than 410000so my desired output should be 2 as there are two cities c1 and c2. I want to first do group by on city and then calculate sum of population for each city and then check if its population is greater than 410000so I have used the query select count(city) from city_table group by city having ...

 sql                     1 answers                     14 view
 2018-10-22         Rose

T-SQL Combine rows in continuation

I have a table that looks like the following.What I want is the the rows in continuation of each other to be grouped together - for each "ID".The column IsContinued marks if the next row should be combined with the current rowMy data looks like this:+-----+--------+-------------+-----------+----------+| ID | Period | IsContinued | StartDate | EndDate |+-----+--------+-------------+-----------+----------+| 123 | 1 | 1 | 20180101 | 20180404 |+-----+--------+-------------+-----------+----------+| 123 | 2 | 1 | 20180501 | 20180910 |+-----+-----...

 sql                     1 answers                     68 view
 2018-10-22         Jeffrey

Return the rows(Ids) which satisfies and has both the values in IN condition

I have a below table with sample data called Recipes which acts as a junction table between other 2 tables.Query - select recipeId, IngredientId from Recipes where IngredientId in (1,31) order by recipeIdWhen i execute above SQL statement it gives below Output. which is fine.What changes i have to make in the query to get below highlighted Output.Now why 6,7 & 21?As RecipeIds 6, 7 & 21 are the only Ids which has both IngrdientIds [i.e. 1,31] You can use group by clause :select RecipeIds from table twhere IngrdientIds in (1, 31)group by RecipeIds having count(dis...

 sql                     1 answers                     44 view
 2018-10-22         Enoch

SQL Relationship to Secondary Table Without Adding Duplicate Column

There are Cities, Job Types, and Tasks. Cities can have multiple Job Types. Tasks are created for a City that can have multiple Job Types. Cities can have Many Tasks. But when adding the Job Types for the Tasks assigned to a City, must make sure the City has that Job Type. How to create Relationship/Constraint when Adding/Updating Task_JobTypes to make sure the City associated with the Task has that Job Type allowed in City_JobTypes? The constraint in Task_JobTypes "FK_Task_JobTypes_JobTypes" need to be referencing that instead of just JobTypes.Cities - Id,Name JobType...

 sql                     2 answers                     45 view
 2018-10-22         Noah

SQL - using calculated dates in WHERE clause

I have a table with future values where I only want the last full week. Meaning af want to find the last Sunday and have a WHERE clause where date is between 'last Sunday - 7' and 'last Sunday'. Meaning I have transactions until '31-10-2018' and want the period between '2018-10-28' and '2018-10-22' (both days included). But every week new data will be added continuously so very soon I have data until '2018-11-30' and so on.I have tried a query like:SELECT *FROM PlannedCountsWHERE [Date] BETWEEN DATEADD(d,-6,MAX([Date])) and MAX([Date])Note: I'm aware that this will only giv...

 sql                     2 answers                     46 view
 2018-10-22         Marcus

Sqlite getting the latest record for this table

I can't figure out what I am doing wrong here. I am querying sqlite table the to get the latest record for each College but the result is not completely accurate. Below is the source table:College Status Date"UTAR "CERTIFIED" "07/30/2018""UTA" "CERTIFIED" "03/19/2018""UTD" "CERTIFIED" "06/08/2018""UTEL" "CERTIFIED" "03/13/2018""UTSA" "CERTIFIED" "02/06/2018""UTT" "CERTIFIED" "03/14/2018""UTPB" "CERTIFIED" "07/23/2018""UTRG" "CERTIFIED" "04/12/2018""UTAR" "CERTIFIED" "05/31/2017""UTA" "CERTIFIED" "03/02/2017""UTD" "CERTIFIED" "04/13/2017""UTEL" "...

 sql                     2 answers                     49 view
 2018-10-22         Kyle

How to rewrite implicit joins to explicit joins?

I'm having some trouble re-writing these JOINS using explicit joins. I'm trying to understand how this is accomplished. Here is the original query using implicit joins that I've inherited:FROM prod.f31122 t, prod.f4801 wo, prod.f30008 r, prod.f0101 abWHERE t.wtdoco = wo.wadoco(+) and t.wtmmcu = wo.wammcu(+) and t.wtmmcu = r.iwmcu and t.wtan8 = ab.aban8I've tried the following, but I'm getting different result sets. The same count, but the result set is incorrect (likely an issue with my groupings which aren't displayed). Here is my translati...

 sql                     3 answers                     49 view

Page 1 of 1890  |  Show More Pages:  Top Prev Next Last