Database

chichocojo wrote:

I need to generate a table with 365 records/days per year per hotel as follows:

DECLARE <a href="/Members/Year">@Year</a>int = 2014;

-- Get 1st January in the specified year:
DECLARE @StartDate date = Convert(date, Convert(char(4), <a href="/Members/Year">@Year</a> + '0101');

WITH cte1 (N) As
(
    SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    -- 8 rows
),
cte2 (N) As
(
    SELECT TOP 366
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM
        cte1 As A
        CROSS JOIN cte1 As B
        CROSS JOIN cte1 As C
        -- 8 * 8 * 8 = 512 rows
),
cteDates (Value) As
(
    SELECT
        DateAdd(day, N, @StartDate)
    FROM
        cte2 -- 366 rows
    WHERE
        -- If this isn't a leap-year, we only need 355 rows:
        Year(DateAdd(day, N, @StartDate)) = <a href="/Members/Year">@Year</a>
)
SELECT
    Value
FROM 
    cteDates
ORDER BY
    Value
;

SELECT  DISTINCT
        CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit, 
        CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
FROM    EMSINFO ems JOIN EMSQUIRKINFO emsqi
    ON  emsqi.emsinfoid = ems.emsinfoid
WHERE   trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND     CC_GETNEXTENTER(ems.EMSINFOID, emsqi.PLATENUMBER, ems.PASSDATETIME) <> 0
AND     CC_CheckForExit(ems.PASSDATETIME, (
            SELECT  ems2.PASSDATETIME from EMSINFO ems2
            WHERE   ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME)
             ),emsqi.PLATENUMBER) <> 0 
AND     ems.masterplatenumber = '150444833' and rownum <100 ;

WITH bla AS (
    SELECT  CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit, 
            CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
            CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME) GetNextEnter
    FROM    EMSINFO ems JOIN EMSQUIRKINFO emsqi
        ON  emsqi.emsinfoid = ems.emsinfoid
    WHERE   trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
    AND     ems.masterplatenumber = '150444833'
    )
SELECT  DISTINCT
        DetailEnterExit,
        DurationInZone
WHERE   GetNextEnter <> 0
AND     CC_CheckForExit(
            ems.PASSDATETIME, 
                (
                SELECT  ems2.PASSDATETIME from EMSINFO ems2
                WHERE   ems2.EMSINFOID = GetNextEnter
                )
                ,emsqi.PLATENUMBER) <> 0 
AND     rownum <100
;

khun_panya wrote:

But would an index on cs.city_id make any different?

khun_panya wrote:

Because it looks like we have to scan entire Customer table anyway.

Shameel wrote:

khun_panya wrote:

Because it looks like we have to scan entire Customer table anyway.

What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.

UPDATE YourTable SET ... WHERE ID = @ID

If @@ROWCOUNT = 0
   INSERT YourTable (...) VALUES (...);

Richard Deeming wrote:

In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]