SQL Server 2014 :: How To Find 5th Working Day In A Month

Oct 29, 2013

I would like to know how to find fifth workingday* of a given month and year.

workingday - working day is just any day apart from saturday and sunday. no need to consider any other holidays.

ex: I would just give month number and year as input and i would like to know date and day of the fifth working day.

Input : 10/2013
Output : 7th October 2013, Monday.

View 4 Replies


ADVERTISEMENT

SQL Server 2014 :: First Day Of Month Value

Nov 3, 2015

I have a table as below.

Timestamp SourceIdChannelIdValue
2015-11-01 16:45:59.95311223,4923
2015-11-01 16:46:00.12712224,9357 *
2015-11-01 16:46:00.32713227,4183 *
2015-11-01 16:46:00.52714221,025 *
2015-11-01 13:17:14.17711223,0304 *

I only want the first value per channel in a new table ( the values marked with *)

I tried with sql below without succes.

set nocount on;
declare @today date;
set @today = CURRENT_TIMESTAMP;
Select * into new_table from (
select @today as 'Now', dateadd(day, -(day(@today)) + 1, @today) as 'FirstOfMonth, Timestamp, SourceId,

[Code] ....

View 5 Replies View Related

SQL Server 2014 :: Order By Month Number In A Group By

Dec 19, 2014

Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,

[code]...

How can I get the result orderd by the month number?

View 3 Replies View Related

SQL Server Admin 2014 :: Transactional Log Backup With Frequency Of 1 Month

Jan 6, 2015

I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .

And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .

So should I need to take any other precautions or concerns with this approach.?

View 9 Replies View Related

SQL Server 2014 :: Convert 6 Digit Letter To Month And Year

Feb 27, 2015

How to convert 6 digit number to mm/yyyy.

View 5 Replies View Related

Find Difference In Month And Year In Sql Server 2005

Jun 17, 2008

--find day,month,year
--for day select datediff(d,'01 may 2008',getdate())
 -- --for month select datediff(m,'01 jun 2006',getdate())
-- --for year select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
 thanks

View 3 Replies View Related

SQL Server 2008 :: How To Find If A Database Has Been Accessed / Used Since Last Month

Jul 6, 2015

Is there anyway I can find or make a report if a databases has been accessed/used since last month?

View 4 Replies View Related

SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?

Dec 12, 2013

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);

[code]....

I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'

SELECT T.[Year],
T.[Month]

-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)

[code]....

What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.

Something like this :

+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----

View 3 Replies View Related

SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?

Jun 7, 2015

How do I find sales trend of an employee via comparing current month and previous month sales?

I got so far query upto following,

;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,

[Code]....

I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

View 3 Replies View Related

SQL Server 2014 :: Find Count For Two Tables And Put Into Other Table

Jun 18, 2015

I have two table in two different database. I have to found count for both table and put into other table.

example : Database --> A , table is T1
Database --> B , table is T2

I want count of both table and put into T3 table in database A or B .

View 7 Replies View Related

SQL Server 2014 :: Powershell Script To Find Windows Server Reboot Date

Oct 16, 2015

I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table).

View 6 Replies View Related

SQL Server Admin 2014 :: Find Report Server Database Name?

May 8, 2015

Is there any way to find Report server database name using T-SQL?

I have SQL server report server instance and authentication credentials.

I can use sys.databases but in case of user had changed ReportDb name at the time of configuration this approach will not work then How I can find?

View 2 Replies View Related

SQL Server Admin 2014 :: Find All The Inactive Windows Logins?

Jan 30, 2014

How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?

View 9 Replies View Related

SQL Server 2014 :: Find Duplicate Rows Like Same Entries More Than One Time?

Sep 11, 2014

i have a table like below

create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)

values like

1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22

I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?

View 3 Replies View Related

SQL Server 2014 :: Find Multiple Entries In Two Fields One Table?

Sep 12, 2014

I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:

SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;

This is a snap shot of the output:

ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS

[Code] ....

What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?

View 3 Replies View Related

SQL Server 2014 :: Find Double Records Within DateTime Range

Apr 13, 2015

I have a query question.

Consider a table with the following structure:
RecordID (PK - int) - RecordDate (DateTime)

I need to find all records that fall within a 7 day period slot based on the first RecordDate of a specific slot.

Example, consider the following records:
RecordID - RecordDate

1 - 2015-04-01 14:00
2 - 2015-04-03 15:00
3 - 2015-04-03 16:05
4 - 2015-04-03 19:23
5 - 2015-04-06 09:15
6 - 2015-04-06 11:30
7 - 2015-04-07 12:00
8 - 2015-04-09 15:15

The result of the query I'd like should look something like this

1
2
5
7
8

So basically I'd like to leave record 3 and 4 out because they fall within 24 hours of record 2 and I'd like to leave record 6 out because it falls within 24 hours of record 5.I'd tried working with a CTE and set a dateadd(d, 1, recorddate), join it on itself and use a between From / To filter on the join but that didn't work. I don't think NTILE will work with this?

View 9 Replies View Related

SQL Server 2014 :: Find Equal And Opposite Rows In A Table

May 25, 2015

How to find the equal and opposite rows in a table.

E.g.:

book position
A 500
B -500
C -500

The output should return the rows having equal and opposite positions.

O/P:
A 500
B -500

I have tried self join on a table but in vain.Do we need to cursors for these row level handling?

View 6 Replies View Related

SQL Server Admin 2014 :: Stored Procedure - Find The Cause For Blocking

Sep 23, 2015

I was trying to create stored proc

[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,

[Code] ....

But the query was continuously blocking the query below

updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0

I was not sure, why the create procedure statement is blocking the update statement.

View 1 Replies View Related

SQL Server Admin 2014 :: How To Find Memory Usage By Index

Oct 4, 2015

I want to create a lot of index for my database for performance.

But I need find memory usage by indexes.

How to find memory usage by index in sql server?

View 1 Replies View Related

SQL Server 2014 :: Find Table In A Linked Server?

Feb 2, 2015

I have multiple linked servers and trying to find if a table (approximate table name) exists in one of the linked servers.

I have a code to find it in local DB....how can I modify to include all linked servers as well:

ALTER PROCEDURE [dbo].[FindTable]
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)

[code]....

View 3 Replies View Related

SQL Server 2014 :: Find Names Of Customers Who Have Purchased Academic Books?

Nov 10, 2013

1. I need to find the names of the customers who have purchased academic books. (Coding required as Subquery NOT as Join)

2. Here, I need to show a list of authors who have written books and list the books they have written.

Sort the list by last name

(Coding required as Subquery)

View 4 Replies View Related

SQL Server Admin 2014 :: Where To Find Availability Group Fail Over Occurrences

Nov 14, 2014

Where can I find dates and times to when an availability group was moved outside of the SQL error log?

View 1 Replies View Related

SQL Server Admin 2014 :: How To Find Used Space In Memory Optimized Filegroup

Jun 11, 2015

How do i find Total allocated space and used space of a memory optimized filegroup?

use memory_optimized_db
Go
select (SUM(size)*8.0)/1024.0 as Space,
FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files
group by data_space_id,type_desc;

above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.

View 0 Replies View Related

SQL Server 2014 :: Find Common Link Between Two Tables In Database Without Key Constraints

Jul 16, 2015

Any tool, script, procedure, or otherwise that will solve this problem?

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )

CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

View 7 Replies View Related

SQL Server Admin 2014 :: Replication Could Not Find Or Execute Stored Procedures

Jul 20, 2015

Replication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.

Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.

Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.

Looks like these sp's got deleted?

View 1 Replies View Related

SQL Server 2014 :: Find Out Last Executed Date For Any Stored Procedure In Database

Oct 6, 2015

Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.

View 2 Replies View Related

SQL Server Admin 2014 :: Script To Find Nested Views In A Database

Oct 7, 2015

Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...

View 1 Replies View Related

SQL Server 2014 :: Find Views Which Has More Than 16 Columns For Unique Index / Constraint

Oct 27, 2015

We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...

View 1 Replies View Related

SQL Server 2014 :: Can't Find Data Flow Task In SSDT 2012

Nov 5, 2015

I Can't find the Data Flow Task in SSDT 2012.

Has it been replaced by another task or do I need to do something to make it visible?

Edit: It popped up under my favorite tasks.

[URL]

View 2 Replies View Related

Find The Last Month

Sep 11, 2007

Hi,

I have a table shows 12 month and the status of each month if it's closed of not, how to find the last month closed in a SQL Staement, the file looks like this:-


year 1 2 3 4 5 6 7 8 9 10 11 12
2007 T T T T T T T T F F F F


Best Regards

View 19 Replies View Related

Select Employees By Start Day For 8 Month Working

Jul 15, 2013

I have this column Sdate in my Employees table. This value represent the start date working.

I need to get all of the employees that between 8 and 10 month of work from today. How can I do it?

View 3 Replies View Related

Help With Modifying Working Query (group By Month)

May 28, 2008

Hi,

I have the following query which is working fine, but its returning a row for each day.

I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?

Any help is much appreciated !!

thanks once again,
mike123






CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]

(
@numDays int
)

AS SET NOCOUNT ON


SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique

FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC


table structure below


CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

View 1 Replies View Related

Calculating Number Of Working Days In A Month

Sep 4, 2007

Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.

can anyone say the solution?

thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved