I know how to get the last run date on all the databases on one serveri have a lot of servers and i was wanting a way of getting the last run date across all servers.
View 3 RepliesI want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
Is this even possible?
I am trying to reconcile two different databases. Each database holds customer information and some of this information is represented as drop down tables. Here is how it is linked
Structure
Tables Fields
Attribute names Name, AttID, AttValue
Attribute Values AttID, AttValue, CustID, CustCode
Customer DBID, DBCode,CustID, CustCode, FName, LName
Database 1 Sample
Attribute name Attribute Values Customer
Color 8, 0 8, 1, 1234, 6 892, 14, 1234, 6, John, Doe
red 8, 1 8, 2, 1234, 6
blue 8,2
Database 2 Sample
Attribute name Attribute Values Customer
Color 5, 0 5, 3, 1234, 6 892, 14, 7434, 5, John, Doe
red 5, 3 5, 4, 1234, 6
blue 5,4
Within a database, the ATTID and Attcode link the attribute and attribute value tables and they link to the Customer table with CustID and Cust Code. One Customer may have multiple entries within the Attribute value table
The Customer tables in each database are linked by the DBID and DBCode values. The other ID's are not necessarily the same across databases.
The object is to compile a list of Customers where these attribute values vary between databases. There are approximately 56,000 Customers in the customer table and 710,000 entries in the attribute values table.
My attempts to do this have produced cross joins with millions of values returned
have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.
Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.Also how will tempdb behave in this scenario.
I have migrated over 700 databases to another server and now I have to add a specific user to all these databases and sync , looking for script to add this user at once to all these databases.
View 9 Replies View RelatedI need a script that will return the mdf & ldf for multiple databases.
I am currently running...
sp_helpdb 'TestDataname'
...and copying the size of the mdf and ldf into an excel spreadsheet.
How can I get the mdf AND ldf file size for all of the databases in an instance? I need the MDF and LDF seperated and I want the actual size of the file as it appears on the file system.
automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:
1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent.
2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT
3) Run the DDL code below.
4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes.
5) Connect to an availability group listener and call CREATE DATABASE :)
I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.
The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.
* AlwaysOn Self-Population Script
* By: Steve Gray / steve@mostlyharmful.net
* Usage: Free, but buy me a beer if you're ever in Brisbane.
**/
USE [master]
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
[code]....
Secondary server is offline due to a hardware issue and the log files are growing on Primary sever for availability databases. The log drive is running out of space. How can I stop the log growth for the primary databases?
View 4 Replies View RelatedLooking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?
View 6 Replies View RelatedI need procedure query for two date between fromdate and todate
alter procdure date
declare @Fromdate varchar(10)
declare @todate varchar(10)
as begin
select * from master
where convert(varchar(10),@Fromdate,121) between CONVERT(varchar(10),@Todate,121) and CONVERT(varchar(10),rr.Original_Date,121)
end
I am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all
[Code] ....
It is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'
I would like changing the date range to be -1 year from today until today
This is the current WHERE, AND
WHERE [entry].[Posting Date] >= DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0)
AND [entry].[Posting Date] < DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)
In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?
In short, how can I convert a 100 year date to Gregorian (any format)?
I am in need of converting serial date to regular date ie...735510.40461 and only need the hours, minutes and seconds, I have used the examples I've seen on different forums,
DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))
I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this
declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)
But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.
I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.
E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp
I need to handle date format "41981".
View 9 Replies View RelatedI have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.
View 5 Replies View RelatedI would like to know how I can make a script in SQL Server you can determine the difference days with bills based on date.
I show you an example:
Invoice Date Difference Day Amount
0001 1/1/2015 0 500.00
0003 18/1/2015 17 600.00
0008 2/2/2015 15 300.00
It is with DATEDIFF, but I'm stuck with logic.
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:
SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod
[Code] ....
This gives me the following results:
IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131
Now what I need is something that looks like this:
LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135
[Code] .....
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.
I have a question regarding windowing functions. I have a sales order table with the columns "orderid", "customerid", "order_date" and "amount". I use the following query to get the amount of every customer as a additional column:
Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid)
FROM sales_orders
My question is if there is a good way to add another column, which includes the SUM(amount) of the customerid, where the order_date > 2012-01-15 , something like this:
Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid),
SUM(amount) OVER (PARTITION BY customerid WHERE order_date > 2012-01-15)
FROM sales_orders
I know, this is not a valid method, so do you know a way to achieve this? Can I maybe use CROSS APPLY or something like this? I know that I could use a subquery to get this, but is there maybe a way / a better way via window functions?
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.
<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime
[code]....
I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?
View 5 Replies View RelatedSee sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year.
SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL
SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL
SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL
SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL
SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL
[Code] .....
-- expected result
SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL
SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL
[code].....
I need to create a table which holds date information for our financial year.
I have all the dates between now and 2045 and the start of the week and the end of the week. What I also have is the first sunday of the previous week in the spreadsheet too.
Please see below attachment
What I need to autofill once I import these three dates into a database is the week and the month.
The difficulty surrounding the month is that, we start a new month on the FIRST Sunday of the month.
So dates 07/04/14 to 04/05/2014 would be month 1.
Month 2 would begin on 05/05/2014 as it is the day after the first Sunday of the month, and so on....Month 5 would start on the 04/08/14.
Need to script something that would automatically calculate the week and month for me on the basis on above, if I have the start date, end date and 1st sunday already in a table?
Given this table;
DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),
[code]....
How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?
HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL
[code]....
I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.
I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies View Related