Duplicate post, see http://www.sqlservercentral.com/Forums/Topic559341-338-1.aspx

Duplicate post, see http://www.sqlservercentral.com/Forums/Topic559341-338-1.aspx

GSquared (8/27/2008)

---

Why build a function for it? That's row-by-row processing. The join I gave you should do what you need. I've used that type of construct many...

Andrew Peterson (8/27/2008)

---

The main issue here is to get the previous odometer readings, given the current vehicle ID and reading date. So, first I would setup a function that...

Gald to be of assitsance, Chnadu!

Each BEGIN should have one corresponding END.

This is also likely to cause some problems...

SELECT distinct

...

Hi Chris, thanks for pointing this out, though I agree that the OP should do some of the work here! My solution will work where there are dupes in the...

Here is another possible solution:

[font="Courier New"]-- Create sample data

CREATE TABLE #Table ([no] INT, [name] CHAR(3))

INSERT INTO #Table ([no], [name])

SELECT 1, 'aaa' UNION ALL

SELECT 1, 'bbb' UNION ALL

SELECT 1, 'ccc' UNION...

This should do the trick:

SELECT...

kumar99ms (8/27/2008)

---

Employee table

EmployeeID Employeename Employeecode Passportcode

1 aaaa ...

[font="Courier New"]-- make some sample data

SET DATEFORMAT MDY

DROP TABLE #Temp

CREATE TABLE #Temp (FuelLogID INT, VehicleID INT, [Date] DATETIME, FuelQty INT, EndOdometer INT)

INSERT INTO #Temp (FuelLogID, VehicleID, [Date], FuelQty, EndOdometer)

SELECT 1,...

Sure Jack!

Multiply the decimal fraction by 4,

0.26 becomes 1.04

0.24 becomes 0.96

then Cast as INT to truncate

0.26 becomes 1

...

Whatever did we do without tally tables?

[font="Courier New"]DROP TABLE #ADPTimeDetail

CREATE TABLE #ADPTimeDetail (EmpID INT, [IN_Punch] DATETIME, [OUT_Punch] DATETIME, Job VARCHAR(3), [Punch_RecID] INT)

INSERT INTO #ADPTimeDetail (EmpID, [IN_Punch], [OUT_Punch], Job, [Punch_RecID])

SELECT 1,...

Good point Ryan:

[font="Courier New"]DECLARE @Delimiter VARCHAR(4)

SET @Delimiter = ':61:'

SELECT * FROM (

   SELECT SUBSTRING([Message]+@Delimiter, number-LEN(@Delimiter),

           LEN(@Delimiter)+CHARINDEX(@Delimiter, [Message]+@Delimiter, number) - number) AS MESSAGE

   FROM Numbers, #t1

   WHERE...

Using a few changes to the ever-popular uftSplitString function,

[font="Courier New"]

CREATE TABLE #t1

(

MESSAGE VARCHAR(8000)

)

INSERT INTO #t1

SELECT '

{1:F01PARBESMXFXXX0074301877}

{2:I950MLILGB4LXKSIN}

{3:{108:MMD0192030369000}}

{4:

:20:GL0710250013

:25:0000110405

:28C:00211/00001

:60F:C071024EUR778881,80

:61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148

ES0111845014 AC.ABERTIS INFRAE

:61:0710251025CR1,31NDIVCD879992ES01//1CD879992

ES0109427734 AC. ANTENA 3

:61:0710251025CR1,31NDIVCD880006ES01//1CD880006

ES0109427734 AC. ANTENA 3

:61:0710251025CR1,31NDIVCD880008ES01//1CD880008

ES0109427734 AC. ANTENA 3

:61:0710251025CR1,31NDIVCD880025ES01//1CD880025

ES0109427734 AC. ANTENA 3

:61:0710251025CR1,31NDIVCD880029ES01//1CD880029

ES0109427734...