I have attached some test data for you that has two temp tables "#worker" and "#worker_rate".
The issue is to find all workers who are sharing SAME SET of rate_codes.
I'm able to get the output as "workers sharing same rate_codes", but unfortunately I could not get the list of workers sharing same SET of rate_codes. Also definition of SAME SET is not defined.
I don't know what I'm missing.
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
I have this query to select the highest currency rate from a currency table:
SELECT
Target_currency AS [Currency]
,rate_exchange AS [Rate]
,date_L AS [Date]
FROM
rates
WHERE
target_currency IN ('USD','GBP','JPY')
ORDER BY
target_currency desc
,date_L desc
My output is something like this:
CurrencyRateDate
USD 1,13572015-06-20 00:00:00.000
USD 1,37952014-03-31 00:00:00.000
USD 1,28352014-03-24 00:00:00.000
USD 1,28522013-04-05 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
JPY1202014-03-24 00:00:00.000
JPY119,32013-04-05 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000
GBP 0,08482014-03-24 00:00:00.000
GBP 0,084392013-04-05 00:00:00.000
How can I get the top 3 of the most recent rates from every rate in the WHERE filtering?
So:
USD 1,13572015-06-20 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000
I have tried many solutions, but I can't get it to work.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
I have a SQL Server hosted on a Win2003 server. I have a monitor on the server letting me know the average download speeds. When I do a large select from a remote connection, it looks like the download rate maxes out at around 10 kb/sec.
Is this an average download rate for a server hosting sql server?
Hi all
In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000.
So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects.
for better illustration i have created a Flash Movie from this process , please check the following Link :
http://www.Parsisoftco.com/movie/Sql/Sql.htm
How can i prevent others from doing so?
Any help greatly Appreciated.
Kind Regards.
I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.
I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.
create table DimDate
(
DateCol date
)
create table TotalsData
(
DateCol date
[code]....
I have a table (Event_Table) like:
EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02
And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:
EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11
Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)
Need to find the list of objects that are referenced from the outside of current database. Example
Use TestDB1
Create Proc TestProc as begin
select * from TestDB1..Sysobjects
select * from sys.indexes
end
Need a query which able to return the following
LinkedServername, Databasename, TableName
null ,TestDB1 , Sysobjects
how would I compare a list of concrete values?
---table with items
SET NOCOUNT ON;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8'
SELECT * FROM @items
-- table with categories
SET NOCOUNT ON;
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON;
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON;
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
[code]....
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
I setup data collection on a production server to capture growth rates.
When I run the dis usage report, it shows a daily growth rate of over 500 megs. This seems excessive to me.
As a troubleshooting step I then ran sp_space used and got these results:
database_namedatabase_sizeunallocated space
rgc_prod 273442.63 MB3648.48 MB
reserved data index_size unused
265345488 KB164385384 KB99826072 KB1134032 KB
What should my next steps be to try and determine why there is so much growth? And isn't the index size rather large?
Hi all
In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000.
So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects.
for better illustration i have created a Flash Movie from this process , please check the following Link :
http://www.Parsisoftco.com/movie/Sql/Sql.htm
How can i prevent others from doing so?
Any help greatly Appreciated.
Kind Regards.
I have database tables for
Stores
StoreId, Name
Products
ProductId, Name
Transactions
TransactionId, StoreId, ProductId
I was just given an excel file with a list of 300 Stores.
I need to find out if these stores are selling our products and if they are , how many products they are selling.
One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.
Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.
I have a Games table in a SQL Server 2012 database with game results from our league, including home and away scores, dates, and teams. How can I query to list streaks - that is, wins or losses of 2 more games in a row throughout a season?
View 6 Replies View RelatedI am trying to create a comma delimited list of InvNo along with the JobNo .
CREATE TABLE #ListString
(
JobNo VARCHAR(10),
InvNo VARCHAR(MAX)
)
INSERT INTO #ListString ( JobNo, InvNo )
SELECT '3079', 'abc'
[Code] ....
I have a requirement for SSRS where the input has the following structure:
Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu
but the user would like to see the following:
Store Number
I have a requirement for SSRS report where part of the input has the following structure:
Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu
but the user would like to see the following:
Store Number Store Owner
542 Jaklin Givargidze, Raymond G. Givargidze
557 Hui Juan Lu, Tong Yu Lu
I am sure that this can be coded, just don't know how. I believe that proper term is to "serialize" the values.
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
Got following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
Trying to build a list of order numbers based on stock availability.
The data looks something like this:
OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
Hi,Is there an easy way to determine the rate of change for table? I realizethat I could do periodic select count(*) from XXX, but that would onlydetermine the size of the table. It would ignore updates.What I'd really like to do would be able to determine the number of non-readoperations over a table so that I can determine the rate of change for thattable.Thanks for any help..
View 1 Replies View RelatedHi All,
I would like to print out a dataflow's tarnsfer rate (row/sec) to the output window and refresh it in each 5. second. Could anybody help me?
Thanks in advance.
m
Hi,
I have this specific problem, that has been causing me alot of headaches for the last couple of days, and I can't seem to fiure it out on my own.
Basically i am working on a many to one currency conversion setup, where I have a fact table with exchange rates for periods and for all input currencies. The reporting currency is USD. Here is a demonstration of the data in it:
Valid Date Source Currency Rate
2008-01-01 GBP 2.0563
2008-01-01 EUR 1.4634
2008-04-01 GBP 1.9968
2008-04-01 EUR 1.5197
Normal period by period conversion is easy enough, but I want to convert the measures at the latest known rate, based on the period selected. Lets say I run an analysis without slicing on time, then it want it to convert at the last entered rate of all time, and if the user wants to run the analysis as of March 08, then it should convert all data to the rate as of January, since this is the latest one known.
I have tried to set the measure to a semi-additive behaviour, with "lastnonempty", but the problem with that is the conversion over time. Here is an example:
I want to show a measure summed "all to date" as of June 08. What I want is it to convert everything at the rates entered in April 08, but what I get is that it convert everything up until Jan 08 and the rates entered in January, and thereafter it starts to use the rated entered in April.
Period Amount LCY Currency Code Exchange Rate
Dec 07 100 GBP 2.0563 (Wrong, should be 1.9968)
Dec 07 100 EUR 1.4634 (Wrong, should be 1.5197)
Jan 08 100 GBP 2.0563 (Wrong, should be 1.9968)
Jan 08 100 EUR 1.4634 (Wrong, should be 1.5197)
Feb 08 100 GBP 1.9968
Feb 08 100 EUR 1.5197
Mar 08 100 GBP 1.9968
Mar 08 100 EUR 1.5197
Apr 08 100 GBP 1.9968
Apr 08 100 EUR 1.5197
May 08 100 GBP 1.9968
May 08 100 EUR 1.5197
Jun 08 100 GBP 1.9968
Jun 08 100 EUR 1.5197
How do I create a measure containing the latest known rates based on time selection?
Thanks in advance
I have a task to provide users with a list of currency rates in the following format:
Base Currency
Hedged Currency
Base to Hedge Rate
Hedged to Base Rate
E.g.
"Base Currency"..."Hedged Currency"..."Base to Hedge Rate"..."Hedged to Base Rate"
GBP....................USD.......................1.70.........................0.59
GBP....................EUR.......................1.25..........................0.80
(the dots are for the visual only)
The table I have been provided in SQL has the following structure:
Currency Code
Currency Rate (Rate against the Base Currency)
Base Currency (BIT to recognise which currency is the base one as it can vary from site to site. Only once currency can be Base per a given site)
I have started to implement, but ended up with cursor and temporary table, where I insert the data.
I would like a single SQL to return all employee's total billablecompensation for a year. Their billable rates change throughout the year sounder the employee table (one), there is a compensation table (to many)which has the employee id, effective date, billable hourly rate. So in agiven year calendar year they could have many different (though usually 2 atmost) rates. These rates then have to correspond to and e multiplied bytheir corresponding billable hours from the time sheet table.I know I could create a series of UNIONs and hard code the effective dates,i.e.select from time sheets where employee=john and timesheet.task_date betweenjan 1 and jun 1, compensation.billable rate * timesheet.billable hoursUNIONselect from time sheets where employee=john timesheet.task_date between jun1 and dec 31 compensation.billable_rate * timesheet.billable_hoursI'd have to do that for every employee in a very large SQL.Is there an easier way using straight SQL? If not could it be done with astored procedure?Thanks for any insight.
View 2 Replies View RelatedOk so I created a SSIS package to get currency exchange rates from web service - http://www.webservicex.net/CurrencyConvertor.asmx . Everything works fine I can run it successfully - i uploaded to SSIS package store - runs successfully./
BUT when I put it in a job - it FREAKING fails every *dem time.
In addition to run it the JOB as SSIS package from store - also straight from the File System = still same freagging error - and SQL 2005 has gotten more stupid than SQL 2000 & DTS - they provide no clue as to y you get an error
Help please?
How to create a script to input one table name(already indexed) to get list of all index for that table?
View 5 Replies View RelatedSupposing there are three Columns: Countries, Provinces and States.Then when the user enters “USA” for country, it will bring up a list of states, if they enter “Canada” for country, it will bring up a list of provinces, if they enter another country, it will not bring up any list?
View 2 Replies View RelatedI would like to replace mymail@mail.com by mypdl@mail.com , where mypdl is basically a people distribution list with 2 emails: my own email and my coworker and fellow DBA.
IF EXISTS
(
SELECT TOP 1 [PercentUsed]
FROM [BackupSize]
WHERE PercentUsed>=70 AND [drivename]='D:' -- D is the internal drive
ORDER BY [MetricDate] DESC
[Code] ...
But I think this won't work by design. Maybe I need to create an operator?