T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values
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
ADVERTISEMENT
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
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
Nov 9, 2014
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
View 4 Replies
View Related
Jul 22, 2014
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
CLASS Days
A sun
A Mon
A wed
View 6 Replies
View Related
May 14, 2014
I have a table with this info:
NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21
How I can get this result:
NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82
[code]....
Should I use unpivot or pivot?
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
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
Aug 11, 2005
Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
View 5 Replies
View Related
May 14, 2014
I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.
Currently it is displaying.....
MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real
-===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL
Output should display as
MeterId Period Reading
4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15
View 4 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
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
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
Feb 26, 2014
I have to change the name of the history table dynamically to what is passed in the start date.
If @start_date= '1/1/2014' then it should be history_jan14
If @start_date ='02/01/2014' then it should be history_feb14 and so on.
Is there a way you can do it ?I am using SQL Server 2008.
Code :
DECLARE @start_date datetime
DECLARE @end_date datetime
SET @start_date = '01/01/2014'
SET @end_date = '02/01/2014'
[code]...
View 2 Replies
View Related
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, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 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
Oct 25, 2006
i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus. I am wanting to pass the Membership.GetUser.ProviderUserKey.ToString() to the asp:parameter but I cant get it to work.So How do I pass a variable to a stored proc parameter using a bound data grid.I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.please helpjim
View 2 Replies
View Related
Apr 17, 2007
Here's a portion of the current statement.
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = 0,
bControlRiskIsAffirmed = 0,
bCombinedRiskIsAffirmed = 0,
bApproachTypeIsAffirmed = 0,
bCommentsIsAffirmed = 0
FROM EngagementAuditAreas WITH(NOLOCK) ...
And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),
bControlRiskIsAffirmed = (numControlRiskID IS NULL),
bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),
bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),
bCommentsIsAffirmed = (parent.txtComments IS NULL)
FROM EngagementAuditAreas WITH(NOLOCK)
Thanks.
View 1 Replies
View Related
Aug 3, 2004
Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
Thanks in advance!
View 5 Replies
View Related
Sep 29, 2015
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
View 14 Replies
View Related
Oct 23, 2014
I have a problem with trying to pull postcodes from a table when I have a lookup table which provides me with a StartPostCode and an EndPostCode.
For instance if, in the input table I have a postcode BETWEEN two values eg: CV1 and CV10 I want to get a third value from the look up table.My problem is my query seems to bring two values back even if the postcode is between the ranges specified.To reproduce the problem first create the tables and populate with the data.
USE [CTSStaging]
GO
/****** Object: Table [dbo].[st_StobartPostCode] Script Date: 10/23/2014 12:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 6 Replies
View Related
Jul 16, 2015
Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.
DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"
Wondering is there a different way in 2012 to pass values to variables dynamically.
View 2 Replies
View Related
May 3, 2006
Suppose a very basic question, but how can I easily update values in a record. I can easily READ the value with this code.
Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
mfnum(i)=Rsx("featureother1")
Rsx.movenext
Wend
..now I try an UPDATE sql to insert the values from the array into another field in the corresponding records - I can get it to work, but it is very stupid done. Actually I must open and close the database for every record to get it to work:
For j=1 To i
Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "UPDATE prodfeatures SET featurenum=" & mfnum(j) & " WHERE id=" & mte(j)
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
next
...I suppose this is silly programming, but it actually works well, but it takes a really lot of time to execute...
...someone who has a faster way of doing this???
View 5 Replies
View Related
Feb 22, 2007
Greetings,
I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.
The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record
-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.
Everytime I to set the value of a field or parameter at runtime I get a read-only error.
Maybee the only way is to use calculated fields.
Any ideas.
View 2 Replies
View Related
Apr 23, 2014
I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my statements.
--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num
[Code] ....
View 8 Replies
View Related
May 6, 2014
I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.
DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL
[code]....
So this would be the resultset:
1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27
View 3 Replies
View Related
May 19, 2014
I've the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
Like I've 30th onwards.....
Expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
These cum column values how to make a code ?????
View 5 Replies
View Related
Sep 29, 2014
find below object and data:
create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)
insert into #StuDetails values('A','new',13,10,0,0,12)
insert into #StuDetails values('B','Old',10,10,41,0,12)
insert into #StuDetails values('C','Fail',10,9,0,0,5)
select * from #StuDetails
Output of above is display as:
CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 0 0 12
BOld 10 10 41 0 12
CFail 10 9 0 0 5
Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.
CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 12 12 12
BOld 10 10 41 12 12
CFail 10 9 5 5 5
View 2 Replies
View Related
Feb 25, 2015
I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.
Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl
ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5
I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.
My expected output would be:
ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305
View 7 Replies
View Related
Mar 6, 2015
I am currently reading through Itzik Ben-Gan's "Microsoft SQL Server 2012 High-Performance T-SQL using Windows Functions." In attempt to test the SUM OVER() function in SQL 2008 because that's what I've got. I do not currently have sample data (trying to generate it has become a major PITA), but I have some pseudocode.
My current code (actual production code) pulls a bunch of ITD (inception to date) contracts then calculates a certain dollar amount based on monthly changes. Not all contracts have values during a given month, so here's what I cobbled together a few months ago. (Per our finance team, these numbers ARE accurate).
WITH MonthlyVals AS
(SELECT ContractID, SUM(Col1 - (Col2 + Col3 + Col4 + Col5)) AS MyTotal
FROM MyTable
WHERE MyDate >= @ThisMonthStartDate AND MyDate <= @ThisMonthEndDate
AND StatementType IN (8,4,2)
[code]....
To test the totals, I also added a COMPUTE SUM(MyTotal) to the end of each query. (Yes, I know COMPUTE is deprecated. Just wanted a quick check.). The difference between the two bits of code was over 68k, with the SUM OVER() code coming up with a total higher than the CTE code. I know CTE code is correct for a fact. It went through extensive testing before getting put in Production. Is it the way I joined the table for the SUM OVER()? Or is it the use of PARITION BY?
View 5 Replies
View Related
May 11, 2015
Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc
The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....
View 7 Replies
View Related
Jun 1, 2015
I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.
Here is an example:
IF OBJECT_ID('tempdb..#events') IS NOT NULL
DROP TABLE #events
create table #events (eventId int,
Category varchar(250),
events1 varchar(250),
[Code] .....
In this case, I'd like to run a query like this one(skip Column Event3):
Select eventId,Category,events1,events2,events4,events5 From #events
View 4 Replies
View Related