Find How Long Items Open Using Date Columns

Mar 11, 2013

I have a sample view with some dates. How would you find the numbers of items open per month. Say between OpenDate and CloseDate I want to find how many were open for January, February,?

Here is a sample table with the data

Code:
CREATE TABLE [dbo].[TestDate](
[ItemTitle] [nvarchar](50) NULL,
[ItemAttachAssignDate] [date] NULL,
[ItemDetachConcludeDate] [date] NULL,
[Status] [nvarchar](50) NULL,
[FullName] [nvarchar](100) NULL,
[OpenDate] [date] NULL,
[CloseDate] [date] NULL
) ON [PRIMARY]
GO

Code:
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Done', 'Jeff Hunter ', '2013-02-18 00:00:00', '2013-02-19 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Tommy Johnson', '2013-01-22 00:00:00', '2013-01-28 00:00:00');

[Code] .....

View 2 Replies


ADVERTISEMENT

Find Items And Datediff From Current Date And Print

Apr 4, 2012

What I want to do is I have a table checks which looks like-:

Items--Waitarea--Datecreated
1-------A1-------2011-10-12
1-------A1-------2011-10-13
1-------A1-------2011-10-14
1-------A1-------2011-10-15
1-------A2-------2011-10-16
1-------A2-------2011-10-18
2-------A2-------2011-10-19
2-------A2-------2011-11-17
2-------A2-------2011-12-17
3-------A2-------2012-01-17
3-------A2-------2012-01-18
3-------A3-------2012-01-19
3-------A1-------2012-01-20
4-------A2-------2012-01-21
4-------A2-------2012-01-22
4-------A2-------2012-01-31

What i need to find is items and datediff from current date to created date with respect to wait area. You will get more clear with example

For Eg. For items 1 the wait area changes from A1 to A2 at
2011-10-16 and remains A2 so i need to find the date differeence from
today date to 2011-10-16

For item 2 that wait area remains same for entire period that is
A2 so datediff from today date to 2011-10-19(last created date of that item)

For item 3 that wait area A2 then it becomes A3 then becomes A1
so need datediff from today to 2012-01-20

For item 4 that wait area A2 remains for the entire period
so need datediff from today-2012-01-21

The expected output wanted
items---daycount
1-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-16
2-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-19
3-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-20
4-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-21

View 9 Replies View Related

Problem With CCommand::Open When Query Str Is Too Long.

Dec 20, 2006

I'm using OLE DB model to insert long texts into ntext field.My code looks like :==BOOL COleDBRowData::Execute(CString strSQL){if(!m_bIsOpen)return FALSE;CCommand<CNoAccessor, CNoRowset>rs;HRESULT hr = rs.Open(m_session, strSQL);if(FAILED(hr))return FALSE;rs.Close();return TRUE;}==When strSQL length is over 5127XX bytes, Open function fails. Is thefunction supposed to be so? Is there any suggestion that I can avoidthis failure? I'm new to OLE DB thing.What is better is if somebody can tell me SQL command to insert a wholetext file content into the field directly. The sql command that I'mcurrently using is==INSERT INTO MyTable(Idx, Value) Values(N'{index}', N'{loooooongtext}')==Please help this poor soul. Thanks.--Daewon YOON

View 1 Replies View Related

Open Cube To Browse Takes Very Long Time

Dec 28, 2006

I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.

When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).

Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:

Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.

and goes on like that....

View 13 Replies View Related

Why Is Taking So Long To Open/create/render The Reports For The First Time?

Jan 14, 2006

Hi,
 
Why is taking so long to open/create/render the reports for the first time? Is there any configuration to change this? I don€™t think this behavior is related to Report Execution or cache! I think there is something else going on! Thanks.

View 12 Replies View Related

SQL Server 2008 :: Find All Items That Call A Stored Procedure

Nov 2, 2015

We have a process that uses a stored procedure: sp_MysteryProcedure....

The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.

One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).

So my question is multi-part.....

1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?

View 9 Replies View Related

Query Help: Item Below Reorder Level-find All Items For Same Vendor

May 1, 2007

Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks

View 4 Replies View Related

Reporting Services :: Find Items With Type Name Search Condition

May 17, 2012

The following 

SearchCondition[] sc = {new SearchCondition() {                                               
Name = "TypeName"
,Values = new string[] {"Report"}         
,Condition = ConditionEnum.Equals
,ConditionSpecified = true
}};
catalogItems = ReportService2010.FindItems("/"
,BooleanOperatorEnum.And
,new Property[] {new Property(){Name = "Recursive",Value="True"}}
,sc
);

Returns the following error

System.Web.Services.Protocols.SoapException: The TypeName field has a value that is not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidElementException: The TypeName field has a value that is not valid.
at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
   at Microsoft.ReportingServices.WebServer.ReportingService2010.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)

The type appears to be correct. I've tried type of "Folder" and receive the same error.

View 5 Replies View Related

I Need To Find Out How Long My SQL Server Has Been Up.

Sep 1, 2000

I need to find out how long my SQL Server has been running and I believe that there is a way of obtaining this info by querying a system table (or executing a procedure).

Does anyone know about this?

Karl

View 2 Replies View Related

SQL Command To Change A Long Date To Short Date

Aug 25, 2006

   Is there a SQL command that will change a Date&Time string to just a Date?

View 3 Replies View Related

Move Columns Into Rows For Same Items?

Aug 28, 2014

I have a table in the following format:

Matter IDIndexDescription
00103-000486Litigation
00103-0004857Trial
00245-000156Deposition
00245-0001557Hearing

I need each matter in a single row with descriptions as columns:

Matter IDDescription_6Description_57
00103-00048LitigationTrial
00245-00015DepositionHearing

View 4 Replies View Related

Find People Names In Long Text

Feb 14, 2007

Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:
Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles in article table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)
Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...
Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?
Thanks once again for taking the time.
Cheers,
Tyro
 
 

View 4 Replies View Related

Reporting Services :: Sorting On Calculated Report Items Columns

Jun 21, 2015

I need to sort my tablix report where I have several calculated columns like:

=ReportItems!Textbox47.value+ReportItems!Textbox48.value..

Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube

View 3 Replies View Related

Delete Statement For A List Of Items With Multiple Columns Identifying Primary Key

Jul 7, 2006

I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?

View 2 Replies View Related

Separate A Long String And Put It Into Three Columns

Apr 17, 2015

How to separate a long string and put them into three columns.

For example string

Toronto|Ontario|Canada,Dallas|Texas|USA,New York|New York|USA,Windsor|Ontario|Canada

I have a table with 5 columns, 3 columns are City, State and Country.

I would like to separate them and put those into categories

Continent City State Country Added

Toronto Ontario Canada
Dallas Texas USA
New York New York USA

View 1 Replies View Related

Parse Long String To Columns

Dec 31, 2007



I have a column with varchar(2000) that contains events with time-stamps. The data looks something like this for a record:

03:14:46: abc 03:14:47: def 03:14:59: xyz 03:15:17: zzz

I would like to parse out each time-stamp and event to a separate columns, like

col1= 03:14:46: abc
col2= 03:14:47: def
col3= 03:14:59: xyz
col4= 03:15:17: zzz

The number of events are dynamic so number of time-stamps with events can be anything from 2-30 of them.

Anyone would suggest how I can resolve this?

Thanks.

View 5 Replies View Related

Find Open Transactions

Feb 16, 2007

Is there an easy way (SQL Query or enterprise manager) to see if there is an open transaction pending?

View 2 Replies View Related

Find And Rollback An Open Transaction

Oct 15, 2007

We have a locked table and all I need is a way to show all open trans, so I can roll teh offender back

View 1 Replies View Related

How To Convert Long Date Format To Short Date Format In Store Procedure.

Feb 1, 2008

E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.

View 1 Replies View Related

Power Pivot :: Count Of Items Between 2 Dates Of The Same Date Column?

Nov 5, 2015

I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015. 

I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.

CountofQ1:=COUNTAX(DATESBETWEEN(DumpLoad1[Start Date],[StrtDate],[EndDate]),DumpLoad1[Start Date])

View 4 Replies View Related

Long Number To Date

Jun 7, 2007

Hi,
I am trying to convert a date from long number to a date format. Can someone please help me.

Example:
Database shows the following:
TimeCreated
1181224346

I would like to see the following
TimeCreated
06-07-2007

Thanks,

View 4 Replies View Related

Transact SQL :: Check 2 Columns And Return Rows As Long As One Column Is Not Null

Oct 19, 2015

how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.

Example Data

create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),

[code]....

View 4 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID          StartDate
1            2014-01-01
1            2014-02-01
1            2014-05-01-------After Gap Restarted
1            2014-06-01
1            2014-09-01---------After last gap restarted
1            2014-10-01
1            2014-11-01
2            2014-01-01
2           2014-02-01
2            2014-03-01
2            2014-04-01
2            2014-05-01
2            2014-06-01
2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id             Startdate
1             2014-10-01
2             2014-01-01

View 4 Replies View Related

Cannot Find Server Or DNS Error When Using JAVASCRIPT Window.open With DNS Alias

Oct 15, 2007

Using SQL Server 2000 Reporting Services and want to open new IE window to link to a site.
We are using IE 6.0.

I am using the following Navigation "Jump to URL" in report:
javascript:void(window.open('http://www.microsoft.com/en/us/default.aspx'))

This works as expected if I am not using the DNS alias to reference Reporting Services. But if I use the DNS alias when referencing Reporting Services, then when I click the link in the report I get the "Cannot find server or DNS Error" in the IE window that opens. Everything else works correctly in the reports when I use the DNS alias.

Anyone have a clue why this is happening?

Thanks, DR

View 3 Replies View Related

Summing Invoice Items - The Multi-part Identifier Items.TAX Could Not Be Bound

Apr 17, 2007

Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record.  My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam  ALTER PROCEDURE [dbo].[UpdateInvoiceSummary]    @Invoice_ID intAS    DECLARE @Amount intBEGIN    SELECT     Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX    FROM         InvoiceItems AS items    GROUP BY Invoice_ID    HAVING      (Invoice_ID = @Invoice_ID)    Update Invoices SET Amount = items.Amount    WHERE Invoice_ID =@Invoice_IDEND

View 3 Replies View Related

Help- Search In Long Date Format 05/05/2008 14:51:03

May 5, 2008

hi need help search in long date format
i have table like this
need to search date + time + minute
no second !
only date + time + minute no second

SELECT *
FROM tb_tb
WHERE (dateinb = CONVERT(DATETIME, '2008-05-05 14:58, 102))

dateinb
---------------------------

05/05/2008 14:51:03
05/05/2008 14:51:03
05/05/2008 14:51:03
05/05/2008 14:51:03
05/05/2008 14:53:03
05/05/2008 14:53:03
05/05/2008 14:53:03
05/05/2008 14:53:03
05/05/2008 14:56:03
05/05/2008 14:56:03
05/05/2008 14:56:03
05/05/2008 14:56:03
05/05/2008 14:58:09
05/05/2008 14:58:09
05/05/2008 14:58:09
05/05/2008 14:58:09


TNX

View 4 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:

SetIdItemCode
1A
1B
24
28
26
310
312
410

[code]....

You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 Replies View Related

Connection.Open () ERROR !!! Unable To Find Stored Procedure Sp_sdidebug.

Feb 15, 2006

Hi all,
When I try to open a connection it gives me error "Stored Procedure sp_sdidebug not found"
Can anyone help me in this respect?
I was running the same application with the SQL Server 2005 only earlier when it was running.But now after I reinstalled VS.NET & SQL Server I am getting this error.What could be the problem?
 
Please help... I am badly stuck up.
 
PAM

View 3 Replies View Related

Table Columns Unable To Open

Mar 26, 2007

hi
i am unable to open table columns. kindly suggest. errcoe 233

reg
ramu

View 2 Replies View Related

Identity Columns And Date Columns On Transactional Replication

Sep 16, 2006

Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

View 1 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

Find The Newest Date Of 2 Date Fields

Aug 20, 2007

I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!

View 1 Replies View Related







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