Generating Unique No Based On Certain Data
Oct 3, 2007
Hi,
I have the following tables :
Code Block
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #Call(Callid int,Calltype int,callDetailid int ) // CallType 1=New 2=Change 3=Delete
Create Table #CallDetail(callDetailId int,empid int)
The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?
SAMPLE DATA
Code Block
Insert into #CallDetail(12123,1)
Insert into #CallDetail(53423,1)
Insert into #CallDetail(6532,1)
Insert into #CallDetail(62323,1)
Insert into #CallDetail(124235,1)
Insert into #CallDetail(65423,2)
Insert into #CallDetail(56234,2)
Insert into #CallDetail(2364,2)
Insert into #CallDetail(34364,2)
Insert into #CallDetail(85434,2)
Insert Into #Call(111,1,12123)
Insert Into #Call(112,1,53423)
Insert Into #Call(114,1,6532)
Insert Into #Call(123,2,62323)
Insert Into #Call(134,1,124235)
Insert Into #Call(143,2,65423)
Insert Into #Call(145,1,56234)
Insert Into #Call(154,2,2364)
Insert Into #Call(185,1,34364)
Insert Into #Call(195,1,85434)
Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);
Insert Into #request Values('225652','Jim','SA055',67,143,0);
Insert Into #request Values('126756','Jasm','SA055',67,145,0);
Insert Into #request Values('786234','Chuck','SA055',67,154,0);
Insert Into #request Values('66234','Mutuk','SA059',72,185,0);
Insert Into #request Values('2232362','Buck','SA055',67,195,0);
EXPECTED OUTPUT will be (See the last column for unique nos). :
Code Block
Insert Into #request Values('324234','Jack','SA023',12,111,1);
Insert Into #request Values('223452','Tom','SA023',12,112,2);
Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1
Insert Into #request Values('22322362','Guck','SA024',44,114,1);
Insert Into #request Values('22654392','Luck','SA023',12,123,3);
Insert Into #request Values('225652','Jim','SA055',67,143,1);
Insert Into #request Values('126756','Jasm','SA023',69,134,1);
Insert Into #request Values('786234','Chuck','SA023',72,145,2);
Insert Into #request Values('66234','Mutuk','SA059',72,185,1);
Insert Into #request Values('2232362','Buck','SA055',67,195,2);
Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem
Plz help. I am stuck from 2 days on this :
Vidkshi
View 8 Replies
ADVERTISEMENT
Mar 7, 2006
Hi,
i have the following requirement
Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?
Is there any SQL Server 2005 feature to do it if possible?
i appreciate your help.
Thanks,
Vaishu
View 3 Replies
View Related
May 25, 2004
Hi
I want to generate a unique record number in the following format
Company initials/year/Counter/RecordType (e.g. SDS/04/00123/WB)
Could someone recommend how I go about generating this number?
Should I create a separate table with columns for each section of the number and concantenate the columns in ID field of the actual table?
... or should I just create a stored procedure to generate the number each time?
... or should I do something completely different?
Regards
John :confused:
View 5 Replies
View Related
Jul 19, 2000
Is there wa way to generate unique sequence numbers in SQL server?
(just like the way it is in Oracle i.e. seqeuence and then use nextval)
View 2 Replies
View Related
Aug 17, 2007
Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?
Thanks you very much!!!!
Sergio
View 2 Replies
View Related
Oct 4, 2006
I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 640484566 TERE BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
View 1 Replies
View Related
Nov 23, 2007
I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
Aeneas.
View 1 Replies
View Related
Mar 18, 2007
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA
05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
View 12 Replies
View Related
Jun 18, 2008
I've managed to get my query to this: Product | Color | Votes
======== ======= =======
Bus Red 10
Bus Blue 5
Train Blue 1Car Red 1
Car Blue 1
I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results.
Any help would be greatly appreciated.
Thanks, Pete.
View 2 Replies
View Related
Apr 17, 2008
Ok... I know this is something that I've seen and probably done before....
I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.
The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.
Value1
Value2
LineNumber
1
Hey
1
1
Can
2
1
Someone
3
1
Help
4
1
With
5
1
This
6
2
Very
1
2
Simple
2
2
Issue
3
2
As
4
2
I
5
3
Have
1
3
A
2
3
Brain
3
3
Freeze
4
--Thanks--
View 4 Replies
View Related
Sep 1, 2015
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 4150.66
Jaime KRiSH 5/3/2015 4150.66
Peter Jason 5/1/2015 321.02
Peter Jason 5/1/2015 321.02
Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66
2 Jaime KRiSH 5/1/2015 1043.66
2 Jaime KRiSH 5/1/2015 1043.66
3 Jaime KRiSH 5/1/2015 4150.66
4 Jaime KRiSH 5/3/2015 4150.66
5 Peter Jason 5/1/2015 321.02
6 Peter Jason 5/1/2015 321.02
7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
View 4 Replies
View Related
Jul 9, 2015
I have a scenario here where the data looks like -
ID Date
100 07/01
100 07/02
100 07/03
100 08/01
100 08/02
100 08/15
Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?
View 5 Replies
View Related
Feb 18, 2014
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.
Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate
SET NOCOUNT ON
GO
declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int
[Code] ....
View 6 Replies
View Related
Jul 1, 2015
TABLE DEFINITION
Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)
CUSTOMER TABLE
CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
OUTPUT
CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
Records should be filtered by
1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo
View 2 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Jun 17, 2015
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
View 4 Replies
View Related
Oct 7, 2006
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
View 1 Replies
View Related
Jul 3, 2006
Hi,
I use SQL Server 2005 Dev Edition and am not new to making databases (then again, I've had enough experience and my dad does the same thing).
I am (unfortunately) a university student and for my dissertation I am going to produce a SQL Server database with a strong emphasis on data mining.
Obviously, for the data mining to be useful at all I need to produce loads and loads of test data.
Fair enough, and there are applications which do this, such as EMS Data Gen, but can anyone recommend me any other data gen utilities? EMS Data Gen has poor handling of unique attributes, and as I am doing a car manufacturer this will give me problems when I come to the registration number attribute.
Also, why are utilities for SQL Server (and Oracle at that) so expensive? This makes it out of my reach and makes it difficult to build a truly good database that will net me good marks, and demotivates me. :(
Lastly, please feel free to recommend to me any utilities for SQL Server - such as performance monitors, backup utilities. Anything. But if they are priced utilities, they have to be sensibly priced (<£100), because I cannot yet afford to pay >£1k on such utiltiies.
Thanks
View 1 Replies
View Related
Jul 20, 2005
Sorry about the huge post, but I think this is the amount ofinformation necessary for someone to help me with a good answer.I'm writing a statistical analysis program in ASP.net and MSSQL7 thatanalyzes data that I've collected from my business's webpage and thehits it's collecting from the various pay-per-click (PPC) engines.I've arrived at problems writing a SQL call to generate certainstatistics.Whenever someone enters our site from one of the PPC search engines, Iwrite out a row to the Hits table. In that table are the followingcolumns:HitID - the Unique ID assigned to each hit that comes into the siteKeyword - the keyword the user searched on when he or she came to thesiteSearchEngine - the PPC engine the user came fromSource - this is pretty much always 'PPC'...if we were to do otherthings, like a newsletter, then this would be different.TimeArrived - the date and time the user arrived at the website. Ihave no idea why I didn't call it "datearrived," since I use "date"and not "time" pretty much everywhere else...(I don't think the rest are important, but they might be, so I'llinclude them for completeness's sake)Referring URL - the URL the user came fromReferring Website - the string between the 'http://' and the first '/'in the URL. I know it's redundant information, but when I designedthis part, I didn't know how to parse it out afterwards, so I justfigured I'd duplicate it.Page Visited - the page the user first arrived atWhen a person comes to the site, I also write out a session cookiecontaining the user's hitID. If the person fills out an enrollmentform (a process which we refer to as "responding"), I attach thatsession ID to the form. The response form (and thus the responsestable) is long; these are the important fields:id - a unique ID for each responsedate - the date and time of the responsestatus - a varchar field containing a status code. I would have madeit a number, but I wanted it to be viewable from looking at the rawdatabase.hitid - the HitID of the user, taken from the session cookie. If thereis no session cookie (for whatever reason), the HidID is written outas 0. While it wouldn't occur often, I can't guarantee that there willnever be more than one response record attached to a singular hitid.Later, some of the responses turn into "confirmations", which meansthat they've actually ordered from us, not just filled out the form.This usually happens about three or four days after the initialresponse. When this happens, the status of the response is changed toa phrase containing the word "confirm" in it (there are a few of them,but they all contain that word).So now that we've collected all this marketing intel., I need toanalyze it.I've written a parser that takes reports from various pay-per-clickcompanies and puts them into a table called PPC. Information in thiscolumn is written out as one record per search engine per keyword perday. The schema is as follows:id - a unique ID for the record in the tabledate - the date to which the information in the record appliessearchengine - the PPC engine to which the information applieskeyword - the keyword to which the information appliesclicks - the number of clicks on the applicable keyword on theapplicable search engine on the applicable day.impressions - same as clicks, but for impressionscpc - the cost per click on the applicable keyword ...avgpos - (I don't always have a value for this field) The averageposition that the keyword was shown in for the applicable keyword ...With this data in, the last step is actually analyzing the threetables for useful statistics on the various keywords, search engines,and time frames. That's the step I've been trying to complete.So what I need is a SQL call that I can run that generates a tablewith the following information:SearchEngineKeywordCost / Click - When calculating the CPC, I can't just take an averageof all the records. I need to calculate the total amount spent per day(clicks * cpc), add that up for every day, and then divide that by thenumber of total clicks. Just doing an average doesn't take intoaccount the fact that some days we'll get more clicks than others.Total Spent - # Clicks * CPC#Responses - counting the number of records in the responses table#Confirms - counting the number of records in the responses table with"confirm" in their statusTotal Spent / #ResponsesTotal Spent / #ConfirmsOh yeah, and I want to be able to order by any four of the fields inany order, narrow my selection to only those keywords that either areor contain a user-specified string, further narrow my selection toonly those records that fit other user-specified criteria for any ofthe columns in the table I'm generating, and select only the top xrecords (where x is a user-specified number). I already haveuser-controls that output the SQL for all of these things, but I needto have places in which I may put that SQL in my call.After many trials and tribulations, I've come up with the followingSQL call. Right now, its output for nearly every row is incorrect, Ithink in a large part due to the fact that the method that I'm usingto generate the number of clicks is yielding incorrect values.If you'd like to help me and you think that modifying the followingcall is easier than writing a whole new one, be my guest; if you'dprefer to write a new one, I'm game for that, too. I'm just concernedwith its working right now, and any help you can give me is greatlyappreciated.Anyway, here's the call:/*sp_dboption @dbname='NDP', @optname='Select Into', @optvalue=true;*//*Running the above might be necessary to get the "Select Into"s towork*/Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPCSELECT Responses.[ID] as [ID], Responses.Status, PPC.SearchEngine,PPC.KeywordInto ResponsesPPCFROM Responses, PPCWHERE Responses.HitID IN(SELECT Hits.HitIDFROM HitsWHERE Hits.SearchEngine = PPC.SearchEngineAND Hits.Keyword = PPC.Keyword)SELECT ID, Status, SearchEngine, KeywordInto ConfirmPPCFROM ResponsesPPCWHERE Status LIKE "%confirm%"Order by SearchEngine, KeywordSELECT PPC.SearchEngine, PPC.Keyword,SUM(PPC.Clicks), /*I noticed that thiscolumn gives me incorrect values(I don't need it in my final report, but it's useful for debugging).For some keywords, it gives me huge numbers(e.g. 265 clicks on one word that got ~10 clicks /day over five days),and for others, it doesn't give me enough. I think this is a majorpartof what's throwing off the rest of the statistics*/Case SUM(PPC.Clicks) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / SUM(PPC.Clicks) END as CPC,SUM(PPC.clicks * PPC.cpc) AS TotalCost,count(ResponsesPPC.ID) As NumResponses,Count(ConfirmPPC.ID) As Confirms,(Case Count(ResponsesPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ResponsesPPC.ID) END) ASCostPerResponse,(Case Count(ConfirmPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ConfirmPPC.ID) END) AsCostPerConfirmFROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngine =ResponsesPPC.SearchEngineAND PPC.Keyword = ResponsesPPC.Keyword)LEFT JOIN ConfirmPPC ON PPC.SearchEngine = ConfirmPPC.SearchEngineAND PPC.Keyword = ConfirmPPC.KeywordGROUP BY PPC.SearchEngine, PPC.KeywordOrder by PPC.keyword desc/*Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPC*//*I don't drop them right now so I can look at them,but normally, one would drop those tables.*/Thanks a lot for your help,-Starwiz
View 4 Replies
View Related
Feb 22, 2007
I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.
Is there a documented work around or will I need to set a primary key on each table?
View 3 Replies
View Related
Nov 22, 2007
Hi,
I am attempting to explain my probelm again. Please read it:
I have 3 tables. CallDetail, Call and Request. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on.
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType. The no will remain same for the same CallDetailID and ordered by the date created. However if the CallDetailId changes, the no. will increment based on the empid, Per StateNo, Per CityNo, Per CallType
For eg:
For Eg: ( Assume Call Detail id is changing for all the days)
Monday - 3 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 1 for all 3 calls
Tuesday - 2 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 2 for both calls
Wednesday - 3 calls made for emp id 1, state SA023, city 12 and call type 2 will generate a unique id 1 for 3 calls as the call type is different than the previous day for same employee
Thursday - 2 calls made for empid 2, state SA023, city 13 and call type 1 will generate unique id 1 for both the calls as combi of city and call type are different.
So the unique id has to be generated considering empid, state, city and call type, ordered by the EntryDt. EntryDt is needed because :
3 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 10.00 AM will generate a unique id 1 for all 3 calls
2 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 12.00 AM will generate a unique id 2 as the call was registered later.
Here is what I wrote with the help of a mod over here:
Code Block
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA023', 12, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA023', 12, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA023', 12, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA023', 12, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA023', 12, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 1, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 2, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 1, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '12/5/2007 12:12:34 PM'
-- Query written with help of a helpful person here
UPDATE r
SET r.UniqueNo = dt.CallGroup
FROM @Request r
JOIN @Call c
ON r.CallID = c.CallID
JOIN (SELECT
CallDetailID,
EntryDt,EmpID,
CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt )
FROM @CallDetail
) dt
ON c.CallDetailID = dt.CallDetailID
select * from @Request
OUTPUT IS
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 2
Where as it should be
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA023 12 114 1
22322362 Guck SA023 12 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA023 12 143 2
126756 Jasm SA023 12 145 2
786234 Chuck SA023 12 154 2
66234 Mutuk SA023 12 185 2
2232362 Buck SA023 12 195 1
as the call for Buck is of calltype 3 which was not done earlier. So the no starts from 1.
Also how to add the paritioning by empid, StateNo, Per CityNo, Per CallType and yet maintain the same unique no for the same calldetailid.
Eg: CallGroup = ROW_NUMBER() OVER (PARTITION BY empid, state, city, calltype ORDER BY EntryDt )
thanks for any help.
View 4 Replies
View Related
Jun 13, 2007
Hi: I have SQL Server 2005 Express edition and I am trying to generate a script that someone can take and import into the full version of SQL Server. Using the Generate Scripts Option, I have been able to generate scripts for the various SQL statements that I created - but cannot get it so that the data is included. I'm new to this and would appreciate any help. I have a populated database that I would also like to transfer to the new server.Any help greatly appreciated.Roger Swetnam
View 2 Replies
View Related
Feb 8, 2006
hi, I'm using sql server 2005 standard, and I want to be able to move my local database to another server, but I can't figure out how to script the database and the data so that I can just run one script to move the whole database. this can be done right? I can't imagine that such an obiviously necessary tool would be intentionally left out, so I'm figuring that I'm just a doofus and don't know where the option is...
can anyone help? thank you!
-SelArom
View 2 Replies
View Related
Jul 21, 2015
Is it possible to write a SP (Automate) to generate STATISTICS on any database and then use the output to create the stats on that database.
I ran the tuning adviser and it suggested indexes with lot of STATISTICS on the dev environment. This dev environment is replicated in several other environment with data size in these environment varying. I would like to know if I can create a SP which generates STATISTICS information pertaining to specific database environment for the query in question for tuning.
View 9 Replies
View Related
Mar 20, 2008
When I attempt to generate a datasource model I get the following error messages:
------------------------------------------------
More than one item in the Entity 'Customer' has the
name 'Customer Merge Custs'. Item names must be unique
among immediate siblings. (DuplicateItemName)
More than one Field in the Entity 'Customer' has the
name 'Customer Merge Custs'. Field names must be
unique within an Entity. (DuplicateFieldName)
More than one item in the Entity 'Pricing Service Layout
Detail' has the name 'Pricing Service
Extensions'. Item names must be unique among immediate
siblings. (DuplicateItemName)
More than one Field in the Entity 'Pricing Service Layout
Detail' has the name 'Pricing Service
Extensions'. Field names must be unique within an Entity.
(DuplicateFieldName)
---------------------------------------------------
Examining any of the above tables in SQL Server Management Studio does not reveal any duplicate column names. In fact, 'Customer_Merge_Custs' does not appear to be a column in 'Customer' nor does 'Pricing_Service_Extensions' appear in 'Pricing_Service_Layout_Detail'.
As an experiment, deleting the table 'Pricing_Service_Extensions' and regenerating did make the two associated messages go away.
Steve P.
View 3 Replies
View Related
Dec 7, 2007
Title is probably poorly worded
Say I have a table setup like this:
Code:
AccountID | GeneratedDesc | Col1 | Col2 | Col3 | Order
-------------------------------------------
1|| Data1 | Data2 | Data3 | 1,2,3
2|| Data1 | Data2 | Data3 | 1,3,2
3|| Data1 | Data2 | Data3 | 2,3,1
1|| Data1 | Data2 | Data3 | 1,2,3
Basically, I want to set GeneratedDesc = Data1 + ' ' + Data2 + ' ' + Data3 where an account sets the order 1,2,3
GeneratedDesc = Data2 + ' ' + Data3 + ' ' + Data1 where an account sets the order 2,3,1
Basically, The Generated Description is set in an order that is chosen by the Account.
I am not sure how to go about doing this, outside of dynamically generating the query and looping throughout all the rows in the table, which, i think for large amounts of data, can get expensive. I don't think creating a query for each combination would be good either (in this case, 6 combinations, but for larger order sets, such as 6, can get quite alot of queries).
any ideas? (not sure if this makes sense to anyone)
View 2 Replies
View Related
Jul 20, 2005
We have an MIS system which has approx 100 reports. Each of thesereports can take up to several minutes to run due to the complexity ofthe queries (hundreds of lines each in most cases). Each report can berun by many users, so in effect we have a slow system.I want to seperate the complex part of the queries into a process thatis generated each night. Then the reports will only have to querypre-formatted data with minimal parameters as the hard part will havebeen completed for the users when they are not in. Ideally we willgenerate (stored procedure possibly) a set of data for each report andhold this on the server. We can then query with simpler parameterssuch as by date and get the data back quite quickly.The whole process of how we obtain the data is very complex. There arevarious views which gather data from the back office system. These arevery complex and when queries are run against them including othertables to bring in more data, it gets nicely complicated.The only problem is that the users want to have access to LIVE datafrom the back office system, specifically the Sales team who want toaccess this remotely. My method only allows for data from the nightbefore, so is there an option available to me which will allow me todo this ? The queries can't be improved on an awful lot, so they willtake as long as they take. The idea of running them once is the onlyway I can see to improve the performance in any significant way.True I could just let them carry on as they are and let them sufferwith the performance on live data, but I'd like to do something toimprove the situation for them.Any advice would be appreciated.ThanksRyan
View 2 Replies
View Related
Apr 1, 2008
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
Thanks!
View 1 Replies
View Related
May 25, 2000
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
View 3 Replies
View Related
Aug 10, 2000
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.
How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.
I tried adding the uniqueidentifier field but this gives error message.
The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming
Any help appreciated,Thanks, Keith
View 1 Replies
View Related
Nov 28, 2005
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select
partno2,
MAX (partno) as partno,
alt,
MAX (C_alt) as C_alt,
Max (cmpycd) as cmpycd,
MAX (type) as type,
compFN,
MAX (pndesc) as pndesc,
MAX (equipment) as equipment
into tbl_CLEANED
from tbl_CLEANING
group by partno2, alt, compFN
ORDER BY partno, compFN
The three main columns I need to check against are:
partno2
alt
compFN
I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
View 3 Replies
View Related
Mar 31, 2006
I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
View 6 Replies
View Related
Jan 22, 2008
Absolute SQL Beginner here
I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0
Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer?
Thanks in advance
Larry
View 4 Replies
View Related