I have three tables:
1. RubricReport 2. RubricReportDetail and 3. RubricReportTemplate
RubricReportDetail has columns ReportID and Age
RubricReportTemplate has columns IndicatorID and Salary
I want to insert a row in RubricReport Table with ReportID = @ReportID (RubricReportDetail) and IndicatorID = @IndicatorID (of RubricReportTemplate). How can I do this?
Kindly help me with this. Thanks in advance.
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
View 7 Replies View RelatedHello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go
create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go
create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--------- ---------------- --------------- ---------------- ----
HR 5 7 12 .84
Finance 10 15 25 1.75
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
i'm somewhat at a loss of where to begin.
I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.
Is it any solution to rollback transactions update already done before
the code breaks due to error ?
I was thinking about combining all stored proc on a big one and use
Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.
Any advise highly appreciated !
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View RelatedCan we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as
( Select ContactID from Contact where ContactID=@ContactID)
MERGE Employee as NewEmp
Using Cont as con
[code]...
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedI am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedAn insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
Hello,
I have a DTS package that migrates data from Excel to Access to SQL Server. Before that occurs, at the very beginning, I have a delete script that deletes the existing data in the Access database. When I hit that first step, it gives me the error:
Microsoft JET Database Engine (-2147467259) - The Microsoft Jet database engine cannot open the file '<path to access .mdb>'. It is already opened exclusively by another user, or you need permission to view its data.
I checked the permissions and the account that the web site is running under (impersonated) has full permissions to the file. Also, I am running the web site on my machine, I'm triggering the DTS package through .NET code, the access database is on a file/print server, and the SQL Server is a separate machine.
I'm thinking that maybe the issue is that the access database is on a separate machine, but I'm not quite sure.
Brian
Hi All,
I have a table called [History] with the following fields and types.
Date(datetime), PriceZone(varchar), ProductID(varchar), ProductName(varchar), Category(varchar), SubCategory(varchar), Price(decimal 2 places), Cost(decimal 2 places), Units(Big Int), DollarSales(decimal 2 places), Margin(decimal 2 places), Profit(decimal 2 places)
Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date.
Here is the sample of the records (Just for example I am just using one product in one zone)
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost, Units, DollarSales, Margin, Profit
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,10,20,25,5
2008-04-29,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,8,16,25,4
2008-04-28,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,15,30,25,7.5
2008-04-25,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,13,26,25,6.5
2008-04-22,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,11,22,25,5.5
2008-04-21,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-20,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-19,Zone1,001,Lays Chips,Snacks,Chips,1.95,1.5,18,35.1,23.08,8.1
2008-04-16,Zone1,001,Lays Chips,Snacks,Chips,1.9,1.5,20,38,21.05,8
2008-04-15,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,40,72,16.67,12
2008-04-10,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,32,57.6,16.67,9.6
2008-04-09,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,27,48.6,16.67,8.1
2008-04-08,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,29,52.2,22.22,11.6
2008-04-01,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,30,54,22.22,12
Now using the table information, I want to run a script to get the following fields;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
So output in the above example would be;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,46,224,92.00,424.70,25,22.30,5.00,88.70
Here;
Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output.
Price is the Price for that product in that zone on Date
Cost is the cost for that product in that zone on Date
About "Last4Dates" & "Last12Dates":
"Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30"
"Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"
UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone
DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone
AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone
ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone
How Can I achieve this in SQL?
Can someone please help me as soon as possible.
Thanks,
Zee
We're experiencing a large number of deadlocks since we began runningSQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyperthreading intel processors. We don't have the same problem on Dell6650's w/o the hyper threading. If I turn off the parallel queryprocessing option the deadlocks stop. I've tried all of the suggestionsfrom the Microsoft Knowledge Base under the following link -http://support.microsoft.com/?kbid=837983The only suggestion that actually yielded results was turning offparallel query processing but I don't want to give up what should be aperformance advantage if it wasn't for the deadlocks. Query tuning andindex tuning hasn't helped. Any suggestions? I haven't applied SP4yet. I'm wondering if anyone has seen the same problem resolved withSP4.*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedHi,I have a tricky sql statment I have to write (tricky for me) and I amstuck. I'm having trouble with the following problem.Table1 (Column a, Column b, Column c)Table2 (Column a, Column b, Column c)Table3 (Column a, Column b, Column c)Table1 contains a row of value (1, 2, 3)Table2 contains a row of value (4, 5, 6)Table3 contains a row of value (7, 8, 9)What I need to do is write a single SELECT statement (embedded selectsand so forth are allowed) to produce the table:TableNew (Column a, Column b, Column c)TableNew contains a row of value (1, 2, 3)and a row of value (4, 5, 6)and a row of value (7, 8, 9)I've been playing with the FULL OUTER JOIN and I getTableNew (Table1.Column a, Table1.Column b, Table1.Column c,Table1.Column a, Table1.Column b, Table1.Column c,Table1.Column a, Table1.Column b, Table1.Column c)TableNew contains a row of value (1, 2, 3, null, null, null, null,null, null)and a row of value (null, null, null, 4, 5, 6, null,null, null)and a row of value (null, null, null, null, null, null,7, 8, 9)Or I get only 1 row of data that looks like (1,2,3,4,5,6,7,8,9)If someone has the solution to this problem, please let me know.Thanks.
View 4 Replies View RelatedI'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
View 3 Replies View RelatedI am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Thank you, forum participants. You are the best!
Here are the tables I am dealing with:
Tables
license TABLE
---------------
license_id int PK
vendor_id int FK
po_id int FK Nulls
license_type_id int FK
lic_user_id int FK Nulls
location_id int FK Nulls
lic_start_date smalldatetime Nulls
days_allowed int Nulls
serial_num varchar(50) Nulls
activation_key varchar(50) Nulls
max_users int Nulls
comments varchar(1000) Nulls
software TABLE
------------------
software_id int PK
os_id int
software_name varchar(150)
comments varchar(1000) Nulls
software_license TABLE
--------------------------
license_id int PK , FK
software_id int PK , FK
comments varchar(1000) Nulls
dept_license TABLE
---------------------
dept_id int PK , FK
license_id int PK , FK
comments varchar(1000) Nulls
purch_order TABLE
---------------------
po_id int PK
po_number varchar(50)
po_date smalldatetime
comments varchar(1000) Nulls
dept_purch_order TABLE
----------------------------
po_id int PK , FK
dept_id int PK , FK
comments varchar(1000) Nulls
I am using the following stored proceedure to try to create a useful report from those tables:
Code Block
ALTER PROCEDURE [dbo].[ListDepartmentLicenses]
@Department int,
@Software int
AS
BEGIN
SET NOCOUNT ON;
SELECT dept_license.license_id,
license.lic_start_date,
department.dept_name,
purch_order.po_number,
software.software_name
FROM dept_license
JOIN license
ON dept_license.license_id = license.license_id
JOIN department
ON dept_license.dept_id = department.dept_id
JOIN dept_purch_order
ON dept_purch_order.dept_id = dept_license.dept_id
JOIN purch_order
ON dept_purch_order.po_id = purch_order.po_id
JOIN software_license
ON dept_license.license_id = software_license.license_id
JOIN software
ON software_license.software_id = software.software_id
WHERE (department.dept_id = @Department or @Department Is Null) and
(software.software_id = @Software or @Software Is Null)
ORDER BY license.lic_start_date
END
The sproc compiles fine but I get no rows returned.
I seem to have a little problem with my SQL. I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS). It's a very simple search setup. The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result. Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items). This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested. But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS. Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL. Now this is where it starts playing up. The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount
2 (
3 @SearchQuery nvarchar
4 )
5 AS
6 SET NOCOUNT ON
7
8 SELECT COUNT(*)
9 FROM StoreName_Products
10 WHERE Title LIKE '%' + @SearchQuery + '%'
Nice and Easy. Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005 and running a 'New Query', it retrieves the correct results. (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system. Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number. So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested. Bingo. It's returning the full number of the products.So my question is... What's the deal? How can the same query return two completely different results? and most of all... How do I overcome this? Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL? I'm a bit confused. Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @StartDate and @EndDate
@StartDate Datetime = null
@EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null
AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)
Hello friends,
I am inserting non-english strings into my database table from my java program.
Code Snippet
sql = "insert into static_string1 values (?)";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,statString);
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database.
The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet
String sql = "select String_Id from Static_String1 where String like ('" + statString +"')";
Statement statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
rs.next();
int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' ืืื' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
Table 1
ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E
Result
ID PID Date
4 1 26/02/07
7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next from record should be the same date as the to date. If the code is E then the next to date can be anytime after the previous to date.
I want to be able to report on all record where there is a day difference between the previous to date. I.e. ID 4 and 7 the previous records both have an X and there is at least a days difference between the dates.
Hi all,
I am quite surprise by getting wrong resultset from a simple query like:
select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'
In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null? simply because there is no such date as 2008 yet in the DB.
Help from anyone is needed. Thanks in advance.
Sha.
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
However, in my simple cube I get (B).
Is there a setting I need to adjust to get (A)?
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
Thanks.
I currently insert into one table with: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable (UserName, Password, Email) VALUES (@person, @pass, @email)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text);but I realized that there's another table related to this table and I need to have something go in it so that the users data will be recorded at the same pace. So I tried: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable, FatherHistTable (UserName, Password, Email), (Father) VALUES (@person, @pass, @email), (@father)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text); comm.Parameters.AddWithValue("@father", fthrsNmeLbl.Text);Not working, so I am thinking I must do two inserts: SqlCommand comm = new SqlCommand("INSERT INTO
UsersTable (UserName, Password, Email) VALUES (@person, @pass,
@email)", sqlConnection);
comm.Parameters.AddWithValue("@person", usrnmeLbl.Text);
comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text);
comm.Parameters.AddWithValue("@email", hemailLbl.Text); SqlCommand comm2 = new SqlCommand("INSERT INTO
FatherHistTable (Father) VALUES (@father)", sqlConnection);
comm2.Parameters.AddWithValue("@father", fthrsNmeLbl.Text); Is that the only way to go about it then? Thanks in advance for any explanations.
I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.
View 3 Replies View Related