Get person's age at a given moment using T-SQL and read only access

I thought SQL Server's DATEDIFF was a neat function to calculate a person's age at a given moment. But I was wrong.

It's important that I get the correct age of a person at a given time comparing that time's date to the date of birth. I'm given read only access. This means I can't create a function, so I have to implement it in the query.

So far came up with CAST(DATEDIFF(day, dateOfBirthColumn, otherDateCoumn) / 365.25 AS Integer) AS Age. This gives me a pretty exact age, but not the correct age in some cases due to the fact that the division by 365.25 isn't actually correct.

Any suggestions on how to calculate the exact age between two dates?

I'm querying a SQL Server 2005 database. Maybe unneccesary to say, but why didn't the engineers at MS thought of this...?

Checking if data violates SQL decimal constraint

I have a table that is getting a column changed from a VARCHAR to DECIMAL(20,7) data type. I need find out what rows have a value in the column that violates the DECIMAL(20,7) data type and I believe I am close.

What I have so far is this:

SELECT * 
FROM tableName
WHERE 
     (ISNUMERIC(columnName)  = 0 and columnName IS NOT NULL)
     OR (columnName LIKE '%,%')
     OR (LEN(columnName) > 20)

I think I am mostly hung up on the the LEN check, as I can still miss results. Is there a way to get deeper into the length so I can check with precision, instead of just a length of 20?

SQL Server 2005 Backups

The place I work at right now has no backups. Each server supposedly has a snap shot every 20 minutes or so and replicated to a backup SAN.

I know I need backup for easy restore, etc, etc... but I would like to hear more reasons for having .bak files instead of the SAN admin telling me he can get me the .mdf and .ldf files. Give me some fire power.

I'm also testing SQL Safe for backup compression and speed for SQL Server 2005 so any information on a tool like that would help too.

How to calculate SQL server stored procedure execution time

I have a stored procedure. I want to check how much time it takes to execute. Please note that I don't have access to profiler. I am using SQL server 2005

I tried using the following batch query

SELECT CONVERT( VARCHAR(24), GETDATE(), 121)
EXEC cif_query_trans_by_card_Final2 '0060F423F28A6095AD070CEFFD7E81D245D7D0CD7B', '11223345'
SELECT CONVERT( VARCHAR(24), GETDATE(), 121)

But I am getting same value in both timestamp as 2014-12-30 18:09:52.653

Any help will be greatly appreciated.

Thanks in advance.

SQL max concurrent sessions per hour of day

I work in a healthcare call-centre environment. I have access to our core system's database and I want to use the data in it to determine the number of staff logged on each hour of the data in a given period that may expand over 24 hours (e.g. 24/12/2014 - 26/12/2014).

There is a userlog table that records when users log on and log off.

userlog table example

UserRef                                 Date                    LogType SessionID   
--------------------------------------- ----------------------- ------  -----------------------
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 14:11:25.763 LOGON   8D451569-0260-46BB-9B9E-F49B3E778161    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 18:11:32.547 LOGOFF  8D451569-0260-46BB-9B9E-F49B3E778161    
60738820-5F72-4E20-A070-57E07C83B6DE    2002-10-17 14:53:31.153 LOGON   C773894C-8B2D-4054-A550-3F04B4C5669F    
60738820-5F72-4E20-A070-57E07C83B6DE    2002-10-17 22:55:25.607 LOGOFF  C773894C-8B2D-4054-A550-3F04B4C5669F
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:26:40.123 LOGON   1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 15:51:28.590 LOGON   7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:58:05.217 LOGOFF  1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 15:58:31.013 LOGOFF  7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:58:32.733 LOGON   03F56AB8-FED5-4CC7-8445-26BF55F58E60    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 16:13:02.827 LOGOFF  03F56AB8-FED5-4CC7-8445-26BF55F58E60    

Desired results (this is not intended to reflect the above sample data):

Date         Hour    Number of users logged in
----         ----    -------------------------
01/12/2014    0                   0
01/12/2014    1                   0
01/12/2014    2                   0 
01/12/2014    3                   0
01/12/2014    4                   0
01/12/2014    5                   1
01/12/2014    6                   1
01/12/2014    7                   1
01/12/2014    8                   3
01/12/2014    9                   7
01/12/2014    10                  7
...                 
01/12/2014    23                  0

To be clear: I'm trying to get the hour to still display with a 0 count when there were no users logged in.

I guess what I'm looking for is the maximum concurrent sessions for each hour of the day, but I'm not overly technical or skilled in SQL (getting better bit by bit, though!) so I hope that terminology doesn't confuse things!

I've googled for this and found a few similar scenarios, but for Oracle and MySQL, or where the log table records the logon data differently. I'm sure I'll get to a point where I can successfully 'translate' other database query code to MS SQL, but I'm not there yet!

I am using: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86).

Thanks in advance for your help.

SQL Server GenRepeatingChars?

On my Sql Server Query i have found a query where there is a statement.

" dbo.GenRepeatingChars('0',8) AABBCCDD "

here AABBCCDD is may table column name. but What is the use of GenRepeatingChars? Please can any one brief me in details...

Calculating due date using business hours and holidays

I need to calculate due date / end date for SLAs. As input values I have the start date and a timespan (in minutes). This calculation needs to take into account business hours, weekends, and holidays.

I've seen a lot of examples where the input is start date and end date, but have been struggling finding anything similar to the above input values.

Is there an elegant solution to this problem? Is there a way to calculate due date without using a loop? I can't think of a way to do the calculation without doing something similar to the following terrible algorithm:

1. Create a return variable "due date" and set it to input variable "start date"


2. Create a control variable "used minutes" and set it to 0


3. Create a loop with the condition "used minutes" <= "input timespan"


4. Inside the loop, add a second to the "due date" return variable


5. Inside the loop, check if the second is within hours of operation (checking business hours, weekends, and holidays). If so, increment control variable "used minutes" by 1.


6. Upon exiting the loop, return variable "due date"

SUM(Value) Group By After PIVOT

Hi everybody (happy merry christmas)

I have the following query for Pivot, that works nice.

WITH PivotData AS 
( 
SELECT fechaHora, idWatimetro, semana, hora, minuto,dia,mes ,ano,valor
FROM   E_Registros_Watimetros 
) 
SELECT * 
FROM PivotData 
PIVOT(SUM(valor) FOR idWatimetro IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]) ) AS P order by fechaHora, semana,dia,mes;

But now, I must be able to get Sum(valor) for every month or days or weeks or Year, but I´m trying without success, any help will be nice

Thank you for your time.

SQL Server 2005 - WHERE NOT EXISTS and a complicated script inside of the NOT EXISTS

Script objective: get a list of numbers from a list where the numbers don't show up in another list.

The complications: the other list of numbers can only be obtained via a complicated script - and for some reason I'm getting no results when I KNOW there should be; since the first list will contain ALL the numbers and the second list of numbers will only contain some numbers; so I should be getting some results.

The script I have written (censored)

SELECT A.Number
FROM   sometable AS A
       INNER JOIN othertable AS B
               ON A.Data = B.Data
       INNER JOIN othertable2 AS C
               ON B.Data = C.Data
       INNER JOIN othertable3 AS D
               ON C.Data = D.Data
WHERE  D.Data = 'int'
       AND NOT EXISTS (SELECT DISTINCT A.Number
                       FROM   sometable AS C
                              anothertable AS B
                                      ON C.Data = B.Data
                              INNER JOIN anothertable AS E
                                      ON B.Data = E.Data
                              INNER JOIN anothertable AS A
                                      ON E.Data = A.Data
                              CROSS apply (SELECT DG.Data
                                           FROM   atable AS DG
                                           WHERE  B.Data = DG.Data) D
                       WHERE  D.Data IN ( 'int', 'int', 'int', 'int' )) 

If I run the part1 (before the non exist) it works fine

If I run part2 (the data within the non exist) it also works fine - with different, and less results (containing numbers from part1)

But together they don't. So I need to know how to do this if not exist is not what I need to use?

how to create pivot query for this result?

I got result from query:

TITAL   PCS RGH_CTS    VALUE 
SEND    10  49.8550    198742.94400
BID     15  76.7070      327461.76300
FINAL   19  88.5630    337954.15600

I want this output - How to solve this?

| TITAL     |    SEND      |    BID       |   FINAL      |
| PCS       |     10       |    15        |     19       |
| RGH_CTS   |   49.8550    |  76.7070     |   88.5630    |
| VALUE     | 198742.94400 | 327461.76300 | 337954.15600 |

Any body can solve this simple query?

How to update all tables in a SQL Server database using a Loop

I have a common column in all my tables in the database, and I need to update the value of this column through a loop in all my tables

I try by this code but nothing happened

DECLARE TBL_CURSOR CURSOR
FOR ( SELECT Name FROM Sysobjects WHERE Type='U' )

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '';

DECLARE @TblName NVARCHAR(MAX);

OPEN TBL_CURSOR

FETCH NEXT FROM TBL_CURSOR INTO @TblName

BEGIN

   SET @SQL = ' If not exists (select column_name from INFORMATION_SCHEMA.columns where  table_name = '+ @TblName +' and column_name = "CommonColumn") update ' + @TblName + ' set CommonColumn= 1 ';
  FETCH NEXT FROM TBL_CURSOR INTO @TblName
END

CLOSE TBL_CURSOR
DEALLOCATE TBL_CURSOR

EXEC (@SQL);

Anyone have a better idea or fixing up this code?

SP_ExecuteSQL;1

Having a major issues with this procedure called SP_ExecuteSQL;1. It keeps executing internally in sqlserver 2000 with high utilization of CPU. When i pass its SPID in DBCC inputbuffer it shows as internal. Is there any way of stopping it.

Database is in Transistion - SQL server

When someone from my team tried to take a Database offline, due to a blocking session the database went into transition state. The session is still present and Now I want to know what is the blocking session running while trying to keep database offline before killing it.

If i try with any dmvs or DBCC Input Buffer i'm getting the same error: Database is in Transition.

Please help me how to find what was the session running.

P.S It is a SQL 2005 database

How to get column name from table from 2 rows of same table having same value?

Id  BatchName      TblName           SubBatchName     Status
1   12032014    Tbl_12032014_raw2_1  raw2           Complete    
2   12032014    Tbl_12032014_raw2_2  raw2           Complete    
3   12182014    Tbl_12182014_raw3_1  raw3           Complete        
4   12182014    Tbl_12182014_raw3_2  raw3           InComplete      
5   12182014    Tbl_12182014_raw4_1  raw4           InComplete      
6   12182014    Tbl_12182014_raw4_2  raw4           InComplete      

i want output like below

BatchName                                          SubBatchname                                       
-------------------------------------------------- -----------
12032014                                           raw2_0     

If both tablename are having status complete then and then only SubBatchName Get select

Thanks

System.Data.sqlClient.sqlError: cannt open backup device ....operating system error2( error not found )

I am working with SQl server 2005 and I am trying to back up the database using SQL server management studio Express but I encountered this error

System.Data.sqlClient.sqlError: cannt open backup device 'E:........' operating system error2( error not found ).

Even if i try to back up the device on another directory like c fore example I still having the same error .

Please how can I solve this Error ?

Please Help me Since I am new to Sql server ......

Sql server 2008 R2 - single instance VS multiple instance on the same box [on hold]

We have got one single dedicated Sql Server 2008 R2 box running multiple named instances for different databases for different country per say for UK, France, Germany etc. Overall we have six databases running under their own individual instances in single box. And the box is having very limited memory (as always) 64 GB.

Facts:

1. All applications (multi-tenant) connecting database with the separate credentials using ADO.NET


2. All databases having their own SSIS packages, SPS etc. I mean, there is no common (shared) data across databases


3. There is no stringent security requirement


4. All instances are having their own disk. I mean, as of I assume, they don't compete for the same controller


5. All the instances are configured with min/max memory settings.(Not sure if it can be done for multiple databases within same instance)

We have been facing performance issues. Apart from exploring other options, one of the option we want to consider is to have all the databases under one instances. Is it a good practical idea? What could be pros/cons? What happens if one of the database is hammered, will it effect other databases in the same instance?

Please share your thoughts, experiences, links, blog, white paper... anything is highly appreciated.

Counting grouped combinations

I'm pretty sure I've done this before, but can't remember how I did it. What I need to do is get a sum of minutes (proc_duration) within a set time frame. So for the first field pictured below I want it to add all of the proc_durations where LN Child 65M BHP match. So if I have 2 120's and a 60 it would show a total of 300 if that makes sense and have it do that for all of the fields. Thank you in advance. SQL Server 2005.

select 

pct.clinic_id,
pct.service_id,
pct.program_id,
pct.protocol_id,
sum(pct.proc_duration)

from patient_clin_tran pct

where (pct.proc_chron between '2011-12-22' and '2014-12-22')

group by pct.clinic_id, pct.service_id, pct.program_id, pct.protocol_id, pct.proc_duration

Parse error in field in input file. Malformed input detected at character 0 using encoding 'UTF-8'

I used to generate a csv file by SQL Server 2005 procedure. This procedure includes a SecureFX commands that creates the CSV file.

After that, I used a .bat file to send the file through SFTP to the target system. The below command is the one that I used to move the file: sfxcl /move

This was working fine with me in SQL Server 2003. However, the problem occurs when I upgraded to SQL Server 2008 R2.

The file is being created and sent successfully, but the target system cannot process it. The below error is being displayed: Parse error in field in input file. Malformed input detected at character 0 using encoding 'UTF-8'

How to fix this problem?

I do appreciate your assistance!