Specific Date With In An Ordered Sequence
Mar 7, 2008
How would i find the 3rd date in a sequence of dates, that is ordered?
Example
Date1 = 08-01-01
Date2 = 08-02-01
Date3 = 08-03-01
Date4 = 08-04-01
i need to retrieve Date3
ADVERTISEMENT
Executing Packages In A Specific Sequence
Oct 10, 2006I am facing some issues while working on SSIS in VS2005,
The Scenario is we have 35 excel files from which we have to import data
to Sql 2005.
We have to execute these packages in a specific sequence so we are not
using For Each Loop Control.
Now the Problem is this that we have 35 executables as an output that is
corresponding to each package, and from each executable we can Change
excel path and Sql Connection String.
The problem is that one has to open each executable to set the path; we
wanted to keep them Configurable like , as if we can have any variable
define in the packages for path, and in the packages we use that variable
and append the XLS file name to it like ..
@PathVariable+ test.xls
And from the Configuration file at run time we can set the path to this
variable like
@Path Variable="C: /Windows/ Test Folder/"
And then executable picks the path from there.
Is there any possibility to implement any thing similar, we have tried but
its not working, any help in this regard will be really appreciating.
Its really urgent, I will wait for feedback on this.
Transact SQL :: Sequence Of Characters - Extract Some Specific Values
May 13, 2015I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
Using A Date In Where Clause, Without Hardcoing A Specific Date
Jun 20, 2006i want to limit the data being stored in a table to the past 6 months only.
data in the table is refreshed each night.
how do i do this without hard coding a date?
SQL Server 2012 :: Date Sequence Missing Values?
Apr 27, 2015Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'
-- test data
;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL
[code]....
Add Specific Date
Apr 16, 2008Hi all
How do i set a specific between date in here
i want to add this line or How do i set specific between date
WHERE between '06-06-2005' And '04-01-2007'
SELECT * FROM gradeaverage
WHERE Name='student'
ORDER BY Date
Thank advance
Get Date With Specific Hour
Jul 8, 2014from getdate(), how can i get today's date with specific time :
For example : (i just need specific 11oclock today)
2014-07-08 11:00:00.000
This do the trick :
select DATEADD( HH, 11, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))
But at second half of the day it changed from 2014-07-08 11:00:00.000 to the next day, i dont know why who controls the past controls the future, who controls the present controls the past. ¯(º_o)/¯ ~~~
Calculate Age From DOB Using Specific Date?
Mar 2, 2015I know how to calculate age from DOB using the current date, but how do I do it if I want to say show me the age as of Jan 1, 2015. Where the DOB is short date looks like:
1946-04-13
Get Timezone Specific Date
Aug 11, 2006
Hello,
I am struggling with one algo i.e. I want to retrieve the date according to time zone
I have table called TIMEZONe in which i have the information about the hours difference (+12 to -12) from GMT...
now my scenario is that I want to get the date, using information from timezone area table (time difference from GMT), of any particluar time zone
can any one tell me, how will I able to achieve this task.
for example:
it 4:00 AM in the mornign in GMT TIME and date is 11-08-2006 but in US it 11:00 PM and date is 10-08-2006
Note: Server date is set to GMT (System date)
regards,
Anas
Search For A Specific Date When Specified As String?
May 18, 2007Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
Counting Content Files For A Specific Date Range
Apr 10, 2008I'm trying to find a way to count the number of times a ContentFile, appears in a ContentLog, for a specified period of time.
SELECT DISTINCT ContentFile FROM ContentLog
Shows a list the list of content files.
I'm not sure though how to count for a specific ContentFile, or how to count for on a specific date range.
Thanks for any help you can give.
Transact SQL :: Update Date With Specific Hour / Minute And Second
May 7, 2015How I can update the following date to get the desired result with specific hour, minute and second
2014-01-01 00:00:00.000
Desired Result
2014-01-01 17:20:20.000
How To Get The Week Number Of A Specific Date In SQL Server 2005
Jan 10, 2008Hi,
Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005.
Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008.
So, the same way I need to know the week number of a given (variable) date.
I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date.
regards,
Saibal
Note : Please feel free to write me if my question is not clear.
SQL Server 2008 :: How To Load SSIS Package For Any Specific Date
May 18, 2015I have a ssis package which runs daily. This ssis package has couple of execute sql tasks which load data for yesterday's transaction. Ex.
INSERT INTO Shipped (Div_Code, shipment_value, ship_l_id, shipped_qty, shipped_date, whse_code,
ord_id, ship_id, ship_l_ord_l_id, Created_date) select ord.DIV_CODE as div_code, ship.SHIPMENT_VALUE as shipment_value, ship_l.SHIP_L_ID as ship_l_id, ship_l.SHIPPED_QTY as shipped_qty, ship.SHIPPED_DATE as shipped_date, ship.WHSE_CODE as whse_code, ord.ORD_ID as ord_id, ship.SHIP_ID as ship_id, ship_l.ord_l_id as ship_l_ord_l_id, Getdate() as Created_date from SHIP ship, ORD ord, SHIP_L ship_l where ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))) and ship.WHSE_CODE='WPP' and ord.ORD_ID=ship.ORD_ID and ship.SHIP_ID=ship_l.SHIP_ID
All execute sql task has query like above query. and in some query we have date filter which loads data for yesterday. Ex. one query has ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). some other query has ord.trans_date=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). this package runs daily through sql server job, so It loads data for yesterday. Now If i want to run for any particular date, How could we achieve from ssis?
SQL Server 2008 :: Load Data For Yesterday And Specific Date
May 31, 2015I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load for any specific date, package should be set for yesterday's date dynamically. How to achieve this as I am new to SSIS.
View 2 Replies View RelatedSQL Server 2008 :: How To Track Modification Date On Specific Table
Jul 13, 2015I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
Power Pivot :: Get Order First Date For A Specific Product And Customer?
Nov 18, 2015I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
Use Script To Read Date From Specific Line Of Flat File
May 10, 2007I have a few flat files that I need to read a date from. The date will always be on Line No 4 and will be in YYYYMMDD string format in chars 2-9 of line 4
Here's what I have so far to convert the string YYYYMMDD into date format... I don't know enough VB to be able to just hit Line 4... I am sure it's simple so could someone pelase put me out of my googling misery
Code Snippet
Public Sub Main()
'
Dim oStream As New IO.StreamReader("Z:TreasuryFilesdeals.dat")
Dim strReadLine As String, filedate As Date
'??? how to just read a date from the fourth line of the file ??
'strReadLine = oStream.ReadLine()
strReadLine = "A20050331" 'sample string variable
'convert the fourth line of the flat file to a date format
filedate = New DateTime( _
CInt(Strings.Mid(strReadLine, 2, 4)), _
CInt(Strings.Mid(strReadLine, 6, 2)), _
CInt(Strings.Mid(strReadLine, 8, 2)))
'write filedate to package variable
Dts.Variables.Item("User::Load_Date_Loan").Value = filedate
System.Windows.Forms.MessageBox.Show(CStr(Dts.Variables.Item("User::Load_Date_Loan").Value))
'
Dts.TaskResult = Dts.Results.Success
End Sub
Power Pivot :: First Sales Date By Customer For Specific Product Only
Dec 21, 2012I have a sales order table that has customer, sales, product and sales date. Â I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer
Product
Sales Date
Sales
First Bike Date
Jon
Bike
12/4/2011
 $10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Â Can I use function to complete? Â I have a datedim table as well related to the sales date.
Power Pivot :: Total For Specific Date Range As Well As JTD Totals
Jul 8, 2015In the typical Job Cost Reporting world you can easily create SQL Views / Stored Procedures (or create a report) that can return both Job To Date values along with period values for another date range. Something like this:
Job,
Phase of Work,
Cost Type,
UM,
Budgeted Cost,
Budgeted Quantity,
Period Cost,
Period Quantity,
Period Unit Cost,
JTD Cost,
JTD Quanity,
JTD Unit Cost,
Variance between JTD and Period Cost,
Variance between JTD and Period Quantity,
Variance between JTD and Period Unit Cost
But in the tabular BI world I am not certain how to have these values show up in a pivot table or BI visualization. My current Fact table is essentially every job cost transaction so it is at the most granular level holding Company, Job, Phase of Work, UM, Cost Type, Date, Budget Cost, Budget Quantity, Actual Cost, Actual Quantity. The Budget values only have a value if the entry was updating the budget and the Actual values hold actual cost for each transaction. If you don't restrict by date you have your Budgeted Quantity / Cost and Job To date Cost / Quantity.
I can generate a Pivot table or chart and not restrict by date and I can get all the Budget Totals and the Job To Date Totals when I have the Company, Job, Phase Of Work, Cost Type.
But what if I want to look at a range like last quarter or yesterday or last week. I can indeed filter that data in Pivot Table and get the period values, but now I don't know how to have the Job To Date values show up for comparison purposes.
I am guessing maybe a DAX formula (if using Excel Powerpivot or SQL Tabular Model), but I am at a loss.
So in the BI world how do you get Job To Date totals while also viewing the data for a period of time?
Here are some example graphs we would want to show for a single Job. Let's say we want to show the 10 worst performing cost distributions.Â
I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Cost, Period Cost, Job To Date Cost.I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Unit / Cost, Period Unit / Cost, Job To Date Unit / Cost.I would want a graph to show the cost distribution and the variance between JTD and Budget and Period and Budget.
As you can see it all stems around seeing JTD and Budget values vs Period Values. Here is a visualization that may work. I am using PivotTable filtered on one Company, Job, Phase of Work. I can see the sum of actual cost which is the totals for the date range I provided. But I still want to see the JTD totals for that Phase of Work and Cost Types and actually the Budget Totals as that is sort of a JTD, but for budget... especially if I want to compare Budget vs JTD vs Actual.
SQL Server 2008 :: Query To Delete All Backup Files Older Than A Specific Date?
Jul 29, 2015I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
Getting Items That Were Ordered Alone
Jun 25, 2007Hello Experts. You may have more luck at this than me.
I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.
Example of how data looks like
I am looking for transactions like orderid 3 and 5.
OrderID
Product
1
hotdog
1
burger
1
taco
2
burrito
2
snack
2
chips
3
burger
4
hotdog
4
burger
4
taco
5
burrito
6
snack
6
chips
When i run
SELECT product, count(orderid)
From Table
Where BusinessDateID = 20060725
group by product
having (count(orderid)=1)
I only get back items that were only sold once.
I am looking for a result that looks like this
Product
Ordered alone
hotdog
2
burger
3
taco
4
burrito
32
snack
12
chips
76
SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date
Mar 5, 2015I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
BCP Export With Ordered Data
Sep 13, 2000I need to export data in a specific order (e.g. the order in which the data was inserted into the table). My BCP was doing this then just started exporting in random order. I know that SQL 7 doesn't guarantee return order unless specified by an ORDER BY clause. However, that's not possible with BCP unless you use the command line BCP with the QUERYOUT option. That's my problem. I'm using the BulkCopy object in DMO and I don't see an option to order the data or use a query with an order by statement. Any suggestions?
--Buddy
Returning Ordered Result Set, Except First Row
Jan 31, 2008Hi all,
I feel like I'm missing something really simple here...
I'm trying to write an sp to return a list of countries alphabetically to populate a web drop-down list in a form. However, since most people using the form will be from USA, I want "USA" to appear as the first row, then the rest should be alphabetical, e.g. ("USA", "Afghanistan", "Albania"... "Zimbabwe")
I'm using a UNION query, but it's ordering the result set so that USA appears alphabetically, not as the first row. I'm not using an ORDER BY clause.
Here's the code I'm using:
CREATE PROCEDURE GetCountries AS
SELECT Country_Name
FROM Countries
WHERE Country_Name = 'USA'
UNION
SELECT Country_Name
FROM Countries
WHERE Country_Name <> 'USA'
GO
I also tried selecting into a temp table and doing a UNION that way, but got the same results.
How To Use BETWEEN With Custom-ordered Values
Jul 23, 2005We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)How do I return values between M000011501 and M000011601 for example?
View 3 Replies View RelatedOrdered Update Problems
Jan 18, 2008I€™m trying to follow examples from: http://www.sqlmag.com/Articles/ArticleID/49240/49240.html?Ad=1 for an update statement that needs to run in a specified order.
Here€™s my problem:
I have a table:
TopicActivity
PK €“ DayNum int (this is a date in YYYYDDD format)
PK €“ TopicId int
Visits int
LifetimeVisits int
LifetimeVisits is a new column that I want to calculate the value using the previous Visits column data.
Sample Data:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 0
2008002 2 1 0
2008002 1 3 0
2008003 1 10 0
I want the end result to look like this:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 5
2008002 2 1 1
2008002 1 3 8
2008003 1 10 18
Here is the query I€™ve been trying:
WITH ordered AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY TopicId ORDER BY DayNum) AS RowNum
FROM TopicActivity
)
update ordered set
ordered.LifetimeVisits = ordered.Visits +
isnull((select o2.LifetimeVisits
from ordered as o2
where o2.TopicId = ordered.TopicId
and o2.RowNum = (ordered.RowNum - 1)),0)
The update doesn€™t seem to be happening in order as LifetimeVisits seems to have random results every time I run the query. (Usually the first couple of rows are right. I€™m dealing with thousands of records) I can€™t use a cursor as the query takes way to long.
I'm running SQL Server 2005.
Reporting Services :: SSRS 2008 R2 - Create A Single Vertical Stripline At Specific Date Value Along X-axis?
Jun 5, 2012I'm struggling with trying to display a single vertical stripline at a date value along my x-axis.Â
I'm using the following non-default settings within the stripline editor:
bordercolor = tomato
borderstyle = solid
borderwidth  = 1pt
intervaloffset =Â Fields!uidate.Value.ToOADate()
where uidate is a calculated field in my dataset that always has the value of 05/29/2012  (using formatdatetime("05/29/2012",dateformat.ShortDate))
Everything else is set to Auto, with stripwidth=0
I'm trying to display a single vertical line at the x-axis value of 05/29/2012
I have previously tried setting the interval offset to "05/29/2012" but that didn't work. I've also tried setting the value of a hidden text box on the report to "05/29/2012" and setting the interval offset to the value of that text box, but that gave me a "text box not declared" error.
How I can simply display a vertical stripline on my line graph at the x-axis value of 05/29/2012 ?
How Can It Automatically Generating A Ordered Number
Jul 5, 2006hi,
i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.
as asp.net is new for me, i would have some simple problems to ask.
1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me
2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.
thanks
Rgds, universe
Aggregate And Group By - Sum Of Items Ordered In One Row
Jan 16, 2014Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:
Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
from orders o, customers c, Employees e, Shippers s, [Order Details] od
where o.CustomerID = c.CustomerID
[Code] ....
Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
Is Bookmark In A Nonclustered Index Ordered?
Jul 23, 2005When a nonunique nonclustered index is built on top of a clusteredindex, is it guaranteed that the bookmark in the nonclustered indexwill be kept in the same order as the clustered index?Here's an example to demonstrate my question:CREATE TABLE indextest (col1 int NOT NULL,col2 int NOT NULL,col3int,col4 int)ALTER TABLE indextest ADD PRIMARY KEY CLUSTERED (col1,col2)CREATE INDEX ix_indextest ON indextest (col1,col3)GOINSERT indextest VALUES (1,2,1,1)INSERT indextest VALUES (1,3,2,1)INSERT indextest VALUES (1,4,2,1)INSERT indextest VALUES (2,1,1,1)INSERT indextest VALUES (1,1,1,1)SELECT col1,col2 FROM indextest WHERE col1=1 AND col3=1DROP TABLE indextestThe select statement above is covered by the nonclustered index, sothat index is used. However, the nonclustered index is defined only toensure the ordering of col1 and col3 within the index; col1 and col2follow within the index as the bookmark to the clustered index. When Irun this query, my desired result is to have the records appear in theorder supported by the clustered index:1,11,2As it happens, the result I got was indeed in that order, but I don'tknow if it was mere coincidence, or if the bookmark in the nonclusteredindex is maintained in the same order as the clustered index. If Iwant to ensure the above order, is it sufficient to have thenonclustered index defined as above, or do I need to define it as:create index ix_indextest on indextest (col1,col3,col2)just to be sure that the results are returned in ascending order forcol1,col2? If the two-column index is sufficient, is it guaranteed tostill be sufficient in SQL2005 and future versions of SQL Server, or amI better off adding the third column just to be safe?Thank you,--Dennis Culley
View 4 Replies View RelatedCan A DELETE's OUTPUT Rowset Be Ordered?
Oct 19, 2007I'd like the "OUTPUT DELETED.* INTO target table" resulting from a DELETE to occur in the same order as the clustering key of the target table. Is this possible?
View 6 Replies View RelatedReturn Set Ordered By Physical Order Of Rows?
Nov 5, 2012I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.
Specifically, something like:
Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"
I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.
(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).