Transact SQL :: Select Data In Pair?
Sep 22, 2015my business user want all record where glcode must start with 2 and 4
<sample data
Tran_No
GLCode
abcd123
2123
abcd123
21235
abcd123
4289
[code]....
ADVERTISEMENT
Transact SQL :: Select Statement To Split Data
Mar 28, 2012I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?
View 10 Replies View RelatedTransact SQL :: Select Data From Table With Custom Format
Sep 15, 2015I want to select custom format from table?
View 7 Replies View RelatedTransact SQL :: How To Select Data With Specific Priority Of A Column
Aug 1, 2015i have table that has rows as below
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[code]...
i want to select the data priority wise. the o/p should look like
e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
Transact SQL :: Select Lowest Level From Hierarchical Data
Jun 22, 2015I have hierarchical data such as:
Id Level ParentId
1 0 1
2 1 1
3 2 2
4 0 4
5 1 4
6 0 6
7 1 6
8 2 7
9 3 8
10 4 9
11 0 11
As you can see even the parent element has parentId(in this case id = parentid)
How can I select the lowest level data in the hierarchy and get this result:
Id Level ParentId
3 2 2
5 1 4
10 4 9
11 0 11
Transact SQL :: Allow A User To Select Data Through A View But Not Restrict Access To Underlying Table
Sep 29, 2015I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?
Sep 29, 2015I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
Name Value Pair
Mar 12, 2008Hi,I need to display a dataset where everything is dynamic.e.g. I have a table with columns "Code", "Description" and "Inspected" andanother table with columns "UserCode", "Name", "PostCode" and "Town" etcAnd I need to dislay data like this from a single db proc with parameters:-(TableName, ColumnName, ColumnValue)Procedure called with these parameters (CodeTable, Code, TD001) would returna dataset like this:-----------------------------------|Code | TD001|Description| Printer|Inspected | Y---------------------------------Not----------------------------------|TD001|Printer|Y---------------------------------Procedure called with these parameters (UserTable, UserCode, CP1) wouldreturn a dataset like:----------------------------------|UserCode | CP1|Name | Charles|PostCode | 2000|Town | Sydney---------------------------------Not---------------------------------|CP1| Charles| 2000| Sydney---------------------------------Any ideas how I would code the database proc, I did consider using XML butnot sure.ThanksAJP
View 3 Replies View RelatedTransact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?
Apr 29, 2015I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Pair Of Records
Sep 17, 2004Hi folks,
I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B
I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?
Any urgent help will be highly appreicated.
Thanks
Name Value Pair Design
Jul 20, 2005I want to store many different types of objects in a single table. Iwas thinking of using the name value pair approach to achieve this.Does anybody have any experience with a such a design?The table might look like thisCREATE TABLE NV (pk int, type int, [name] varchar(100), valuevarchar(100))--Insert a manager - type = 1INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'FirstName', 'John')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'LastName', 'Smith')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'Position', 'CEO')--Insert an employee - type = 2INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'FirstName', 'Joe')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'LastName', 'Blog')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'Position', 'Developer')--Insert an inventory item - type = 3INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Name', 'Chair')INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Color', 'White')INSERT INTO NV (type, [name], val)VALUES (3, 3, 'Price', '$150')
View 3 Replies View RelatedKey-Value Pair Design
Aug 8, 2007Hi,
I have to log the Details of the incoming xml message into databse.
But the values logged will vary with the message.So I cant fix the mumber of columns.
I thought of using table in which the fields are logged as Key-Value Pairs. The table looks as below.
TransactionID ColumnKey ColumnValue
1111 PONumber 123
1111 Sender xxx
1111 Recever yyy
using dynomic query i was able to get the results as follows
TransactionID PONumber Sender Receiver
1111 123 xxx yyy
Till now every thing was fine. but now i got new requirement where i have to identify each column with its parent. For example if we consider the line items of the PO, table may look like below.
TransactionID ChildKey ChildValue ParantKey ParantValue
1111 PONumber 123 null null
1111 Sender xxx null null
1111 Recever yyy null null
1111 ItemName soap ItemID 123
1111 Quantity 4 ItemID 123
1111 UnitPrice 2.2 ItemID 123
1111 ItemName Brush ItemID 222
1111 Quantity 5 ItemID 222
1111 unitPrice 4.4 ItemID 222
I am unable to design the database which satisfy the requirement of the reporting.
I not even know how to query the data which is logged like this.
Help me by giving the inputs to design databse for the above problem and to query the data .
advance thanks
Srinivasa Mahendrakar
How Do I Delete One Of A Pair Of Records.
Sep 8, 2007I have a table, gdbdoc, that contains record-key pairs, linking records in another table. There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is SELECT ... Where DCIindiid = A ... union SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2)
The link-creation process normally checks whether there is already a link in either direction. Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction. However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link.
If I run a query: - select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
this displays all the records where one record links A -> B and there is also another record that links B -> A.
How do I write a query to delete ONE of the pair of duplicate records? I have two problems: -
Problem 1: Table gdbdoc is keyed on (DCIindiid, DCILinkid). Both guids are needed to create a unique key, and the table does not have a single key field. You can't write DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
as the DELETE ... SELECT ... syntax only seems to support a single returned value.
Problem 2. If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links.
Afterthought: Problem 2 seems easily solvable: add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement. Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select. Obviously I don't care which of the two links (A->B or B->A) is deleted.
Regards, Robert Barnes
Need Another Pair Of Eyes Sql 'Order By'
Feb 9, 2006my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.
the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/Newindex/trip_types.asp, line 53
line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53---> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.
any comments gratefully received thanks.
Primary Key Pair Constraints
Apr 29, 2008Hi Folks,
I would like to create a table with primary key pair:
Key1 : nchar(10)
Key2: nchar(10)
Value: money
That is, Key1 and Key2 are the primary key columns for the table. I would like to think of (Key1='Foo', Key2='Bar') to be the "same" as (Key1='Bar', Key2='Foo'). Is there a way to enforce this as a table constraint, or do I have to enforce this manually in all procedures that modify and read the table?
Thanks!
Adam Cataldo
Transact SQL :: Select From A Select Using Row Number With Left Join
Aug 20, 2015The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
Key/value Pair Table Update Query
Jan 18, 2005I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.
Table of course takes this form key | value
---------------------------------
config_setting1 | value1
config_setting2 | value2
I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object???
Thanks for any help and suggestions,
ecolner@yahoo.com
Extended Events With Pair Matching
Oct 12, 2015I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end). Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL?
Code:
USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT *
FROM sys.server_event_sessions
WHERE name='TimedOutSQL')
[code]...
Show Difference In Pair Of Nearly Identical Rows
Sep 28, 2007I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3
1 - 0 - AA - BB - CC
1 - 1 - AA - BF - CC
2 - 0 - GG - NN - TT
2 - 1 - DE - NN - TA
3 - 0 - etc
3 - 1 - etc
4 - etc
How can I render the differences in red in RS?
Transact SQL :: Pass Results From Select To Another Select
Oct 13, 2015I've got a select as follows:
select computer, count(*) as MissedCount from WInUpdates_Neededreq
WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE())
group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
How To Do Pair-wise Comparision Like Oracle In MS Sql Server 2000?
Apr 18, 2008
How can we do pair wise comaprission using Sub query as generally can be done in Oracle?
Thanx
Transact SQL :: How To Select Inverse
Jun 8, 2015I have two tables
first table name is table_VR and it has one column named CsNo and following values
table_VR
CsNo
1
2
3
second table is table_VS and it has also one column named CsNo and following values
CsNo
1
2
7
Now i want follow result
Select CsNo from table_VR where CsNo does not match with CsNo of Table_VS
result would be following
CsNo
3
so you saw in table_VR first two records matches in table_VS but the thirst row value does not match.I am not understanding what would be the query to get this result.
Transact SQL :: Select User By Name AND / OR ID
Apr 28, 2015I'm working with the following query to try and determine if a user already exists in a DB before adding a new user.
SELECT COUNT (*) AS ENTITY_COUNT
FROM ORG_ENTITY
INNER JOIN ORG_ENTITY_TYPE ON ORG_ENTITY.N_TYPE_ID = ORG_ENTITY_TYPE.N_TYPE_ID
LEFT OUTER JOIN E_MAIL ON ORG_ENTITY.N_ORG_ENTY_ID = E_MAIL.N_ORG_ENTY_ID
WHERE (ORG_ENTITY.M_ORG_FIRST = 'paul' AND ORG_ENTITY.M_ORG_LAST = 'test')
OR ORG_ENTITY.N_USER_ID = 'TST1'
The thing is there could be an existing DB record with the same name but the new user to be added may have a different N_USER_ID in which case I want to reject the new entry. But if the name is the same and the N_USER_ID already exist then I want to reject the new entry. I'm returning an Int from my query.
Transact SQL :: Multiple Select CTE
Jun 11, 2015I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.
I got a wonderful select statement that gives me the latest Results for each Account.
WITH cte AS
(
SELECT
Accounts.AccountID,
Accounts.AccountName,
Results.ResultTime AS LastUpdated,
[Code] ....
I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:
SELECT SUM([Lots]) AS Longs
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 0;
SELECT SUM([Lots]) AS Shorts
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 1;
But it has to "join" the CTE somehow so that I get the correct answer for each Account row.
Transact SQL :: Select (sum) Statements With Two Table?
Jun 5, 2015I have a database with two tables (Table1 and Table2)
looking for something like:
Table1:
+----+-------+----------+
| id | Name | email |
+----+-------+----------+
| 1 | name1 | mail1 |
| 2 | name2 | mail2 |
| 3 | name3 | mail3 |
| 4 | name4 | mail4 |
| 5 | name5 | mail5 |
| 6 | name6 | mail6 |
+-- +-------+----------+
Table2:
+----+------------+---------+------+
| id | table1_ID | fee | type |
+---+-------------+---------+------+
| 1 | 1 | 20000 | 1 |
| 2 | 3 | 1000 | 1 |
| 3 | 1 | 10000 | 1 |
| 4 | 3 | 1000 | 1 |
| 5 | 5 | 500 | 1 |
| 6 | 5 | 500 | 2 |
| 7 | 1 | 60000 | 2 |
| 8 | 2 | 1000 | 2 |
+----+------------+---------+-------+
i want the query that return:
+--------+-------+---------------------------------+---------------------------------+--------+
| name | email | a:sum(fee) where type=1 | b:sum(fee) where type=2 | b/10 |
+--------+-------+---------------------------------+---------------------------------+--------+
|name1 | mail1 | 30000 | 60000 | 6000|
|name2 | mail2 | 0 | 1000 | 100 |
|name3 | mail3 | 2000 | 0 | 0 |
|name4 | mail4 | 0 | 0 | 0 |
|name5 | mail5 | 500 | 500 | 50 |
|name6 | mail6 | 0 | 0 | 0 |
+--------+-------+---------------------------+---------------------------------------+---------+
Transact SQL :: Select Conditional Dates
Oct 15, 2015TSQL statement, it should select the first date of the month at 11:59 PM as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies View RelatedTransact SQL :: Select Column On Condition
Jul 4, 2015I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true my select query should select the number column. if the variable is false , only the first two columns are selected.
e.g @displaynum = true
@displaynum = false
Firstname | Lastname | number Firstname | Lastnam
blah | blah | 12345
blah | blah
i am not sure if this is possible as a case still selects the column but just changes the values.
Transact SQL :: With CTE N Multiple Select Statement
Apr 29, 2015SELECT FirstSet.Country,FirstSet.[Month]
,ABC.ABC1
,DEF.DEF1
FROM (
SELECT [Answer Text]'Country',interview_start 'Month',[ID respondent],
[Code] ....
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 351
Incorrect syntax near the keyword 'as'.
Transact SQL :: Calculation In SELECT Command?
Nov 4, 2015I want to calculate the Total_Cost and get the result of the formula from SELECT Query. Following is the formula of Total_Cost. I have declared variables for formula. How I can create SELECT/RUN query successfully for mathematical calculation to calculate the Total_Cost.
--Formula of Total_Cost = [min (price, Pay1) x Interest1 + max (min (price - Pay1, Pay2), 0) x Interest2 + Max ((price - Pay2), 0) x Interest3, LMT]
declare @price numeric(18,7)=255550
declare @Pay1 numeric(18,7)=645500
declare @Pay2 numeric(18,7)=235000
declare @Interest1 numeric(18,7)=0.05500
declare @Interest2 numeric(18,7)=0.03533
declare @Interest3 numeric(18,7)=1.00000
declare @LMT numeric(18,7)=10000.00
Select [min (@price, @Pay1) x @Interest1 + max (min (@price - @Pay1, @Pay2), 0) x @Interest2 + Max ((@price - @Pay2), 0) x @Interest3, LMT] as Total_Cost
Transact SQL :: Query To Select Particular Record?
Sep 21, 2015I have created a sample Table with inserting below data,
ID Subject CreatedDate
1 Test 2015-09-20 22:59:07.373
2 Test 2015-09-21 09:16:58.290
3 Test 2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?
Transact SQL :: Select Summary Query
Aug 2, 2015I'm trying to make a summary daily production report on the data below:Want to summarize the data with the sum of the Correct Weight between start and end date.
eg. of summary.
Recipe Name Total Weight
Hedge Shears - Lasher/kudu
500
Grass Slasher
200
eg.
But it needs to summarize when selected between start and end date.
RecipeName
CorrectWeight
CurDateTime
Weight
[code]....
Transact SQL :: Select With Value For Specific Month
Nov 13, 2015I have table with below scheme
Year Month Day RELH
------ -------- ------ -------
1973 4 1 50
...... etc
1973 4 30 20
.....etc
1974 5 1 50
....etc
1974 5 30 99
I need to get years where average of RELH of months within year meet
Month 4 Average(RELH) > 33 and
Month 5 Average(RELH) > 60 and
Month 6 Average(RELH) < 33
How can I achieve this?
Transact SQL :: SELECT STATEMENT With Increment
Jun 23, 2015How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.
Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10))
insert into #seq values (1,1002,'10/10/2015','SEDT')
insert into #seq values (2,1005,'12/10/2015','PEFT')
insert into #seq values (3,1004,'1/1/2015','QEGT')
insert into #seq values (4,1008,'1/2/2015','TEWT')
insert into #seq values (5,1007,'3/10/2015','REDT')
Result from the above query
serial_no Scode Sdate SErr
1 1002 10/10/2015 SEDT
2 1005 12/10/2015 PEFT
3 1004 1/1/2015 QEGT
4 1008 1/2/2015 TEWT
5 1007 3/10/2015 REDT
Desired SELECT STATEMENT Result with increment of serial_no (Max(serial_no+1)
Select 6, 1002, '10/10/2015', 'SEDT'
Select 7, 1005, '12/10/2015', 'PEFT'
Select 8, 1004, '1/1/2015', 'QEGT'
Select 9, 1008, '1/2/2015' ,'TEWT'
Select 10, 1007, '3/10/2015', 'REDT'