I have a view using a CTE and I want use a row number to simulate a key for my edmx in Visual Studio When I look in properties of column I see Id bigint … NULL And my edmx exclude this view cause don’t find a column can be used to key When I execute my view ID have
Tag: sql-server
How to return data for specific day names from date column
Imagine I have a SQL Server table as shown below, named dbo.tblNAV: How can I select the data only for Mondays and Fridays? I am a beginner in SQL Server and almost have no idea to handle this. I would appreciate your help. I am using Microsoft SQL Server Management Studio. Answer SQL Server does not make this easy to
How to calculate rows count in where statement in sql?
I have two tables in SQL Server: order (columns: order_id, payment_id) payment (columns: payment_id, is_pay) I want to get all orders with two more properties: How many rows where is_pay is 1: And the count of the rows (without the first filter) So I wrote this query: The problem is how to calculate the rows without the is_pay = 1?
TSQL to perform aggregation with Exists
My sample data source is following I want SQL to check if there exists any row with cond=X or cond=Y by site and if yes, don’t perform any aggregation; if no, perform a sum of val. I am currently achieving it by following Method1 Method 2 I was wondering if there is any better way of achieving this. Edit Desired
Need to add double quotes to SQL SERVER string values
I have integer values that are being passed from a parameter that needed to be input as a string and padded so that they are 7 digits. This will then be passed into another query. This codes sets @control_num as (‘0005675, 0045464, 0002323’). I need this to be passed as (“0005675”, “0045464”, “0002323”). I’ve looked at other examples on-line but
Adding x work days onto a date in SQL Server?
I’m a bit confused if there is a simple way to do this. I have a field called receipt_date in my data table and I wish to add 10 working days to this (with bank holidays). I’m not sure if there is any sort of query I could use to join onto this table from my original to calculate 10
Extract data from a historical table keeps only data with changes
I have a table like this and here is the log table So as you understood, my main table contains data from a source every day. But keeps only those with changes. Thus if everyday if 100k records comming normally, but very few (<100) are really has some changes. Thus my table tracks all the records using the log ID
Need to handle custom query summation for group
I have this table name “TB1” contain of these columns : I need a group with Bill.NO with sum column qty ,price from “TB1” to be like this : What I tried to do , I wrote this query but not doing as I want : Answer Really, you should leave these kind of things to the presentation layer. But
In SQL, how do I coalesce the first value of 3 columns that is 10 characters long
I am trying to only select one column from 3 distinct phone number columns, and I would prefer to have the column with the longest phone number (some don’t have area codes). Currently, I have coalesced the 3 columns as some are also null, with a preference on cell phone. Ex. What I’ve tried : I would prefer to just
How to find the average time? SQL Server 2017
I have a request like this: I need to find the average time for each status. For example, add up the time of all statuses 2 and divide by their number. As a result, get something like 2 days 5 hours. How to fix my request to make belt operations possible? Answer Taking your limited example data, something like this