Viewing 15 posts - 286 through 300 (of 355 total)
The following query will return a resultset with 64 rows for each day that lies within the specified date range (@StartDate to @EndDate).
The columns in the resultset are:
SlotNumber intInteger between...
February 16, 2009 at 7:38 pm
SELECT
START.Value AS StartTime,
STOP.Value AS StopTime,
...
February 16, 2009 at 5:46 pm
Are you sure you want 64 columns in the resultset?
I think it might be simpler to return 1 row per 15 minute slot, i.e. 64 rows per day.
Also, do you...
February 16, 2009 at 4:54 pm
If all you need are dates and can do with out time of day and assuming that all rows have date first with similar format, You can try this:
datediff(d,convert(datetime,left(start,10)),convert(datetime,left(stop,10)))
This...
February 16, 2009 at 2:54 pm
What are the different date/time formats you've already got in your table?
Is there any sort of control (e.g. front-end validation) over what strings can be inserted?
How is data inserted...
February 16, 2009 at 1:50 pm
Either format the total hours worked in front end, or if this is not applicable something like:
SELECT CONVERT(varchar(10), CONVERT(int, SUM(HoursWorked))) + ':'
+ RIGHT('0' + CONVERT(varchar(2), CONVERT(int, FLOOR(60.0 * SUM(HoursWorked)...
February 16, 2009 at 1:34 pm
Storing date/times in SQL server as a varchar (or nvarchar) data type is usually a very bad idea.
The following TSQL shows how you can convert your date/time string with time...
February 16, 2009 at 12:58 pm
Jeff,
You got me worried with your doubts about this ROW_NUMBER() difference technique.
I used this technique in a proposed solution to this topic:
http://www.sqlservercentral.com/Forums/Topic656492-338-1.aspx
However, I think the proposed solution is valid,...
February 15, 2009 at 4:43 am
The query below produces a resultset that matches the data in tblExpectedResults provided by the OP. It requires a Tally table containing consecutive integers starting at 0 and containing at...
February 13, 2009 at 6:32 pm
Just thought of another way of dealing with the XML encoding, which is probably better than my previous offering. This method uses the XQuery value method to decode the generated...
February 13, 2009 at 3:04 pm
Or this...
SELECT DISTINCT S.Computername
FROM dbo.tblSoftware S
LEFT JOIN dbo.tblRegistry R
ON (S.Computername = R.Computername AND R.RegKey IN ('Regkey1', 'Regkey2'))
WHERE (S.SoftwareName = 'App1')
AND (R.Computername IS NULL)
February 13, 2009 at 2:32 pm
I think these are the XML characters that will most likely give you trouble (and it is hard work to get the entity encodings to display properly in this forum...
February 13, 2009 at 1:44 pm
I adapted the following solution from this article by Jeff Moden:
http://www.sqlservercentral.com/articles/Test+Data/61572/
INSERT #product (ProductId, Categories)
SELECT
C1.ProductID,
Categories = (
SELECT LTRIM(C.CategoryText + ' ')
FROM #category C
WHERE (C.ProductId = C1.ProductId)
FOR XML PATH('')
)
FROM...
February 13, 2009 at 1:08 pm
These error messages are pretty clear.
Read the error messages then look at your query.
February 13, 2009 at 11:25 am
This should be doable by joining table dbo.tblSupplierSeasons with a Tally table or a Calendar table to produce a resultset where each row corresponds to a single day, then joining...
February 13, 2009 at 7:23 am
Viewing 15 posts - 286 through 300 (of 355 total)