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 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
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)
I am having problems building a CTE. I have a table:
declare @tbl table ( ID int, S time(0), E time(0) )
with the following data:
insert@tbl ( ID, S, E )
values ( 1, '03:00', '05:00' ),
( 1, '04:00', '06:30' ),
( 1, '22:00', '23:00' ),
( 1, '23:00', '23:40' ),
( 1, '23:30', '23:45' )
I need to build a CTE that will eliminate overlaps and produce the following result:
ID S E
-- --------- ---------
1 03:00:00 06:30:00
1 22:00:00 23:45:00
I have a scenario where I need to develop a stored proc to identify invalid input provided.
Following is a sample scenario
Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10));
insert into Product
select 'Prod1',10,'Black' union ALL
select 'Prod1',10,'BLue' union ALL
select 'Prod2',20,'Green' union ALL
select 'Prod2',10,'Black' ;
[Code] ....
In following TSql Code , Color and Size are optional. Both are provided as comma separated input. I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.
I had tried out join but it is not serving needs.
---===========================================
-- Sql
--============================================
DECLARE
@ProdId varchar(10),
@color varchar(max) = Null,
@size varchar(max) = Null
BEGIN
set @ProdId='Prod1';
[Code] .....
I have some data in the following format;
MYTABLE
DOC_NO // REV_NO // FILE_NAME
ABC123 // A // abc123.pdf
ABC123 // B // abc123_2.docx
ABC124 // A // abc124.xlsx
ABC124 // A // -
ABC125 // A // abc125.docx
ABC125 // C // abc125.jpg
ABC125 // C // abc125.docx
ABC125 // C // -
ABC126 // 0 // -
ABC127 // A1 // abc127.xlsx
ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE]
WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN
(SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
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 would like to build a report with nice functionalities like filter, sorting, drill-down, something like a PowerPivot Table, but with some layout/design/format capabilities. I would also want to publish the report, refresh it let´s say once a week, notify users when a new version is available, etc.
If I use PowerPivot, then I am not able to customized the report or to mix data from different sources in one table.
If I convert the cells of the PowerPivot table to workbook formulas I lose the filter, sorting, etc functionalities.
I still have to try using Reporting Services, but I think that always something is missing.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
I am trying to add to a query to produce a report in Apex. Currently I have this
SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,
[Code] ....
I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?
join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
[Code]....
Aim –To produce a unique line by month for the FDMSAccountNo
I have created the following query (see below) which produces the following result set
FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000
SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate
[Code] ....
Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08
[Code] ....
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location
location shows the dif cities and the sql query will show cities will a count of people per city
how do you do this?
I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.
I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...
exec sp_QueryInvalidForNotification "exec myTestSproc 99"
Does such a sproc exist? Or does anyone know how to write one?
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
I am working on a scenario where I would need to repeat the same row for previous MonthIDs, here's the example (snapshot attached):
CustomerIDMonthIDRSCIDFlag1Flag2Flag3
1232943456000
1233001234011
1233033542010
1233032345111
1233043542010
1233042345111
1233053542010
1233052345111
1233062345111
1233063542010
1233072345111
1233073542010
1233083542010
1233093542010
1.For MonthIDs 309 & 307, I would need to add same combinations to 4 prior months (for 309, I would need the 309 row as 308, 307, 306, 305 and for 307, I would need to add 306, 305, 304, 303).
2.From the snapshot, Green rows are added for 309 MonthID and Orange rows are added for 307.
3.While we are adding 309 row to prior to 4 months, if any prior month has different combination than 309, that same combination need to be repeated for prior months.
I was using the below code to achieve the same:
declare @SC_j as int = 0;
declare @SC_k as int = 4;
while @SC_j <= 4
begin
insert into #Test
select distinct dt.CustomerID
[code]....
But my code starts from 1st MonthID however I would want this to be starting from last MonthID which I am not able to achieve.
and how can it be done?
View 1 Replies View Relatedcreate table #t1 (id int)
create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
I've the following two tables:
create table #temp1 (name varchar(5), id int)
insert into #temp1 (name, id)
(
select 'a', 5
union
select 'a', 8
[code]....
As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...
Selectdistinct b.name
from(
Selectdistinct name
, id
from#temp1
wherename = 'a'
) as a
cross join(
Selectdistinct name
, id
from#temp2
) as b
wherea.id = b.id
Hi
I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this.
Below is the exception from my log file
The cursor type/concurrency combination is not supported.
com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source)
The following is the piece of code where the problem I'm assuming is happening, how can I correct it.
varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Have tried using both JDC v1.1 and 1.2 but of no use.
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
Below is what I have so far Also I am getting other FRID besides 0 as well.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V
On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C
[Code] ....
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View RelatedI have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .
CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)
[code]....
I work for a federal government client who restricts us to using virtual machines for dev work. Now the VM team has gotten MUCH more aggressive with "right-sizing" (which always means down-sizing) our virtual machines. I have a Dev server with 8 GB RAM and I need that much when developing, but I don't develop all the time. So I want it to look like it needs all that RAM before they bust me down to 1 GB, which they will do.How can I get SQL Server to use up all the memory I've allocated to it? Is there a relatively easy query to do this?
View 8 Replies View RelatedI have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.
View 9 Replies View RelatedFrom MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2.
Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause.
@p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401.
if object_id('tempdb..#tbl') is not null
drop table #tbl;
create table #tbl
(
[No] nvarchar(20)
,[Description1] nvarchar(250)
[Code] ....
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead