T-SQL (SS2K8) :: Finding Gaps In Dates

Mar 25, 2014

I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:

CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)

INSERT INTO #SessionTest
SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL
SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL
SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL
SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL
SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL

[code]....

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Finding Gaps Within Date Ranges

Sep 13, 2013

I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.

Example dataset SQL below:

CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'

[Code] ....

I would expect a dataset to be returned consisting of:

1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013

I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...

View 9 Replies View Related

Need SQL For Finding Gaps Comparing Two Sets Of Pe

Feb 27, 2008

Hi all,

I have two tables - Planning and Appointments:

Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.

Id,
TypeId - the type of the planned items
BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item

In the Planning table we can have as many records per day as we need:

1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00

Appointments - contanis a list with appointments

Id,
BeginTime DateTime
EndTime DateTime

1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30

What is needed?

What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:

Free planned time:

1, 1 Jan 2008 09:30, 1 Jan 2008 10:00
2, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00

So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.

I've tried several aproaches, but I always faced performance problems.

Thanks in advance.

View 1 Replies View Related

Need SQL For Finding Gaps Comparing Two Sets Of Periods

Feb 22, 2008

Hi all,
I have two tables - Planning and Appointments:

Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.
Id,
TypeId - the type of the planned items

BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item

In the Planning table we can have as many records per day as we need:

1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00

Appointments - contanis a list with appointments
Id,

BeginTime DateTime
EndTime DateTime


1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30

What is needed?
What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:

Free planned time:
1, 1 Jan 2008 09:30, 1 Jan 2008 10:002, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00

So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.
I've tried several aproaches, but I always faced performance problems.

Thanks in advance.

View 1 Replies View Related

Transact SQL :: Finding Gaps And Filled With Last Validate Data?

Aug 26, 2015

currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions.  I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:

I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:

The data for testing are:

DECLARE @PublicationsByUser AS TABLE
(
  Id   INT,
  PublicationId  INT,
  MetricId       INT,
  ProviderId     INT,
  DateCreated    DATE,
  Amount         FLOAT

[code]....

View 14 Replies View Related

Report Containing Sequential Dates (removing Date Gaps)

Jul 23, 2005

I have a table containing typed log entries. One log entry is supposedto be created every twelve hours, but sometimes there are gaps. I needto create a report showing the time of entry, and the actual log entry.I can't just list the contents of the log table, because if I do thatthere will be dates missing. Instead, when there isn't a log entry fora date, I need to print the date, and then just leave the log entryblank.The SQL bellows shows what the output should look like. HOWEVER, thecode below makes use of a temp table containing all possible dates. Myquestion is, is there a better way to do this - one that doesn'tinvolve the temp table? Thanks in advance.create table StationLog (LogDate datetime, LogText char(11))insert StationLog values ('1/1/2005 00:00:00','entry one')insert StationLog values ('1/1/2005 12:00:00','entry two')insert StationLog values ('1/2/2005 00:00:00','entry three')insert StationLog values ('1/3/2005 00:00:00','entry four')create table Date_List (TempDate datetime)insert Date_List values ('1/1/2005 00:00:00')insert Date_List values ('1/1/2005 12:00:00')insert Date_List values ('1/2/2005 00:00:00')insert Date_List values ('1/2/2005 12:00:00')insert Date_List values ('1/3/2005 00:00:00')insert Date_List values ('1/3/2005 12:00:00')select TempDate, LogTextfrom Date_Listleft outer join StationLog on Date_List.TempDate = StationLog.LogDatedrop table StationLogdrop table Date_List

View 6 Replies View Related

T-SQL (SS2K8) :: Count Of Consecutive Years Of Participation (islands And Gaps)

Jun 29, 2015

I have a data set (snippet below) and I need to count the number of consecutive years based on a date in time for each ID as represented below.

ID DATE
------ --------
1 2000-05-03
1 2001-06-10
1 2002-04-02
1 2005-07-29
1 2010-12-15
4 2001-05-07
4 1999-08-01
4 2000-07-05
4 2001-08-01
9 2002-05-01
9 2000-04-02

My result set needs to be something like:

ID Count of Consecutive Years
------- -----------------------------
1 2
4 2
9 0

I know this is a gaps and islands type problem but nothing I have been able to find is working once I attempt modification so that it can fit my dataset. Please note that I am going to use the data return to populate another table that is currently being populated using a cursor that utilizes an insert statement based on different codes.

View 9 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Finding Dates

Mar 18, 2008

Any suggestions on how I would find the 2nd and 4th tuesday of every month?

View 5 Replies View Related

Finding Dates Within 15 Day Timespan

Sep 7, 2014

I have a table that contains activity dates. If a certain activity occurs 3 times within fifteen days, I need to flag them that account.What is the best approach?

View 1 Replies View Related

Finding Missing Dates For Each EmpIDs

Jun 6, 2008

Friends

I'm using Sql Server 2005, in which I've table like this

USE [Sample]
GO
/****** Object: Table [dbo].[Table1] Script Date: 06/07/2008 03:10:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpID] [int] NULL
) ON [PRIMARY]


INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)

My task is I want to find Missing Dates (Except Saturday, Sunday)
for each Employee.

Note: Missing Dates should be Working Days only (Excluding Saturday & Sunday)

Help me out

Thanks
Rajesh N.

View 6 Replies View Related

Excluding Weekends When Finding The (dd/hh/mm/ss) Between Two Dates

Mar 27, 2008

I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.



Any help would be greatly appreciated.



Thanks

JP

View 6 Replies View Related

Help With Dates-finding Number Of Days Per Month

Mar 28, 2000

I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.

A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.

Thanks in advance

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Non Valid Dates With A Char Field

Aug 9, 2007

I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below

select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1

This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Any way around this?

View 2 Replies View Related

Finding Differences Between Dates In A Single Colu

Feb 28, 2008

I have a log table that displays the history of the status of an entity and the date the status changed.

member_id | modification_date | status

I wish to find the differences between datetime values in different rows of the modification_date column. I would need the amount of time between whatever the next sequential time was for the member. I would also need to know the status change in that time. I don't know how to do this with just one date column.

Any assistance is appreciated.

Thank you.

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Maximum Value Out Of 5 Different Columns?

Jun 2, 2011

How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:

Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A

I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.

View 9 Replies View Related

T-SQL (SS2K8) :: Finding Total Execution Time?

Oct 30, 2014

I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.

i planned to test it as follows

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

----- for the SP that is before optimize

declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'

View 9 Replies View Related

T-SQL (SS2K8) :: Finding Rows From CSV Column With CSV Parameter

Nov 2, 2015

I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.

I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema

CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Get Particular Day Between Two Dates?

Aug 8, 2014

I want to list Dates for particular day between two dates.

for eg. if user enters 08/01/2014 and 08/31/2014 with value 0 then all the Sundays and related dates should be display.

if above contamination with 1 then all Mondays,

View 9 Replies View Related

T-SQL (SS2K8) :: Finding Stored Procedure Defaults For Parameters

Jun 2, 2014

I have various ways of getting the parameters of a stored procedure:

I have a procedure that has all defaults 4 are null and 2 are 0.

The following shows most of what I need but no defaults

SELECT PARAMETER_NAME ,
ORDINAL_POSITION ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH ,
NUMERIC_PRECISION ,

[Code] ...

This one has two values:

PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)
sp_procedure_params_rowset proc procedure

Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Out Missing Segments From Source Dataset

Oct 23, 2014

I have 2 tables, #MainSample and #SampleCode

In the #MainSample, Line_ID is the Primary key

Line_ID BegMeasure EndMeasure

656 0.00 254500.00
657 0.00 7000.00
658 0.00 308000.00
659 0.00 20000.00

#Sample Code

Line_ID BegMeasure EndMeasure Code

659 665.00 9456.00 APL-XL
657 0.00 200 BHP

From this, I have to find out the segments for which there is no defined code value. I want the output as

Line_ID BegMeasure EndMeasure

656 0.00 254500.00
657 200.00 7000.00
658 0.00 308000.00
659 0.00 665.00
659 9456.00 20000.00

How to achieve this?

View 0 Replies View Related

T-SQL (SS2K8) :: Finding The Item Filled In Prior To Current One

May 13, 2015

I have data similar to the below

CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))

[Code] ....

Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.

However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.

I have done this:

SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ

And it seems to work. Is this the easiest way to do it or am i missing something?

View 3 Replies View Related

T-SQL (SS2K8) :: Finding Last Records Inserted Into All Tables In A Database

Jul 27, 2015

I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.

View 4 Replies View Related

T-SQL (SS2K8) :: Display Dates For A Given Value?

Mar 4, 2015

I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:

Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14

I want to display the following:

Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20

Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Get MIN / MAX Dates Per Grouping

Jul 8, 2015

I have a table with Employee, it lists their department, and has a row for each "position/promotion". (Just the pertinent fields below in the example shown of course and only a single employee for example)

An employee may have a single row in a department, or if they were promoted from line staff to manager they could have many.

Additionally, an employee may move around departments during their employment.

I need to get the min and max dates (basically duration) of their time at each department.

The issue is because they repeated departments when I go to group it loses movement between departments.

Raw data:

Employee DepartmentId StartDt EndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2001-04-30
999 7 2001-05-01 2005-06-30
999 7 2005-07-01 2006-09-30
999 5 2006-10-01 2009-10-31
999 5 2009-11-01 2012-01-01

Result of doing MIN/MAX:

Employee DepartmentId MinStartDt MaxEndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2012-01-01
999 7 2001-05-01 2006-09-30

However, Ideally I would get 3 rows (Department 5, then the stay at Department 7, then back to Department 5 again).

Employee DepartmentId MinStartDt MaxEndDt
----------- ------------ ---------- ----------
999 5 2000-01-01 2001-04-30
999 7 2001-05-01 2006-09-30
999 5 2006-10-01 2012-01-01

I played with RANK and ROW_NUM hoping I could get it to group / partition on each department and repeat the number for each department so the first department 5 would be RowNum=1, then the next group of Dept 7 would be =2 and the last group of Dept 5 would = 3 and then I could min/max on that grouping, but I didn't have any luck with that approach either.

My table:
CREATE TABLE [dbo].[EmployeeDepartmentHistory](
[Employee] [int] NOT NULL,
[DepartmentId] [int] NOT NULL,
[StartDt] [date] NOT NULL,
[EndDt] [date] NOT NULL
) ON [PRIMARY]

[Code] .....

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Previous Even Numbered Month And Appropriate Year From Given Date

Mar 25, 2014

I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.

Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013

View 2 Replies View Related

T-SQL (SS2K8) :: Finding Rows Where User Has Access To Contents But Not Record

Mar 27, 2014

I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )

Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.

Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.

I thought I could do this with a CTE, but I'm having a bit of difficulty..

Here's the code:

CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e

[Code] ....

View 1 Replies View Related

T-SQL (SS2K8) :: Finding 5 Most Recent Records For Each Customer With Abnormal Order Amounts

Nov 5, 2014

The database consists of the following tables:

create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)

Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.

Possible result: [URL] ......

View 7 Replies View Related

T-SQL (SS2K8) :: Finding Tree Structure - Show All Upward And Downward Nodes

Jun 3, 2015

I have the following table:

SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL

[Code] .....

I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output

View 3 Replies View Related

T-SQL (SS2K8) :: Retrieve Dates For Last 7 Days

Mar 26, 2014

The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7

However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..

I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6

View 5 Replies View Related







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