This code gives the following table cntqueue cntdate cntINKTONERBLACK signalcolumn Queue01 2001-04-04 3 0 Queue01 2001-04-05 1 0 Queue01 2001-04-06 100 1 Is there a way to count ‘1’ in signal column and group 1st column to have? cntqueue NumberOfJumps Queue01 1 Answer You can use a subquery:
Tag: sql-server
Select all rows between dates
I have a #tmp table that looks like the following. It has a total of three lines. title receipt_date decision_date “It wasn’t as bad as I thought it would be” 2017-06-12 15:07:10.893 2017-06-23 09:37:31.667 “It wasn’t as bad as I thought it would be” 2017-07-11 10:35:24.337…
SQL query join tables data
I have 2 tables Users and TimeSheets, Timesheets table has column UserId which is users id form users table obviously 🙂 Logic And I’m trying to get all data of Timesheets table plus just name of user from users table but somehow it seems like my query is other way around! Now I know this might be a simp…
Find min and max data column in Table
I have a table that specifies exactly what date and time each employee was in a particular office. EmployeeTable looks like this: id EmployeeID DateP TimeP 1 11111 1397/01/02 01:30 2 11111 1398/05/09 05:30 3 11111 1398/06/07 05:10 4 22222 1398/08/09 06:12 5 22222 1399/02/01 07:15 6 11111 1399/07/02 08:51 7 11…
SQL Substring Case Condition
I’m trying to solve the following question on SQLPAD. Write a query to return the number of actors whose first name starts with ‘A’, ‘B’, ‘C’, or others. The order of your results doesn’t matter. You need to return 2 columns: The first column is the group of act…
How can I find a record that equals a value inside a JSON array
I have the following Table mytable I’m trying to query where id = 2 and partNum = 2423 Here is what I wrote so far: What would be the most efficient way to query? Answer Here is what worked for me.
String date to SQL datetime conversion ‘Mon Dec 09 2019 12:00:54’
I have a field in database which has date time saved in string format like this. Mon Dec 09 2019 12:00:54 I want to convert this to SQL Datetime. I tried by splitting but it is not working for me. Answer SQL Server is pretty flexible about recognizing formats for date/time values, but not with the day of the …
Select rows after the current row which are null and combine them to rows
I have written the following SQL code to display the data as rows, where the row after data is having null values except on the description column. This results in the output as follows: I have written the following query to group and display rows: The data is displayed as follows Expected output is as follow…
Find the latest version of a dataset available in SQL Server on specified dates, using RStudio
Each month I run a script in RStudio, on the 9th of the month. I need to manually append the latest DATAyymm sql data set onto the my_data vector, before running the script. Is there a way I can have the program automatically create this vector? Maybe it could connect to SQL Server and find what the latest av…
How can I get specific rows in a table by the value of a specific queried column of a different table?
I have the Table1 and the Table2 tables. Table1 has the B (Primary Key) and IC columns. Table2 has the CC, SC and IC columns: Table Table1 … B IC … … B1 IC1 … Table Table2 … CC SC IC … … CC1 SC1 IC1 … … CC1 SC2 IC1 … … CC2 SC1 IC1 … ̷…