2

The code i use gives me the correct information based on a date in a where clause. I want to have the same information on other dates. So now I have to change the date myself and run the code, copy/paste it somewhere else and start over again with a new date. That takes a lot of work if i want the information for every day of the year. Is it possible to automaticly change the date in the where clause and what is the best or easiest way to do that?

Select t4.Count, t4.Status
From(
    SELECT count(l.VoerID) as Count, l.Datum, l.Status, l.LogID
      FROM (
             SELECT k.VoerID, k.Datum, MAX(k.LogID) AS LogID
               FROM DB.LogStatus k
                Where Datum < '2013-07-01'
              GROUP BY k.VoerID
           ) m
      JOIN DB.LogStatus l
        ON l.VoerID = m.VoerID AND l.LogID = m.LogID
    Where status in ('B','IN1','IN2''V','Reserv')
    Group by Status
)t4

EDIT:

original table (selected on one VoerID) (table consist of thousands of VoerID's)

LogID Datum UserID Status Time VoerID

1299772 2013-04-17 259 N 14:09:11 50174

1319774 2013-05-23 68 B 11:19:17 50174

1320038 2013-05-23 197 IN1 16:53:30 50174

1322002 2013-05-28 68 IN2 09:22:32 50174

1325052 2013-05-31 161 G 09:00:59 50174

1325166 2013-05-31 10 400 09:15:12 50174

1325182 2013-05-31 10 V 09:30:07 50174

1325208 2013-05-31 10 V 09:45:06 50174

1325406 2013-05-31 10 Reserv 11:45:06 50174

1325522 2013-05-31 10 Reserv 12:15:06 50174

1325954 2013-05-31 10 Reserv 15:15:13 50174

1328474 2013-06-05 10 Reserv 13:15:06 50174

1329230 2013-06-06 10 Reserv 09:45:03 50174

1329244 2013-06-06 10 Archived 10:00:08 50174

1329268 2013-06-06 10 Archived 10:15:08 50174

1330286 2013-06-07 10 Archived 10:15:06 50174

I want to now what was the status of the VoerID on all first of months. so on 2013-05-01 status = N, on 2013-06-01 status = Reserv and from 2013-07-01 it is Archived.

So above is for one VoerID. I want to count the number of VoerID's per first of month, per last LOGID before the first of next month and per status

Finally if I get the information i want to edit it in MSExcel to a crosstable and Chart:

1-1-2013 1-2-2013 1-3-2013 1-4-2013 1-5-2013

N 20 22 24 26 28

B 23 21,5 20 18,5 17

IN1 12 15 18 21 24

IN2 15 7 14 18 25

V 800 1000 1200 1400 1600

Reserv 50 63 76 89 102

Archived 100000 101220 102440 103660 104880

Community
  • 1
  • 1
DBek
  • 21
  • 5

1 Answers1

0

Doing a cross join of all the days of the year, then grouping by that day.

Something like this:-

SELECT COUNT(l.VoerID) as COUNT, m.aDate, l.Status
FROM 
(
    SELECT Sub1.aDate, k.VoerID, MAX(k.LogID) AS LogID
    FROM DB.LogStatus k
    CROSS JOIN 
    (
        SELECT DATE_ADD('2013-01-01', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) AS aDate -- return the first day of the year + all the numbers from 0 to 999
        FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units -- Select units of days
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens -- select tens
        CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds -- select hundreds
        WHERE  DATE_ADD('2013-01-01', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) <= '2013-12-31' -- limit the dates to the days of the specific year
    ) Sub1
    WHERE k.Datum < Sub1.aDate -- This should give up multiple copies of record, one for each date where the d Datum is less that that date
    GROUP BY Sub1.aDate, k.VoerID -- GRoup by date and id, so getting the max log id for each date and id
) m
JOIN DB.LogStatus l
ON l.VoerID = m.VoerID AND l.LogID = m.LogID -- Join where log it is the max log id
WHERE status in ('x','y','z')
GROUP BY m.aDate, Status

EDIT - or for each month:-

SELECT COUNT(l.VoerID) as COUNT, m.aDate, l.Status
FROM 
(
    SELECT Sub1.aDate, k.VoerID, MAX(k.LogID) AS LogID
    FROM DB.LogStatus k
    CROSS JOIN 
    (
        SELECT DATE_ADD('2013-01-01', INTERVAL units.i MONTH) AS aDate -- return the first day of each month of the year
        FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11) units -- Select units of days
    ) Sub1
    WHERE k.Datum < Sub1.aDate -- This should give up multiple copies of record, one for each date where the d Datum is less that that date
    GROUP BY Sub1.aDate, k.VoerID -- GRoup by date and id, so getting the max log id for each date and id
) m
JOIN DB.LogStatus l
ON l.VoerID = m.VoerID AND l.LogID = m.LogID -- Join where log it is the max log id
WHERE status in ('x','y','z')
GROUP BY m.aDate, Status
Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • that seems to work fine. can you break down year code so that i can learn what it does? I must say that the code is very slow though because of the immens table... – DBek Jul 10 '13 at 13:51
  • as the code per day takes way to much time (lost connection) is it possible to change the code to retrieving the information on the first day of the month? – DBek Jul 10 '13 at 14:57
  • Updated it to add a few comments. Are you only ever going to deal with a max of 365 days (ie a year)? If so the select to get the dates could get a smaller range of numbers and be a bit quicker. – Kickstart Jul 10 '13 at 16:02
  • Thanks for the breakdown.It could be that i want the info for the last five years or so, will that be possible? Or can yuou get it quicker if i only work with a max of 365 days? – DBek Jul 11 '13 at 09:47
  • And the Adata givs an image of BLOB... i know i can turn an option on to see the value but is that to fix within the code? – DBek Jul 11 '13 at 09:48
  • I must also add that the MaxLOG id is more important to dertermine the last Status. Now the code sees the last date (e.g. in juni), than selects the first of june and the corresponding status. However the last status is different from the status on the first of juni. (every VoerID ends with status 'archived') ALSO in case of 1 VoerID the code returns one row being the first day of the last month of logging. However i want to see the history of a VoerID: it's status on each first of the month. (count all the VoerID's with a certain status on the first of the mont. – DBek Jul 11 '13 at 10:01
  • Can be made to work for 5 years easily. Just change the inner select that generates the dates. There isn't a field called Adata, and aDate should be a date format field. As to your last point I am getting very confused over what you want. It might be easiest if you edit your original question with the structure of your tables (even better the table declare) and also some same data and the results you would want from that sample data. – Kickstart Jul 11 '13 at 10:13
  • I am a bit unsure about the second stage of your requirements ( _I want to count the number of VoerID's per first of month, per last LOGID before the first of next month and per status_ ). However I am not sure that the inner select to find the latest log id for each voerid for each month can be done more efficiently within SQL. It will generate a massive number of intermediate records (ie, for 5 years worth of months and 10000 voerids it will generate 6000000 intermediate records). Part of the problem is that the laest logid for a month could be from months earlier.. – Kickstart Jul 11 '13 at 12:17
  • I know! difficult problem. I think i'll just do it manualy per month. Takes some time for the past years. After that i can monthly add the latest month.. Thanks for your help,understanding and patience :-) – DBek Jul 11 '13 at 13:27
  • No problem. Even a single months calculation is fairly complex – Kickstart Jul 11 '13 at 13:44