Category Archives: SqlServer

Divide rows with date in SQL Server 2014

Questions: I have a problem with SQL. I have the following table: declare @t table (START_DATE datetime, END_DATE datetime, GROSS_SALES_PRICE decimal(10,2) ); insert into @t values (‘2014-08-06 00:00:00.000’, ‘2014-10-06 23:59:59.000’, 29.99), (‘2014-09-06 00:00:00.000’, ‘2014-09-09 23:59:59.000’, 32.99), (‘2014-09-10 00:00:00.000’, ‘2014-09-30 23:59:59.000’, 32.99), (‘2014-10-07 00:00:00.000’, ‘2049-12-31 23:59:59.000’, 34.99) I would like to separate the dates which overlaps.… Read More »

Implementing Holidays in an Employee Management System

Questions: I’m working on an Employee Management System where employees can Clock in/Clock Out (Mark daily attendance) Apply for Leaves i.e. Sick Generate Reports i.e. Thier daily check in/Check out time, Total Worked Hours etc. Now I have to introduce a way to deal with public holidays and employee absentees in these reports. I read… Read More »

how to download a database to local

Questions: As a part of my .net learning process I want to do some experiments like adding new functionality to a project which is not yet live, but I have given the access to the server and all. So how can I download the database to my local and do my on experiments in it… Read More »

Using ORDER BY on SELECT DISTINCT in TSQL

Questions: I am trying to retrieve a list of date strings ordered by date like this… SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate FROM dbo.ProviderProcessGeneratedDate ORDER BY GeneratedDate This orders by the varchar that I converted the dates to. example… 02/01/2008 02/15/2008 02/21/2007 02/23/2007 02/29/2008 I have worked around this using an inline query… SELECT… Read More »

Test-First development tool for SQL Server 2005?

Questions: For several years I have been using a testing tool called qmTest that allows me to do test-driven database development for some Firebird databases. I write a test for a new feature (table, trigger, stored procedure, etc.) until it fails, then modify the database until the test passes. If necessary, I do more work… Read More »

Using OpenID for website Authentication

Questions: I’d like to implement OpenID in a new application using ASP.NET 2.0 and SQL Server 2005. I chosen Twitter, Facebook and Google as potential OpenID providers. I’ve found the Twitter implementation in .NET and I was studying Google’s OpenID implementation, but I want to make sure that my design is (mostly!) flawless. Is my… Read More »

Pass an array as value in an ado.net DBParameter

Questions: The project I’m working on has a lot of IN-queries like: SELECT something, anotherthing FROM atable WHERE something IN (value1, value2, value3) This is an example of a query with 3 parameters in the IN-part but the same query could be executed with 1 or 2 or 5 or 10 or … parameters. The… Read More »