For SQL Gurus ... Problem With Matrix

Jun 15, 2008

Are many days that I've a problem with a matrix.... I hope that somebody can help me :-((

CREATE TABLE [dbo].[Matrice](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDProfilo] [int] NULL,
    [IDAttivita] [int] NULL,
    [CodiceCompetenza] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
    [ObiettivoAttivita] [int] NULL,
    [PesoCompetenza] [int] NULL,
 CONSTRAINT [PK_Matrice] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

in this table I've a matrix with ... for each profile ... IDAttivita e CodiceCompetenza


in the row I've a list of activites (3) and for each activity I've an "ObiettivoAttivia"
in the columns I've all "CodiceCompetenza"

In excel I have a table like this

Attività - Obiettivo attività - SM - CO - CM - CT
att 1 - 4 - ...
att 2 - 3 - ...
att 3 - 2 - ...

"Obiettivo attività " is the same for all SM - CO - CM - CT of the same "Attività "
In my SQL table I've

[IDAttivita] row
[CodiceCompetenza] column
[ObiettivoAttivita] one for each activity
[PesoCompetenza] row/column value


this is an example:



in my external table I've
Profile
116 IDProfilo

Activities
1 att 1
2 att 2
3 att 3

Competenze
SM
CO
CM
CT



INSERT INTO Matrice VALUES (116,1,'SM',4,1)
INSERT INTO Matrice VALUES (116,1,'CO',4,2)
INSERT INTO Matrice VALUES (116,1,'CM',4,3)
INSERT INTO Matrice VALUES (116,1,'CT',4,1)

INSERT INTO Matrice VALUES (116,2,'SM',3,1)
INSERT INTO Matrice VALUES (116,2,'CO',3,1)
INSERT INTO Matrice VALUES (116,2,'CM',3,2)
INSERT INTO Matrice VALUES (116,2,'CT',3,2)

INSERT INTO Matrice VALUES (116,3,'SM',2,1)
INSERT INTO Matrice VALUES (116,3,'CO',2,3)
INSERT INTO Matrice VALUES (116,3,'CM',2,4)
INSERT INTO Matrice VALUES (116,3,'CT',2,1)

with these inserts I see that:

attività 1 has "obiettivo attivita" 4
attività 2 has "obiettivo attivita" 3
attività 3 has "obiettivo attivita" 2
my problem:

I calculate the "valore peso" ... the product (obiettivo attivita * PesoCompetenza) for each row


Now I've to to sum all "valore peso" of the same "CodiceCompetenza"

I will have

for SM
4*1=1
3*1=3
2*1=2
Total SM 9

for CO
4*2=8
3*1=3
2*3=6
Total CO 17

...
Total CM 26
Total CT 12

now I've to se a variable @range_massimo = 4


and calculate the  "Max" variable as a sum of all "obiettivi attività " * @range_massimo
(I've to consider only the 3 different activities for the "obiettivo attivita" and not all 12 activities of the matrix)
"Max" will be:
(4*4)+(3*4)+(2*4) = 36 -> "Max"

The final step is Total / Max

(for each Total of my  Competenza)
Total SM
Total CO
Total CM
Total CT

...

(9/36)*100 = 25
(17/36)*100 = 47,2
(26/36)*100 = 72,2
(12/36)*100 = 33,3


now
 
* @range_massimo /100

(25*4)/100= 1,0
(47,2*4)/100=1,9
(72,2*4)/100=2,9
(33,3*4)/100=1,3


I hope you can help me!!

Please note that I will have dinamic IDAttivita and CodiceCompetenza for each IDProfilo!

Thanks
 

View 4 Replies


ADVERTISEMENT

Silly Question - For SQL Gurus And SWYNK Gurus

Nov 3, 2000

Probably this will be a silly question?

1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.

2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.

3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.

I had sent many emails to SWYNK. No reply so far.

Any inputs r well appreciated!!!!!!

-MAK

View 2 Replies View Related

Reporting Services :: SSRS Matrix - Add A Column In A Matrix With A Variance

Aug 6, 2015

I got the following code to add a column in a matrix with a variance:

IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)

This code works fine, except that the first row of the matrix shows an #error

This happens with each matrix where I use this expression. A warning emerges:

rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.

Attempted to divide by zero.

The strange thing is that the part

Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'

An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)

As you can see, all other 2013 rows show a blank cell, except the first row.

View 3 Replies View Related

A Matrix Above Two Charts. Right Chart Moves Depending On Matrix Growth ... ???

Jan 10, 2008

I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.

Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.

How? Why does the right chart move?

Thanks in advanced
Kind Regards

View 1 Replies View Related

SQL Gurus Help!!!

Oct 16, 2006

My boss has a crazy idea that he want to implement using SQL 2005 and ASP.NET 2. Its something like this... If a user retrieve a record and someone is updating that same record a control on a asp.net page would warn that user that its being updated. Just like a stop light, green for go, yellow for warning, and red for stop. Were implementing optimistic here not pessimistic. Is this possible? I know pessimistic can lock that certain record. But what about in optimistic? Is it possible to monitor or know if a certain record or row in SQL 2005 is being access, updated, and deleted? How?

View 3 Replies View Related

Sql Gurus

Feb 14, 2001

Hi,

How to hide my database with the users, I am deploying my application, i want to hide my database to client. Please help me regarding this.

Rajesh.

View 2 Replies View Related

Gurus

Sep 22, 2000

Our company is looking for fulltime Java/ASP/SQL gurus immediately.Big pay.If interested please send resume to polasp@hotmail.com.
Thanks!

View 1 Replies View Related

SQL Gurus Please Help

May 14, 2002

I have 2 tables :
tab1 col1 char(10), col2 char(10), col3 char(10)....Primary key on col1+col2
tab2 col4 char(10),col5 char(10),col6 char(10)......Primary key on col4+col5


I want to insert a row from tab2 if its not there in tab1.
And I want to update col3 of tab1 from col6 of tab2 if col1+col2 matches col4+col5.

Any thoughts on doing these 2 tasks. I am sure these will be 2 differnet commads.

SQL Gurus please help....

jfk

View 4 Replies View Related

SQL Gurus, Please Help

Jul 23, 2005

Hi,I have a table in which two fields(FirstID, SecondID) together make theprimary key for the table. Let's look at the following example:FirstID SecondID******* ********2 22 32 55 55 78 79 1011 12Here is what I am trying to do. For a given FirstID (say, 2) find allSecondID with this FirstID (2,3,5). Now for these second ID's is therea FirstID other than 2? (Yes, 5). I would say FirstID=2 is related toFirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.I am trying to write a SQL (some kind of self join, I think) which willgive me all related FirstID for a given First ID. For First ID = 2 theSQL will give me 5 and 8 in the above example. For First ID = 9 the SQLwill give me 11.Any help will be greatly appreciated. Thanks!!-Raj

View 6 Replies View Related

For All MS SQL Gurus :( Please Advice

Aug 30, 2005

Hi guys,we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again  :( and i m very sad abt it all )..please adviceThanx in advance    ashish

View 2 Replies View Related

SQL Question...for Gurus!!

Oct 24, 2000

We need to return the results of a query as a single string.For example if the query returns 10 rows,all the 10 rows should be concatenated and put it in an output @returnstring. Is that possible in SQL stored procedures ??
Thanks!

View 1 Replies View Related

Calling All GURUS ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

May 19, 2002

SQL Server Load Balancing -- Any ideas?

There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.

Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?

Will this work? If not, why not?
What will work? Will federated servers work for an entire database??

View 1 Replies View Related

Attn: Sql Gurus

Jul 15, 2002

I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.


TableA
and
TableB
----------

ID int
ComBank int
inboth int
EqorMA int
TranType int


I need to know the rows that Are different in TableA from TableB.

Any help would be appreciated.

Thanks

James

View 2 Replies View Related

TSQL Gurus - Need Your Help

Oct 9, 2001

Hi there!

I'm trying to insert a MSWord/Bitmap/Powerpoint file into a table in SQL 2000. Unfortunately, I haven't had the luck to get it work. Here is my table structure :

Create table tbl_blob (
doc_id varchar(20) not null,
doc_blob image null )

My question is, do you have any script on how I can insert a MSWord/Bitmap file in my blob table. I will be running the script using Query Analyser.

thanks!
100NA

View 3 Replies View Related

Any Cursor Gurus Out There?

Jun 28, 2006

i realize i am starting to ask a lot of questions here so, thanks to all who tolerate me.

In my previous post, i figured out (with the help of r937) how to create a non-identity column using a cursor. I now have some resulting questions.

1. where do cursors live? there is no folder like triggers or stored procedures. is a cursor its own file or is it embedded in a stored procedure?

2. how do i get my cursor to "act" upon my stored procedure? I want the values in my cursor's "relativeposition" column to replace the values in my stored procedure's photo_number column. how do i do this?

BELOW IS MY STORED PROCEDURE:

Code:


ALTER PROCEDURE dbo.sp_NewPersonalPic

(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)

AS

BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END


--SET ANSI_WARNINGS OFF
INSERT INTO PersonalPhotos
(photo_name, photo_location, photo_size, user_name, photo_caption, photo_default, photo_private, photo_number, photo_date)

VALUES (@photo_name, @photo_location, @photo_size, @user_name, @photo_caption, @photo_default, @photo_private, @photo_number, @photo_date)
--SET ANSI_WARNINGS ON

END

RETURN



AND HERE IS THE CURSOR:

Code:


DECLARE RelativePositionCursor

CURSOR LOCAL FAST_FORWARD FOR
SELECT photoId, photo_date
FROM PersonalPhotos
ORDER BY photo_date ASC

OPEN RelativePositionCursor

DECLARE @curs_id INT
DECLARE @curs_date DATETIME
DECLARE @totalRows INT
DECLARE @firstRow INT

SET @firstRow = 0

DECLARE @Results TABLE
(
curs_id INT NOT NULL PRIMARY KEY,
curs_date DATETIME,
RelativePosition INT
)

FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @totalRows = (SELECT COUNT(*) FROM PersonalPhotos)


WHILE(@firstRow <= @totalRows)
BEGIN
SET @firstRow = @firstRow + 1
BREAK
END

INSERT @Results
VALUES (@curs_id, @curs_date, @firstRow)

FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
END

CLOSE RelativePositionCursor

DEALLOCATE RelativePositionCursor

SELECT *
FROM @Results
ORDER BY curs_date

View 1 Replies View Related

Q: Calling All Gurus

Jul 20, 2005

The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat

View 14 Replies View Related

For The SQL Gurus Out There, A Question

Jul 20, 2005

I am not good at SQL - hence here goesConsider this scenario of 2 tables X and Y with a many to manyrelationshipTable X (name,weightage)X1 2X2 1X3 5X4 1Table Y (name ,attrib)Y1 attrib1Y2 attrib2Y3 attrib3Y4 attrib4Relationship table Z (id,id)X1 Y1X1 Y2X2 Y1X3 Y1X3 Y2X3 Y4X4 Y4So based on Z the distribution (count of Y/ Total Y ) is likeY1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7Now I want to factor in the weighatge column in X alsoSoX1 Y1 2*1X1 Y2 2*1X2 Y1 1*1X3 Y1 5*1X3 Y2 5*1X3 Y4 5*1X4 Y4 1*1Sum: 21Given these tables ,How would I factor in the weights and get thedistribution in a single SQL query.Desired Output of SQL QueryId AttribDistrib--------------------------------Y1 attrib1 100*8/21Y2 attrib2 100*7/21Y3 attrib3 0Y4 attrib4 100*6/21ThxJP

View 19 Replies View Related

Guidance From The Gurus.......

May 2, 2007

Greetings All,



I have a SQL2005 table that I'm needing to export to a 'formatted' csv flat-file to emulate a mainframe output. I'm new to SSIS so don't beat me up to bad... The current output of my ssis csv file is as follows:



95752,95725,0001,0, ,N
11473,8704,0001,0, ,N
1279215,436612,0001,0, ,N
43595,43592,0038,0, ,N



and I need to get to here:



95752 ,95725 ,1 ,0 ," ","N ",0

11473 ,8704 ,1 ,0 ," ","N ",0

1279215 ,436612 ,1 ,0 ," ","N ",0

43595 ,43592 ,38 ,0 ," ","N ",0



Basically I need to pad the first 2 columns with trailing spaces to a fixed width of 11 characters, 3rd column convert number format and pad to fixed 5, 4th column pad to fixed 3, 5th column add double quotes around 20 character fixed. 6th column add double quotes around and pad to fixed 2. Finally adding a , 0 to each row which is used a LF/CR.



Any feedback in regards to getting pointed in the right direction would be greatly appreciated...

View 5 Replies View Related

Code Help Needed VB Gurus

Feb 20, 2008

I have a problem.. My code below works fine but when I want to add user to the database, it gives me error.
I added this part
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
 
and it gives error here
MyCmd.ExecuteNonQuery()
 
Thanks Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As StringDim sConnString, sSQL As String
Dim u As MembershipUser 'Receiving values from Form, assign the values entered to variablesname = Request.Form("name")address = Request.Form("address")
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
'comments = Request.Form("comments")'declare SQL statement that will query the database
sSQL = "INSERT into test (name, address, user) values ('" & _
name & "', '" & address & "', '" & nice & "')"
'define the connection string, specify database'driver and the location of database
sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString() 'create an ADO connection object Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()End Using
Response.Write("The form information was inserted successfully.") End Sub
End Class

View 7 Replies View Related

SQL Gurus, Need Help Joining 2 Tables.

Jan 18, 2004

Hi, I need help in joining 2 tables. This is not a regular 2 table join. it's little bit more complicated. Below is the working query I use. it gets the data I need. The problem I am having is joining the query below with another table.

-----------------------
SELECT BuddyID
FROM (SELECT TOP 10 BuddyID
FROM (SELECT TOP 20 BuddyID FROM Buddies Where UserID = 100025 ORDER by BuddyID) as a ORDER by BuddyID DESC) as b
ORDER by BuddyID
---------------

the query above will give me the BuddyID for about 75 records. I want to join the BuddyID on the "Buddies" table to the BuddyID on the "Users" table. I just don't know where to put "INNER JOIN Buddies ON Users.BuddyID = Buddies.BuddyID"

View 3 Replies View Related

Question On Jobs For Gurus!

Sep 28, 2001

Some of my jobs hang at FTP step for several hours until some one kills the FTP (it's a perl process) on the box. Is there a way to find how much time a particular step of the job is executing? and know it by somehow if it's hanging at that step?? If we know this,I want to kill the perl process(FTP) on the box in an automated way whenever the job hangs! Any ideas welcome!! Thanks. :-)

Di

View 2 Replies View Related

Concatination Help Required From Gurus!!

Oct 17, 2001

I need to execute the following code by passing the username as a parameter.Can I get help to complete the SQL statement??

exec ('sp_changeobjectowner '+ @Table_name +','+@ToUser) ---> This works fine.

But I need to have something like this:

exec ('sp_changeobjectowner '@username.+ @Table_name +','+@ToUser)

Note:
Here is the sample working syntax:
sp_changeobjectowner 'user1.eb_sku', user2

So,I need user1 and user2 as two different parameters.

As you see,I need to qualify the table name with the owner name which itself I want to pass as a parameter.

Any help with syntax is greatly appreciated!!
Di.

View 3 Replies View Related

Question For Replication&#39;s Gurus

Dec 2, 1999

Hello!
I have push transactional replication to set up.
I have 2 publisher’s servers with tables, which replicate data in the same table on subscriber.
Tables on both publishers already have data.
When I set up replication between first publisher and subscriber
Snapshot of existing data was done, so I didn't miss any data from the
First publisher table.
But I don't have idea what to do with second publisher, because if I
will make snapshot it will overwrite the data have been replicated from
First publisher. If I won't make snapshot I will miss already existing data
in second publisher.
I have headache. Can you help me to resolve the problem?

Thanks a lot in advance,
Elena

View 1 Replies View Related

Urgently Need Help From SQL Server Gurus

Aug 17, 2000

4 days ago we had a Server crash luckily we have the daily full database backups...Since the server which was crashed is unusable I installed SQL 7.0 on another machine...I tried to restore databases from our backup tapes...Here is the T-SQL I am running:

restore database pentatel from tape='.ape0' with file=2,move 'PentaTel_dat' TO 'D:mssql7datapentatel.mdf', move 'pentatel_log' to 'D:mssql7datapentatel.ldf'

The error message I am getting is:

Processed 8992 pages for database 'pentatel', file 'PentaTel_dat' on file 2.
Processed 1 pages for database 'pentatel', file 'PentaTel_log' on file 2.
Server: Msg 9004, Level 21, State 1, Line 1
The log for database 'pentatel' is corrupt.

Connection Broken

After these when I look at the SQL Enterprise manager I see that the database is in loading status...I left it in that status for hours but there was no reaction...So I tried to detach and attach a single file.Here are the TSQLs and Error messages...


exec sp_attach_single_file_db pentatel, 'd:mssql7datapentatel.mdf'

Server: Msg 945, Level 14, State 2, Line 1
Database 'pentatel' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'pentatel'. CREATE DATABASE is aborted.

We have 7 backups and I tried all of them...Same error messages...

When I try to change the status from loading to emergency or normal the database is always inaccessible

I are running these statements

exec sp_configure 'allow',1
reconfigure with override
update sysdatabases set status=32768 where db_id=6

and I stop and start the Mssql7 service but still inaccessible

Any idea???

I despretaly need help...Any recomendation is greatly apreciated...

Sefa Sevtekin

View 4 Replies View Related

Performance Questions For Gurus

Apr 15, 2003

We are new to SQL 2000, and would like to bounce a couple questions off some of you gurus out there.
We are using SQL Server 2000 to build a data repository to assist us in transitioning from our old flatfile legacy system to SAP. We are also looking to use the SQL Server 2000 repository to build a smallish Enterprise Data Warehouse on the same SQL 2000 platform.

Here is our problem:
We have SQL Server 2000 loaded on a little scrapper PC with 1.4GHZ single processor, 1GB of memory, and a single 40 GB IDE drive.

When we are initially loading any of our repository tables the process cruises along pretty well, even respective of trying to locate the record for update before doing an insert. But, if we do something as simple as selecting count(*) against the table that's loading, performance on the load goes to its knees.
We understand we're pretty much at the mercy of the hardware we have (that's the budget), but we'd like to get as much bang as we can out of what's there.

Our questions are:
1. Is there anything we can do with our server configuration (short of new hardware) that will help us?
2. Are there any recommendations as far as monitors to help us better tune this specific configuration?

Thanks in advance for any help.

View 7 Replies View Related

Calling TSQL Gurus...

Jul 17, 2002

Might not need a TSQL guru to solve this.
Can you guys help me out with a TSQL statment to display the output of a table like this.

Required Output :
col1 col2
----- ----
x001 x002
x002 x003
x003 x004

Input Table
Code
----
x001
x002
x003
x004

No temp table or variable should be used. Only an TSQL statement.


Thanks
Paul

View 2 Replies View Related

CPU Bottlenecks? -- Gurus&#39; Help Needed

Aug 10, 2001

QUESTION 1:

I am getting high DPC utilization in Performance Moniter. What is DPC
(seems to be CPU bound). How can I tune my system to be use this resource?

QUESTION 2:

Is the equation below correct?

Total elapled time = cpu + memory + disk + network + program time

According to query analyzer, my elapsed time is ie 100
BUT my cpu and disk time totals only 20. Where is the rest of the
elapsed time coming from??

Thanks

View 1 Replies View Related

Complicated Query - SQL Gurus Help Please

Mar 20, 2008

Hi,
I am positive some SQL Gurus can help me with this complicated query. I am not sure if it is possible with a single query or needs multiple queries.

I have 3 objects. First, PaintOrder; Second, Combination; and Third, Material. Each Paint Order can have multiple Combinations and each Combination can have multiple (from 0 to 5) Materials. All the data is stored in single table called paintMatCom.

Here is the sample Data

PaintOrderId CombId MaterialId
1000 1 1
1000 1 2
1000 2 3
2000 1 1
2000 2 3
2000 2 4
3000 1 1
3000 1 2
3000 2 3
3000 3 4
3000 3 5

I have a Search Screen where user can select multiple Materials (upto 5) to search for the appropriate PaintOrders. Also, use has the option to select AND/OR between materials. Please look at the attached image for clear understanding.

Some of the sample searches are as follows (Combination IDs are not needed in the Output; just PaintOrder IDs).
1. Find PaintOrders with MaterialIDs 1 AND 2 OR 4
2. Find PaintOrders with MaterialIDs 2 OR 3 OR 4
3. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1

Hope you got the idea. I think it would require some Joins and GROUP BY which I am not expert at. I would appreciate any help.

[IMG]C:Documents and SettingsOwnerDesktopmaterials.JPG[/IMG]

Thanks
vmrao

View 13 Replies View Related

Call To Gurus -- SQLDateTimeAPI

Nov 15, 2004

Hey gurus ... I am in the process of writing a DateTime API for SQL Server 2000 ... Here's what I have come up with so far ... if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FirstDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fn_FirstDayOfMonth]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_LastDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fn_LastDayOfMonth]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_LastWorkingDayOfMonth]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fn_LastWorkingDayOfMonth]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_WorkingDaysBetweenDates]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fn_WorkingDaysBetweenDates]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_PopulateTimeSeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_PopulateTimeSeed]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TimeSeed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[TimeSeed]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE Function fn_FirstDayOfMonth (@SearchDate datetime)Returns DatetimeasBegin Declare @ReturnDate datetime Select @ReturnDate = CalendarDate from TimeSeed where CalendarYear = Year(@SearchDate) and CalendarMonth = Month(@SearchDate) and DateOfMonth = 1 Return @ReturnDateEndGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCreate Function fn_LastDayOfMonth (@SearchDate datetime)Returns DatetimeasBegin Declare @ReturnDate datetime Select @ReturnDate = Max(CalendarDate) from TimeSeed where CalendarYear = Year(@SearchDate) and CalendarMonth = Month(@SearchDate) Return @ReturnDateEndGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCreate Function fn_LastWorkingDayOfMonth (@SearchDate datetime)Returns DatetimeasBegin Declare @ReturnDate datetime Select @ReturnDate = Max(CalendarDate) from TimeSeed where CalendarYear = Year(@SearchDate) and CalendarMonth = Month(@SearchDate) and WorkingDayFlag = 1 Return @ReturnDateEndGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE Function fn_WorkingDaysBetweenDates (@SearchDate1 datetime, @SearchDate2 datetime)Returns intasBegin Declare @CountDate int Select @CountDate = Count(CalendarDate) from TimeSeed where CalendarDate >= @SearchDate1 and CalendarDate <= @SearchDate2 and WorkingDayFlag = 1 Return @CountDateEndGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE TABLE [dbo].[TimeSeed] ( [CalendarDate] [datetime] NOT NULL , [DateOfMonth] [int] NOT NULL , [DayOfMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CalendarMonth] [int] NOT NULL , [CalendarYear] [int] NOT NULL , [FiscalYear] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FiscalMonth] [int] NOT NULL , [WorkingDayFlag] [bit] NOT NULL , [WorkingHours] [int] NOT NULL , [WeekOfYear] [int] NOT NULL , [WeekOfMonth] [int] NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[TimeSeed] WITH NOCHECK ADD CONSTRAINT [PK_TimeSeed] PRIMARY KEY CLUSTERED ( [CalendarDate] ) ON [PRIMARY] GO CREATE INDEX [IX_TimeSeed] ON [dbo].[TimeSeed]([DateOfMonth]) ON [PRIMARY]GO CREATE INDEX [IX_TimeSeed_1] ON [dbo].[TimeSeed]([CalendarYear]) ON [PRIMARY]GO CREATE INDEX [IX_TimeSeed_2] ON [dbo].[TimeSeed]([CalendarMonth]) ON [PRIMARY]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCreate Procedure usp_PopulateTimeSeedasDeclare @StartDate datetimeSelect @StartDate = '19000101'Set DateFirst 1While @StartDate <= '21001231'begin insert into TimeSeed ( IntDate ,CalendarDate ,DateOfMonth ,DayOfMonth ,CalendarMonth ,CalendarYear ,FiscalYear ,FiscalMonth ,WorkingDayFlag ,WorkingHours ,WeekOfYear ,WeekOfMonth ) Select Cast (@StartDate as int) ,@StartDate ,Day(@StartDate) ,Datepart(DW,@StartDate) ,Month(@StartDate) ,Year(@StartDate) ,Case When Month(@StartDate) <= 3 Then Year(@StartDate) - 1 Else Year(@StartDate) End ,Case When Month(@StartDate) <= 3 Then Month(@StartDate) + 9 Else Month(@StartDate) - 3 End ,Case When Datepart(DW,@StartDate) in (1,7) Then 0 else 1 End ,8 ,Datepart(wk,@StartDate) ,(Day(@StartDate)%7)+1 Select @StartDate = Dateadd(dd,1,@Startdate)EndGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO Here's what has me stumpedI'd like to calculate the hours difference for a given start and end datetime in SQL server. Certainly function datediff() can do the job but the tricky part is that the hours calculation should be based on 8 hrs / per business day (from 8:00am to 5:00pm), and weekends (Saturdays and Sundays) are excluded as well. For example:start time: 11/10/04 4:00pmend time: 11/11/04 8:00 amthen the hours difference should be only 1 hour. (if there is fraction, then round to nearest hour). Now ... remember that the table TimeFeed is modifiable and can be changed to suit our needs. Any ideas ??? Also ... you can all chip in with your own functions ...

View 3 Replies View Related

Memory Question For The Gurus

Nov 17, 2004

Hi Guys, first time posting here. My boss has a Lotus Notes application accessing SQL server with about 60 users. I have a custom VB app with 40+ users. Our SQL Server has
2 gigabyte of memory on it. Is this too low? My boss is expecting to have 300+ users on his Notes app when he rolls it out to our other branches. What would be the ideal amount of memory for 300+ users?

View 14 Replies View Related

Security Gurus - Questions For You

Sep 25, 2006

I want to create an MS Access front-end for my SQL database.
I want to create user accounts with passwords for each user.
Should I change SQL Server to "MIXED authentication mode" (SQL Server authentication and Windows authentication) ?

********************************************
*** Thank you very much in advanced, security gurus ***
********************************************

View 5 Replies View Related

Any SELECT Statement Gurus Out There?

Sep 12, 2005

Hi all,I'm having real trouble wrapping my newbie brain around this problem. Cansomeone please tell me the most efficient (or any!) way to write a SELECTstatement to return a set of rows from 5 tables:These tables are part of our medical database. For example, a person seekinghealthcare may want to know the names and contact information for alldoctors in Reno, NV, who do cardiology. Sounds simple, but it isn't.A medical group can have many doctors.A doctor may be a member of more than one medical group.A doctor or group can have multiple specialties.A group can have multiple facilities (physical locations).So the tables look like this...Table: Doctors---------------DoctorNameDoctorIDTable: Groups--------------GroupNameGroupIDTable: Docs2Groups (provides many-to-many relationship between Doctors andGroups)---------------------DoctorIDGroupIDTable: Specialties (provides many-to-many relationshop between Doctors,Groups, and Specialties)-----------------SpecialtyIDSpecialtyDescDoctorIDGroupIDFacilities---------FacilityName (provides one-to-many relationship between Groups andFacilities)FacilityIDGroupIDAddressCityStateZipPhoneE-mailAny help would be GREATLY appreciated.--Eric Robinson

View 6 Replies View Related

Syntax Error, Calling All SQL Gurus!

Apr 21, 2008

I'm having trouble with the select below. The problem is in the WHERE clause. The Query Builder says this: "Query Definitions Differ. Error in WHERE clause near '('. Unable to parse query text."Here is my code: SELECT Members.MemberID, MemberFamily.FamilyID, MemberFamily.FamFirstName, MemberFamily.FamLastName, MemberFamily.Relation, Members.Street, CASE WHEN MemberFamily.Phone IS NULL THEN (SELECT TOP 1 Phone.Phone FROM Phone WHERE Phone.MemberID = MemberFamily.MemberID) ELSE MemberFamily.Phone END AS Phone, CASE WHEN MemberFamily.Email IS NULL THEN (SELECT TOP 1 Email.Email FROM Email WHERE Email.MemberID = MemberFamily.MemberID) ELSE MemberFamily.Email END AS Email, Checkin.DateFROM MemberFamily INNER JOIN Members ON MemberFamily.MemberID = Members.MemberID INNER JOIN Checkin ON MemberFamily.FamilyID = Checkin.FamilyIDWHERE (SELECT * FROM Checkin WHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '02/01/2008' AND '03/01/2008')) AND (NOT (SELECT * FROM Checkin WHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '04/16/2008' AND '04/19/2008')))   I can run it without the WHERE clause and it works fine and I can run these arguments separately and they run without errors also:  SELECT *FROM CheckinWHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '02/01/2008' AND '03/01/2008') SELECT *FROM CheckinWHERE (Checkin.FamilyID = 11) AND (Checkin.Date BETWEEN '04/16/2008' AND '04/19/2008')    

View 2 Replies View Related







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