i have a table with 2 columns and i wanted to filter the records where column1 in (Hierarchy,Single) and column2 in (‘Relate’, ‘NoRelate’). but not return the record when Column1 = Single and Column2 =…
Tag: sql-server
SQL: how do you calculate occupancy/ number of in-use for a facility?
I am trying to calculate the number of occupied lockers in the changing room. The dataset goes like this: My ideal output will be the number of occupied lockers per hour: I am able to manually calculate the number in excel (‘no. of check-in lockers during the hour’ + ‘no. of lockers in-use d…
Update script for a column with (max+1 for the column group value) on basis of another id column
As shown in the image. I have a table with Id, OrderId, SubId. For which the some row values in SubId column is zero as shown in the left table in the image. I have to update the SubId rows which are …
Delete rows from table using JOIN – SQL Server
I have a table_A – id | name | is_active | —-+———+————+ 1 | jon | 1 | 2 | ham | 0 | 3 | gary | null | I have a table_B – id | name | -…
how to get last 6 week start date and end date in MSSQL
For Example, today is 18 Feb 2021 then the start date will be 14 Feb 2021 and the End Date will be 20 Feb 2021. These ways for the last 3 weeks will be : week start date End Date Week 1 14 Feb 2021 20 Feb 2021 week 2 7 Feb 2021 13 Feb 2021 week 3 31 Jan
Normalize dates to periods starting at same origin
I’m attempting to track the evolution of customer spending over time. Essentially, I need to identify the first instance of customer purchase, assign it a rank of 1, and assign all of their purchases …
Fastest way to get the max date in SQL database (Azure)
I have a table that has Many rows up to 1000000. and 300 columns Question : I would like to know the fastest way to get the max date of the column ‘LastModifiedDate in table. In My table I have the columns : Id, LastModifiedDate and others columns… Thanks in advance for your help Answer If the ins…
Self joining columns from the same table with calculation on one column not displaying column name
I am fairly new to SQL and having issues figuring out how to solve the simple issue below. I have a dataset I am trying to self-join, I am using (b.calendar_year_number -1) as one of the columns to join. I applied a calculation of -1 with the goal of trying to match values from the previous year. However, it …
Append Group with data from another table based on its existence in first table
I have a table like this I have another table with these data Based on the second table I need to find which phases are missing for each Project codes So I need to get a result like this I tried cross join it doesn work since its creating 4 entries for each one item in the first table. So
SQL Server – grab part of string after a value sequence
I have a table called Note with a column named Notes. It has font info in the beginning which I don’t need. I’ve created a new column name final_notes and would like to grab everything after the “fs” plus two characters. The final result would be Answer We use PATINDEX to find the firs…