I have a column that’s called appt_date (varchar(50)) that’s in the format of YYYY-MM/DD and I want to convert it to YYYY-MM-DD I’ve tried several replace, convert, cast functions and solutions found on here but I still end up with the same result. I greatly appreciate the help in advance. A…
Tag: tsql
Query to sum the top “n” records
I would like to sum the forecast qty column for only the first “n” date records. For example for each item number the earliest(or first) three date records Current Query: Table Desired Result Answer Assign a row number to each record, partitioning by Item (this will start a new row counter for eac…
Unfold Data by Column
I have a dataset that looks like this (where the codes of ID1 and ID2 can never be the same. So putting them together they are still unique): ID ID1 Name AGE ID2 Primary Secondary 1 1234 Jim 34 1111 Mars A 2 1234 Tom 24 1111 Mars A 3 1234 Rick 55 1112 Mars B 4 2222 Ann 22
SQL statement to increment multiple case functions from multiple tables
I’m trying to increment the SQL generated column “Counter” by making 1 case statement. I get the error “Invalid Column” for Counter in my case statement. Any help is appreciated. Answer Shot in the dark. It’s not clear what order you need the rows to be counted or what the …
Function to check if a date is a Christmas day or New Years day
I am creating a function in SQL Server to validate if given date is a Christmas day or new year’s day. Please kindly advise if following code is OK: Above is the simplest way I can think of regardless or timezone and date format Answer I would create a table-valued function like this, rather than what I…
SQL How many of a counted row is in another counted row?
I’ve been stuck on how to write a particular query for the following question: How many employees are in how many businesses? My end result should look like this: EmployeeId Count BusinessId Count 1 23473423 2 56245764 3 834456 So there are 23473423 businesses that have 1 employee, 23473423 businesses t…
Pagination with grouping
I have a table with over 800K records. This table needs to be “grouped by” a certain column. An example would be: However, with pagination, things get complicated. I want to show 50 rows for every group. This means that if I have the following data: Id Color Name 1 Red Paper #1 2 Red Paper #2 3 Re…
Dynamic LIKE in WHERE clause in T-SQL with STUFF and FOR XML Path
What I’m trying to do is search a text column for anything LIKE anything in a list of values. The table with the text column is very large, so I can’t join on LIKE ‘%’ + valuename + ‘%’ (the list of values temp table is not terribly large). I thought I would try to do it th…
How to split comma delimited data from one column into multiple rows
I’m trying to write a query that will have a column show a specific value depending on another comma delimited column. The codes are meant to denote Regular time/overtime/doubletime/ etc. and they come from the previously mentioned comma delimited column. In the original view, there are columns for each…
Pivoting but handling Column Names
I’m currently getting a result-set back as follows: What I’m trying to do is get the results to appear as follows: I’ve put together the following query, and I was curious as to whether someone had a better way of doing it: Here’s the sample data: Answer You can use conditional aggrega…