Ugh!
I need to create some SQL that will generate all the dates for a recurring meeting on multiple days per week.
For example:
a meeting on Monday , Wednesday and Friday at 10:00 am - 10:30 am from December 1st 2004 to Feb 28th 2005.
I'm not sure what to do here.
Any Ideas?
Regards
Russ
Hi
I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.
I also need to adjust rows, say mth and nth that meet condition , say y.
My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)
This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.
Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:
ID
Int1
Type1
Ideal Rank by Type
1
1
1
1
2
1
1
2
3
2
1
3
4
3
1
4
5
5
1
5
6
8
2
1
7
13
1
6
8
21
1
7
9
34
1
8
10
55
2
2
11
89
1
9
12
144
1
10
13
233
1
11
14
377
1
12
15
610
1
13
16
987
2
3
17
1597
1
14
18
2584
1
15
19
4181
1
16
20
6765
1
17
The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...
I hope this posting is clear, albeit long.
Thanks in advance
Greg
PS The code:
USE
master
GO
CREATE DATABASE CertainRowsToChange
GO
USE CertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE PROCEDURE Initialise
AS
BEGIN
INSERT INTO InitialisedValues (Int1 )
SELECT 2
INSERT INTO InitialisedValues (Int1 )
SELECT 4
INSERT INTO InitialisedValues (Int1 )
SELECT 8
INSERT INTO InitialisedValues (Int1 )
SELECT 16
END
GO
EXEC Initialise
/*=======================================================*/
CREATE TABLE AllRows
(
AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE TABLE RowsToChange
(
RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE PROCEDURE InitialiseRowsToChange
AS
BEGIN
INSERT INTO RowsToChange (Int1 )
SELECT 2
INSERT INTO RowsToChange (Int1 )
SELECT 4
INSERT INTO RowsToChange (Int1 )
SELECT 8
INSERT INTO RowsToChange (Int1 )
SELECT 16
END
GO
EXEC InitialiseRowsToChange
GO
CREATE PROCEDURE PopulateAllRows
AS
BEGIN
INSERT INTO AllRows (Int1 )
SELECT 1
INSERT INTO AllRows (Int1 )
SELECT 1
INSERT INTO AllRows (Int1 )
SELECT 2
INSERT INTO AllRows (Int1 )
SELECT 3
INSERT INTO AllRows (Int1 )
SELECT 5
INSERT INTO AllRows (Int1 )
SELECT 8
INSERT INTO AllRows (Int1 )
SELECT 13
INSERT INTO AllRows (Int1 )
SELECT 21
INSERT INTO AllRows (Int1 )
SELECT 34
INSERT INTO AllRows (Int1 )
SELECT 55
INSERT INTO AllRows (Int1 )
SELECT 89
INSERT INTO AllRows (Int1 )
SELECT 144
INSERT INTO AllRows (Int1 )
SELECT 233
INSERT INTO AllRows (Int1 )
SELECT 377
INSERT INTO AllRows (Int1 )
SELECT 610
INSERT INTO AllRows (Int1 )
SELECT 987
INSERT INTO AllRows (Int1 )
SELECT 1597
INSERT INTO AllRows (Int1 )
SELECT 2584
INSERT INTO AllRows (Int1 )
SELECT 4181
INSERT INTO AllRows (Int1 )
SELECT 6765
END
GO
EXEC PopulateAllRows
GO
SELECT * FROM AllRows
GO
WITH Temp(OrigID)
AS
(
SELECT OrigID FROM
(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable
INNER JOIN
RowsToChange AS ToTable
ON FromTable.RowScore = ToTable.Int1
)
UPDATE AllRows
SET Int1=1000
FROM
Temp as InTable
JOIN Allrows as OutTable
ON Intable.OrigID = OutTable.AllRowsID
GO
SELECT * FROM AllRows
GO
USE
master
GO
CREATE DATABASE ComplexCertainRowsToChange
GO
USE ComplexCertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL
)
GO
CREATE PROCEDURE Initialise
AS
BEGIN
INSERT INTO InitialisedValues (Int1 )
SELECT 2
INSERT INTO InitialisedValues (Int1 )
SELECT 4
INSERT INTO InitialisedValues (Int1 )
SELECT 8
INSERT INTO InitialisedValues (Int1 )
SELECT 16
END
GO
EXEC Initialise
/*=======================================================*/
CREATE TABLE AllRows
(
AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL,
Type1 int NOT NULL
)
GO
CREATE TABLE RowsToChange
(
RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,
Int1 int NOT NULL,
Type1 int NOT NULL
)
GO
CREATE PROCEDURE InitialiseRowsToChange
AS
BEGIN
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 2, 1
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 4, 1
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 8, 1
INSERT INTO RowsToChange (Int1,Type1 )
SELECT 16, 1
END
GO
EXEC InitialiseRowsToChange
GO
CREATE PROCEDURE PopulateAllRows
AS
BEGIN
INSERT INTO AllRows (Int1, Type1 )
SELECT 1, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 1, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 2, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 3, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 5, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 8, 2
INSERT INTO AllRows (Int1, Type1 )
SELECT 13, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 21, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 34, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 55, 2
INSERT INTO AllRows (Int1, Type1 )
SELECT 89, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 144, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 233, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 377, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 610, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 987, 2
INSERT INTO AllRows (Int1, Type1 )
SELECT 1597, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 2584, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 4181, 1
INSERT INTO AllRows (Int1, Type1 )
SELECT 6765, 1
END
GO
EXEC PopulateAllRows
GO
SELECT * FROM AllRows
GO
WITH Temp(OrigID)
AS
(
SELECT OrigID FROM
(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable
INNER JOIN
RowsToChange AS ToTable
ON FromTable.RowScore = ToTable.Int1
)
UPDATE AllRows
SET Int1=1000
FROM
Temp as InTable
JOIN Allrows as OutTable
ON Intable.OrigID = OutTable.AllRowsID
GO
SELECT * FROM AllRows
GO
Here’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
RegionalID pbxID userID
0 1012 17
0 543 2
0 10961 6
0 16499 26
0 14061 36
0 15882 2
4 15101 6
4 15101 26
6 16499 2
6 16012 26
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXIDDailupDT DongleAccessNum CLI RegionalID RegionalDialup
838/8/2006 8:58:11 AM T2 UQ 28924 013249370000800003554
5438/8/2006 8:55:44 AM T0 UA 33902 012362350000800003554
12198/8/2006 8:59:03 AM T3 ZD 02031 015295809500800003554
10128/8/2006 9:02:54 AM T0 UA 41261 017301105000800003554
13318/8/2006 8:59:57 AM T0 UA 01938 012460462700800003554
19798/8/2006 9:02:52 AM T0 UA 09836 016375121000800003554
19038/8/2006 8:58:41 AM T0 UA 26009 014717535600800003554
15228/8/2006 8:58:54 AM T3 MB 94595 057391287100800004249
3198/8/2006 8:51:28 AM T2 ZD 32892 054337510000800004249
32708/8/2006 9:04:26 AM T2 MB 8733100800004249
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeIDRegionalID ExtName SubsNDCDLocCDUpdateStatusRegionDesc
79731 PRETORIA 0123620NORTH EASTERN REGION
79741 HARTEBEESHOEK 012 30120NORTH EASTERN REGION
79751 HARTEBEESHOEK 01230130NORTH EASTERN REGION
79761 PRETORIA 01730140NORTH EASTERN REGION
79771 PRETORIA 01230150NORTH EASTERN REGION
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaIDRegionalIDDongleAreaCodeAreaDescUpdateStatus
121UAOumashoop0
131UBPietersburg0
141UCWarmbad01
151UDNylstroom0
161UEPotgietersrus0
271UFLouis Trichardt0
281UGMessina0
291UHEllisras0
301UIThabazimbi0
311UJPhalaborwa0
321UKTzaneen0
331UTStanderton0
341UMMeyerton0
351UNNelspruit0
361UOWitrivier0
371UPLydenburg0
381UQMiddelburg0
391URWitbank0
401USBronkhorstspruit0
461UZOlifantsfontein0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialupRegionUpdateStatusRegionCodeLocalRegion
10800003554North Eastern010
20800005027Gauteng020
30800006194Eastern030
40800004249Central040
50800201859Southern050
60800201989Western060
70800113515HO101
80800222204Tellumat070
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
Guys can you help please...
At the moment I have an unknwon number of ID's and need their related info in another table (1,2,3,4,5 for example) and I'm looping and merging to get them as one table:
For i = 1 to however many...
Dataset = Select where ID = array(i)
FinalDataset.Merge(Dataset)
Next
How can I write a stored proceedure or something where the loop is happening at the database end, to avoid going back and forth?
I was thinking dynamically generating the sql statement:
Select * from table where ID = 1 or ID =2 or ID =3......
But this could become huge if you had a couple of hundred to do...there must be a more efficient way to do it...
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
Here's my problem:
I have a date that is entered into the field, 12/17/2004. My client wants the site set up so that when after the date is entered, it automatically updates itself every week. So the date represents an event, and he wants to update the event date automatically instead of having a user login and update the date manually. So 12.17/2004 shoudl turn into 12/24/2004, and then 12/31/2004 and so forth.
One brilliant guy told me that if you entered a start date of and an end date then some how that might me be a little easier.
Any thoughts good people?
I have a situation where a common table within multiple databases across multiple servers is randomly getting corrupt. The database schema is identical for each database. There are approx. 200 copies of the database on each server. I am seeing one or two errors per week. There doesn't seem to be any similarities between the databases that are effected... It is always the same table that gets corrupted. The mulitple servers are using a SAN but since it is different databases / common table I have a hard time believing it is hardware related.
The error that the application throws is:
Microsoft OLE DB Provider for ODBC Drivers (-2147467259) [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find the index entry for RID '16b700000000000000030002' in index page (1:5366), index ID 0, database 'DISOPT'.
When running a DBCC on the table the following is returned:
Database DISOPT consistency errors in table TF_FILE_METADATA_VALUES fixed no data loss reported.
DBCC results for 'TF_FILE_METADATA_VALUES'.
The error has been repaired.
Clustered index successfully restored for object 'dbo.TF_FILE_METADATA_VALUES' in database 'MUTGEN'.
There are 277 rows in 2 pages for object 'TF_FILE_METADATA_VALUES'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
This corrects the problem and the app functions as normal. The errors are random across databases but ALWAYS the same table.
Any thoughts?
Is it possible to display when a scheduled job is next due to run ineither a view or a table?If so, how?thanksBryan
View 3 Replies View RelatedHi!
My problem/"goal to achieve" is the following..
Users can insert orders in a db-table. The date on which they've been created is stored.
I want the server to insert a new record in another table (accounts) for every order which is older than 2 weeks.
How should I do that?
With a custom action which checks all orders (a customer must press some button every day).
Or a static "update-class" with a kind of timer which does the action every 24 hours?
Of course an automatic solution will be the best! Another question is: how can i ensure that an instance of this "update-class" will never die?
Thanks so far...
Hi,
I have been asked to add a feature to some software I have written which needs to send reminder emails.
The reminders can have a recurrence, for example it might recur every 3 days or every 2 weeks. The reminders also have a start and end date. I have created a sql table with these fields:
Reminder ID
RecurrenceType (e.g. Daily, Weekly, Monthly)
RecurrenceAmount (e.g. 1, 2 etc.)
StartDate
EndDate
ReminderText
AddedBy
DateTimeStamp
I now need to write a stored procedure that will run every morning and send the reminders. I have no problem with sending emails from sql, the trouble I am having is I cannot figure out how to get the recurrences for the current day.
Hope this makes sense!
Sorry there is no code sample, I have been thinking about this one for a couple of days but I still don't know where to begin!
If anyone could offer any help it would be greatly appreciated!
Thanks
Jimbo
I downloaded VisualBasic 2005 Express Edition recently, and the installation went flawlessly, but when I tried to download the optional component SQL server express, the installation failed. When two subsequent installations failed, I decided I'd get along without it, until such time as I discovered I *needed* it. Well, I'm here, and the installation is still failing. I've tried both installing it though the VBstudio setup, and downloading it separately, but both fail at roughly the same point. The error from the standalone installation is more descriptive:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup Failed to compile the Managed Object Format (MOF) file c:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof. To proceed, see "Troubleshooting an Installation of SQL Server 2005" or "How to: View SQL Server 2005 Setup Log Files" in SQL Server 2005 Setup Help documentation.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29513&EvtType=sqlca%5csqlmofca.cpp%40Do_sqlMofcomp%40Do_sqlMofcomp%40x80041002
The link leads to an apology - they have no information on this subject. Of the help files, the troubleshooting page contains nothing (noticeably) relevant, and the log files have nothing obvious either, at least as far as I could tell... but then, I have no idea, which is why I'm here
We have some SSIS packages that are running on a recurring SQL Job. They work and play fine. They ran for about three iterations and then decided to stop....they don't report errors, they just don't do anything. They sit as if they were disabled (but they aren't disabled, I checked the MSDB database).
You can manually execute the packages and they work fine.
I have dumped and reloaded these packages and that hasn't fixed the issue.
If I setup a simple recuring job that executes a sql script every few minutes that works fine and you can see that it executes every few minutes. But these SSIS packages just sit....and do nothing. But they were working for the first few iterations.
I have also redeployed the SSIS packages and that hasn't fixed it either.
Anybody seen anything like it?
Hi guys, wonder if you could help.
Ok, basically, my SSIS flow gets data from different excel worksheets and puts in the db.
For some reason its being very incosistent. I fixed all the problems I was having but one component keeps coming up with the [ component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA" ] error.
So, I click on the component, it asks if i want to fix the metadata stuff, I say yes, it fixes it, I run the app, everything's cool.
The I run the app again and again it comes up with this f** error?
Is there anyway SSIS can't just ignore this metadata stuff??
thanks!
Hi All,
I have created a SSIS package which calls child packages internally. In other words there is hierarchy of packages. I am using For Loop Container with certain check conditions to execute whole set of packages repeatedly. I have to execute this set for almost 5000 times. But my problem is this set fails after every 50 and sometimes 55 cycles. Can Anybody let me know how to get solution for such a problem?
Regards,
Prash
Hello,
This is my first project at the company I recently joined. It is the most challenging project I ever had.
It is a kind of web-scheduler.
it let you save appointments which could be one time or periodic.
For example, it could be like every monday, every other monday, first tuesday every monday, and so on.
once they are saved, it should be able to display only those appointments the user have on a specific day.
I'd like to know how database has to be designed to meet such a challenging requirement?
I'd like to hear anything from you if you have had similar project before.
Thank you,
Charlie
I am trying to load Project Real. I get warnings when opening the RecurringETL package. However I created the two environment variables it's looking for and have confirmed these variables are functional.
I am unsure how to troubleshoot this and am afraid to do anything given these warnings. Where is the configurations collection??????:
Warning 1 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Configuration". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 2 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Root_Dir". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 3 Warning loading TestHarness.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Thanks
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
Hi,
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
Thanks,
Darrell Young
How would I bring back a column that shows all the days from the begining of the year to the current date. Also make sure this moves onto the next year.
View 9 Replies View RelatedDid a bit of searching, but couldn't find it. I'm looking to get data from the past 7 days:
select * from inquiry
where created_date is within the last 7 days
Thanks,
Hey heyI've written my site in asp.net 2 and have set up roles and various logins etc which work beautifully locally. Now, when i upload to my host I get the: An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified)The thing is, I've checked my connection string a million times and it's perfect, I've also checked all the database settings on my host and they're all fine. However, when i use SQL Server Management Studio Express to copy my database from the local to the remote server (from a tutorial on these forums), I get the "Operation completed successfully" message yet when I expand my tables, there is no data in them. I've tried copying and pasting the data in manually but i still get the same issue. It's driving me absolutely insane! Anyone any ideas?Thanks
Hi all,
I have a table in which I have two fields in my DB.
FromDate and ToDate.
Both are stored as Varchar(MAX).
I would like to have an SP which gives me the Days in Between them.
Regards,
Naveen.
Is there any way to find out in sql server that whether it is Monday to friday or saturday sunday.I have to write a stored procedure in which the logic shoukld be done only if there is a weekend.if saturday or sunday select * from status_tempPlease let me know if there is any function that is available in sql
View 4 Replies View RelatedIs there anyway to get SQL to convert a date to days of the week?
Example, I have a query where the date is "01/01/2006" but I would like it to display "Sunday". Thank you.
How can I select records from a schedule table that have end dates older than 90 days?
Can I do getdate() -90?
Code:
select *
from dbo.schedule
where enddate <> endate-(90 days?)
Hi,
I just want to know, is there anything more better solution than this one to find out the no of days in a month ?
I have done this but I am not satisfied,anybody has a smarter solution?
Plz comment..
My Solution :
/* check leap year*/
if year(getdate())%4<>0
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 28
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end )
else
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 29
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end)
Joydeep
I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..
January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22
here is my sample code
select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'
Thanks, Jeff
Hi, I used the following query to update my columns according to the change of time and days of the week. But my query is not working as it should. Today is Tuesday and the time now is 9am so according to my query Mon_Night column should be updated but Sat_Night column is being updated when i execute the query. Please advice..
declare @Weekday bit, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
Update Weekly set
Sat_Night=CASE WHEN (@Weekday= 1 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Sun_Day=CASE WHEN (@Weekday= 1 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Sun_Night=CASE WHEN (@Weekday= 2 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Mon_Day=CASE WHEN (@Weekday= 2 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Mon_Night=CASE WHEN (@Weekday= 3 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Tue_Day=CASE WHEN (@Weekday= 3 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Tue_Night=CASE WHEN (@Weekday= 4 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Wed_Day=CASE WHEN (@Weekday= 4 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Wed_Night=CASE WHEN (@Weekday= 5 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Thu_Day=CASE WHEN (@Weekday= 5 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Thu_Night=CASE WHEN (@Weekday= 6 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Fri_Day=CASE WHEN (@Weekday= 6 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Fri_Night=CASE WHEN (@Weekday= 7 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Sat_Day=CASE WHEN (@Weekday= 7 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END
from ALD join Weekly on Weekly.TesterID = ALD.TesterID
How would I do a date range from the first day of this year to the getdate(), and so it will change once the new year hits as well?
View 6 Replies View RelatedI've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9
tblSetValue
Main_ID | Hit2
---------------
206| 3
201| 5
204| 3
203| 1
202| 8
205| 7
*Main_ID is a primary key
Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2
The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206| 5/6/2008 | 300 | 6 | 3 | 3
201| 5/4/2008 | 500 | 9 | 5 | 4
204| 5/3/2008 | 800 | 4 | 3 | 1
203| 5/9/2008 | 300 | 6 | 1 | 5
....
....
....
Anyone can help me to built the query?
dear gurus,
I want to get the working days between two days..
in a single query.
i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.
06-02-2008Monday
06-03-2008Tuesday
06-04-2008Wednesday
06-05-2008Thursday
06-06-2008Friday
06-09-2008Monday
06-10-2008Tuesday
06-11-2008Wednesday
06-12-2008Thursday
06-13-2008Friday
Thanks in advance.
cool...,
Dear gurus..,
I need to get all the days in a year in a single query.
Thanks in advance.,
cool...,