MS SQL Server: central database and foreign keys

I’m am currently developing one project of many to come which will be using its own database and also data from a central database. Example: the database “accountancy” with all accountancy package specific tables. the database “personelladministration” with its specific tables But we also use data which is general and will be used in all […]

SQL – Can I calculate a new column based on another new one?

I’m creating a query in which there is a new column calculated similar to the following: CASE WHEN DATEDIFF(day,lastpurchase,getdate())< 31 AND turnover >= 3000 THEN ‘500’ WHEN DATEDIFF(day,lastpurchase,getdate())< 31 AND turnover < 3000 THEN ‘200’ ELSE ‘0’ END as OfferAmountEuro, This works fine. I now want to create another calculated field on the same table […]

SQL Update master table with new table data hourly based on no match on Composite PK

Using SQL Server 2008 I have an SSIS task that downloads a CSV file from FTP and renames the file every hour. After that I’m doing a bulk insert of the data into a new table called NEWFTPDATA. The data in this file is for the current day up to the current hour. The table […]

SQL: How can I order by a specific range of ID numbers, then time?

This code is very likely poorly written. It’s my first foray into SQL – I’m taking on a project that isn’t exactly my job so I’m trying to learn it as I go. My two big problems right now: I need to separate ID numbers over a certain number. For our instances lets just say […]

Update SQL Server Table from Access Table containing Date/Time Data Type

I’m having an issue transferring some existing data from an Access Back-End Table to a SQL Server 2012 Back-End table. I’m up sizing our database, and since our Access database relies on tables from our ERP software which is also on SQL Server, it makes sense to run all queries on SQL server and just […]

which one is better:Filter on join clause or Filter on where clause when joining two table?

I am trying to join 2 or more tables on MS SQL Server. All the tables have IsActive field which determines the active record. (IsActive = 1 means active record and IsActive = 0 means inactive record or record has been deleted from system) So I have two conditions for joining the two or more […]

How to calculate difference between two datetime fields (yyyy-mm-dd hh:mm:ss) in time format (hh:mm:ss) in sql server?

I have 2 columns in my table in sql server – [Occurrence Time (NT)] and [Clearance Time(NT)] having the format yyyy-mm-dd hh:mm:ss and a third column [Outage Duration] with the format hh:mm:ss [Identifier] | [Occurrence Time(NT)] | [Clearance Time(NT)] | [Outage Duration] 4 | 2014-12-28 15:06:33.000 | 2014-12-28 15:18:18.000 | 00:11:45.000 Outage duration is calculated […]

Is there a more efficient way to update the sort order of an entire table?

I’m using client side drag and drop sort ordering for a table of data. The sort order is persisted in the database as a simple integer value. The schema looks like the following: Id | Title | SortOrder 1 | A | 0 2 | B | 1 3 | C | 2 Data can […]

generating past months and joining unrelated data

In MySQL or MSSQL I need to generate a result set that will return the qheader grouped by month for the last three months. So I have the questionsec table schema +———–+ |questionsec| +———–+ |qid (PK) | |qheader | +———–+ After (select * from questionsec) I get: +——-+——+ |qid| qheader | +—|———-+ | 1 | […]

Inserting into table1 for all rows in table2

I have five groups in Group table as Group1, Group2, Group3, Group4, Group5 and whenever a new role added into Role table I have to make an entry into GROUP_ROLES table as below declare @gid int, @rid int select @gid1 = id from Group where name = ‘Group1’ select @gid2 = id from Group where […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.