I am calculating value based on value in previous row. I used LAG for getting value from previous row and it works well for first two but not for next rows. Let me elaborate my scenario if row is first then I am taking Open_HA same as O column. But for next rows I am taking previous row Open_HA and
Tag: sql-server
Get table name without schema
There is a string that could be a table name with ANY schema, not necessarily dbo. How do I get the table name only from the string without knowing the schema? E.g. from ‘dbo.MyTable’ I would need to extract ‘MyTable’. Answer PARSENAME can be used for this:
update with csv file using python
I have to update the database with the CSV files. Consider the database table looks like this: The CSV file data looks like this: As you can see the CSV file data some data modified and some new records are added and what I supposed to do is to update only the data which is modified or some new records
Recursive query to get totals by distinct item from a row of a data
I have SQL data that looks like the following: How can I recurse through this data to get total wins, draws, losses by team for both home and away? My output would look something like: I was able to get this done in a very linear way by simply get a list of distinct teams and for each team looping
Select data where sum for last 7 from max-date is greater than x
I have a data set as such: Like this: Here’s the query i tried, This is clearly not right. What is a simple way to do this? Answer Find the max date by type. Then used it to find last 7 days and sum() the value.
SQL Can I use CASE statement with addition condition in a JOIN condition
I want to use join with case statement and addition condition, for example : But this code results in a syntax error. Can anyone help to explain a little? Answer Using Case When then in Join Condition and in Where Clause is not possible in this case What you are trying to achieve is doable with And and OR lik…
JSON Blob into SQL Server
I have data from a web API that I want to store in a SQL database. Each record in the web API looks like this: Answer That query is invalid. Should be of the form INSERT INTO TableName(Column1, Column2) VALUES (?,?), so something like:
Why can’t I add a column to an existing table with a checkConstraint that references other columns in SQL
I’m using SQL Server and am trying to add a column and a check constraint. I’ve found that the following works: However a less verbose way of writing this does not work: The following error is output: Column CHECK constraint for column ‘isTrue’ references another column, table ‘t…
How to combine columns, group them then get a total count?
Below is a table that has candidate_id, two interviews they attended with the interviewer’s name, and results for each interview. candidate_id interview_1 interview_2 result_1 result_2 1 Interviewer_A Interviewer_B Pass Pass 2 Interviewer_C Interviewer_D Pass Reject I need help to combine column intervi…
MS SQL Server view with conditional values
I have a table A that can reference to itself to inherit some values from a parent. The reference is represented with ParentID. Parents have null values in ParentID. ID ParentID Field A Field B Field C 1 NULL ValueA1 ValueB1 ValueC1 2 NULL ValueA2 ValueB2 ValueC2 3 1 null ValueB3 null 4 1 null ValueB4 null 5 …