Combination Of Columns Not In Another Table

May 13, 2014

I want a list of rows in one table where a combination of two columns aren't in another table. One column is character and the second column is date/time. This happens to be SQL 2005. My first attempt using only one column worked OK:

SELECT * FROM TABLEA
WHERE CUSTID NOT IN (SELECT CUSTNO FROM TABLEB)

But when I added the combination of customer number and sales date, it made the query "run away" - I had to cancel it and it didn't return anything:

SELECT * FROM TABLEA
WHERE CUSTID + CONVERT(VARCHAR, SLS_DATE, 101) NOT IN
(SELECT CUSTNO + CONVERT(VARCHAR, SALES_DATE, 101) FROM TABLEB)

I wonder what might be wrong, but also whether this is the correct approach to begin with.

View 3 Replies


ADVERTISEMENT

Help Me Getting A Unique Combination From Table

Jun 18, 2004

Hi, I have following table

BetId GameId
==== ======
500 108
500 109
501 108
501 109
501 110
502 108
502 109

My query would have the form: select BetId where GameId in(108,109)
from Bets then it has to get me BetId : 500 and 502.
Not 501,since this is different combination(108,109,110) ;)

View 3 Replies View Related

Permutation And Combination Between Source And Lookup Table?

Jun 21, 2015

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)

View 13 Replies View Related

SQL Server 2008 :: Compare If Row-Combination Exists In Other Table

Mar 10, 2015

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

View 3 Replies View Related

How To Fetch Record From Temp Table - Unique Combination

Sep 23, 2014

Table :StudentTeacherRelation

Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi

Second table : Student

Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3

I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come

Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi

I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query

select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1

I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.

show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)

So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?

View 1 Replies View Related

Transact SQL :: Comparing 2 Records / Multiple Instances In The Same Table For A Specific Combination?

Jun 10, 2015

I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.

Givens: All SQL Server 2008 or 2012 tools at your disposal.

Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.

-- A player of our game

-- Table greater than 10 million rows

CREATE
TABLE [dbo].[User]
(
[UserId]              
[bigint] NOT
NULL  
,[country]             
[int] NULL   
-- User’s home country
,[name]                
[nvarchar](15)
NULL  -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]    
[int] NULL
)
-- 0 == free, 1 == paid, for instance

Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.

Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation). 

For instance, if the sample data were:

-- example of [Events.UserLogon] data  -- not the query output we want

EventId     UserId               TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1           1               1000             2010-10-16 00:19:56.460
2           1               1500             2010-10-16 00:20:56.460
3           1               3000             2010-10-16 00:21:56.460
4           1               10000            2010-10-16 00:29:56.460

Event 1 is okay because there’s nothing to compare it against

Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute

Event 3 should be flagged, as the value grew 1500 in a minute

Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)

Query Output (your query should return data in a format like this):

User      Flagged Logon Time    Rate Since Last Logon (money/minute)
John      2010-10-16 00:21:56   1500
Dave      2010-10-16 00:30:50   3200
Bill      2010-10-16 00:35:23   1000

It is likely that you will need to create sample data for both the User and [Events.Logon] tables.  We are looking for a single query that returns data like what is represented in Query Output.

View 3 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Dumping Rows Of 2 Columns Of A Table To A Different Table With Different No Of Columns

Sep 19, 2007



Hi,

I have two tables

Table Source
{

category varchar(20),
LastUpdate datetime
}



Table Destination
{


SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}

Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.

Is there anyway to bulk insert into these columns.


Your quick response will be appreciated

~Mohan Babu

View 2 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

Matching A View's Columns To It's Underlying Table's Columns

Jul 20, 2005

Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name

View 2 Replies View Related

Insert Into Combination

Jul 11, 2006

I would like to select data from two different fields within the same row and insert them into a new table in different rows.

For example:

Source table contains the fields: DateTime, FirstValue, SecondValue

Destination table would have the fields: DateTime, Value

Sample source data:

2006-01-01T00:00:00, 25.OO, 20.OO

2006-01-02T00:00:00, 28.00, 24.OO

Sample destination data (inserted):

2006-01-01T00:00:00, 25.00

2006-01-01T00:00:00, 20.00

2006-01-02T00:00:00, 28.00

2006-01-02T00:00:00, 24.00

Is this doable in T-SQL?

View 3 Replies View Related

Combination Graphs?

Jun 11, 2007

Are combination graphs possible in reporting services?

View 2 Replies View Related

Mapping A Table (10 Columns) To A Table (100 Columns)

Apr 4, 2008

I'm in the process of converting legacy DTS packages to SSIS. I need to populate a table that has more fields than the source file. In DTS I did this with an ActiveX script. How do I go about doing this within SSIS.

In the ActiveX script most of the fields were defaulted with either spaces or zeroes.

One of the Destination fields needs to be incremented by 1 for each new record inserted.

Any help would be appreciated.

Thanks,

Jeff

View 6 Replies View Related

How To Get Unique Combination Of Rows

Jun 18, 2004

Hi,
Following is my table:Bets

BetId GameID

500 108
500 109

501 108
501 109
501 110

502 108
502 109

I want BetId 500 and 502 to be returned as result if i give select
criteria where game id = 108,109.
Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110).
Similarly if i give, select criteria where game id =(108,109,110) it should return
BetId 501.not the 500 and 502..which is different combination..

Hope i clarified my problem..pls help me in this regard.Thanks a lot...

View 2 Replies View Related

Query For Permutation And Combination

May 12, 2014

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

View 9 Replies View Related

Create View With Field Combination

May 30, 2004

Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%

So, I always get a message error said wrong datatype, how can i do, please help me.

View 2 Replies View Related

SQL Server 2014 :: How To Repeat The Same Row Combination

Jan 23, 2015

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.

View 9 Replies View Related

T-SQL (SS2K8) :: Find Same Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
( nRef INT,
nOrd INT,
Token INT,
nML INT,
nNode INT,
sSymbol VARCHAR(50),

[code].....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed.

Hence the desired output will be

Symbol Count
Castorseed-Silver 8

How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 1 Replies View Related

T-SQL (SS2K8) :: Generate Combination Of Numbers?

Jun 19, 2015

I want to get "all combinations of the same count" for provided list of numbers.As an example, if I have a string 'NA,T1,T1a'

For this string, I want to generate following:

NA,T1,T1a
NA,T1a,T1
T1,NA,T1a
T1,T1a,NA
T1a,NA,T1
T1a,T1,NA

query to achieve the same or if the solution is already available, provide me with the links.

View 7 Replies View Related

SQL Help Needed - Popular Combination Problem

Oct 28, 2007

For a class, I need to write SQL statements for an assortment of problems and I'm stuck on the last few:

What is the most popular combination of 2 products that are ordered simultaneously? The most popular combination is defined as follows: Consider any combination of two products such as (LS200A, ATM50A). Looking at all orders, count the number of times these two products were present in an order. An order could of course have other products in it than just these two, so you are not limited to examining only orders with exactly two products. If you repeat this process for all possible combinations of two products, then that combination which was present the highest number of times in all orders is the most popular combination of two products that were ordered simultaneously. The quantities ordered are not relevant in this context.

The result of the query should look something like the following:

First Product ----- Second Product ------ Max Number of Times
--- LS200A --------- ATM50A ------------------- 7 -----------

I know little to no SQL, just the basics of it and my professor is pretty terrible at teaching it. I'm thinking I'll need to make a virtual table with a CREATE VIEW but I'm not sure where to go after that. I also need to write statements for the most popular combination of three products. If anyone could give me a hand, I would really appreciate it. Thanks a lot.

View 1 Replies View Related

Counting Based On A Field Combination

Jul 23, 2005

Having a brainfart....I need a query that returns a record count, based on two distinct fields.For example:Order Revision Customer001 1 Bob001 2 Bob002 1 John003 1 John004 1 John005 1 Bob006 1 Bob006 2 BobThe query on the above data should return a count of orders, regardless ofthe revision numbers (each order number should only be counted once).So WHERE Customer = 'Bob', it should return OrderCount = 3TIA!Calan

View 2 Replies View Related

CONTAINS And WHERE Clause Combination Taking Too Long

Aug 21, 2007

Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3
FROM

tbl
WHERE

col1 = '1234'
AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

View 4 Replies View Related

Simple Fiedls Combination Question ???

May 9, 2008

Dear all,

I have the follwing procedure:



Code Snippet
ALTER PROCEDURE [dbo].[Read_Current_User]
-- Add the parameters for the stored procedure here
@LoginUserName varchar(50) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- get current user name
SET @LoginUserName=(SELECT [NomosConfig].[dbo].[Users].[UserName]
,[NomosConfig].[dbo].[Users].[Id]
,[NomosConfig].[dbo].[Users].[FirstName]
,[NomosConfig].[dbo].[Users].[LastName]
,[NomosConfig].[dbo].[Users].[Email]

FROM [NomosConfig].[dbo].[Users]
WHERE [NomosConfig].[dbo].[Users].[LoggedIn]=1


END




How can I set my @LoginUserName output parameter to be a combination of fields [NomosConfig].[dbo].[Users].[FirstName] + [NomosConfig].[dbo].[Users].[LastName] ?

As a result I should get @LoginUserName = FirstName + ' ' + Lastname


Thanks
regards
serge

View 4 Replies View Related

GETDATE.In A Combination Of Year And Month

Mar 7, 2008

I am trying to calculate the work experiance of an employee. I am using the function DATEDIFF for that.
For eg:




Code SnippetDATEDIFF(yy,JoiningDate,GETDATE())








But this will display the date difference in years, if I use mm, instead of yy then I will get the result in months.

But I want to dislay the result as a combination of year and month, like if an employee has 13 months of experiance then i want to get the result as 1.1 years. Is that possible using SQL?
Please help. All your helps will be appriciated.

View 12 Replies View Related

Transact SQL :: Find Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
(    nRef INT,
     nOrd     INT,
     Token     INT,
     nML              INT,
     nNode            INT,
 sSymbol          VARCHAR(50),
 nMessageCode     INT
)
INSERT INTO A
(   nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),

[code]....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 6 Replies View Related

Primary Key On Combination Of Nullable Fields, At Least One Not-null

Jul 23, 2005

I have a case where a table has two candidate primary keys,but either (but not both) may be NULL. I don't want to storea copy of the concatenated ISNULL'ed fields as an additionalcolumn, though that would work if necessary. Instead, I triedthe following (this is a related simplified example, not myreal one):CREATE FUNCTION ApplyActionPK(@IP int = NULL,@DNS varchar(64) = NULL)RETURNS varchar(74) -- NOT NULLASBEGINdeclare @val varchar(74)set @val = str(ISNULL(@IP, 0), 10)set @val = @val + ISNULL(@DNS, '')return @val-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"-- ... and other things...ENDGOcreate table ApplyAction(-- An action applies to a computerAct varchar(16) NOT NULL,-- The action to applyIP int NULL,-- The computer IP address, orDNS varchar(64) NULL,-- The DNS name of the computerTarget as dbo.ApplyActionPK(ComputerID, DNS), -- PK value-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target))SQL Server always complains that the primary key constraint cannot becreated over a nullable field - even though in no case will the 'Target'field be NULL.Please don't explain that I should store an IP address as a string.Though that would suffice for this example, it doesn't solve myactual problem (where there are four nullable fields, two of whichare FKs into other tables).What's the reason for SQL Server deciding that the value is NULLable?What's the usual way of handling such alternate PKs?Clifford Heath.

View 7 Replies View Related

Combination Problem SQL Express, Crystal Reports, VB.NET

Mar 9, 2007

I do not know where to post this strange problem.

I have a perfectly working application, accessing a SQL Express database, displaying data and images.

Now I am trying to add a Crystal Report. I add a new connection (OLE DB (ADO)) The connection points to my .MDF database. I drag the fields I want to print to the report, including an image. Now I click the Report Preview. I get a message saying "The database table "Table-Name" can not be found, Proceed to remove this table from the report?" Yes/No. I select NO, and the preview shows up correctly.

Happily I continue to execute the program in debug mode. At startup I get errors saying that login failed. To get around this error, I have to close down Visual Studio and then open it up again, running the program without touching the Crystal Report Design. Now the program starts up normaly without problems.

Now comes the real strange behavior. I click on my link to request the Crystal report and I get a request to login to the database. I try to login but nothing is accepted. I again close down Visual Studio and open up SQL Server Management Studio Express. I connect to SQL Express and expand "Databases" MY .MDF file is listed but the + sign in front of it is missing and I have to delete the database and attach it again to get it back.

Where is this problem occuring????? I have no idea. Please help.

View 6 Replies View Related

Transact SQL :: How To Find Combination Of Column - Row Unique

Mar 8, 2012

I have table with some columns with no primary key..

I have to find the combination of columns which may the row unique...

How to do that like microsoft...

View 14 Replies View Related

Impossible? Sort On Dynamic Field (in Combination With Row_number)

Aug 17, 2007

I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by  m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1) 

View 9 Replies View Related

How Can I Concatenate A String To A Int Variable? Need Correct Combination Of Quotes!

May 26, 2008

Hi,
I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below:
 1 @Price int
2
3 DECLARE @SqlPrice varchar(50)
4
5 if (@Price is not null)
6
7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + ''
8

 

View 4 Replies View Related

SQL 2012 :: Optimal Combination Of Tools For Flexible Reports

May 28, 2014

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.

View 1 Replies View Related

SQL Server 2012 :: Query To Produce Permutation Combination

Jun 4, 2015

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),

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved