This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it
Scenario.
I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.
To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.
SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry
Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.
How do I get this working - can't seem to get my head around how I can compare the 2 dates ?
I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?
Hi,I need to compare Todays Date with a Date in the Table (DateExpired)to see if the membership expired. How do I do that? thanks SELECT DateExpired FROM Member
I am new to SQL and I have a problem to deal with dates. I have a field called LogDate (smalldatetime) and default value getdate(). The date is added immediately when a visitor visits a page of my site.
The problem is that I cannot retrieve the no of the visitors for today using getdate(). I use the following query but I am sure that something missing.
SELECT Count(*) As DailyVisitors FROM stats WHERE LogDate = CONVERT(varchar(10), GETDATE(), 103)
Hi, I wanted to display the createDate provided by the asp membership table. my where clause is this .. WHERE CreateDate = GetDate() I have create an account today but when i run my select statement with this where clause, the result doesnt show up. It seem like they compare the time as well. How can i make the GetDate to compare only the dates but not the time?
I need to compare several dates in a single record and determine the maximum value. Not sure how to do this in TSQL. Something like this (though I obviously can't use the MAX function because):
SELECT MAX(datefield1,datefield2,datefield3) as latestchange,jobnum FROM table1
Anyone have a function that will do this? Or is there one built in?
Well, I am venturing into new territory for me. I'm very illiterate when it comes to SQL Server and so I need assistance. I have the beginnings of my stored procedure, which is supposed to compare two dates/times and If they are not equal I need to kick off a DTS Package.
So, here's what I have so far (it returns two dates like I would expect):
CREATE PROCEDURE usrCompareDataDownload AS BEGIN SELECT MAX(ASP_ZZ_CHNG_TMST) FROM tbl_MaterialWeeklyData; SELECT MAX(ZZ_CHNG_TMST) FROM TV_ASP_DPUL_WKLY;
I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate
id startdate enddate rmd 1 10-oct-15 16-oct-15 11-oct-15 2 11-oct-15 14-oct-15 11-oct-15 3 09-oct-15 11-oct-15 10-oct-15 4 07-oct-15 08-ot-15 07-oct-15 5 if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3
I need to pull dates from a DB2 database via TSQL (Linked server - IBM DB2 for i IBMDASQL OLE DB Provider) and compare it to today for a less than or greater than type comparison.
Database: DB2, Customer information housed here Columns: UTOFMM - Month (2 character, numeric) UTOFDD - Day (2 character, numeric) UTOFYY - Year (2 character, numeric. Problem: years from 2000 to 2009 are stored as 0, 1, 2, ... etc) UTOFCV - Century Value (2 char, numeric. 0 = before 2000, 1 = in or after 2000)
I need to concatenate the date to be "sql" friendly, and then compare to today's date. It's to find any customer with date values in the fields above, and then differentiate between dates before today and after today.Here is the snippet of what I'm trying to fix. This portion of a nightly job is just checking for <u>any</u> value in the UTOFMM column of the current record.
Add Customer ID Update [responder].[Temp_RX_CUSTOMERS] set CustomerID = lf.UTCSID from [responder].[Temp_RX_CUSTOMERS] LEFT Outer Join [HTEDTA].[THOR].[HTEDTA].UT210AP lf ON [responder].[Temp_RX_CUSTOMERS].LocationID = lf.UTLCID where lf.UTOFMM = 0 GO
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
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,
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.
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth How can I get the keepmonth? can I use parameters ?
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.
Hello, I am writing a SP, inside the SP i have a select statement. I also have another SP, when i run it, it sends a list back with all the users that are currently online. Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.
This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online): SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId
This is my second SP, in witch i want to get the online users data: SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers)) Is it possible to exec a SP into a select statement?
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are: PersonSkills: id, ProductID, Skills
Product: id, ProductName
Ive been reading and playing around with this without success.
I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.
The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B
'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table B
I have two tables in the same DB, one named “Client” one named “Case”.
-One column inside Client is “ClientID”, another is “ClientCode”.
-One column inside Case is “CaseID” and another is “ClientCode”, which corresponds to the ClientCode column in the previous table.
I need a file with output that looks like this CaseID+ClientID.
So, an example row from the “Client” table” could be ClientID = 123, ClientCode=999. An example row from the “Case table” could be CaseID=555, ClientCode=999. So, I need output of 555123 .
Scenario: 2 sets of data Set 1 (200 records): firstname, lastname, address, city, phone Set 2 (100 records): firstname, lastname, address, city, phone
I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers
However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.
select xx.firstname, xx.lastname, yy.Firstname, yy.Lastname, xx.address, yy.address, xx.city, yy.city, yy.phone from set2 xx, set1 yy where substring(soundex(xx.Firstname),0,3)=substring(soundex(yy.Firstname),0,3) and substring(soundex(xx.lastname),0,4)=substring(soundex(yy.Lastname),0,4) and substring(xx.address,1,7)= substring(yy.address,1,7) and xx.city=yy.city;
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees 1 Dave 2 Peter 3 John
Site 1 Mon John Tue Peter Wed John Thu John Fri Peter
Site 2 Mon Dave Tue Dave Wed Dave Thu Dave Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).
Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,
If I know that the final trade is:
15 $10
And I have the following trades in my set:
TradeId Qty Price 1 10 $10 2 7 $20 3 5 $10
I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.
In the real situation, the number of trades that I need to check is not set to a specific number.
Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...
Here is a sample SQL code (table and data) to work with.
