I have written some SQL that relates to the (partial) result set below -
I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.
My query looks like the following -
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
[Code].....
Why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.
My query looks like the following -
Code:
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
if @whCode = '02'
set @binLoc = 'S%'
[code]....
why my results are doubling up in the two indicated columns.
DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)
SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'
SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________
This is part of a bigger project but I am stuck on this part. I get back 2 result sets
Data Data
101 2013
102 2014
103
104
105
I want to insert the results in a new table 2 columns and get the results below.
New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
How can I get the results of this query>>
SELECT
RoleName = pr.name
,RoleType = pr.type_desc
,PermissionType = pe.state_desc
,Permission = pe.permission_name
,ObjectName = s.name + '.' + o.name
[Code] .....
WHICH RESULTS IN THIS>>
RoleNameRoleTypePermissionType PermissionObjectNameObjectType
publicDATABASE_ROLEGRANTDELETEdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTINSERTdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTSELECTdbo.AgencyUSER_TABLE
[Code] ....
best way to snapshot results from a query?
View 3 Replies View RelatedI have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.
The table is holding the path like this "/folder/folder/file"
I am trying to convert that path to "folderfolderfile"
In my Select statement I have
SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah
The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:
DECLARE@return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:
1. How can I have the stored procedure return distinct rows?
2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".
when I change the sql above to:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName
That is when I get the error message.
A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.
I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.
Perhaps using the datediff() function, or other functions as required to achieve what I need?
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
I've got a query that returns the data I need. I want to put the query in a stored procedure such that, when the SP runs I get a pipe delimited text file on disk. I don't really want to mess with SSIS, etc. Is there a Q&D way to do this?
View 1 Replies View RelatedI am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
I have a report that looks like the following
NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65
HOWEVER I need it to look like the following:
NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65
The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.
Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.
I am currently working on extracting data from a text file and loading into sql server.I have a table with 1 column and the data in the column is in this format:
TableName1
A
1
10
TableName2
D
2
20
I want to convert this to 2 columns in this format:
ColumnA ColumnB
TableName1 A
TableName1 1
TableName1 10
TableName2 D
TableName2 2
TableName2 20
I've the table structure below. Example table of my original
create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime
[Code] ....
Expecting Output :
Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500
How to do that?
I tried like
select
k.fs_acoounts,
k.200Prod,
(
my query
)k
its showing error '200prod'
I want to display full name column as FirstName and LastName.
Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav
i.e. Before Space is FirstName and After Space is LastName.
I have a query (SELECT * FROM Pricing) that produces the following results
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23
I want to convert this to the following :
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23
We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23
Hi ,I need to place the results of two different queries in the same resulttable parallel to each other.So if the result of the first query is1 122 343 45and the second query is1 342 443 98the results should be displayed as1 12 342 34 443 45 98If a union is done for both the queries , we get the results in rows.How can the above be done.Thanks in advance,vivekian
View 7 Replies View RelatedHi,
I have following data coming from prior transformations.
Id Unit Name NewFlag
___ __________ ____________
1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0
and so on.
I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.
Just wondering if it is possible by using Aggregate and how.
Thanks for your help in advance,
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.
Suppose a table being
Create table myTable (ID int, col1 int, col2 int)
I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2".
Getting the average would be "computed_column = (col1 + col2)/2" But how to get the Max, Min?
Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...
I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list
--SQL CODE IS AS BELOW
WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken
[code]...
I have a table with the following columns
Num,ID,Pos,Value
74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo
I am trying to get the result as follows.
Num Id Final Value
74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo
I tried to use Stuff and XMLpath but it is not giving me distinct results.
T1
-------
a1
a2
datetime
a4
a5
.....
i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. for every specific a1 there is many entries of a2 + timedate etc.
create table abc_test (
id int
,runs int
,date1 datetime
[code]....
I can either get distinct ID + latest date or ID + largest #ofRuns, both will do but also need the third column.
I keep thinking this can be done in one query:UPDATE H
SET Phone1 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone1 = D.Phone;
UPDATE H
SET Phone2 = NULL
[Code] ....
I need to NULL any phone number in the Hold table where the number is in the Lookup table.
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
SELECT 'Total' AS Dept,
[A], [B], [C], [D]
from
[code]....
I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.
How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.
SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',
[Code] ....
How to get the lowest U.Price along with FX and Rate.
ID-U.Price-FX-Rate
1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00
Required output.
ID-U.Price-FX-Rate
1280 6.32 RM 1.00