Below query:
Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)
Insert into @table
Select 1,123
union all
[Code] ...
--Expected output concatenation of consumer numbers all permutation and combinations having same
customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7
I have one table and I am trying to accomplish two tasks, generate all possible combinations and the probability of these combinations occuring. My sample data is below:
Time of Incident
7AM
8AM
9AM
10AM
11AM
12PM
1PM
2PM
3PM
4PM
5PM
6PM
7PM
Location
Off-Site
OnSite PC
Off-Site Virtual
IssueStatic
CR
CR Unable to Hear caller
Caller Unable to Hear CR
Garbled Speech
Echo
Call disposition
Able to Handle Call
Caller Initiated Disconnect
Transfered to 5508
Transfered to CCV
Provider
AT&T U-verse
Cox Cable
Mercury
Time Warner
VA Network
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),
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 RelatedIm trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output
select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate
[Code] .....
how this can be achieved by writing a procedure in SQL Server DB.Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.
Hosting_Site (Source File)
YEAR MONTH HOSTING CLASS REGION HOSTING COST
2015 1 A UK 75
2015 1 A IN 80
2015 1 B IN 60
2015 1 C US 100
BENCHMARK (Lookup Table)
Row Year Month Hosting class Region BENCHMARK COST
1 2015 1 A US 100
2 2015 1 B US 200
3 2015 1 A UK 50
4 2015 1 A null 70
STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)
Row Year Hosting class BENCHMARK COST
1 2015 A 22
2 2015 B 33
Then result should be:
FACT LOAD:
YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST
2015 1 A UK 75 50
2015 1 A IN 80 70
2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used)
2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
Hello everybody. When I start to test SQL 2008 Febuary CTP I detect that sume of new options work incorrectly. This is description of this bug from Microsoft Connect:"I don`t know, may be somebody detect this issue too. Sorry If I create duplicate. I start SQL Server Managment Studio & click new query. After that I try to create simple query like this
Uses Master
Select "column name" from "table".
I was surprised because when start write column name I saw the pop-up objects list. But this was very strange list, because I didn`t find necessary columns. This list show only "common" objects(such as database nemes, functions & ect). I think that such list(after first select) should show only children objects(columns, keys, triggers, indexes & ect.), after that is you write "from" such list should show only parents objects (Such as table name, database name & ect).
PS. Sorry for my english." After that(today) I get the answer from MS SQL Team "Hi,
Your observation is correct. Selection list (column names) can suggests correct candidates once user specifies FROM clause.
Regrads,
Eric Kang
SQL Server Program Manager" & bug was closed as "By Design". Then I reopen this bug with such comments "
But It`s lokks very strange. If I want to select column name from the list I should: 1. write SELECT 2. Press Space 2. Write FROM "Table name" 3. Returt to free space after SELECT 4. Start Write "Column name". 5. Choose the necessary Column name from the list. There are too much for such simple operation.
Posted by stanisluv on 3/19/2008 at 11:45 AM
Too much steps." Sorry I make a typo. I mean: "looks". If anybody else argee with me please validate & vote the feedback. This is the link for it: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=333047
I have [TableAccount.AccountType] field which can store these values:
Value
C
S
E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
the field "Expertise" contains available options "Accounting", "Language", "Engineering" and so on. How do I list all the available options for the field "Expertise" in ms sql 2014?
View 5 Replies View RelatedI want to priorities three items one by one every next day Item1, Item2 and Item3.For example:
Feb 01, 2015: Item1, Item 2, Item3
Feb 02, 2015: Item2, Item3, Item1
Feb 03, 2015: Item3, Item1, Item2
Feb 04, 2015: Item1, Item2, Item3
i have a table like below
CREATE TABLE #Test (FromDate DATE,ToDate DATE)
insert into #Test VALUES ('2015-08-08','2015-08-11')
insert into #Test VALUES ('2015-08-13','2015-08-16')
insert into #Test VALUES ('2015-08-19','2015-08-21')
SELECT * from #Test
drop TABLE #Test
i need to display the dates as single column between from and todate.my expected result is like below
CREATE TABLE #Result (ResDate DATE)
insert into #Result VALUES ('2015-08-08')
insert into #Result VALUES ('2015-08-09')
insert into #Result VALUES ('2015-08-10')
insert into #Result VALUES ('2015-08-11')
[code]....
I wondered if it is possible to convert/transpose a list into a 1 row. I have attached a small sample of data and what outcome I would like.
View 19 Replies View RelatedI am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies View RelatedHi,
I have a table with 2 columns (S.No,Name) with rows
1,Raja
2,Ramu
3,Rane
I need to generate all 2 pair combinations of the names(i,e)
(Raja -Ramu)
(Raja -Rane)
(Ramu -Rane)
Can some one give me a generalized query for this problem?
Thanks,
Prakash.P
I need to build a reference table that will contain the time zone, offset value and DST changes for all time zones. Is there a way I can pull this from the windows server hosting sql server?
View 4 Replies View RelatedI would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?
View 21 Replies View RelatedI have a replace statement like the following:
select (replace('FMG','FMG','FM'))
So this is straightforward as it will replace the word 'FMG' with 'FM'
However, SSRS is passing a comma separated list like this:
select (replace('FMG','AFM','FMG','FM'))
And what I need to do is replace the comma separated list of 'FMG','AFM' only. I tried this:
select (replace('''FMG','AFM''','FMG','FM'))
But it still complaining about syntax errors. How do I get the comma separated list to be seen by the replace function?
I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.
I'm not clear on what all could be the possible key / value combinations for the SetComponentProperty Method. From the examples I have seen its either SqlCommand or OpenRowSet. But I'm not sure about the "AccessMode" with values either 0 or 2. Is there any reference where I get more information on these?
Code Block
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand", "Select * from devdb..empl_karun");
Thanks
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int
set @compare = 8
declare @table table
(
Number int
)
insert into @table
values(1),
(2),
(3),
(4),
(5),
(10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
I have a requirement, we need to pass comma separated list using powershell script.
How can we achieve the above scenario?
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL
SELECT '2015-06-13' UNION ALL
SELECT '2015-06-14' UNION ALL
SELECT '2015-06-15' UNION ALL
SELECT '2015-06-16' UNION ALL
SELECT '2015-06-22' UNION ALL
SELECT '2015-06-23' UNION ALL
SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL
SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL
SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
I have two tables and a CTE table.
-Shop : list of all shops with an ID_SHOP
-Contract : list of all contract with a FK to the shop FK_SHOP. A shop can have multiple contracts
I have a cte where I calculate for each FK_SHOP the maximum contract start date. I now want to join my shop table with the contract table but only for the contracts >= their maximum contract start date.
WITH cteContracts AS
(
SELECT
FK_SHOP
,ID_CONTRACT
,MAX(cont.DTT_START_DATE) DT_STARTDATE
[Code] ....
I want to start with a table that has 4 records:
-Self
-Supervisor
-Peer
-Direct Rep
And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)
-Self , Supervisor
-Supervisor , Self
-Self , Peer
-Peer , Self
-Self , Direct Rep
-Direct Rep , Self
-Peer , Direct Rep
-Direct Rep, Peer
How would I do this in an SQL Query? Thanks for your help!
The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.
Here I wrote a query which selects top 1 only.
Please help me to write to get the top 2nd along with the top 1st row.
select A.cust_alias_nm_id,
A.carrier_cd_id, A.acct_nbr_id,
sum(A.pd_clm_amt) clm_amt,
sum(A.pd_med_amt) med_amt,
sum(A.pd_exp_amt) exp_amt,
A.lst_updt_timestamp
from bal_load_stg A
group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp
having A.lst_updt_timestamp
in (
(select max(lst_updt_timestamp) from bal_load_stg B
where
A.cust_alias_nm_id = B.cust_alias_nm_id
and A.carrier_cd_id = B.carrier_cd_id
and A.acct_nbr_id = B.acct_nbr_id))
The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
The desired output should look like:
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
Thanks in advance.
I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
Thanks in advance
I.Shaame
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do
select distict One, Two from MyTable
How do I return the count of such unique combinations? The following of course does not work but you get the idea.
select count(distinct One, Two) from MyTable
Thanks.
i have the following:
DECLARE @Table TABLE (
OrgRoleNumTxt VARCHAR(10)
, AccountNm varchar(100)
, EffectDate DATETIME
, OperationNm varchar(100)
, Premium decimal(18,2)
)
Insert into @Table(OrgRoleNumTxt, AccountNm, EffectDate, OperationNm, Premium) VALUES
('00236', 'R.R. Donnelley', '2010-01-01', 'Chicago', 1000),
('00236', 'R.R. Donnelley', '2010-01-01', 'Boston', 3000)
select *
from @Table
but want this
Is it possible using basic T-SQL?
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
I am searching for the Sql Server Script of all country State and City with the Following type..
For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)
For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)
For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).
I need this type of Script with Column name.
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;
Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
how to find all possible combinations of values, for example:
My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);
I need the output to be like this:
apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry
I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.