Add Values To A Table Based On Values In Another Table
Mar 23, 2005
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.
Thanks
View 1 Replies
ADVERTISEMENT
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
May 19, 2006
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies
View Related
Jun 10, 2015
Here is my table:
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?
View 0 Replies
View Related
Mar 7, 2008
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 need a SQL Script to accomplish this. thanks
View 6 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Apr 2, 2015
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.
View 1 Replies
View Related
Mar 29, 2007
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
View 9 Replies
View Related
Sep 10, 2007
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.
View 2 Replies
View Related
Jul 26, 2014
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
View 2 Replies
View Related
May 16, 2008
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
Groupid field1 field2
-1 100 200
-1 100 300
-1 300 500
-1 300 600
-1 400 100
the insert 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
View 1 Replies
View Related
Feb 6, 2007
Hello!
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
Tab2
DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,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
CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
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')
View 1 Replies
View Related
Nov 21, 2007
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.
View 10 Replies
View Related
Feb 16, 2005
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')
View 1 Replies
View Related
May 21, 2014
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)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
View 2 Replies
View Related
Dec 12, 2014
There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]
[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]
SET @Total='select '+@formula
EXECUTE sp_executesql @Total;
PRINT @Total;
View 3 Replies
View Related
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.
View 0 Replies
View Related
Oct 27, 2015
I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
View 8 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
May 20, 2014
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)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
How can I update Table 1 to see desired results?
View 3 Replies
View Related
Jul 31, 2015
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.
View 5 Replies
View Related
Oct 1, 2015
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,
[code]....
View 4 Replies
View Related
Jun 17, 2015
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.
View 4 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Jun 9, 2008
Gurus,
I have two list boxes, user can move items back n forth, from second listbox I am inserting values into a table. So far everything is working fine.
Now I want to delete all the existing values from the table before inserting evertime..Please help me in this I dont know what to do.
thanks
kalloo
View 8 Replies
View Related
Jun 22, 2005
I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )If anyone has this answer please let me know.Thanks,RB
View 3 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Aug 4, 2015
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV)
1X
1Y
Results Table(SQL)
CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
2015-08-04 08:35:29.203 successfully inserted value 1Y
2015-08-04 08:30:29.203 Test Pass
2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output:
All values from values table are inserted successfully
Total count of values inserted: 2
If only few values are inserted, example only 1X from Values table is inserted in Message
Example:
Results Table CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
Output:
All values from values are not inserted successfully in result table.
Total count of values inserted: 1
Missing Values not inserted in results table are: 1Y
View 3 Replies
View Related
