Hi, i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.
We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.
DECLARE @FINALVAUE VARCHAR(MAX) = (Convert(decimal,@CAUSATIVE_FACTORS_ID) - 1) + Convert(decimal,@LU_PERCENTAGE_FAILURE_IN_SAMPLE_ID) + Convert(decimal,@LU_REPORTING_HISTORY_ID)
PRINT @FINALVAUE -- the answer is 3
The Calculation is like this
2) Query 2
DECLARE @FINAL VARCHAR(MAX)
SET @FINAL= (4-1) + 0.08 + 0.2SELECT @FINAL -- the asnwer is 3.28
The first Query i am retrieving values from the Variables which are varchar and converting to decimal and doing the calculations. and the second Query i am just taking the value as is. i used this to debug in trying to solve this problem. My problem here in the first Query in the @FINALVAUE variable the answer is "3" and in the second Query the answer is "3.28"
, i want the first Query to display the same value which is "3.28"
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
I'm trying to consolidate 3 operations into 1
Not sure if it's possible, but it's seems like it is.
I want to select some data, and take the data from 1 table and update it to another table.
Her's what I have. It's kind of a soup at the moment, experienenting with different ideas.
In my Create Table, I can't figure out how to use those value in the update in place of the parameters.
Perhaps I should just use the line in Update, the select for CardLabel.
Here I get must declare the scalar @CCInfo in the UPDATE Section
I understand what it means, I just don't know how to go about getting the scalar to be recognized in Update.
The first part works, just having trouble with the update.
I went for the later in this function, but found out later that I could of just declared and set all the card data first
and then run the UPDATE using the variables.
So I ended up with this, I shortened it to keep it more brief
UPDATE" & _
OrderInfo " & _
, Cardlabel=(SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=(SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpMonth=(SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpYear=(SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID)
But later the next day I ended up writing something that does everything in one shot, far beyond I could imagine that I can do. So I modified the above to this below
DECLARE" & _
, @CardBrand VarChar(80)
, @CardExpMonth VarChar(80)
, @CardExpYear VarChar(80)
, @New_OrderNumber VarChar(80);
SET @CardLabel = (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardBrand = (SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpMonth = (SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpYear = (SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID);
IF EXISTS(SELECT * FROM OrderInfo WHERE OrderNum=@OrderNum)
Then, I was able to do more,
delete previous cart items,
generate a new order number,
copy the cart contents over
return the new order number
all in one shot now, use to take me like 8 functions to do it all. I'm amazed at how fast it runs now.
I would post it, but it's too large
I'm stoked on how it came out, and learned a lot on my own. I think I got the semi-colons right this time, I need to read up on that.
I have created a UNION query as the source for a report that is written to a Word Document using VBA. The UNION joins a Table (historical information from before the current database Application was written) and the output of a Query that extracts corresponding information from the live data. I have recently added a new text field to both 'sides'. The value of the text field for the Query (either "S" or "") is generated in an IIF() statement within it.
When I Open the UNION query via the Access interface, it works as intended - the new text field is set to the correct value for records derived from either source. When I open a ADO Recordset based on the UNION query in VBA, the records derived from the Table are correct, but the contents of the new field in the records derived from the Query is always a Null String (N.B. not Null). If I convert the Query which is the second source of the UNION into a Make Table Query, run it, and then UNION the two Tables, everything again works as it should. Does this make any sense to anyone?
How to execute the below piece of code correctly. Please help me.
INSERT INTO #TempTable
if (@Id = 0)
SELECT PatchAppId, PatchName,HostName FROM Vw_ProductionAutoMailData1
SELECT PatchAppId, PatchName,HostName FROM Vw_ProductionAutoMailData2
Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM)
I want to convert it into (16/04/2014). When i use convert function it gives error message
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem.
these formats i have been using this statement to convet data.
select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1
The other two formats will be impossible to convert unless your data contains another field indicating whether they're dd/MM/yyyy or MM/dd/yyyy. Without that additional field, there would be no way to know whether 01/02/2014 should be 1st February or 2nd January.
If you do have an additional field indicating the format of the date, then your options depend on the version of SQL that you're using. SQL 2012 and 2014 have the PARSE method[^]:
PARSE('16/4/2014'As date USING 'en-GB')
For older versions, you would need to write your own function to parse the string.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Last Visit: 31-Dec-99 18:00 Last Update: 22-Aug-14 5:06