How To Find My Previous Topic??
Jul 23, 2005Plz help
How to find my previous topic??
Cheers
Dishan
ADVERTISEMENT
Slightly Off-topic Topic
Feb 14, 2006Hi,I wanted to know how actually a database converter works.I am working on a converter from DBF to MS SQL server 2000using Visual Basic 6.0. I wanted to know that once a legacy databaseis enterd in the program, how does it get normalised.I have aboout 40 tables in DBASE IV format. I want to convert theminto relational database and store them on server. But on conversion,how can the converted data be normalised by itself.Awaiting the replies,Thanks
View 2 Replies View RelatedFind Records For X Previous Days
Jul 13, 2005On a webform, I have three button ... [7 days] [15 days] [30 days]When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))How do I parameterize the number of days?Thanks,Tim
View 2 Replies View RelatedHow Find Rowcount Of The Previous Query
Oct 15, 2007
Query1: SELECT TOP 5 A,B,C from Table1 where A > 10 or B < 10
Query2: SELECT @@rowcount
After 'Query1' if 'Query2' is executed will return 5.
But how to get the total resultset number of rows for 'Query1' (without TOP) by executing the 'Query2' or others.
The above option is available in mysql.
T-SQL (SS2K8) :: Find Previous Date From Table
Nov 4, 2015I want to find previous date from selected date. below is the sample data.
DECLARE @StartDate SMALLDATETIME = '1/11/2016'
declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')
Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.
if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.
Rolling Sum To Find Total Of 3 Previous Months Gas Production
Nov 5, 2012I'm in need of creating a rolling sum to find the sum of 3 previous months gas production. Below is an example of the basic data I will be using to find the sum.
OperatorIDRRCID YearReported MonthReported YearCode DateReported GasProduction
71054993207201222012022012-02-01 89
71054993207201212012012012-01-01 721
710549932072011122011122011-12-01 353
710549932072011112011112011-11-01 409
710549932072011102011102011-10-01 544
71054993207201192011092011-09-01 707
71054993207201182011082011-08-01 506
71054993207201172011072011-07-01 536
71054993207201162011062011-06-01 472
I want the sum of the Year & Month of 2012-2 to be the sum of the gas production for 2012-1, 2011-12, and 2011-11.
Reporting Services :: Expression To Find Value For Previous Year SSRS?
Apr 17, 2015I need a report that shows calcul of a field for current year as well as previous year respecting this rule expression(Last-Last Previous)/Last Previous*100 (I work with MDX Query and i work with SSRS 2008).
Also the report runs on a Year Parameter. Below is an example for the result for example i selected the years 2010 2011 2012 i can select another years because i have the report runs on a Year Parameter
year
Data 2010 2011 2012
hp 14 25 30
Dell 17 18 20
and the result i want
year
Data 2010 2011 2012 2011/2012
hp 14 25 30 0.002 (Last -Last Previous)/(last Previous*100) =(30-25)/(25*100)
Dell 17 18 20 0.0040
SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?
Dec 12, 2013I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
Off Topic Question?
Jun 20, 2003Does anyone know if there is a way to set a screensaver to auto log off users?
thanks for your time.
Emergency Topic 3.
Dec 4, 2006I absolutely agree with you.
I managed to find the error, it was a typo.
Here is the code, if somebody is interested about it. It is oracle, not SQL server, but the idea is the same.
drop view a;
drop view b;
drop view figure5_9;
create view a
as
select d.donorID, dt.donortype, dt.donortargetq1
from donortype dt, donor d
where d.donortype=dt.donortype;
select * from a;
create view b
as
select a.donortype, sum(c.ContributionAmount) as amount
from Contribution c, a
where c.ContributionDate between '01-JAN-06' and '31-MAR-06' and a.donorID=c.donorID
group by a.donortype;
select * from b;
create view figure5_9
as
select b.donortype, a.donortargetq1, b.amount, b.amount/a.donortargetq1 as percentage
from a, b
where a.donortype=b.donortype
group by b.donortype, a.donortargetq1, b.amount;
select * from figure5_9;
Thanks for the help anyway ;-)
MySQL To MS SQL(Not This Topic Again!! ;-))
Feb 26, 2008Hi,
I am trying to extract data from a MySQL DB. I have installed the MySQL Connector (mysql-connector-odbc-3.51.23-win32), then set up an ODBC connector in the Control Panel > ODBC.
Then in BIDS I have setup a new ODBC connection in Connection Managers, and then used the ODBC connection I setup above so at that point everything looks cool.
Now what? Do I setup a Execute SQL Task or how do I get the data out and into a Data Flow Task?
Thanks
Off Topic: Paul OOF Until 2nd October
Sep 14, 2006I'm in China doing TechEds in Shanghai, Guangzhou, Beijing and Hong Kong - returning to the office Monday 2nd October.
There may be significant delays in my replies to any topics on this forum. Just fyi so you don't sit waiting for me to respond.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Off Topic: Paul OOF Until January
Dec 13, 2006It's been pretty quiet here recently - hopefully people either aren't experiencing corruptions or they're finding what they need in the previous threads.
Just a quick note to say that I'll be totally offline from tomorrow until January - going diving in Indonesia (http://www.wakatobi.com).
Happy holidays and may your pagers be silent throughout
Cheers
Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
Bit Off Topic: Street Name Database US
Sep 23, 2005I'm thinking of doing some basic parsing of address. I want to seprateout the house number from the street name from the suffix. I have thesuffix's from the USPS, but I cant find a database of US street names.Anyone come across one or know where I can get one?TIARob/end off topic
View 2 Replies View RelatedDate Of Previous Year Previous Month
Sep 10, 2013Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
Off Topic - Calling Procedures From MS Access
Nov 15, 1999Hi all!
Is there a possibility to call (and receive records) SQL Server (or other databases) procedure from MS Access?
My SQL Is Not Working!!! My Hair Is Gone Is The Topic - FK Creation Not Being Called And More
Aug 1, 2006I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'
ALTER PROCEDURE [domainnamemyaccountname].[Categories_I]
AS
BEGIN
/* delete contents from Category table and reseed
Cannot use truncate on a table which contains constraints therefore
use DBCC to reset the seed and DELETE to clear the records
*/
DELETE dbo.Category
DBCC CHECKIDENT ('Category', RESEED, 0)
-- Now, insert the initial 'All' Root Record
ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1
PRINT 'Dropped FK'
SET IDENTITY_INSERT Category ON
INSERT INTO dbo.Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
PRINT 'Inserted All Record'
INSERT INTO dbo.Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
PRINT 'Inserted Store Record'
SET IDENTITY_INSERT Category OFF
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO dbo.Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END
PRINT 'Inserted Rest of Category Records'
PRINT 're-create FK Call'
ALTER TABLE dbo.Category
ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY
(
ParentCategoryID
) REFERENCES Category (
CategoryID
)
PRINT 'create FK2'
END
Other errors:
(1 row(s) affected)
Checking identity information: current identity value '2', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3728, Level 16, State 1, Line 6
'Category_Category_FK1' is not a constraint.
Msg 3727, Level 16, State 0, Line 6
Could not drop constraint. See previous errors.
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.
Schemas & Data:
http://www.webfound.net/category.bmp
http://www.webfound.net/category.sql
http://www.webfound.net/categoriesstaging.sql
http://www.webfound.net/stagingdata.txt
Urgent: A Simple Question About The Most Asked Topic - DATE
May 7, 2004Hi,
The subject say it all :)
I have a field which stores date which the datetime datatype... The problem is that I am also getting time information in this stored field.
I just want to extract the date part in dd-Mmm-yy format (e.g. 07-May-04). I tried convert (char (8), MyDateField, 112) but it gives me date in yyyymmdd format. How do I get the results in dd-Mmm-yy format instead?
Thanks.
Search Day Before Last Day Of The Previous Month + Day Last Day Of The Previous Month
Dec 22, 2007how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"
Code BlockSELECT empid, basedate, unit_date, shift, na
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)
AND
(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
TNX
Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?
Jul 26, 2005I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedProblem: Find All Table Names In Db And Then Find
Jun 12, 2008I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Next/previous Record
May 2, 2007Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek.
SELECT ProblemIDFROM dbo.tblProblemsWHERE (ProblemID = 10)
Last And Previous Query
Mar 20, 2004I'm kindof at a lose for how to ask my database to show me the last payment amount and payment date for clients and also show me the previous payment amount and payment date for clients based on a date range.
Could anyone offer any pointers or examples?
Thank you very much.
We could use a very simple table and field setup like this just so you could show an example:
tblClient
Client_ID
tblOrder
Order_ID
Client_ID
tblOrderPayments
Payment_ID
Order_ID
Client_ID
PaymentDate
PaymentAmount
How To Get Last Day Of Previous Month
Jun 7, 2000Hi Friends
do you have any soluyion or function for last day of Previous Month
thanks
Nilesh
Previous Value Best Method
Jan 25, 2006I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
Previous Value Retrieval
Jul 10, 2006I have a table of vehicle usage records with fields including vehicle number, driver, date/time, starting odo, ending odo. I'm looking to compare the starting odo of a given record to the ending odo of the last time that vehicle was used. I also need to return other fields from that previous record, like the date/time and driver.My users basically run a report for any given day, and the vehicles used on that day need to be compared to their most recent usage (most recent relative to the record at hand). This is to ensure that the vehicle hasn't been used in the interim and not recorded (which may indicate theft).I've got a very convoluted process in place, but I'd like to see if it can be streamlined. The current process is done in Access and has a number of queries built on other queries. It is very slow. The data is in SQL Server. Any thoughts on the best ways to accomplish this?TIA
View 4 Replies View RelatedPrevious Months
Mar 19, 2007I have a View there I need my criteria to select 13 months from previous month.
Example. This is 3/1/2007 - I need to query 2/1/2007 to 2/1/2006.
How To Get The Previous And Next IDs From A Table
May 21, 2007I have a table that holds images. I want to get the previous image id and the next image id based on the current ID:
ImageID
4
21
56
74
99
So if the current image ID is "21" I'd like to return 4 and 56.
Thanks!!
From Previous Post
Sep 28, 2007Ok so someone answered my last quesiton about finding what tables are in a Stored procedure. Is there an opposite of that. If I wanted to type in a table and it shows all the stored procedures that use that table.
View 3 Replies View RelatedGet Previous Record
Dec 5, 2005Please see DDL and INSERT statements below.Let's say that some process throws out the second row, where theClocktime = '02/01/2005 12:34'Without the use of a cursor, how can I retrieve the PREVIOUS value forthat employee? Pseudo SQL might be something like:SELECT*FROMtblTestWHEREfldCLocktime = THE-ONE-IMMEDIATELY-BEFORE '02/01/2005 12:34'ANDfldEmployeeID = 1TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTest]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTest]GOCREATE TABLE [dbo].[tblTest] ([fldTestID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldClocktime] [datetime] NULL ,) ON [PRIMARY]GOINSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'01/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'02/01/2005 12:34')INSERT INTO tblTest(fldEmployeeID,fldClocktime)VALUES(1,'03/01/2005 12:34')
View 3 Replies View RelatedLast Day Of Previous Month
Jul 20, 2005How can i calculate the last day of the previous month?Help me,please
View 3 Replies View RelatedPREVIOUS FUNCTION
Mar 27, 2008Has anyone used the 'previous' function to return the previous row of data? I have tried this
=previous(Fields!SomeColumn.value) - when I run the report, no data returns.
Previous Month On First Day
Aug 29, 2007Newbie question. I am using a query that pulls month-to-date data that has the following where clause:
WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))
this works just fine but what I would like for it to do is give me the previous month of data if the if it's
the first day of the month and then any other day give me month to date. Is this possible?
Thanks in advance,
Marco