Problem With Transact SQL Query
Nov 12, 2007
Event
Event_ID
Person_ID
Event
Start_Date
1001
50
Referral
02-02-2007
1002
50
Appointment
03-02-2007
1003
50
Assessment
07-02-2007
1004
84
Referral
14-02-2007
1005
84
Appointment
20-02-2007
1006
50
Referral
01-03-2007
Outcome
OutcomeID
Person_ID
Event_ID
Outcome_Date
Outcome
4000
50
1001
02-02-2007
Appointment
4001
50
1002
03-02-2007
Assessment
4002
50
1003
09-02-2007
No Further Action
4003
84
1004
15-02-2007
Appointment
4004
84
1005
22-02-2007
No Further Action
4005
50
1006
03-03-2007
No Further Action
Result
Person_ID
Event_ID
Start_Date
OutcomeID
Outcome_Date
50
1001
02-02-2007
4002
09-02-2007
84
1004
14-02-2007
4004
22-02-2007
50
1006
01-03-2007
4005
03-03-2007
I have a problem writing a query for the above two tables Event and Outcome. The event table conisists of an event id, the person id, the event and the start date of the event. The Outcome table is the based on the outcome of the event; the table includes the outcome id, the person id, the event id, when the outcome decision was made (this can be after the event start date) and the outcome.
A client may have a referral which goes onto an appointment then an assessment. If the outcome is No Further Action then no further events take place. No Further action can be outcomed at any stage. A person may only have one event open at a time i.e. they cannot have a new referral if they have an assessment.
I want to create a query that displays when the referral started (event start_date) until it ended (when No Further Action was selected). Because a person may have referrals I am struggling with the query with regards to dates.
View 7 Replies
ADVERTISEMENT
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Jul 22, 2015
I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
View 10 Replies
View Related
Aug 18, 2015
I have table like below, its period wise ,here the value get cumulative period wise.
amtname period
10CHR201202
20TNG201202
10CHR201203
20TNG201203
View 3 Replies
View Related
Jun 23, 2015
I need query to get the Max date of CODE.
OID CODE Current_date
3710 250 01/01/1997
3910 250 03/03/1998
4100 250 01/01/2014
1200 251 01/01/2013
1301 252 01/01/2001
1450 252 01/01/2014
1451 252 01/01/2015
Expected result after taking max(effdate) of CODE
OID CODE Current_date
4100 250 01/01/2014
1200 251 01/01/2013
1451 252 01/01/2015
View 7 Replies
View Related
Sep 24, 2015
i need to run query that will run on two servers.
one is local (Server1), and one on 192.144.22.22 (Server2)
i try this:
SELECT *
FROM [Db1].[dbo].table1 A
INNER JOIN [Server2].[Db1].[dbo].table2 B
ON A.Id = B.Id
but this work fine because the two database is on the local machine.
how to do it ?
View 2 Replies
View Related
Oct 20, 2015
comparing a value, my sql statement fails at the last bit i.e. )<>0I'm trying to compare the last three characters i.e. not equal to 0? error message
Msg 102, Level 15, State 1, Procedure StoredProcedure, Line 137
Incorrect syntax near '<'.
Basically I'm trying to validate the field name Code to have a letter followed by 3 numbers..
if LEFT (@Code, 1) NOT LIKE '[a-Z]%' OR ISNUMERIC (RIGHT(@Code,3)<> 0
View 2 Replies
View Related
Nov 18, 2015
I am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?
Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int
Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'
[Code] .....
View 3 Replies
View Related
Dec 2, 2008
Howto write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i don't want. everyday i need to search the data from yesterday whole day.
View 13 Replies
View Related
Nov 19, 2015
I need to retrieve list of all databases and the size occupied by them in sql server. As of now am pulling data for each database using sys.dqatabase_files which is hard for me as there are around 40-50 db on SQL Server.
View 4 Replies
View Related
Jun 17, 2015
CREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
[x_billable_yn] int, [x_bill_rate] int, [COST_TYPE] varchar(19) )
INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
[code]...
how to get records using stuff keyword as above i want to query using where condition with where objid=1 and should frame output as Parking, Toll only 1 input parameter need to given.
View 20 Replies
View Related
Jun 2, 2015
I need to show maxInspectionDate in my result but I can't figure out how to do it.
SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits
FROM Equipment E
WHERE EXISTS
(SELECT t.equipmentID, r.maxInspectionDate
FROM (
SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
FROM equipmentInspection
GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate)
View 9 Replies
View Related
Jul 22, 2015
I have a table with email addresses and CC_Flag.
Email | CC_Flag
xxxx 0
yyyy 1
zzzz 1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag. Is it possible to fill two variables from a single SQl Task.
View 8 Replies
View Related
May 28, 2015
The below is my query in SQL SERVER 2012
DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;
1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value
2. I have written the below query to return 0 when @PN_INC is null but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not null
SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
View 6 Replies
View Related
Oct 28, 2015
I totally forgot how can I make a percentage. Look at the code below:
create table #acca (name varchar(10), tipo varchar(10), lett int)
insert into #acca values ('Italy','Europe',15), ('France','Europe',10), ('Colombia','America',15), ('Cile','America',75)
select * from #acca
/*Query Number 1 */
select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon,
sum(lett) as Total
from #acca group by name,tipo,lett;
/*Query Number 2*/
with cte as (
select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon,
sum(lett) as Total
from #acca group by name,tipo,lett)
select name, tipo, lett/totcon from cte
Question query number 1: how can I retrieve the absolute total? Sum(lett) over what?
Question query number 2: why lett / totcon retrieves 0?
Question plus: is there a way to retrieve the percentage without using the cte?
View 4 Replies
View Related
Sep 16, 2015
After running a query (from the Query Builder) in SQL Server 2008 sometimes I can right-click on the results pane and "Save results as CSV file", other times it's not an option. After running a query for 24 hours (several million record results) I can't seem to do anything with the results. I have my settings:
Options | Query results | SQL Server | Default Destination for Results
set to "Results to File" and a path entered, but it doesn't work. Is there wording I can add to the end of my SQL statement such as "TO FILE xxx.csv" or something?
View 4 Replies
View Related
Aug 28, 2015
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)
[code]....
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc
[code]....
View 4 Replies
View Related
Jul 13, 2015
I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.
Select[Field1]
From
(
SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, *
From[dbo].[Table1]
) A
WhereID > 1
OrderBy [Field1]
Those highlighted in yellow colours are duplicate records, I need to remove one of them.
View 8 Replies
View Related
Jul 14, 2015
I am trying to run a SQL query from a Different Datawarehouse DB on the same server but getting error:
Msg 208, Level 16, State 1, Line 3
Invalid object name 'VirtualManagerDB.dbo.tbl_Cloud_CloudCapacity'.
My query is
select VM1.ID, VM1.VirtualCPUCount, VM1.Memory, VM1.Storage
from [VirtualManagerDB].[dbo].[tbl_Cloud_CloudCapacity] AS VM1
Am running above query in a different database: OperationsManagerDW
View 5 Replies
View Related
Dec 3, 2015
Is it possible to group the below code into an Island type scenario? The data represents people in a location. If they are in the same place one after another, to group and provide the min/max scenarios.
DECLARE @Table TABLE
(
PersonVARCHAR(10)
,LocationCHAR(1)
,Order_WhenINT
[code]....
View 6 Replies
View Related
Sep 21, 2015
I 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?
View 14 Replies
View Related
Oct 9, 2015
I have a table which contains a 2 columns "LoanID" and "LoanStatus" and the values in "LoanStatus" are either 0 or 1 in it. If I run a select statement; I get:
LoanID LoanStatus
1 0
2 1
3 1
4 1
5 0
I need a query to return the following from the LoanStatus (distinct Values):
1 NA
0 Current
-1 Late
View 8 Replies
View Related
Aug 2, 2015
I'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]....
View 6 Replies
View Related
Aug 28, 2015
Is it possible to pull name & email from Active Directory using SSMS w/o a linked server connection?
And this would be using SQL 2008
View 7 Replies
View Related
Sep 24, 2015
I need sql script where i will pass startindex, endindex and sortcol and order then query return result accordingly. it would be better if query looks small and dynamic.
Here one sample of dynamic sql but there case is used which is not require.
DECLARE @sql NVARCHAR(MAX)
SET @sql = ';WITH cte as(SELECT *,
ROW_NUMBER() OVER
(ORDER BY ' + CASE (@SortColumn + ':' + @SortDirection)
[Code] ...
My requirement is that i will create a sp where i will pass start index and end index and sort column,sort order and filter means where clause and sp will generate and execute dynamic sql and return data. i need a sample of that kind of sp.
View 9 Replies
View Related
Jun 18, 2015
I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID Branch Group Date Value
1 A C 2015-06-01 10
2 A C 2015-06-02 15
3 A C 2015-06-03 25
4 A C 2015-06-04 20
5 B D 2015-06-01 20
6 B D 2015-06-02 25
7 B D 2015-06-03 10
8 B D 2015-06-04 20
I want the Output like below with a Running value difference in comparison to previous day.
ID Branch Group Date Value Running Value
1 A C 2015-06-01 10 10
2 A C 2015-06-02 15 05
3 A C 2015-06-03 25 10
4 A C 2015-06-04 20 -5
5 B D 2015-06-01 20 20
6 B D 2015-06-02 25 05
7 B D 2015-06-03 10 -15
8 B D 2015-06-04 20 10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?
View 6 Replies
View Related
Oct 5, 2015
I have 4 different queries in one SSMS New query window that are returning expected results in 4 resultsets. However I want to output these results to a single .js file one after the other in the order of queries. Is that possible?
View 7 Replies
View Related
Jul 29, 2015
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
View 3 Replies
View Related
Jun 9, 2015
When I am doing the divide all values are showing Zero.
DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample
and also I want to show the QTR with %, for example, 66.57%
View 6 Replies
View Related
Sep 2, 2015
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
[Code] ....
Desired Result:
esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1
OR
esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap
View 5 Replies
View Related
Aug 6, 2015
This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00
Declare @goodtries as int, @badtries as int
select @goodtries = convert(decimal(18,4),count(userID))
from table1
WHERE logintype IN ('Valid', 'Success')
select @badtries = convert(decimal(18,4),count(userID))
[code].....
View 7 Replies
View Related
