SQL Server 2008 :: Get Previous Values Based On Separate DB / Table?
Apr 24, 2015
I am pulling down out of range values from a single table on one database to a different table on a different database on a different server (one i have full access to). Basically, it looks something like this:
id1 value1 prev_value1 value2 prev_value2 date prev_date
id2 value1 prev_value1 value2 prev_value2 date prev_date
id3 value1 prev_value1 value2 prev_value2 date prev_date
all the "prev"'s are null. I want to do one do one query that will get me the previous values and dates for each id from the original database. how to do this.
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.
What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
I'm trying to load data from old SQL server 2000 to new SQL server 2014. I need to do a checksum to check if all the source data is loaded in the target database(SQL server 2014). I've created the insert statement for the same which works. I need to use checksum to make sure all the source rows are loaded in the target table. I haven't done checksum before.
Here is my insert statement:
INSERT INTO [Test].[dbo].[Order_tab] ([rec_id] ,[date_loaded] ,[Name1] ,[Name2] ,[Address1] ,[Address2]
I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.
I have a stored procedure that is passed two values. Both are strings representations of GUID values, the first is a single value, the second is a comma delimited string of values.In the stored procedure I call a split function to separate the comma delimited values into a table and this is used in my WHERE clause to filter my select results.This is an example:
Code: SELECT item.uiGUID as ItemGUID, stores.strStoreName as Store, location.strLocationName as Location FROM tblItems as item INNER JOIN tblStoreLocations as location ON item.uiLocationGUID = location.uiGUID INNER JOIN tblStores as stores ON location.uiStoreGUID = stores.uiGUID WHERE CAST(item.uiGUID as varchar(36)) IN (SELECT Value FROM dbo.Split(',',@ItemGUIDList))
When I run this query in the management studio, passing a list of 5 values in the second parameter, my results include one item for each of the 5 values. However, when I pass the parameters from my ASP project, (I've verified the values I think are being passed are indeed being passed), I only get one item.I believe the error is in my split function. Both split functions return the same results in the SQL management studio, but only one returns the correct results in the the ASP project.
When I use this version of the function it returns the correct table values to the calling application, but it chokes when the item list does not have a trailing comma. I figure that to be a bug in the SQL function.
I'm aware that it's best practice to separate mdf and ldf files onto separate drives.
However, I see a lot of servers where the underlying disk array is the same for drives on the server.
Is there still any performance benefit to separating mdf and ldf files in this situation?
For example, a single virtual server running SQL Server, with multiple drives attached.All of the drives are connected to a shared storage via iSCSI.There drives C:, D:, E: etc are all actually sharing the same underlying disks.
Obviously, there are some benefits from an administration perspective whereby individual drives can be reconfigured without affecting the others.
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1 SET FeeInvalid = 'Y' FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID WHERE t1.CustID = t2.CustID AND t2.OrderDate > t1.OrderDate AND t2.FeeDate > t1.CancelledDate CREATE TABLE [dbo].[MockData]( [OrderID] [float] NULL,
I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.
I am not sure if SQL Server 2008 has this capabilities.
I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.
create table TEST ( MRN varchar(10), EncDTTM datetime, Sequence int ) insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1') insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale. In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD. That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
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.
I have tables in my database, tblNames1, tblNames2, tblNames3 for ex, and for addresses tblAddresses, can I match the address IDs in the names tables to the address table based on the values of a field? In this case CompanyName. Can they be matched automatically? My issue is that I have a huge CSV file with all the address information that needs to be assigned a foreign key based on the company name field which is also present in all the names tables, all the names are normalized.
Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:Need help to do this:Open Table_AWITH TableADO UNTIL .EOFRead value from TableA.ColumnARun SQL Statement on TableB based on valueMove to the next recordLOOPENDHow do I do this in TSQL?Thanks,Bubbles
HiBelow is a table with sample data to explain what I want to achieve.trackID member markerallele1------- ------ -------------------------------------------------------------------------------------------------------4734 4577 01-D8S1179 13.54734 4577 02-D21S11 12.64734 4577 03-D7S820 2.04734 4577 04-CSF1PO 3.04734 4577 06-TH01 4.04734 4577 07-D13S317 5.04734 4577 08-D16S539 9.04734 4577 11-vWA 6.04734 4577 12-TPOX 7.04734 4577 13-D18S51 1.04734 4578 01-D8S1179 13.04734 4578 02-D21S11 12.64734 4578 03-D7S820 2.04734 4578 04-CSF1PO 4.04734 4578 06-TH01 3.04734 4578 07-D13S317 5.04734 4578 08-D16S539 9.04734 4578 11-vWA 7.04734 4578 12-TPOX 6.04734 4578 13-D18S51 1.0Okay I am trying to compare allele1 values for differences for two orthree different member having the same trackID. The list of markerswould always be same for all members belonging to a particulartrackID.So in the above sample data. The trackID is same which is 4734. Thereare two members 4577 and 4578. The markers are same for each membersbut the corresponding allele1 values are different.I want to compare these two and state whether they are same or not. Iwould like to do this on a form containing subforms.Feel free to ask for clarification.Thank you.
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already. the table looks like this
INSERT Into table1(groupid,field1,field2) select -1,@passedvalue,field2 from table1 where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100 Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist? eg if @passedvalue = 300 and passedvalue1 = 500
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition --------------- S100 | Def of S100 S101 | Def of S101 S102 | Def of S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition FROM Tab1 WHERE SubjectCode IN (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39) FROM Tab2 WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition --------------- S100 | Def of S100 S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition FROM Tab1 WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SubjectCodeDefinition] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DepartmentReturn] GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition) SELECT 'S100', 'Definition of Code S100' UNION ALL SELECT 'S101', 'Definition of Code S101' UNION ALL SELECT 'S102', 'Definition of Code S102' UNION ALL SELECT 'S103', 'Definition of Code S103' UNION ALL SELECT 'S104', 'Definition of Code S104' UNION ALL SELECT 'S105', 'Definition of Code S105' GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes) SELECT 1,'P100','S100,S101,S104' UNION ALL SELECT 1,'P101','S102,S103' UNION ALL SELECT 1,'P102','S100,S101,S105' UNION ALL SELECT 2,'P100','S100,S101,S104,S105' UNION ALL SELECT 2,'P103','S103,S104,S105' UNION ALL SELECT 3,'P100','S100,S102,S104' GO
SELECT SubjectCode ,Definition FROM SubjectCodeDefinition WHERE SubjectCode IN (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39) FROM DepartmentReturn WHERE DeptID = 1 AND PurposeCode = 'P102')
When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated.
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT. I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId". What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display. Sample: ProjectId - SiteId 1 - 5 1 - 9 1 - 16 1 - 18 1 - 20 1 - 27 1 - 31 ProjectId stays the same, only values for SiteId are being different. I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
