It is currently Wed Sep 18, 2019 9:11 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Create monthly table from sqlite?
PostPosted: Sat Jul 20, 2019 1:32 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 719
Location: Athens, Greece
Let's say we have two tables in sqlite db.
The first table, "Workers", has workers with the columns: id, first_name, last_name.
The second table, "Hours", has working details: worker_id, date, working_hours.
What we need is to create a monthly view that will have the columns: first_name, last_name, 1, 2, 3, 4, 5, 6,... 30 or 31 according to month's max day number. The rows will have worker's name and working hours. Of course there will be many blank table cells. That is not a problem. I can get the data with:
Code:
SELECT W.first_name, W.last_name, H.date, H.working_hours FROM Workers W INNER JOIN Hours H WHERE W.id = H.id AND H.date >= '2019-09-01' AND H.date <='2019-09-30'

With the above query we get dates in a column. Is there a way to separate days and have different column for every single day?


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sat Jul 20, 2019 5:57 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 297
Location: United Kingdom
Something like:
Code:
SELECT h.worker_id, w.first_name, w.last_name,
  SUM(CASE WHEN strftime('%d', h.date) = '01' THEN h.working_hours END) AS D01,
  SUM(CASE WHEN strftime('%d', h.date) = '02' THEN h.working_hours END) AS D02,
    ...
  SUM(CASE WHEN strftime('%d', h.date) = '30' THEN h.working_hours END) AS D30,
  SUM(CASE WHEN strftime('%d', h.date) = '31' THEN h.working_hours END) AS D31
FROM Hours as h
JOIN Workers as w
ON h.worker_id = w.id
WHERE h.date >= '2019-09-01' AND h.date <= '2019-09-30'
GROUP BY h.worker_id;


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sat Jul 20, 2019 8:06 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 719
Location: Athens, Greece
spikey wrote:
Something like:
Code:
SELECT h.worker_id, w.first_name, w.last_name,
  SUM(CASE WHEN strftime('%d', h.date) = '01' THEN h.working_hours END) AS D01,
  SUM(CASE WHEN strftime('%d', h.date) = '02' THEN h.working_hours END) AS D02,
    ...
  SUM(CASE WHEN strftime('%d', h.date) = '30' THEN h.working_hours END) AS D30,
  SUM(CASE WHEN strftime('%d', h.date) = '31' THEN h.working_hours END) AS D31
FROM Hours as h
JOIN Workers as w
ON h.worker_id = w.id
WHERE h.date >= '2019-09-01' AND h.date <= '2019-09-30'
GROUP BY h.worker_id;

That means I have to write "SUM(CASE... AS Dx" 30 or 31 times. I am also trying to find a PB way to solve it, not only sqlite one. Something with list and array but it seems enough complicated.


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sat Jul 20, 2019 9:38 pm 
Offline
Addict
Addict
User avatar

Joined: Thu Jul 03, 2003 6:53 pm
Posts: 1278
Location: England
doctorized wrote:
I am also trying to find a PB way to solve it, not only sqlite one. Something with list and array but it seems enough complicated.

Here are the days as a delimited string:
Code:
Procedure.s DaysForMonth(nDate)
  TestMonth = Month(nDate)
  nDay = Date(Year(nDate),TestMonth,1,0,0,0)
  LoopMonth = TestMonth
  Days$ = ""
  While LoopMonth = TestMonth
    If Days$ <> "" : Days$ + #CRLF$ : EndIf
    Days$ + FormatDate("%yyyy-%mm-%dd",nDay)
    nDay + (3600 * 24)
    LoopMonth = Month(nDay)
  Wend
  ProcedureReturn Days$
EndProcedure

Debug DaysForMonth(Date())


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sat Jul 20, 2019 10:22 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 719
Location: Athens, Greece
Just to make sure we understand each other, here is an image with the desired table. The editor cannot determine image dimensions so I add the image as link:
http://users.sch.gr/arahiotis/table.png


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sun Jul 21, 2019 9:56 am 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 1905
Location: Germany
Perhaps...
Code:
Procedure DaysOfMonth(Date)
  Protected nDate
  Month = Month(Date)
  If Month < 12
    Month + 1
  Else
    Month = 1
  EndIf
  nDate = Date(Year(date), Month, 1, 0, 0, 0)
  nDate - (3600 * 24)
  ProcedureReturn Day(nDate)
EndProcedure

Debug DaysOfMonth(Date(2019,2,5,0,0,0))
Debug DaysOfMonth(Date(2000,2,5,0,0,0))

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sun Jul 21, 2019 4:11 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 719
Location: Athens, Greece
Unfortunately, there is a misunderstanding. My problem is not how to set column headers. My problem is how to set data read from the db in table cells. As I said in my initial post, I can read date data in a single column. I want to find a way to expand them in all month's columns.
I get days of month with:
Code:
Procedure DaysOfMonth(Month.a, Year.i)
   Select Month
      Case 1,3,5,7,8,10,12
         ProcedureReturn 31
      Case 4,6,9,11
         ProcedureReturn 30
      Case 2
         If Mod(Year, 4) = 0
            ProcedureReturn 29
         Else
            ProcedureReturn 28
         EndIf
   EndSelect
EndProcedure

Debug DaysOfMonth(5,2019)
Debug DaysOfMonth(6,2018)
Debug DaysOfMonth(2,2020)


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sun Jul 21, 2019 4:42 pm 
Offline
Addict
Addict
User avatar

Joined: Thu Jul 03, 2003 6:53 pm
Posts: 1278
Location: England
Code:
SELECT W.first_name, W.last_name, H.date, H.working_hours FROM Workers W INNER JOIN Hours H WHERE W.id = H.id AND H.date >= '2019-09-01' AND H.date <='2019-09-30'

Add
Code:
Order By W.id, H.date
to your SQL

Have a CurrentRow index that's incremented whenever the W.id changes since the last iteration
Use Day(H.date) to figure out the column index for writing H.working_hours into


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Sun Jul 21, 2019 6:33 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Jun 25, 2014 5:25 pm
Posts: 359
Location: Germany
doctorized wrote:
That means I have to write "SUM(CASE... AS Dx" 30 or 31 times.
No, a programmer does not write this himself, but lets the program write it :wink:
Code:
Define i
Define query$

query$ = "SELECT h.worker_id, w.first_name, w.last_name,"
For i = 1 To 31
    query$ + " SUM(Case WHEN strftime('%d', h.date) = '" + Str(i) + "' THEN h.working_hours End) As D" + Str(i) + ","
Next
query$ = RTrim(query$, ",")
query$ + " FROM Hours as h JOIN Workers As w ON h.worker_id = w.id WHERE h.date >= '2019-09-01' And h.date <= '2019-09-30' GROUP BY h.worker_id;"

Debug query$

_________________
Image
Why OpenSource should have a license
PureBasic-CodeArchiv-Rebirth: Git-Repository / Download -- Any help is welcome!
Manjaro Xfce x64 (Main system) :: WindowsXP/Xubuntu x86 (VirtualBox) :: PureBasic (Linux: x86/x64, Windows: x86) :: All are up to date


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Mon Jul 22, 2019 4:22 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 719
Location: Athens, Greece
the.weavster wrote:
Have a CurrentRow index that's incremented whenever the W.id changes since the last iteration
Use Day(H.date) to figure out the column index for writing H.working_hours into

With your precious help I managed to make it. Here is the code:
Code:
UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

Procedure DaysOfMonth(Month.a, Year.i)
   Select Month
      Case 1,3,5,7,8,10,12
         ProcedureReturn 31
      Case 4,6,9,11
         ProcedureReturn 30
      Case 2
         If Mod(Year, 4) = 0
            ProcedureReturn 29
         Else
            ProcedureReturn 28
         EndIf
   EndSelect
EndProcedure

DatabaseFile$ = "C:\Users\user7\Desktop\project\data.file"
OpenWindow(0,0,0,600,400,"test",#PB_Window_ScreenCentered | #PB_Window_MinimizeGadget)
ListIconGadget(0,10,10,580,380,"id",40,#PB_ListIcon_FullRowSelect | #PB_ListIcon_GridLines)
AddGadgetColumn(0,1,"Last name",80)
AddGadgetColumn(0,2,"First name",80)
daysCount.a = DaysOfMonth(9,2019);let's say we need to check September
For i=1 To daysCount
   AddGadgetColumn(0,i+2,Str(i),25)
Next
For i=1 To daysCount+3;+3 for id, first and last name
   txt.s + #LF$ + "";use it to create blank cells. Cannot put data in the gadget without it first.
Next
For i=1 To 6
   AddGadgetItem(0,-1,txt)
Next
row.i = -1: tmp_id.s
If OpenDatabase(0, DatabaseFile$, "", "")
   If DatabaseQuery(0, "SELECT W.first_name, W.last_name, H.date, H.working_hours FROM Workers W INNER JOIN Hours H WHERE W.id = H.id AND H.date >= '2019-09-01' AND H.date <='2019-09-30' ORDER BY H.id,H.date")
      While NextDatabaseRow(0)
         If tmp_id <> GetDatabaseString(0, 0)
            row + 1
            tmp_id = GetDatabaseString(0, 0)
            SetGadgetItemText(0,row,GetDatabaseString(0, 0),0)
            SetGadgetItemText(0,row,GetDatabaseString(0, 1),1)
            SetGadgetItemText(0,row,GetDatabaseString(0, 2),2)
         EndIf
         dte.a = Val(Mid(GetDatabaseString(0, 3),9,2))
         SetGadgetItemText(0,row,GetDatabaseString(0, 4),dte+2)
      Wend
      FinishDatabaseQuery(0)
   EndIf
   CloseDatabase(0)
Else
   Debug "Can't open database !"
EndIf

Repeat
   Event = WaitWindowEvent()
   Select Event
      Case #PB_Event_CloseWindow
         Quit = 1
   EndSelect
Until Quit = 1


but now I have to deal with another problem. If we check to show two or more months instead of one, how can we show them? Every month in a different list icon gadget? If yes, where will the other gadgets be in my window? Each one in a different panel gadget tab?


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Tue Jul 23, 2019 7:40 am 
Offline
Addict
Addict
User avatar

Joined: Thu Jul 03, 2003 6:53 pm
Posts: 1278
Location: England
I don't understand this bit:
Code:
For i=1 To daysCount+3;+3 for id, first and last name
   txt.s + #LF$ + "";use it to create blank cells. Cannot put data in the gadget without it first.
Next
For i=1 To 6
   AddGadgetItem(0,-1,txt)
Next
How do you know the number of rows to create before querying your database?

Why not add a row each time you encounter a new employee?:
Code:
      While NextDatabaseRow(0)
         CurrentId$ = GetDatabaseString(0, 0)
         If tmp_id <> CurrentId$
            row + 1
            tmp_id = CurrentId$
            AddGadgetItem(0,row,CurrentId$)
            SetGadgetItemText(0,row,GetDatabaseString(0, 1),1)
            SetGadgetItemText(0,row,GetDatabaseString(0, 2),2)
         EndIf
         dte.a = Val(Mid(GetDatabaseString(0, 3),9,2))
         SetGadgetItemText(0,row,GetDatabaseString(0, 4),dte+2)
      Wend

doctorized wrote:
but now I have to deal with another problem. If we check to show two or more months instead of one, how can we show them? Every month in a different list icon gadget? If yes, where will the other gadgets be in my window? Each one in a different panel gadget tab?
Personally I wouldn't like lots of ListIconGadgets on tabs. Perhaps a ComboBoxGadget with the months in? Repopulate the ListIconGadget whenever the user selects a different month in the Combo.


Top
 Profile  
Reply with quote  
 Post subject: Re: Create monthly table from sqlite?
PostPosted: Tue Jul 23, 2019 12:58 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 719
Location: Athens, Greece
the.weavster wrote:
How do you know the number of rows to create before querying your database?

I posted a preliminary code and I used 6 rows as I knew that my test db has 3 workers. I moved row creation after new worker is found.

the.weavster wrote:
Personally I wouldn't like lots of ListIconGadgets on tabs. Perhaps a ComboBoxGadget with the months in? Repopulate the ListIconGadget whenever the user selects a different month in the Combo.

I don't like tabs either. The combo box seems a good idea... I will try it! Thank you very much!!


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye