Brilliant thank you Set that does the trick
Stef van der Merwe
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 06 March 2017 09:05 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] QUERY QUESTION
WITH TMP(ItemNr
t;
> Stef van der Merwe
>
>
> -Original Message-
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Sent: 03 March 2017 10:27 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] QUERY QUESTION
>
&
.
>>
>> However when trying to query a snapshot for an earlier date, other than
>> the
>> most recent day, seems problematic.
>>
>> Stef van der Merwe
>>
>>
>> -Original Message-
>> From: firebird-support@yahoogroups.com
>> [ma
-support@yahoogroups.com]
Sent: 03 March 2017 10:27 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] QUERY QUESTION
Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [firebird-support]:
> Good day,
>
> I have a question for you SQL boffins, how can I q
You could use a stored procedure
SET TERM ^ ;
CREATE OR ALTER PROCEDURE CONSECUTIVE_DAYS (ipITEMNUMBER CHAR(3))
RETURNS (opDATEFROM DATE, opDATETO DATE, opCONSECUTIVEDAYS INTEGER)
AS
DECLARE VARIABLE vItem CHAR(3);
DECLARE VARIABLE vDate DATE;
BEGIN
opCONSECUTIVEDAYS = 0;
-- get latest
Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [firebird-support]:
> Good day,
>
> I have a question for you SQL boffins, how can I query a table to get the
> count of consecutive days found ?
>
> A Table contains rows with dates where an item(s) has been added for every
> day this item was
From: firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>
[mailto:firebird-support@yahoogroups.com]
Sent: 03 March 2017 01:20 AM
To: firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>
Subject: RE: [firebird-support] QUERY QUESTION
@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 03 March 2017 01:20 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] QUERY QUESTION
Stef,
Maybe this query might work for you.
WITH CTE
AS (SELECT J0.*,
(SELECT COUNT(*)
FROM
Stef,
Maybe this query might work for you.
WITH CTE
AS (SELECT J0.*,
(SELECT COUNT(*)
FROM TEST_TABLE S1
-- LEFT OUTER JOIN TEST_TABLE S2 ON S1.ITEMNR = S2.ITEMNR AND
S1.DATEUSED = S2.DATEUSED - 1
WHERE S1.DATEUSED >= J0.START_DATE
Select itemnr,count(*) as days
>From table
Group by itemnr,cast(dateused as date)
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 02 March 2017 06:37 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] QUERY QUESTION
Importance: High
select itemnr count(dateused) from table group by itemnr
Best Regards
| ISMAEL |
- Original Message -
From: 'Stef' s...@autotech.co.za [firebird-support]
To: firebird-support@yahoogroups.com
Sent: Thursday, March 02, 2017 11:36 AM
Subject: [firebird-support
11 matches
Mail list logo