To start I am using SSMS 2018. I am trying to create a Common Table Expression to use in some code later. I need to get down to a single row per applicable quarter per person, I’ve tried using Havings/…
Why am I getting “Error converting data type varchar to int.”
I am creating a mock database for a fictional gym. I have 3 relevant tables: Program, Workouts and Exercises (see below). Each Program contains one or many Workouts and each Workout contains one or many Exercises. I am trying to create a stored procedure to add rows to these three tables. I keep getting the e…
How to Select Records from large table batch wise in SQL
I have a table that contains billions of records. I want to select records in a batch using the c# while loop. I used Offset and Fetch Next, but it is taking too long to get results. If the table contains below 100k records, it works fine. What is the best way to batch select records? Sample Code Answer If
Count the length of Chinese character in sql
I have a string that includes Chinese characters in it. I want to calculate the length of the string but I want to count 1 Chinese character equals to 3 characters. Here are the strings and how I am …
behavior of sql query with OFFSET and fetch clause in postgres/oracle with deletion of records
I have a query like : select * from mytable order by mycol_1 OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY; Now, if in this iteration ,I delete several records out of the 10 records fetched by above query …
Self Join to calcuate one day before a given date
I have this table Employee_Hire which has Hire date of an employee (ID) in a Department. Now I want to write a Select query which adds one more column (Last_day) to show last day he worked in that …
Multi-table select, limiting number of results
So let’s say you had a query to retrieve stats on all baseball players in a db that has constantly updating stats such as walks, home runs, etc, such as: Now say you also wanted to pull stats from another table, say a salaries table, but you wanted to only pull the most recent salary, so something like:…
Arranging Columns in Microsoft Access
I am using Microsoft Access to generate a report, but I’m encountering difficulty in figuring out how to have the information displayed in way that will comply to my preference. Specifically: the …
Count grouped dates in SQL
I currently have data looking like: where grp is grouped consecutive dates. I want to count each of this grp, so with above data, I would like to get result of 5 (2 consecutive date groups for id 1, 3 consecutive date groups for id 2). Anyone have idea how to tackle this? Answer Do you just want count(distinc…
Occurrence of a string inside another string
I was wondering if there is an easy way to look for a occurrence of a string inside another string. I’m using this query: $checkfileName[0] will be set in a if-statement, where it’s set to GiM1_0. My problem is that the column doesn’t contain that specific string, only a part of it. Image of…