Browsing the sql category

sql server query takes longer to run with parameters

(I also initially searched “sql query doesn’t run with parameters“)

I have a SQL query that I was running in Microsoft Management Studio and it took ~20 seconds to run.  It was a rather complicated query, with UNIONs and a Common Table Expression, and a Pivot.  But it ran.

I had some hard-coded values that, when I used parameters instead, the same query took too long to run (i.e. after 20 seconds, I gave up on it).

I changed my hard-coded values to be dynamic values, using this:

declare @first_of_this_month datetime;

declare @date_start varchar(20)

declare @date_end varchar(20)

 

SET @first_of_this_month = CONVERT(datetime, STR(MONTH(getdate())) + ‘/1/’ + STR(YEAR(getdate())));

SET @date_start = DATEADD(m,-5, @first_of_this_month);

SET @date_end =  DATEADD(m,10, @first_of_this_month);

 

And even though this runs in seconds,

select @date_start

select @date_end

select @first_of_this_month

 

the full query took much longer.

 

The solution: just add this to the end of the query:

 

OPTION (RECOMPILE)

 

Not really sure what it does, but it worked!  Note: this was for an SSRS report with two datasources. Even though the “long” query ran okay in SSMS and one query was instant, I had to add this onto BOTH queries (in each dataset) for it to work.

Read the article

SQL for Sunday-based Financial Period

At my company, we don’t use a “Calendar” period; we’ll use a “Financial” period.  The financial period  ends on the Sunday of the previous month.  This means that, for “March 2017,” the third period of the year will not end on March 31, but rather March 26.  This means that our “month” really started on February 27.  And the month of April will start on March 27.

There are some caveats; specifically you’ll never overlap years.  So January will always start on the 1st, and December will always end on the 31st – despite how  the Sunday falls.

I didn’t want to rely on maintaining a database table (with the individual years + the start/end of the financial period), so I have two SQL statements.

The “normal” approach (for most months) is to get the last Sunday of the previous month to determine the end; and add one day (Monday) to determine the start of the new period.  As previously mentioned, you have to watch out for a change in the year, or when you’re late in a month, and the start/end will differ.

As with all code I publish, there’s nothing company-sensitive here; this is just date manipulation:

 

Read the article

  • 03.09.2017
  • sql

SQL UNION query doesn’t work

I had a UNION-based SQL query that wasn’t working.   In addition to making sure that you have the same number of columns/fields in each query, try removing the ORDER BY clause.

I’ll have to Google this myself, but there’s a difference between UNION and UNION ALL …

Read the article

  • 02.22.2017
  • sql

Web Development for Intermediate Programmers – with PHP

I’ve written a ~300 page book on PHP!  While I do go over the basics, I go over “common” concepts of database manipulation and Ajax. I’ve also sprinkled in some User Interface and User Experience comments:

http://www.stevesohcot.com/php-book

Read the article

mysql errno: 150

I went to add a Foreign Key to my MySQL database, and got an “errno: 150” error.

The solution: apparently it’s case-sensitive.  My table name begins with a capital letter, but the SQL had everything in lowercase.  Once I matched the cases, it worked.

Remember to create/drop  tables in the order that you need for it to work!

Read the article

  • 01.03.2017
  • sql

incorrect search results / Search Results giving all possible values

I had a SQL query (with Classic ASP, although it could really have been for PHP or any scripting language), that was incorrectly showing ALL results, and not what I had specified.  I had a lot of fields to search, so the query was similar to:

qry = qry & ” WHERE “

qry = qry & ”                       table.field_one LIKE ‘%” & search &”%’ OR “

qry = qry & ”                       table.field_two LIKE ‘%” & search &”%’ OR “

qry = qry & ”                       table.field_three LIKE ‘%” & search &”%’ OR “

qry = qry & ”                       1=1 “

 

I just copied-and-pasted that line for each field.  I wanted the lines to be consistent, so they all ended with “OR”.  But I needed something at the end of the statement – I can’t have an “OR” hanging at the end.  So I put “1=1”.  But this was causing a problem – because 1=1 is always true, so it was returning ALL results from the  database.

The solution is to NOT have  a consistent line throughout the SQL, and just leave the final “OR” off.

Read the article

  • 12.20.2016
  • sql

SQL Server INSERT (append) statement doesn’t work

I had a typical INSERT statement in SQL that followed the format

INSERT INTO [table]

VALUES [values]

The problem was that I was inserting values from a SQL statement.  The “VALUES” clause is not needed:

INSERT INTO [table]

SELECT * FROM [other_table]

Read the article

SQL – UNION example

It’s more efficient to have one really complex SQL query vs many queries.

You can combine two SQL statements with a UNION clause.

SELECT field_one, field_two FROM table_one

UNION

SELECT field_one, field_two FROM table_two

The (2) queries must have the same number of fields, and be in the same order (ex. you can NOT have “field_one, field_two” in the first query and then “field_two, field_one” in the second query.

This becomes even more useful when you use this query in another query; a “subquery” if you will.

Suppose you have a database that has employee labor charges.  You have one table for when an employee is out sick, and you have a separate table for when an employee is on vacation.  You want to write a query that combines – and sums – all hours entered into the database.  Using the UNION example above, you could have:

SELECT employee_name, SUM(hours) As HoursAbsent FROM

(

SELECT employee_name, hours FROM tblSick

UNION

SELECT employee_name, hours FROM tblVacation

) as qryMain

GROUP BY employee_name

 

This will sum all hours by employee. Note that I’ve given the combined queries an alias of “qryMain”.  I’m in essence using that as a table to query off of (SUM, and GROUP BY).

More Subqueries

You can also link a table to a subquery.  Assuming you have a field “field_a” in a table “tblOne” – you can link to a query that you create, giving an alias for the table/field name you are linking on:

SELECT tblOne.field_one, qryOther.field_two FROM tblOne

INNER JOIN

(

SELECT blah as field_a, field_two FROM tblBlah

UNION

SELECT foo as field_a, field_two FROM tblFoo

) as qryOther

ON qryOther.field_a = tblOne.field_a

Read the article

  • 08.02.2016
  • sql

sql running totals

Might be specific for SQL Server:

SELECT
CONVERT(datetime, STR(MONTH(the_month)) + ‘/1/’ + STR(YEAR(the_month))) AS the_date,
COUNT(1) as TheCount,
SUM(COUNT(1)) over (order by the_month rows unbounded preceding) as cumulative_count
from dbo.MyTble
GROUP BY the_month
ORDER BY the_month

Afterward, I wanted to have a placeholder for months that didn’t have a value.  I can generate the SQL with my server-side language, and I’d just add in UNION queries; so I ended up with this:

 

SELECT the_month,
SUM (TheCount) AS TheCount ,
SUM(TheCount) over (order by the_month rows unbounded preceding) as cumulative_count
FROM (

SELECT
CONVERT(datetime, STR(MONTH(date_field_here)) + ‘/1/’ + STR(YEAR(date_field_here))) AS the_month,
COUNT(1) as TheCount
FROM dbo.MyTable
WHERE date_field_here IS NOT NULL
GROUP BY date_field_here

UNION SELECT CONVERT(datetime, ‘2016-01-01’) AS the_date, 0 as MyCount
UNION SELECT CONVERT(datetime, ‘2016-03-01’) AS the_date, 0 as MyCount

) as qryMain
GROUP BY the_month, TheCount
ORDER BY the_month

 

Read the article

sql server different query results when explicitly specifying the database name

Well, I feel silly!

A customer ran a report and questioned the data.  the report was based off of a complicated SQL statement that was querying from two separate tables.  As such, I needed to explicitly state the name of the database (and schema) when querying the tables.

So I tried to replicate the query, manually, and did come up with a different answer. I then simplified the query to the bare necessities.

It turns out that the two queries had two different date ranges: but the numbers [dates] were so similar, I didn’t notice.

So there was no reason that I would get different query results by specifying (or not) the database name.

Read the article

  • 03.22.2016
  • sql