Here's another way from the question [get table primary key using sql query][Get Primary Key]:



<pre><code>SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
  AND TABLE_NAME = '<i>&lt;your table name&gt;</i>'
</code></pre>

It uses [`KEY_COLUMN_USAGE`][KEY_COLUMN_USAGE] to determine the constraints for a given table  
Then uses [<code>OBJECTPROPERTY(<i>id</i>, 'IsPrimaryKey')</code>][OBJECTPROPERTY] to determine if each is a primary key


[KEY_COLUMN_USAGE]: http://msdn.microsoft.com/en-us/library/ms189789.aspx

[OBJECTPROPERTY]: http://msdn.microsoft.com/en-us/library/ms176105.aspx
[Get Primary Key]: https://stackoverflow.com/q/3930338/1366033

Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table, but can't remember how to get the PK.

How do you list the primary key of a SQL Server table?

Adapted from the post on [How to Find Size of All the Indexes on the Database][1], you can use:

```sql
SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
	   OBJECT_NAME(i.OBJECT_ID) AS TableName,
	   i.[name] AS IndexName,
	   i.index_id AS IndexID,
	   8 * SUM(a.used_pages) AS [Indexsize(KB)]
FROM sys.indexes i
JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID, i.index_id, i.[name]
ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id
```

Which uses the following tables:


* [`sys.indexes`][2]
* [`sys.partitions`][3]
* [`sys.allocation_units`][4]


[1]: https://blog.sqlauthority.com/2016/11/13/find-size-indexes-database-interview-question-week-097/
[2]: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
[3]: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql
[4]: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql


I have an index -- let's call it `IX_MY_INDEX` -- in a SQL Server table (both compatibility modes 80 and 90) that I would like to determine the size of. How do I do this?

**Update:** Allain Lalonde's second solution below only works when the compatibility mode is set to 90; however, the particular database I am working on is in compatibility mode 80. Does anyone have a solution for compatibility mode 80?

How do you determine the size of an index in SQL Server?

If there are a lot of data points, you can also [group ranges together][1] like this:



    SELECT FLOOR(grade/5.00)*5 As Grade, 
           COUNT(*) AS [Grade Count]
    FROM TableName
    GROUP BY FLOOR(Grade/5.00)*5
    ORDER BY 1

Additionally, if you wanted to label the full range, you can get the floor and ceiling ahead of time with a CTE. 



    With GradeRanges As (
      SELECT FLOOR(Score/5.00)*5     As GradeFloor, 
             FLOOR(Score/5.00)*5 + 4 As GradeCeiling
      FROM TableName
    )
    SELECT GradeFloor,
           CONCAT(GradeFloor, ' to ', GradeCeiling) AS GradeRange,
           COUNT(*) AS [Grade Count]
    FROM GradeRanges
    GROUP BY GradeFloor, CONCAT(GradeFloor, ' to ', GradeCeiling)
    ORDER BY GradeFloor

**Note**: In some SQL engines, you can `GROUP BY` an Ordinal Column Index, but with MS SQL, if you want it in the `SELECT` statement, you're going to need to group by it also, hence copying the Range into the Group Expression as well.

**Option 2**: You could use [case statements to selectively count values into arbitrary bins and then unpivot them][2] to get a row by row count of included values

[1]: https://web.archive.org/web/20180205094300/https://www.wagonhq.com/sql-tutorial/creating-a-histogram-sql "ARCHIVE of https:&#47;&#47;www.wagonhq.com/sql-tutorial/creating-a-histogram-sql"
[2]: https://stackoverflow.com/a/52105218/1366033

Let's say I have a database column 'grade' like this:

    |grade|
    |    1|
    |    2|
    |    1|
    |    3|
    |    4|
    |    5|

Is there a non-trivial way in SQL to generate a histogram like this?

    |2,1,1,1,1,0|

where 2 means the grade 1 occurs twice, the 1s mean grades {2..5} occur once and 0 means grade 6 does not occur at all.

I don't mind if the histogram is one row per count. 

If that matters, the database is SQL Server accessed by a perl CGI through unixODBC/FreeTDS.

**EDIT:** Thanks for your quick replies! It is okay if non-existing values (like grade 6 in the example above) do not occur as long as I can make out which histogram value belongs to which grade.

Generating a histogram from column values in a database

If you want to *only update on rows that are not currently uppercase* (instead of all rows), you'd need to identify the difference using [`COLLATE`][2] like this:

    UPDATE MyTable
    SET    MyColumn = UPPER(MyColumn)
    WHERE  MyColumn != UPPER(MyColumn) COLLATE Latin1_General_CS_AS 

----

### A Bit About Collation

Cases sensitivity is based on your collation settings, and is typically case insensitive by default.

Collation can be set at the [Server, Database, Column, or Query][1] Level:

    -- Server
    SELECT SERVERPROPERTY('COLLATION')
    -- Database
    SELECT name, collation_name FROM sys.databases
    -- Column 
    SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL

[Collation Names][3] specify how a string should be encoded and read, for example:

* <code>Latin1_General_<b><i>CI</i></b>_AS</code> → Case Insensitive
* <code>Latin1_General_<b><i>CS</i></b>_AS</code> → Case Sensitive


[1]: https://www.webucator.com/how-to/how-check-case-sensitivity-sql-server.cfm
[2]: https://learn.microsoft.com/en-us/sql/t-sql/statements/collations
[3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/sql-server-collation-name-transact-sql

I need a SQL query to make all data in a column UPPER CASE?

Any ideas?

SQL query to make all data in a column UPPER CASE?

Dean's method works perfectly well, but I think it looks a little odd. Here's an extension method that you can use to overload `AddWithValue` to take in a `SqlDbType` so that you can turn the function call into a one liner:



    <Extension()>
    Public Function AddWithValue(ByVal sqlParameterCollection As SqlParameterCollection,
                                 ByVal parameterName As String,
                                 ByVal value As Object,
                                 ByVal sqlDbType As SqlDbType) As SqlParameter
        sqlParameterCollection.Add(parameterName, sqlDbType)
        sqlParameterCollection(parameterName).Value = value
        Return sqlParameterCollection(parameterName)
    End Function

Then call it like this:



    cmd.Parameters.AddWithValue("@entity_uid", clientId, SqlDbType.Int)

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx

I'm used to adding sql parameters to a sqlCommand using the add() function. This allows me to specify the type of the sqlParameter, but it requires another line to set the value. 

It's nice to use the AddWithValue function, but it skips the "specify the parameter type" step.

I'm guessing this causes the parameters to be sent over as strings contained within single quotes (''), but I'm not sure.

Is this the case, and does this cause significantly slower performance of the stored procedures?


Note: I understand that it is nice to validate user data on the .NET side of things by specifying the data type for params -- I'm only concerned about reflection-type overhead of AddWithValue either on the .NET or SQL side.


SQL - .NET - SqlParameters - AddWithValue - Are there any negative performance implications when Param Type is not specified?

You can check whether a transaction is already open by checking if `cmd.Transaction` is `null`.  

Then wrap your code accordingly so if a transaction is already open, then the calling function owns that transaction and will commit it / roll it back appropriately.  


 
    //begin transaction unless one was already started
	bool newTransaction = cmd.Transaction == null;
	if (newTransaction) cmd.Transaction = cmd.Connection.BeginTransaction();

	try {
		// do Stuff Here
		cmd.ExecuteNonQuery();
        cmd.ExecuteNonQuery();

		// commit if it's our to commit
		if (newTransaction)	cmd.Transaction.Commit();

	} catch (SqlException ex) {
		if (newTransaction && cmd.Transaction != null) cmd.Transaction.Rollback();
		throw;
	}
    
Then the parent function can pass in a command and optionally choose to begin it's own transactional block, or if not, one will be created and commited by the called function.

I am getting a `SqlConnection does not support parallel transactions.` exception and [this answer](https://stackoverflow.com/questions/407320/strange-sql2005-problem-sqlconnection-does-not-support-parallel-transactions/432165#432165) mentions its when a connection tries to open two transactions. This is exactly what i am doing. I thought nested transactions were ok (i was using sqlite for the prototype).

How do i check if the connection is already in a transaction? I am using Microsoft SQL Server Database File.

Check if a connection is in a transaction

### What is Soundex?

[**Soundex**][1] is:

> a phonetic algorithm for indexing names by sound, *as pronounced **in English***; first developed by Robert C. Russell and Margaret King Odell in 1918

### How does it Work?

There are several implementations of Soundex, but most implement the following steps:

1. Retain the first letter of the name and drop all other occurrences of vowels and `h,w`:  
  `|a, e, i, o, u, y, h, w  | → ""  |`  
2. Replace consonants with numbers as follows (after the first letter):  
  `| b, f, p, v             | →  1  |`  
  `| c, g, j, k, q, s, x, z | →  2  |`  
  `| d, t                   | →  3  |`  
  `| l                      | →  4  |`  
  `| m, n                   | →  5  |`  
  `| r                      | →  6  |` 
3. Replace identical adjacent numbers with a single value (*if they were next to each other prior to step 1*):  
  `| M33                    | → M3   |`   
4. *Cut* or *Pad* with zeros or cut to produce a 4 digit result:  
  `| M3                     | → M300 |`   
  `| M34123                 | → M341 |`   

Here's an interactive demo in [**jsFiddle**][2]:

[![jsFiddle Demo][3]][2show]

And here's a demo in SQL using [**SQL Fiddle**][7]

In SQL Server, [`SOUNDEX`][4] is often used in conjunction with [`DIFFERENCE`][5], which is used to score how many of the resulting digits are identical (just like the game mastermind<sup>†</sup>), with higher numbers matching most closely.

[![Mastermind][6]][6]


### What are the Alternatives?

It's important to understand the limitations and criticisms of soundex and where people have tried to improve it, notably only being rooted in English pronunciation and also [discards a lot of data, resulting in more false positives][11].

-----

Both [**Metaphone**][8] & [**Double Metaphone**][9] still focus on English pronunciations, but add much more granularity to the [nuances of speech in Enlgish][10] (ie. `PH` → `F`)

Phil Factor wrote a [Metaphone Function in SQL][13] with the [source on github][12]

----

Soundex is most commonly used on identifying similar names, and it'll have a really hard time finding any **similar nicknames** (i.e. `Robert` → `Rob` or `Bob`).  Per this question on a [Database of common name aliases / nicknames of people][14], you could incorporate a lookup against similar nicknames as well in your matching process.  

Here are a couple free lists of common nicknames:

* [**SOEMPI** - `name_to_nick.csv` | Github][15]
* [**carltonnorthern** - `names.csv` | Github][16]


### Further Reading:

* [Fuzzy matching using T-SQL][17]
* [SQL Server – Do You Know Soundex Functions?][18]


  [2show]: http://jsfiddle.net/KyleMit/zyjLph3n/show
  [1]: https://en.wikipedia.org/wiki/Soundex
  [2]: http://jsfiddle.net/KyleMit/zyjLph3n/
  [3]: https://i.stack.imgur.com/g04t6.png
  [4]: https://learn.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql
  [5]: https://learn.microsoft.com/en-us/sql/t-sql/functions/difference-transact-sql
  [6]: https://i.stack.imgur.com/KPCBj.png
  [7]: http://sqlfiddle.com/#!18/9eecb/30588
[8]: https://en.wikipedia.org/wiki/Metaphone
[9]: https://en.wikipedia.org/wiki/Metaphone#Double_Metaphone
[10]: https://en.wikipedia.org/wiki/Ghoti
[11]: https://stackoverflow.com/a/6576522/1366033
[12]: https://github.com/Phil-Factor/SQLMetaPhone/blob/master/SQLMetaphone.sql
[13]: https://www.red-gate.com/simple-talk/blogs/string-comparisons-sql-metaphone-algorithm/
[14]: https://stackoverflow.com/q/2381522/1366033
[15]: https://github.com/MrCsabaToth/SOEMPI/blob/master/openempi/conf/name_to_nick.csv
[16]: https://github.com/carltonnorthern/nickname-and-diminutive-names-lookup/blob/master/names.csv
[17]: https://stackoverflow.com/q/921978/1366033
[18]: http://www.sqlservergeeks.com/sql-server-do-you-know-soundex-functions/

Here's an example of Soundex code in SQL:



    SELECT SOUNDEX('Smith'), SOUNDEX('Smythe');
    ----- ----- 
    S530  S530  

How does `'Smith'` become `S530`?

In this example, the first digit is `S` because that's the first character in the input expression, but how are the remaining three digits are calculated?

How does the Soundex function work in SQL Server?

As Aaron Bertrand pointed out, you can use the undocumented `xp_regread` in SQL Server 2005 and SQL Server 2008, but there is a better way, starting with SQL Server 2008R2 SP1.

From the article [How to identify the SQL Server Service Account in T-SQL][1], you can use [`sys.dm_server_services`][2] like this:



    SELECT  DSS.servicename,
            DSS.startup_type_desc,
            DSS.status_desc,
            DSS.last_startup_time,
            DSS.service_account,
            DSS.is_clustered,
            DSS.cluster_nodename,
            DSS.filename,
            DSS.startup_type,
            DSS.status,
            DSS.process_id
    FROM    sys.dm_server_services AS DSS;

![SQL Service Account Query Results][3]

[1]: http://sqlity.net/en/1868/identify-sql-server-service-account-in-t-sql/
[2]: https://msdn.microsoft.com/en-us/library/hh204542.aspx
[3]: http://i.imgur.com/HdfsUZF.png

How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005).
Is it possible to get using SQL statements or WMI ?



Get Service Account Details of the SQL Agent Service

**NOT mySQL**, but for other people finding this question and using SQL, another way to resolve the [tag:greatest-n-per-group] problem is using [`Cross Apply`][cross apply] in MS SQL


    WITH DocIds AS (SELECT DISTINCT id FROM docs)
    
    SELECT d2.id, d2.rev, d2.content
    FROM DocIds d1
    CROSS APPLY (
      SELECT Top 1 * FROM docs d
      WHERE d.id = d1.id
      ORDER BY rev DESC
    ) d2

###[Here's an example in SqlFiddle][sql fiddle]

[cross apply]: http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
[sql fiddle]: http://sqlfiddle.com/#!18/25b5c/1

I have this table for documents (simplified version here):


| id   | rev   | content                              |
|------|-------|--------------------------------------|
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |


How do I select one row per id and only the greatest rev?  
With the above data, the result should contain two rows: `[1, 3, ...]` and `[2, 1, ..]`. I'm using ***MySQL***.

Currently I use checks in the `while` loop to detect and over-write old revs from the resultset. But is this the only method to achieve the result? Isn't there a **SQL** solution?



SQL select only rows with max value on a column

If you don't like the look of nested subqueries, you can take [GarethD's great solution][GarethD] and convert it into a Common Table Expression (CTE)

    WITH Transactions AS (
        SELECT ProductID, 'Bill' [Type], Amount FROM Bills
          UNION ALL
        SELECT ProductID, 'Ret'  [Type], Amount FROM Returns
    )
    SELECT  p.*,  [Type], Amount
    FROM    Products p
    JOIN    Transactions t
                ON t.ProductID = p.ProductID

### [Here's a demo in SqlFiddle][fiddle]

[GarethD]: https://stackoverflow.com/a/9032048/1366033
[fiddle]: http://sqlfiddle.com/#!18/336a8/1

My Problem is the following:
Say you have three different tables (Products, Bills and Returns)

    | ProductId | Name |
    =====================
    | 1         | Car  |
    
    | BillId | ProductId | Amount |
    =================================
    | 1      | 1         | 100$   |
    | 2      | 1         | 200$   |
    
    | ReturnId | ProductId | Amount |
    ===================================
    | 1        | 1         | 50$    |

How would a SINGLE Query look like to get the following output:

    | Product-ID | Name | Type | Amount |
    =====================================
    | 1          | Car  | Bill | 100$   |
    | 1          | Car  | Bill | 200$   |
    | 1          | Car  | Ret  | 50$    |

I was trying with all sorts of Joins, and somehow I can't get my head around this.
What am I doing wrong?
The closest solution I have found till now was something like this:

    SELECT p.*,
           (CASE
               WHEN b.Amount IS NOT NULL THEN 'Bill'
               ELSE 'Ret'
           END) AS Type,
           COALESCE(b.Amount, r.Amount) AS Amount
    FROM Products p
    LEFT JOIN Bills b ON b.ProductId = p.ProductId
    LEFT JOIN Returns r ON r.ProductId = p.ProductId

One thing is very important to me: The real scenario-query is MUCH bigger, and I don't want to copy/paste the whole logic of there query as if it would be the case when using a Union.

T-SQL: Single-Query Join with either one Table or another

I don't know how you are connecting Emplyee(sic?) and Employment, but you want to use a join to connect two tables and in the join specify how the tables are related.  Joins usually look best when they have aliases so you don't have to repeat the entire table name.  The following query will get you all the information from both Employment and Distribution tables where the distribution location is equal to california.  You can join employee to employment to get name as well.


    SELECT *
    FROM Employment e
    JOIN Distribution d on d.DistributionID = e.DistributionID
    WHERE d.Location = 'California'


This will return the contents of both tables.  To select particular records use the alias.[Col_Name] separated by a comma in the select statement, like d.DistributionID to return the DistributionID from the Distribution Table

I am trying to use a second `SELECT` to get some ID, then use that ID in a second `SELECT` and I have no idea how.

    SELECT Employee.Name 
    FROM Emplyee, Employment 
    WHERE x = Employment.DistributionID 
    (SELECT Distribution.DistributionID FROM Distribution 
           WHERE Distribution.Location = 'California') AS x

**This post got long, but here is a short "tip"**

While the syntax of my select is bad, the logic is not. I need that "x" somehow. Thus the second `select` is the most important. Then I have to use that "x" within the first `select`. I just don't know how

**/Tip**

This is the only thing I could imagine, I'm very new at Sql, I think I need a book before practicing, but now that I've started I'd like to finish my small program.

**EDIT:**

Ok I looked up joins, still don't get it

    SELECT Employee.Name 
        FROM Emplyee, Employment 
        WHERE x = Employment.DistributionID 
        LEFT JOIN Distribution ON
        (SELECT Distribution.DistributionID FROM Distribution 
               WHERE Distribution.Location = 'California') AS x

Get error msg at `AS` and `Left`

I use name to find ID from upper red, I use the ID I find FROM upper red in lower table. Then I match the ID I find with Green. I use Green ID to find corresponding Name

![enter image description here][1]

I have `California` as output data from C#. I want to use `California` to find the DistributionID. I use the DistributionID to find the EmployeeID. I use EmployeeID to find Name

**My logic:**

    Parameter: Distribution.Name (from C#)

    Find DistributionID that has Distribution.Name
    Look in Employment WHERE given DistributionID 
          reveals Employees that I am looking for (BY ID)
    Use that ID to find Name
          return Name

Tables: 

**NOTE:** In this example picture the Employee repeats because of the select, they are in fact singular

In "Locatie" (middle table) is Location, I get location (again) from C#, I use `California` as an example. I need to find the `ID` first and foremost!

![enter image description here][2]


Sory they are not in english, but here are the create tables: 

![enter image description here][3]


  [1]: http://i.stack.imgur.com/SqgB6.jpg
  [2]: http://i.stack.imgur.com/LV4uO.png
  [3]: http://i.stack.imgur.com/6oQte.png

How to use the result from a second select in my first select

just exclude those group ID's that have a record where HasData = 0.

    select distinct a.groupID
    from table1 a 
    where not exists(select * from table1 b where b.HasData = 0 and b.groupID = a.groupID)



I'd like to do a query for every GroupID (which always come in pairs) in which both entries have a value of 1 for HasData.

	|GroupID | HasData |
	|--------|---------|
	|  1     |  1      |
	|  1     |  1      |
	|  2     |  0      |
	|  2     |  1      |
	|  3     |  0      |
	|  3     |  0      |
	|  4     |  1      |
	|  4     |  1      |

So the result would be:

    1
    4

here's what I'm trying, but I can't seem to get it right.  Whenever I do a `GROUP BY` on the GroupID then I only have access to that in the selector



    SELECT GroupID
    FROM Table
    GROUP BY GroupID, HasData
    HAVING SUM(HasData) = 2 

But I get the following error message because HasData is acutally a bit:  

    Operand data type bit is invalid for sum operator.

Can I do a count of two where both records are true?

Finding records sets with GROUP BY and SUM

### Update for SQL Server 2012+

I always find the `CONVERT` magic numbers hard to remember.  You can use [`FORMAT`][1] instead and provide a formatting pattern string like this:


```sql
SELECT FORMAT(GetDate(),'yyyy-MM')
```

**See Also**: [Convert date to YYYYMM format][2]

[1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql
[2]: https://stackoverflow.com/a/49890439/1366033

need the date in the format yyyy-mm and should be grouped to get 
the count but when i give the 

    group by date is says invalid

i have found this solution 

can anyone help me by providing other solution


    select 
    count(*) as count, 
    Convert(char(10), RH.updated_datetime, 105) as date,
    SUM( 
      datediff(SECOND, 
           PRI.procedure_performed_datetime ,RH.updated_datetime
      )
    )/count(*) as average_reporting_tat 
    from 
    report R, 
    report_history RH, 
    study S, 
    procedure_runtime_information PRI, 
    priorities PP, 
    patient P, 
    "procedure" PR 
    
    where 
    
    RH.report_fk=R.pk and RH.pk IN ( 
      select pk from ( 
        select * from report_history where report_fk=r.pk  
      ) as result 
      where old_status_fk IN (21, 27)
    ) AND R.study_fk = S.pk 
    AND S.procedure_runtime_fk = PRI.pk 
    AND PRI.procedure_fk = PR.pk 
    AND S.priority_fk = PP.pk 
    AND PRI.patient_fk = P.pk 
    AND RH.updated_datetime >= '2012-05-01' 
    AND RH.updated_datetime <= '2013-09-12' 
    group by Convert(char(10), RH.updated_datetime, 105)



need date in the format yyyy-mm in mssql


Try the below

    select daily_user_count, count(*) from (
    SELECT COUNT(*) as daily_user_count FROM Activity
    GROUP BY entity_uid, date
    ) Z
    group by daily_user_count
    


I'd like to know how often a particular user does an activity on the same day



    SELECT COUNT(*) FROM Activity
    GROUP BY entity_uid, date

This returns a long lists of counts: i.e.

    1
    1
    2
    1

I'd then like to group the results of this query that tell me how often each number appears within the result set, like this

    #    Count(*)
    1    3
    2    1

Here's what I have, but it doesn't work



    Select x = (
                SELECT COUNT(*) FROM Activity
                GROUP BY entity_uid, date
               ),
           Count(*)
    GROUP BY x

How can I select the results of a subquery in a variable?

Group results of grouped query

Depending on you flavor of SQL, you can also [imply the else statement][1] in your aggregate counts.

For example, here's a simple table `Grades`:



<pre><code>| <b>Letters</b> |
|---------|
| A       |
| A       |
| B       |
| C       |</code></pre>

We can test out each Aggregate counter syntax like this ([**Interactive Demo in SQL Fiddle**][3]):



    SELECT
        COUNT(CASE WHEN Letter = 'A' THEN 1 END)           AS [Count - End],
        COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END) AS [Count - Else Null],
        COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)    AS [Count - Else Zero],
        SUM(CASE WHEN Letter = 'A' THEN 1 END)             AS [Sum - End],
        SUM(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END)   AS [Sum - Else Null],
        SUM(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)      AS [Sum - Else Zero]
    FROM Grades

And here are the results ([unpivoted][2] for readability):



<pre><code>|    <b>Description</b>    | <b>Counts</b> |
|-------------------|--------|
| Count - End       |    2   |
| Count - Else Null |    2   |
| Count - Else Zero |    4   | *<b>Note</b>: Will include <i>count</i> of <i>zero</i> values
| Sum - End         |    2   |
| Sum - Else Null   |    2   |
| Sum - Else Zero   |    2   |</code></pre>

Which lines up with the docs for [Aggregate Functions in SQL][4]

Docs for [**`COUNT`**][5]:

> `COUNT(*)` - returns the number of items in a group. This includes NULL values and duplicates.  
> `COUNT(ALL expression)` - evaluates expression for each row in a group, and returns the number of nonnull values.  
> `COUNT(DISTINCT expression)` - evaluates expression for each row in a group, and returns the number of unique, nonnull values.

Docs for [**`SUM`**][6]:

> `ALL` - Applies the aggregate function to all values. ALL is the default.  
> `DISTINCT` - Specifies that SUM return the sum of unique values.

[1]: https://dba.stackexchange.com/a/123668/31340
[2]: https://stackoverflow.com/a/19056083/1366033
[3]: http://sqlfiddle.com/#!18/9eecb/32254
[4]: https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-2017
[5]: https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-2017
[6]: https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-2017

I have a table and i need to present the output in the following fashion.

    tb_a:
    
    col1  |  reg_id | rsp_ind 

Count of rows with rsp_ind = 0 as 'New' and 1 as 'Accepted'

The output should be 

  

    NEW | Accepted
    9   | 10
I tried using the following query.

    select 
      case when rsp_ind = 0 then count(reg_id)end as 'New',
      case when rsp_ind = 1 then count(reg_id)end as 'Accepted'
    from tb_a


and i m getting output as 

    NEW | Accepted
    NULL| 10
    9   | NULL

Could someone help to me tweak the query to achieve the output.
Note : I cannot add a sum surrounding this. Its part of a bigger program and so i cannot add a super-query to this.

using sql count in a case statement

Yes, SQL Server **2012** supports multiple inserts - that feature was introduced in SQL Server **2008**.

That makes me wonder if you have Management Studio 2012, but you're really connected to a SQL Server **2005** instance ...

What version of the SQL Server **engine** do you get from `SELECT @@VERSION` ??

These StackOverflow questions [here][Q1], [here][Q2], and [here][Q3] all say the same thing, but I can't get it to run in SSMS or SQLFiddle



    CREATE TABLE #Names
      ( 
        Name1 VARCHAR(100),
        Name2 VARCHAR(100)
      ) 

    INSERT INTO #Names
      (Name1, Name2)
    VALUES
      ('Matt', 'Matthew'),
      ('Matt', 'Marshal'),
      ('Matt', 'Mattison')

When I execute this is SSMS, the insert fails with the following message on the first line after `VALUES`

> Msg 102, Level 15, State 1, Line 10    
> Incorrect syntax near ','.

This [**Fiddle**][fiddle] runs without the `#` sign, and the schema executes successfully when the table name is `#Names`, but I get the following message when I try to select * from the table 

> Invalid object name '#NAMES'.: SELECT * FROM #NAMES

Does SQL Server 2012 support multiple inserts?

----

**Update**: Apparently accessing a 2005 server on SSMS 2012....



    SELECT @@VERSION --Returns: Microsoft SQL Server 2005

    



 [Q1]: https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query
 [Q2]: https://stackoverflow.com/questions/2676493/insert-multiple-rows-into-temp-table-with-one-command-in-sql2005
 [Q3]: https://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part
 [fiddle]: http://sqlfiddle.com/#!18/32d8f/1

Insert Multiple Rows Into Temp Table With SQL Server 2012

They should perform and behave the same - the difference is in developer semantics

Here's a sample demo:

```sql
-- Setup
DECLARE @Codeset TABLE (id INT, [name] VARCHAR(50));
DECLARE @Student TABLE (id INT, [name] VARCHAR(50), age INT);

INSERT INTO @Codeset  
VALUES (1, 'HomeRoom');

INSERT INTO @Student  
VALUES (1, 'Jolly', 20), 
       (2, 'Sally', 22);
```

Let's say we want back every row from `@Student` and also to bring back the name value from `@Codeset`:

```none
| id | name  | age | name     |
|----|-------|-----|----------|
| 1  | Jolly | 20  | HomeRoom |
| 2  | Sally | 22  | HomeRoom |
```

Any of the following syntaxes will accomplish this when either table has 0, 1, or 1+ records:

```sql
-- INNER JOIN 1=1
SELECT s.id, s.[name], s.age, c.[name]
FROM @Student s
INNER JOIN @Codeset c ON 1=1

-- Multiple From tables
SELECT s.id, s.[name], s.age, c.[name]
FROM @Student s,
     @Codeset c

-- CROSS JOIN
SELECT s.id, s.[name], s.age, c.[name]
FROM @Student s
CROSS JOIN @Codeset c
```

In this situation, you should prefer [**`CROSS JOIN`**][1] to be as explicit as possible as to your intentions to build a single select statement from multiple unrelated tables


> **Note**: `CROSS JOIN` will output the same as `INNER JOIN ON 1=1`.  Both produce the Cartesian product of all rows in both rowsets, so if one table is empty, the results will be as well. 
>
> If you need to guarantee results when `@Students` has records, but `@Codeset` might be empty, you will need to use `LEFT JOIN ON 1=1`

**See Also**: [Correct way to select from two tables in SQL Server with no common field to join on][2]

[1]: https://learn.microsoft.com/en-us/u-sql/statements-and-expressions/select/from/joins/cross-join
[2]: https://stackoverflow.com/q/17582313/1366033

I am joining a large table in postgresql to a table with 1 row in it. Yes, I know I could just take the values from this single row table and put them in my query written out, but there's 210 columns.

So my question is this: should I join up the single row table to everything using a cross join or using a regular join on a tautology (1 = 1 or something). Is either of those ways bound to be slower?

Or is there a third faster way?

Join on 1=1 versus cross join

You can use a recursive query to build up a table of whatever numbers you want. Here we stop at 24. Then left join that to your comments to ensure every hour is represented. You can turn these into times easily if you wanted. I also changed your use of `hour` as a column name as it is a keyword.

    ;with dayHours as (
    	select 1 as HourValue
    	union all select hourvalue + 1
    	from dayHours
    	where hourValue < 24
    )
    ,
    CommentTimes As (
           SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As HourValue,
                  COUNT(*) AS Count
           FROM Comments
           WHERE UserId = ##UserId##
           GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate)))
    SELECT h.Hour, c.Count
    FROM dayHours h
    left JOIN CommentTimes c ON h.HourValue = c.HourValue

This feels simple, but I can't find an answer anywhere.   
I'm trying to run a query by time of day for each hour.  So I'm doing a `Group By` on the hour part, but not all hours have data, so there are some gaps.  I'd like to display every hour, regardless of whether or not there's data.  

Here's a sample query:



    SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As Hour,
           COUNT(*) AS Count
    FROM Comments
    WHERE UserId = ##UserId##
    GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))

My thought was to Join to a table that already had numbers 1 through 24 so that the incoming data would get put in it's place.
 
Can I do this with a CTE?



    WITH Hours AS (
       SELECT i As Hour    --Not Sure on this
       FROM [1,2,3...24]), --Not Sure on this
    CommentTimes AS (
       SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) AS Hour,
              COUNT(*) AS Count
       FROM Comments
       WHERE UserId = ##UserId##
       GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))
    )
    SELECT h.Hour, c.Count
    FROM Hours h
    JOIN CommentTimes c ON h.Hour = c.Hour

###Here's a sample [Query From Stack Exchange Data Explorer](https://data.stackexchange.com/stackoverflow/revision/185460/239532/comments-by-hour)

Select Consecutive Numbers in SQL

This does it:

    ;WITH versions (suffix) AS (
        SELECT '_R1' UNION ALL
        SELECT '_v2' UNION ALL
        SELECT '_v1'
    )
    SELECT	name, 
    		REPLACE(A.name,ISNULL(B.suffix,''),'') VersionlessName
    FROM applications A
    LEFT JOIN versions B
    	ON A.name LIKE '%'+B.suffix

The results are:

    ╔════════════╦═════════════════╗
    ║    name    ║ VersionlessName ║
    ╠════════════╬═════════════════╣
    ║ MyApp_v2   ║ MyApp           ║
    ║ MyApp_v1   ║ MyApp           ║
    ║ MyApp      ║ MyApp           ║
    ║ YourApp_R1 ║ YourApp         ║
    ╚════════════╩═════════════════╝
And [**here is**][1] the modified sqlfiddle.


  [1]: http://sqlfiddle.com/#!3/400cb/1

I'm trying to strip off version suffixes (`_v1`...) from a whole bunch of app names. 

For example, let's say I have the following data:

    CREATE TABLE applications 
     (
       name varchar(20)
     );
    
    INSERT INTO applications
     (name)
    VALUES
     ('MyApp_v2'),
     ('MyApp_v1'),
     ('MyApp'),
     ('YourApp_R1');

I could normally do this by nesting a lot of replace statements:

    SELECT REPLACE(REPLACE(REPLACE(
             name,
           '_R1', ''),
           '_v2', ''),
           '_v1', '')
           As AppNameWithoutSuffix
    FROM applications

But I have a lot of version numbers to check for, so I was hoping for something cleaner.  Based on the following questions:

* [SQL Nested Replace][SO1]
* [Are multiple Replace statements a bad practice/is there another way to write them?][SO2]



I wanted to create a CTE that stored all the prefixes and then [`REPLACE`][Replace] them all like this:

    ;WITH versions (suffix) AS (
        SELECT '_R1' UNION ALL
        SELECT '_v2' UNION ALL
        SELECT '_v1'
    )
    SELECT REPLACE(a.name, v.suffix, '') As AppNameWithoutSuffix
    FROM applications a,
         versions v

But this does not quite work.  The multiple from statements gives me the cartesian product of both tables and only strips out the suffix on the rows where the value happens to line up.

### [Demo in Sql Fiddle][Sql Fiddle]

*Note*: I know I could convert this to a function, but I'd rather keep everything within a single query if possible.  

[SO1]: https://stackoverflow.com/q/13088921/1366033
[SO2]: https://stackoverflow.com/q/5475428/1366033
[Sql Fiddle]: http://sqlfiddle.com/#!18/165bb/3

[Replace]: http://msdn.microsoft.com/en-us/library/ms186862.aspx

Use Column value as string pattern in REPLACE function

It appears this is **not possible**.

According to [Andrew Barber's comment on "Get windows user login name from sql server"][1]:

> If they are logged in as a SQL Server user, they are *not* logged in as a Windows user; no Windows credentials are passed at all; so you simply can't get that information directly

[1]: https://stackoverflow.com/q/25629000/#comment40043473_25629000

We are using a SQL Login as a vehicle for issuing permissions, but once authenticated through SQL, we'd still like to know the Active Directory name of the connected user?

We've tried:

```sql
SELECT SUSER_NAME()
SELECT SUSER_SNAME()
SELECT SUSER_SID()
SELECT USER_NAME()
SELECT USER_ID()
SELECT SYSTEM_USER
SELECT SESSION_USER
SELECT ORIGINAL_LOGIN()
```

Is that information still available somewhere?

**Note**: This is different from how to [get windows user login name from sql server][1] since we're using *SQL Login*, not a *Windows Login* to connect to SQL Server


[1]: https://stackoverflow.com/q/25629000/1366033

Get current user while using SQL Authentication

Just use `.slider('getValue')` like this:





    $("#pricelimit").slider('getValue');

Which should return  <code>[*min*, *max*]</code>
    
If you need to access the values on the server in PHP or SQL, just makes sure the input element is posting back to the server.  Then get the value of the input element and use it however you'd like.

For example:



    $("#pricelimit").val();

Should return  <code>"*min*, *max*"</code>

### Demo in Stack Snippets





    $(function() {

      $("#pricelimit")
      .slider({})
      .on('slide', function() {
        $('#output').html(this.value);
      }).trigger('slide');

    });



    <link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.2/css/bootstrap.css" rel="stylesheet"/>
    <link href="//seiyria.github.io/bootstrap-slider/stylesheets/bootstrap-slider.css" rel="stylesheet"/>

    <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.1.3/jquery.js"></script>
    <script src="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.2/js/bootstrap.js"></script>
    <script src="//seiyria.github.io/bootstrap-slider/javascripts/bootstrap-slider.js"></script>

    <div class='priceslider'>
      <p>Range selector, options specified via data attribute.</p>
      <div class="well">
        Filter by price: <b>€ 10</b> 
        <input id="pricelimit" type="text" class="span2" value=""
               data-slider-min="10" 
               data-slider-max="200" 
               data-slider-step="5" 
               data-slider-value="[20,100]"/>
        <b>€200</b>
      </div>
    </div>
    <p>
     <b>Min/Max</b>: <span id="output"></span>
    </p>

I'm using a price slider in my site to select products between a certain price range, but I'm not really sure how to do this. I am using the price range slider from http://www.eyecon.ro/bootstrap-slider/.



    <div class='priceslider'>
      <p>Range selector, options specified via data attribute.</p>
      <div class="well">
        Filter by price: <b>€ 10</b> 
        <input id="pricelimit" type="text" class="span2" value="" 
               data-slider-min="10" 
               data-slider-max="200" 
               data-slider-step="5" 
               data-slider-value="[20,100]"/>
        <b>€200</b>
      </div>
    </div>



    $(document).ready(function() {
      $("#pricelimit").slider({});
    });





How do I get the value(s) from this price range slider and how do I use them in a query to select products between those price values?

Price limit slider with Bootstrap

The syntax is correct, but you're not doing anything with the value once inside the loop.  You'll want to persist it somehow.  Here's an example of running some basic SQL inside of powershell, with two different types of commands (Text/SP) and two different methods of execution (DataAdapter/DataReader).  Either one of each should work fine.



    # config
    $svr = "serverName"
    $db = "databaseName"
    
    # connection
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$svr;Database=$db;Integrated Security=True"
    $sqlConnection.Open()
    
    # command A - text
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.Connection = $sqlConnection
    $sqlCmd.CommandText = "SELECT name AS TABLE_NAME FROM sys.tables"
    
    # command B - stored procedure
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.Connection = $sqlConnection
    $sqlCmd.CommandText = "sys.sp_tables"
    $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
    $sqlCmd.Parameters.Add("@table_owner", "dbo")
    
    # execute A - data reader
    $reader = $sqlCmd.ExecuteReader()
    $tables = @()
    while ($reader.Read()) {
        $tables += $reader["TABLE_NAME"]
    }
    $reader.Close()
    
    # execute B - data adapter
    $dataTable = New-Object System.Data.DataTable
    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCmd
    $sqlAdapter.Fill($dataTable)
    $sqlConnection.Close()

I'm trying to read data from a SQL table in a powershell script. I can see the data in reader object, but when reading it using `While (readerobject.read()){}`, it's not going inside the loop.


**Powershell**:



     $cmd = $sqlConn.CreateCommand()
     $cmd.CommandText ="SELECT * from user"
     $movedUserDetails = $cmd.ExecuteReader()
     while ($movedUserDetails.Read())
       {
          "[0] : " + $movedUserDetails.GetValue(0)
       }
     $movedUserDetails.Close()

ExecuteReader() in Powershell script

These questions ([one][SO1], [two][SO2], [three][SO3]) all identified how to return an empty string instead of `NULL` *for a single column*:

    SELECT ISNULL(ColA,'') AS ColA FROM Table1

However, I have a query that returns a whole bunch of columns that I'm too lazy to select individually (don't worry - this isn't a production query)

    SELECT * FROM Table1

Is there a way to return an empty string for all columns returned by the wildcard operator?

For Example, the following naive attempt *won't work*:

    SELECT ISNull(*,'') FROM Table1

[SO1]: https://stackoverflow.com/q/21233442/1366033
[SO2]: https://stackoverflow.com/q/12394545/1366033
[SO3]: https://stackoverflow.com/q/17244808/1366033

Replace NULL with empty string in SQL for ALL columns

## Problem

There's some weird behavior when using [`SqlConnectionStringBuilder`][1] in PowerShell - let me explain

Since it's a dotnet class, you'd expect all of the same properties and methods available in C#

For example, this works fine in C#:

```cs
var cnnBuilder = new SqlConnectionStringBuilder();
cnnBuilder.DataSource = "server_name";
cnnBuilder.InitialCatalog = "db_name";
```

So the equivalent code in PS, *should* work:

```cs
$cnnBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder.DataSource = "server_name"
$cnnBuilder.InitialCatalog = "db_name"
```

However, `SqlConnectionStringBuilder` is built ontop of [`DbConnectionStringBuilder`][2] which implements [`IDictionary`][3] so fundamentally we're working with a dictionary object that has some syntactic sugar wrappers

.NET resolves this with an [override on the dictionary accessors and setters][4] like this (simplified here):

```cs
public override object this[string keyword] {
    get {
        Keywords index = GetIndex(keyword);
        return GetAt(index);
    }
    set {
        Keywords index = GetIndex(keyword);
        switch(index) {
            case Keywords.DataSource: DataSource = ConvertToString(value); break;
            case Keywords.InitialCatalog: InitialCatalog = ConvertToString(value); break;
            // ***
        }
    }
}
```

So really, it's taking the `DataSource` property and mapping it to the `"Data Source"` key <sup>(*with space*)</sup>

Whenever PS assigns or retrieves a value, it has to decide whether to use the underlying dictionary implementation or the property.  And when you look for `DataSource` in the dictionary (without the space), that [sql connection keyword][6] doesn't exist.



## Solutions

### Opt 1 - Use Dictionary Names

You can use the bracket or dot notation with the actual sql key to access the entry in the [hashtable][7]

```cs
$cnnBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder["Data Source"] = "server_name"
$cnnBuilder."Initial Catalog" = "db_name"
```

### Opt 2 - Use `PSBase`

`PSBase` returns the [*"raw view of the object"*][8] and will give us the default behavior in dotnet

```cs
$cnnBuilder  = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder.PSBase.DataSource = "server_name"
$cnnBuilder.PSBase.InitialCatalog = "db_name"
```

### Opt 3 - Use `-Property` Parameter

During the construction, you can set the [`-Property`][9] parameter on `New-Object` which *"sets property values and invokes methods of the new object."*

```cs
$cnnBuilder  = New-Object System.Data.SqlClient.SqlConnectionStringBuilder `
    -Property @{
        DataSource = "server_name"
        InitialCatalog = "db_name"
    }
```

### Additional Reading

[Using SQLConnection object in PowerShell][5]


[1]: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?view=dotnet-plat-ext-3.1
[2]: https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbconnectionstringbuilder?view=netcore-3.1
[3]: https://learn.microsoft.com/en-us/dotnet/api/system.collections.idictionary?view=netcore-3.1
[4]: https://github.com/microsoft/referencesource/blob/4.6.2/System.Data/System/Data/SqlClient/SqlConnectionStringBuilder.cs#L250
[5]: https://superuser.com/q/453531/180163
[6]: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver15
[7]: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_hash_tables?view=powershell-7
[8]: https://devblogs.microsoft.com/powershell/whats-up-with-psbase-psextended-psadapted-and-psobject/
[9]: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/new-object?view=powershell-7#parameters

In .Net we can get the datasource from a connectionstring using below mechanism:

    System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
    
    string server = builder.DataSource;

I was trying to do that in PowerShell but getting the following exception:

    $ConstringObj = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($conString)

> New-Object : Exception calling ".ctor" with "1" argument(s): "Keyword
> not supported: 'metadata'." At line:1 char:17
> + $ConstringObj = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($con ...
> +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
>     + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

How to do that in PowerShell?

How to get datasource from Connectionstring using PowerShell?

You can also use the [`BETWEEN`][1] operator in a `JOIN` like this:

```sql
SELECT a.id, a.Count, b.SizeName
FROM tableA a
JOIN tableB b ON a.id BETWEEN b.LowerLimit AND b.UpperLimit
```

[1]: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/between-transact-sql?view=sql-server-ver15

I have the two following tables:

```none
| ID  | Count |
| --- | ----- |
| 1   | 45    |
| 2   | 5     |
| 3   | 120   |
| 4   | 87    |
| 5   | 60    |
| 6   | 200   |
| 7   | 31    |
```

```none
| SizeName | LowerLimit | UpperLimit |
| -------- | ---------- | ---------- |
| Small    | 0          | 49         |
| Medium   | 50         | 99         |
| Large    | 100        | 250        |
```
    

Basically, one table specifies an unknown number of range names and their associated integer ranges. So a count range of 0 to 49 from the person table gets a `small` designation. 50-99 gets '`medium`' etc. I need it to be dynamic because I do not know the range names or integer values.

Can I do this in a single query or would I have to write a separate function to loop through the possibilities?

How to join a number with the range of number from another table

When using [`Group By`][2], the SELECT statement may only contain fields in the Group By statement or aggregate functions (like SUM or COUNT).

You can use [a Common Table Expression (CTE)][1] to consolidate the logic of building the `CardStatus` into a select statement, and then funnel that into a new query which leverages it in both the group by and select statements



    ;WITH BalanceCards AS (
        SELECT a.balance,
               (CASE WHEN c.status='Active'                THEN 'Active'
                     WHEN c.status IN ('Inactive','Other') THEN 'Inact'
                     WHEN c.status IS NULL                 THEN 'No Card' 
                END) AS CardStatus
        FROM people p
        JOIN accounts a ON p.id = a.PeopleFK
        LEFT JOIN cards c ON p.id = c.PeopleFK
    )
    SELECT bs.CardStatus, 
           SUM(bs.balance) As Total, 
           COUNT(*) As [Count]
    FROM BalanceCards bs
    GROUP BY bs.CardStatus

[1]: https://technet.microsoft.com/en-us/library/ms190766
[2]: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

I have 3 tables that I need to join, count/sum data on, and group the data. Below are very simplified versions of my tables.

        People p
    ID 
    1
    2
    3
        Accounts a
    ID | Balance | PeopleFK
    1  | 50      | 1
    2  | 25      | 2
    3  | 15      | 3

        Cards c
    ID | Status | PeopleFK
    1  | Active | 1
    2  | Inact  | 1
    3  | Active | 2
    4  | Other  | 2


What I have so far:

    select sum(a.balance),count(c.*),
    case when c.status='Active' then 'Active'
    case when c.status IN ('Inactive','Other') then 'Inact'
    case when c.status is null then 'No Card' end as 'CardStatus'
    from people p
    join accounts a on p.id=a.PeopleFK
    left join cards c on p.id=c.PeopleFK
    group by c.status


Required result would be something like:

    Balance  |  CardStatus  |  CardCount
    50       |  Active      |  2
    25       | Inactive     |  1
    0        | NoCard       |  1


But my result groups by all of the Card statuses. I understand why this happens but I'm not sure how I can get around this without writing separate queries for each case.

So instead of Inactive and Other grouping together, they group apart.

I'm still toying with this but any suggestions?

Using case, count, and group by in 1 query

From [Can LINQ be used to find gaps in a sorted list?][1]: 


    var strings = new string[] { "7", "13", "8", "12", "10", "11", "14" };
    var list = strings.OrderBy(s => int.Parse(s));
    var result = Enumerable.Range(list.Min(), list.Count).Except(list).First(); // 9

Basically, order the list.  Then create an array of sequential numbers (`1,2,3...`) from the minimum all the way to the max.  Check for missing values in the list, and grab the first one.  That's the first missing number.

[1]: https://stackoverflow.com/q/4270321/1366033

One of my small database management projects (written in delphi) used sql queries to find the first free id of mysql table.

Example: I have to find the first free id (hole) in a table like this:



    | id   | Col1 |
    |------|------|
    | 5101 | ABC  |
    | 5102 | BCD  |
    | 5103 | CDE  |
    | 5105 | EFG  | 🡔 first missing id
    | 5106 | GHI  |
    | 5108 | ILM  |

The code should find the first free id `5104` 

Here's how I'd do it in SQL (in old project): 



    SELECT
      MIN((doc.id + 1)) AS nextID
    FROM (doc
      LEFT JOIN doc doc1
        ON (((doc.id + 1) = doc1.id)))
    WHERE (ISNULL(doc1.id) AND (doc.id > 5000))


Now, which I am rewriting in c # language, I need to convert sql statements into a LINQ query (which uses Devart dotConnect for mysql Entity Framework).
Starting from here:

    DC db = new DC();
    var nums = db.Documentos.OrderBy(x => x.Id);

Find the first free id

One method uses a little bit more storage, but it accomplishes what you want:

    create table addresses (
        . . .,
        codeset as (convert(varchar(?), 'states')) persisted,
        foreign key (codeset, state) references codevalues (codeset, codevalue)
    );

The `?` is for the length of the `varchar()` column in the `codevalues` table.

Happily, you don't need triggers or user-defined functions to accomplish this.

Unhappily, you need to persist the `codeset` in each row, so that eats up a wee bit of space in the table.



## Setup

Here's two simplified* tables that illustrate the problem I have    
<sub>\*The actual tables are built such that I can't really refactor the columns or split them apart easily</sub>

### Table: `CodeValues`



<pre><code>| <b>Code<i>Set</i></b> | <b>Code<i>Value</i></b> | <b>Code<i>Text</i></b> |
|---------|-----------|----------|
| States  | 1         | Vermont  |
| States  | 2         | Hawaii   |
| YN      | 1         | Yes      |
| YN      | 2         | No       |
</pre></code>

<sup>Where `CodeSet` + `CodeValue` are a composite primary key</sup>

### Table: `Address`



<pre><code>| <b>AddressID</b> | <b>Zip</b>   | <b>State</b> |
|-----------|-------|-------|
| 1         | 96701 | 2     |
| 2         | 05001 | 1     |
| 3         | 05602 | 1     |</pre></code>

<sup>Where `AddressID` is the primary key</sup>

----


**What is the appropriate DB constraint to add to `Address.State`?**



It should *always* be a value that is present in `CodeValues Where CodeSet = 'States'`, but I don't believe I can create a [Foreign Key that is part of a Composite Primary Key][1]

Should it just be a [check constraint based on a query][2] like this?

```sql
CREATE FUNCTION checkCodeValues(
    @codeSet   VARCHAR(50),
    @codeValue SMALLINT

)
RETURNS BIT
AS
BEGIN
    IF EXISTS (SELECT * FROM CodeValues WHERE CodeSet = @codeSet
                                          AND CodeValue = @codeValue)
        RETURN 1
    RETURN 0
END
```

```sql
ALTER TABLE Address
    WITH CHECK ADD CONSTRAINT CK_State
    CHECK (checkCodeValues('States', State))
```

My concern is that db design tools won't really recognize the full impact of this constraint against, when it really is a FK, but just against a subsection of the table.

[1]: https://stackoverflow.com/q/12679312/1366033
[2]: https://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint

Add FK / Constraint to subsection of table

This seems like an iteration of the [tag:greatest-n-per-group] problem

I'm not quite certain what constraints you're looking to impose

1. Largest Date
2. Most Recent Date (but not in future)
3. Closest Date to today (past or present)

Here's an example table and which row we'd want if queried on **6/3/2019**:

```
| Item | RequiredDate | Price |
|------|--------------|-------|
| A    | 2019-05-29   |    10 |
| A    | 2019-06-01   |    20 | <-- #2
| A    | 2019-06-04   |    30 | <-- #3
| A    | 2019-06-05   |    40 | <-- #1
| B    | 2019-06-01   |    80 |
```


But I'm going to guess you're looking for **#2**

We can identify we the row / largest date by grouping by `item` and using an aggregate operation like `MAX` on each group

```
SELECT o.Item, MAX(o.RequiredDate) AS MostRecentDt 
FROM Orders o
WHERE o.RequiredDate <= GETDATE()
GROUP BY o.Item
```

Which returns this:

```
| Item | MostRecentDt |
|------|--------------|
| A    | 2019-05-29   |
| A    | 2019-06-01   |
| B    | 2019-06-01   |
```

However, once we've grouped by that record, the trouble is then in joining back to the original table to get the full row/record in order to select any other information not part of the original `GROUP BY` statement

Using `ROW_NUMBER` we can sort elements in a set, and indicate their order (highest...lowest)

```sql
SELECT *, ROW_NUMBER() OVER(PARTITION BY Item ORDER BY RequiredDate DESC) rn
FROM Orders o
WHERE o.RequiredDate <= GETDATE()
```

```
| Item | RequiredDate | Price | rn |
|------|--------------|-------|----|
| A    | 2019-05-29   |    10 | 1  |
| A    | 2019-06-01   |    20 | 2  |
| B    | 2019-06-01   |    80 | 1  |
```

Since we've sorted `DESC`, now we just want to query this group to get the most recent values per group (`rn=1`)

```sql
WITH OrderedPastItems AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Item ORDER BY RequiredDate DESC) rn
  FROM Orders o
  WHERE o.RequiredDate <= GETDATE()
)
SELECT * 
FROM OrderedPastItems
WHERE rn = 1
```

Here's a [**MCVE in SQL Fiddle**][1]

**Further Reading**:

* [SQL selecting rows by most recent date][2]
* [Select row with most recent date per user][3]

[1]: http://sqlfiddle.com/#!18/495c0/11
[2]: https://stackoverflow.com/q/189213/1366033
[3]: https://stackoverflow.com/q/17038193/1366033

I am trying to get the closest date for item no and price based on the current date. The query is giving me output, but not the way I want. 

There is a different price for the same item and it's not filtering. 

Here's my query:

```
SELECT distinct [ITEM_NO]
     ,min(REQUIRED_DATE) as Date
     ,[PRICE]
  FROM [DATA_WAREHOUSE].[app].[OHCMS_HOPS_ORDERS]
  where (REQUIRED_DATE) >= GETDATE() and PRICE is not null
  group by ITEM_NO,PRICE
  order by ITEM_NO
```

[![Current vs Expected Output][1]][1]

Any Ideas?

  [1]: https://i.stack.imgur.com/pW29k.png

Query item with closest date based on current date

I'm trying to make it easier to read some SQL code where we need to hardcode in some large numbers

I'd like to do something like this:

```sql
SELECT 3_800_000
```

Which, according to [this post][3], is really treated like this:

```sql
SELECT 3 _800_000
SELECT 3 AS [_800_000]
```
JS allows [numeric separators][1]

```
let x = 1000000000000
let y = 1_000_000_000_000
console.log(x==y) // true
```

Also, C# added [digit separators][2] in 7.0 as well

```cs
// These are equivalent.
var bigNumber      = 123456789012345678;
var bigNumberSplit = 123_456_789_012_345_678;
```

Is something similar possible in T-SQL?

**Note**: I'm not looking for a way to format the output, I'm looking for a way to make the source code easier to read for big numbers

[1]: https://github.com/tc39/proposal-numeric-separator
[2]: https://learn.microsoft.com/en-us/dotnet/csharp/whats-new/csharp-version-history#c-version-70:~:text=Binary%20Literals%20and%20Digit%20Separators
[3]: https://stackoverflow.com/q/35988034/1366033