See DDL and sample data below. What would be the easiest way to get the desired output without hard coding the values? Data in both tables may change over time.
DECLARE @num AS TABLE (
Id INT IDENTITY(1, 1)
,Price MONEY
)
DECLARE @range AS TABLE (
Id INT IDENTITY(1, 1)
,Rng MONEY
[code]....
I have a variable that is created dynamically that will be used as an input for a sql statement.
When I display the variable in a text box it looks like this:
(PROP_CLASS IN ('" & 100 & "','" & 101 & "' ))
The following works when I hard code the values into the SQL statement:
strSQL = "SELECT * FROM web_transfer WHERE (PROP_CLASS IN ('" & 100 & "','" & 101 & "' )) AND ........."But I get no values returned when I use the variable directly in the sql: strSQL = "SELECT * FROM web_transfer WHERE (PROP_CLASS IN (" & propertyClass & ")) AND ..........." The propertyClass variable is the text string “(PROP_CLASS IN ('" & 100 & "','" & 101 & "' ))“. How do I add this variable to the sql string so that it is recognized. I guess that it is adding some hidden characters. Any help please?? I am a newbie to this, s
Hi all,We're selecting data from our database, FirstName, LastName, MobileNumber etc.We're using the detaials view function to return it in a table upon selection. However all of the variables are returned as they are in the database, ie: without spaces. We tried putting in spaces by selecting "AS what ever", but MSSQL does not seem to like spaces.Any ideas?Thanks
View 2 Replies View Relatedusing below query to raplace the string values (REPLACE abc with T1223), how to use the query without hard coding.
i want to store the values in another temp table and access in main query.
'abc', 'T1223',
'def', 'T456',
'ghi', 'T789',
'jkl', 'T1011',
'mno', 'T12'
select id,name,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,
'abc', 'T1223'),
'def', 'T456'),
'ghi', 'T789'),
'jkl', 'T1011'),
'mno', 'T12'))) New_id
from TAB
Dear Friends, I've created one table, with the following attributes.
everything is ok, but while retrieving data, the question numbers should be starting from 1,2.......... but i'm not getting that result. please suggest me to the output like that.
--create table question_code_level (qno int,code varchar(10),level varchar(10),question varchar(500),option1 varchar(200),option2 varchar(200),option3 varchar(200),option4 varchar(200),option5 varchar(200))
select * from question_code_level
1htmlbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
3c#expertwhat is clr?somethingsomethingsomethingsomethingsomething
4c#intermediawhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
6vbbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
7htmlexpertwhat is clr?somethingsomethingsomethingsomethingsomething
8sqlserverintermediawhat is clr?somethingsomethingsomethingsomethingsomething
10oraclebeginerwhat is clr?somethingsomethingsomethingsomethingsomething
11javabeginerwhat is clr?somethingsomethingsomethingsomethingsomething
12javaexpertwhat is clr?somethingsomethingsomethingsomethingsomething
13sqlserverbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
--select * from question_code_level where code='c#' and level='beginer'
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
Vinod
Gopinath writes "Let us consider we have the following data in a table,
Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple
in the above table, i want to query and get output in the below format
Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple
is it possible? if yes, kindly let me know the query.
Thanks in advance.
Regards,
Gopi."
hi,
I fired the replication command sp_replcmds on my publisher and it returned the fillowing output.
"Another log reader is replicating the database."
can anybody out there explain why is that so and how can i get the output.
Thanks in advance
Jacx
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies View RelatedI have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
Is there a Microsoft published document for SQL database coding standards and guidelines/T-SQL coding standards available??
View 7 Replies View RelatedI thought a stored procedure was taking much too long to complete. So, I moved the main query to Query Analyzer and found that when I substituted actual values for variables that my SELECT statement ran in seconds. Just to test, I created DECLARE statements, set the variables equal to the same values, re-ran the SELECT statement and it took over a minute. Even the Execution Plan was much different. Any suggestions?
View 4 Replies View RelatedHow 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
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.
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
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
I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.
I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).
Is there any way of stopping these extra rows from being created using T-SQL in my query?
Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure
Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter
DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1)
DECLARE @parmRET1 int
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @sqlStr = N'Exec ' + @procName + ' @strAccount,@intHospital,@patType, @outDupCheck OUTPUT'
SET @ParmDefinition=N'@strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';
EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1
SELECT @parmRET1
--The parameter @parmRET1 returns NULL instead of 1 .
The @procName returns value 1 correctly if I run it separately ( outside of the dynamic sql)
Not sure what is wrong here...
Get output of SQL Procedure and assign it to a variable used in WHERE Clause
Later I want to use this variable in my code in the WHERE Clause
Declare @ProjectNo nvarchar(10)
--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.
SELECT ID from TABLEA where Project in (@ProjectNo)
How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'
I've the table like
create table test
(
product varchar(50),
QTY int,
rate float,
Bill_date datetime,
total_val float
)
insert into test values ('Milk','50','500','2014-04-20', '25000')
values ('Milk','20','560','2014-05-20','12000')
values ('Milk','30','450','2014-06-18','13500')
values ('Milk','30','700','2014-06-18','21000')
My expecting output:
Month product Qty Rate T_val
April Milk 50 700 25000
MAY MILK 20 700 12000
Like I've to show the price of latest price in all of months product billed. How to make Query?
I have two tables to be joined
Doc:
ID, DivID
Division:
ID, Div
CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL
[Code] ...
As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
View 2 Replies View RelatedThe problem is as follows. SQL Express 2008 R2, backup is made by the means of bat file and sp. Need to have a table that would hold columns like 'date_of_backup', 'duration', 'message' (the one that appears on 'messages' tab in the result area after backup completion/failure). This is an example of real message that I want to catch:
Server: Msg 911, Level 16, State 11, Line 1
Database 'not_existing_db' does not exist. Make sure that the name is entered correctly.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',
[code]....
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]....
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.
How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?
View 2 Replies View RelatedI have table
CREATE TABLE [dbo].[tblreg]
(
[RN] [nvarchar](50) NOT NULL,
[SC] [nvarchar](6) NULL,
[DC] [nvarchar](12) NULL,
[code]....
So when i write a select query i want the NULL Values should be displayed as zero in the output.
Hello everyone!
Maybe someone of you folks already stumbled upon this one and can help.
I'm using an Unpivot transform in a dataflow.
I want to unpivot various columns that are all of type dt_str(50).
Some of those (input) columns may contain NULL values in some rows, like this:
col1
col2
col3
col4
BusinessKey
val1
NULL
val3
val4
As I run this package, I'd expect the Unpivot's output looking like this:
BusinessKey
col1
val1
BusinessKey
col2
NULL
BusinessKey
col3
val3
BusinessKey
col4
val4
But Unpivot won't output a Key/Value pair if an input value is NULL.
So in fact the output looks like this (col2 is missing):
BusinessKey
col1
val1
BusinessKey
col3
val3
BusinessKey
col4
val4
The BOL documentation doesn't tell anything about Unpivot behaviour if input values are NULL (or at least I couldn't find it).
Now, is this a known problem?
If so, is there a fix or workaround so that I can tell Unpivot to output NULL values?
Thanks for your help!
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] ....
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
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 ?????
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
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