|
Hi,
I need to create a table in SQL that will compute costs. I have to read in some information from two other tables and use this to populate the new table from which the costs are calculated. As the data is from a website, it is possible that it will change and I need the costs to update if the data changes. Do I need to create a function to do this, or will the table do it on its own? ( I elected to do a function, but if there is a better way I am open to that)
This is what I have so far:
set ANSI_NULLS ON
go
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[fcn_computecosts]
( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint,
@Cost money, @percent int, @totalDays int, @totalcost)
RETURNS @ComputeCosts TABLE
( ProjectID int, userID int, startDate datetime, enddate datetime, quarter tinyint,
Cost money, percent int, totalDays int, totalcost)
AS
BEGIN
SELECT
P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
COUNT(C.date) AS totalDays, ((percent/100)*totaldays*Cost*8) AS totalcost
FROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <=
P.enddate
WHERE (C.isWeekday =1) AND (C.isHoliday=0)
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
totalDays, totalcost
ORDER BY P.ProjectID
RETURN
END
However when I run this I am getting the following error: "msg 207, Level 16, state 1: Invalid column
name 'totalDays', 'totalcost', 'Cost'
Please help!
|
|
|
|
|
Isn't @totalcost missing the datatype declaration ?
CREATE FUNCTION [dbo].[fcn_computecosts]( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint, @Cost money, @percent int, @totalDays int, @totalcost)
|
|
|
|
|
In your group by clause, you are trying to group by totalDays and totalcost which are aliases to computed columns. I've seen it where you have to repeat the function again in the group by clause instead of referencing the alias name. Something like below.
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent, COUNT(C.date), ((percent/100)*totaldays*Cost*8)
Do you really need to group by these values ?
|
|
|
|
|
You Not Grouping Properly
SELECT
P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
COUNT(C.date) AS totalDays, ((percent/100)*COUNT(C.date)*Cost*8) AS totalcost
FROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <=
P.enddate
WHERE (C.isWeekday =1) AND (C.isHoliday=0)
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent
ORDER BY P.ProjectID
You Have No Need To It totalDays,totalCost in Group By
|
|
|
|
|
Hi,
My front end is VB.net 2008. Database - sql server 2005
I have two import processes. In both process there are two tables where insertion updation takes place. After running both the process simultaneously, I am facing the following crash
Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
This is the exception, which is coming in between the simultaneous import process.
I then used an alternative to resolve this problem. When ever this exception comes, I resend te control(at front end) from catch block to the beginning of the function, this is letting the second process to complete and as I mentioned the control again starts to execute the process from the scratch , it do the work nicely now for first process also.
Now both the imports are working fine.
For both the processes, I am using the Begin , Commit , Rollback Transaction.
Is it enough?
Please let me know if you face any problem in understanding the question.
|
|
|
|
|
Even we had deadlock proplem in one of our SQl Job. We did following.
Used Begin Try End Try block & Begin catch End catch block.
Use a flag in the permanent table which indicated processed or not. When trying to rerun run only those which is not processed.
This way second time when you run it processes only which are not processed.
|
|
|
|
|
Hi again,
I need to count how many times a certain value is appearing but I have no idea how to do it.
I have yet to get a response in my other thread so I am not holding out too much hope but it would be cool if someone who knew could shed some light? 
Cheers
|
|
|
|
|
If you mean by using T-SQL then you can do by this query:
select count(fieldname) as appearedvalue,fieldname<br />
from tablename<br />
groub by fieldname
Hope this will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You asked:
stevemarsh99 wrote: I need to count how many times a certain value is appearing but I have no idea how to do it.
The statement provided above will only count how many values are in a certain field. If that's what you need then ignore the rest of this post
However, if you want to count how many times a certain value appears you will need to use the distinct function. I recommend:
SELECT Count(DISTINCT FieldName) FROM TableName
Easy 
|
|
|
|
|
Sorry stevemarsh99 and blue_boy 
I just realized what you actually wanted 
blue_boy's solution will work better 
|
|
|
|
|
I'm glad that my solution works for you. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Guys and Girls
I have been wrecking my brain all day trying to solve this problem and I hope some of you can help. I am using sql server 2005 to write a compounded stored procedure that does a series of things:
1. it reads the values from one column, removes the empty spaces around it, and stores it in another column (lets call it x) on the same table. *works*
2. it must check if the the new column (x) exists in the column of a different table *no errors*
3. if it exist, it needs to check if a value has 12 characters and begins with the character 's' *broken*
4. if it exist, it needs to check if another value has 13 characters and begins with the character 's'
5. finally, it must tag all the rows where the value exists in another table column
the code so far is:
ALTER PROCEDURE [dbo].[spValidateSerials]
@LineItemID int
AS
BEGIN
UPDATE dbo.SerialNumberStaging
SET dbo.SerialNumberStaging.NewSerialNumber = lTrim(rTrim(dbo.SerialNumberStaging.SerialNumber)),
dbo.SerialNumberStaging.NewMacAddress = lTrim(rTrim(dbo.SerialNumberStaging.MacAddress))
if @LineItemID IN (Select dbo.sn_VendorProduct.fkCompanyID from dbo.sn_VendorProduct)
BEGIN
-- step 3: check company & serial number
if ((Len(dbo.SerialNumberStaging.NewSerialNumber) = 12) AND
(SubString(dbo.SerialNumberStaging.NewSerialNumber, 1, 1) = 's'))
UPDATE SerialNumberStaging
SET SerialNumberStaging.NewSerialNumber = SubString(SerialNumberStaging.NewSerialNumber,2,11)
END
UPDATE SerialNumberStaging
SET RowIsError = 1
WHERE (NewSerialNumber
IN
(SELECT SerialNumber FROM LineItem))
RETURN
END
as it stands, it is giving me the follwing error:
The multi-part identifier "dbo.SerialNumberStaging.NewSerialNumber" could not be bound. in reference to line 26 (the second IF statement)...
ANY HELP WILL BE GREATLY APPRECIATED!!!
Thanks
|
|
|
|
|
Use sp_help on the table SerialNumberStaging to show the table definition and post it here.
|
|
|
|
|
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
SerialNumberStaging dbo user table 2009-03-18 14:39:18.647
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
snsID int no 4 10 0 no (n/a) (n/a) NULL
SerialNumber nvarchar no 510 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
HostID nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
AssetTag nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
IPAddress nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
MacAddress nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
NewSerialNumber nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
NewMacAddress nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
RowIsError nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
snsID 1 1 0
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_SerialNumberStaging clustered, unique, primary key located on PRIMARY snsID
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK_SerialNumberStaging (n/a) (n/a) (n/a) (n/a) snsID
No foreign keys reference table 'SerialNumberStaging', or you do not have permissions on referencing tables.
No views with schema binding reference table 'SerialNumberStaging'.
|
|
|
|
|
The specs for the stored proc were incorrect. With the new specs we've managed to fix the problem. The gritty step that was causing the confusion now looks like this:
UPDATE dbo.SerialNumberStaging.NewSerialNumber
SET SerialNumberStaging.NewSerialNumber = SubString(SerialNumberStaging.NewSerialNumber,2,11) --12char
WHERE (Len(SerialNumberStaging.NewSerialNumber) = 12)
AND SubString(dbo.SerialNumberStaging.NewSerialNumber, 1, 1) = 's' --starts with 's'
AND dbo.SerialNumberStaging.NewSerialNumber -- it exists in lineItem
IN (SELECT dbo.LineItem.SerialNumber
FROM LineItem
WHERE dbo.LineItem.ID = @LineItemID
AND (dbo.LineItem.fkCompanyID -- line item or order???
IN (SELECT dbo.sn_Company.fkCompanyID
FROM dbo.sn_Company
)
)
)
Everything seems to be working now. 
Thanks Guys
|
|
|
|
|
Is there an equivalent '#Define' preprocessor for Microsoft's T-SQL, as in C++ ?
I want to be able to declare two table names, then go on and use these name definition
elsewhere in the SQL statement
eg.
<br />
#DEFINE TableA 'SomeTable'<br />
SELECT * FROM TableA
I Dream of Absolute Zero
|
|
|
|
|
|
RChin wrote: Is there an equivalent '#Define' preprocessor for Microsoft's T-SQL, as in C++ ?
Not that I'm aware of, but you might consider writing a view 
I are troll
|
|
|
|
|
Hi all!!
I need to pull a table from this simple db:
User:
[p_user_id] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](50) NOT NULL,
[sname] [varchar](50) NOT NULL,
[email] [varchar](50) NOT NULL,
[accesslevel] [int] NOT NULL,
[password] [varchar](50) NOT NULL,
[lastlogin] [datetime] NULL,
[logincount] [int] NULL,
Event:
[p_event_id] [int] IDENTITY(1,1) NOT NULL,
[eventdate] [datetime] NOT NULL,
[eventtype] [varchar](50) NOT NULL,
[firsthalfcolour] [int] NULL,
[secondhalfcolour] [int] NULL,
[firsthalfscore] [int] NULL,
[secondhalfscore] [int] NULL,
and Link:
[p_link_id] [int] IDENTITY(1,1) NOT NULL,
[f_event_id] [int] NOT NULL,
[f_user_id] [int] NOT NULL,
[f_colour_id] [int] NOT NULL,
And I need to get a list of all the users with their win percentage. firsthalfcolour (winning colour) = f_colour_id (your team colour).
I have this to calculate an individuals percentage but I dont know how to get a 'group' overview?  :
CREATE procedure [dbo].[getwinpercentage_byuserid]
(
@userid int
)
as
declare @First integer
declare @Second integer
declare @Third integer
select @First = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on firsthalfcolour = p_colour_id
where f_user_id = @userid and firsthalfcolour = f_colour_id
select @Second = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on secondhalfcolour = p_colour_id
where f_user_id = @userid and secondhalfcolour = f_colour_id
select @Third = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on firsthalfcolour = p_colour_id
where f_user_id = @userid
select (CONVERT(varchar, ((@First + @Second) * 100) / (@Third * 2)) + '%') as 'Success rate'
If you can help it would be greatly appreciated!
|
|
|
|
|
Hi,
I have a Microsoft Office Excel Comma Separated Values File (.csv)with 200,000 number of rows in excel.
I want to import this to SQL 2008 database.
There is a option to import in SQL but no option to import .csv file. So what would be the best way to import this file.
I have created a table with correct column name, next thing i wanna do is to import all the data.
I have tried below query, but no luck so far...
<br />
BULK <br />
INSERT abc_table<br />
FROM 'C:\xyz303.csv'<br />
WITH<br />
(<br />
FIELDTERMINATOR = ','<br />
)<br />
GO
Thanks
|
|
|
|
|
|
I do not have SQLServer 2008 installed, but I do have SQLServer 2005. Did you see an option for a flat file source? You should be able to import it using that.
Tim
|
|
|
|
|
I want to create a sequence in Oracle, and I want the start value to be the same as the value in another sequence.
This doesn't work:
CREATE SEQUENCE CUSTOMERID_SEQ
MINVALUE 1
MAXVALUE 99999999
INCREMENT BY 1
START WITH (SELECT THAT_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL FROM DUAL)
NOCYCLE
NOORDER
NOCACHE As there can be no subqueries in a Create Sequence
Any hints?
|
|
|
|
|
cant be done .. at least, not like this - start has to be an integer (so it doesnt even evaluate your select clause)
I could do it from c++ using oci - I'd do a [SELECT THAT_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL FROM DUAL] into an integer variable and then use that variable as a replacement into a create statement and execute the create statement.
Sorry, likely not what you were looking for ...
'g'
|
|
|
|
|
I wonder if it's possible to do that in pl-sql?
Then it could be saved to a script.
Thanks for the idea!
I'll try it this afternoon.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
