Puzzler
Aug 7, 2001
Hey,
Here's a little puzzler for you all
I have a Windows NT4 server installed with MS SQL 7 and am working at the server itself. I log on and my regional settings are set to English (British).
I log off and a work mate of mine logs on and his regional settings are set to English (US).
Now, when we run a query that returns a date, the date format changes to the regional setting (as expected).
Here's the question: When no-one is logged on to the server and all the SQL Server services are running and I connect via a client machine, What regional settings does the server take when returning date formats? Also, If a stored procedure is called on the server and manipulates dates, what regional settings will it use?
Cheers
Jon
View 3 Replies
Nov 9, 2007
I need to write a query that finds a set of records from a table that meets certain conditions:
1) First, the table has to be sorted by a dollar amount column, lowest to highest
2) Then the table has a "rate" column that needs to add up to a certain value, say 1 (100%) (rate has numbers < 1 and >0, say .0024) starting with the bottom of the sorted table (highest dollar amt) working backwards until it reaches the target amount (1).
Example: After sorting the table by $, take the rate (e.g. .005) from the last record (highest $), add the rate to the second-to-last record, and keep doing a running total going backwards until the sum(rate) = 1. This then is the resultset that needs to be returned.
I know I can do this with temp tables and cartesian products, but would like to know if there is a more elegant way of doing this with the PARTITION, RANK and ORDER functions in SQL 2005, or possibly a CTE.
-Kory
View 10 Replies
View Related
Nov 8, 2007
Ok, here's a puzzler for you...
It's the classic story; I have inherited a system etc etc which stores timesheet information.
The design is, as you'd expect, "intriguing".
There are fields to store the number of hours worked as a decimal; Rate1 & Rate2
There are also fields used throughout the application which are the datetime equivalents (from the base date) of these times; Eng_Hours_Rate1 & Eng_Hours_Rate2.
The problem is, the Eng_Hours_Rate fields do not get updated when a user changes the Rate value(s).
Now because I don't know the language the FE is programmed in too well, I thought I'd stick to something I am a bit better in.
So I thought - UPDATE TRIGGER!
However, I can't get my head around the conversion calculation - below shows the closest I have got yet. When I set the Rate field to 2.5, I get the datetime value 1900-01-01 02:29:59.940 which is very close, but not quite!
Any ideas on what to do to get this working are greatly appreciated :D
Here's the ddl of the relevant fields
--Create mock table
CREATE TABLE timesheet_mod (
UniqueID int identity(1,1) primary key NOT NULL
, Eng_Hours_Rate1 datetime NULL
, Eng_Hours_Rate2 datetime NULL
, Rate1 decimal(5,2) NULL
, Rate2 decimal(5,2) NULL
)
Here's some test data
--Insert test data
SET NOCOUNT ON
INSERT INTO timesheet_mod (Eng_Hours_Rate1, Eng_Hours_Rate2, Rate1, Rate2)
SELECT '1900-01-01 01:30:00.000','1900-01-01 01:00:00.000',1.50 ,1.00 UNION ALL
SELECT '1900-01-01 03:00:00.000','1900-01-01 00:00:00.000',3.00 ,0.00 UNION ALL
SELECT '1900-01-01 04:30:00.000','1900-01-01 00:30:00.000',4.50 ,0.50
SET NOCOUNT OFF
GO
Here's my attempt at the trigger
CREATE TRIGGER rate_Update
ON timesheet_mod
FOR UPDATE
AS
UPDATE timesheet_mod
SET eng_hours_rate1 = (u.rate1 / 24)
, eng_hours_rate2 = (u.rate2 / 24)
FROM timesheet_mod t
INNER
JOIN inserted u
ON u.uniqueid = t.uniqueid
WHERE t.UniqueID IN (SELECT UniqueID FROM inserted)
GO
And here's the test bit followed by a cleanup
SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)
UPDATE timesheet_mod
SET rate1 = 2.5
WHERE uniqueid IN (1,2)
SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)
--Clean up
DROP TRIGGER rate1_Update
DROP TABLE timesheet_mod
If I've not made this clear, or you fancy insulting my methods vigorously - please feel free ;)
EDIT: In theory the users should only ever be entering timesheet entries accurate to the nearest 1/4 of an hour (or so I'm told, ha!)
View 6 Replies
View Related
Aug 8, 2001
Hi,
I have an interesting query problem. The result needs to be a single recordset as from a select statement, here's the prerequisites:
Tables:
tShip
tPerson
tAddress
tCertificate, has a field dtmExp (datetime)
where each Ship has one or more Persons which have one or more Addresses and each ship has one or more certificates.
Now, I need the query to return all ships, all persons and all addresses for those persons BUT ONLY the certificate with the latest expiration date (sort of like the result from a 'select top 1 from tCertificate order by dtmExp desc')
How can this be packed into one query? using inner joins on all tables will return multiple rows for each certficate for a ship, which it must not.
Any help greatly appreciated!
-. Balt
View 2 Replies
View Related
Dec 4, 2007
Greetings,
I have a somewhat complex stored procedure running on 3 production servers. On 2 of the 3 servers the stored procedure executes in three seconds. The stored procedure runs to completion on the 3rd production server but requires 8 minutes to complete.
The sproc contains a single query that contains correlated subqueries that each call the same user function. If I copy the sproc's query and execute it directly from Management Studio, the query runs in 3 seconds, just like the stored procedure on the other two production servers. The sproc has one parameter.
I have updated statistics and recompiled the stored procedure and user function (on the 8 minute server). That doesn't help. I have used the WITH RECOMPILE option in the stored procedure. The query execution plan for the stored procedure is not the same execution plan prepared for the sproc's query when it is run on a standalone basis. I'm no expert with execution plan analysis but I can see significant differences between the sproc and the sproc's query.
The 3 servers are supposed to be identical in hardware and software configuration.
Can anyone provide any insight into what might be going wrong on the 8 minute server?
Thanks,
BCB
View 9 Replies
View Related
