Skip to content

Tag: sql

How to write a query to produce counts for arbitrary value bands?

sql

My table had 3 fields: id and unit. I want to count how many ids have <10, 10-49, 50-100 etc units. The final result should look like: This is the query that returns each id and how many units it has: How can I build on that query to give me the category, countIds result? Answer edit: changed sum() to

PostgreSQL Index Usage Analysis

Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the “profiler” tool for SQLServer, but I’m not aware of a similar tool included with Postgres. Answer I …

Getting first day of the week in MySql using Week No

How do I get the first day of a given week whose week number is available? For example as I write this post we are at WEEK 29.I would like to write a MySQL query that will return Sunday 18 July using this WEEKNO 29 as the only available parameter. Answer You can use: This would give you the Monday

Delete duplicate records in SQL Server?

Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field. Using one query, I want to delete the records which are repeated. How can this be done with TSQL in SQL Server? Answer You can do this with window functions. It will order the dupes by…

Excel 2003 send date parameters to SQL query

I have a few worksheets that use sql queries to retrieve data. I would like all of these sheets to query data based on the same date range entered by a user in excel. eg. I would enter somewhere in a form or in a cell a date range and retrieve the value in sql to obtain data based on

DataReader.GetString() via columnname

Dictionary Fields = new Dictionary(); for (int i = 0; i < reader.FieldCount; i++) { Fields.Add(reader.GetName(i), i); } this._MyField1 = reader.GetString(Fields[“field1”]); this._Myfield2 = reader.GetInt16(Fields[“field2”]); doing this makes me want to cry but i can’t seem to …

Case expressions may only be nested to level 10

I have the following code for handling phone numbers such as country code for Australia +61, 61, 001161 etc. The problem that I have I can’t insert any CASE statement anymore under: CASE WHEN LEFT(@BPartyNo, 4) = ‘+610’ It said that Case expressions may only be nested to level 10 How do I st…