Create monthly table from sqlite?

For everything that's not in any way related to PureBasic. General chat etc...
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Create monthly table from sqlite?

Post by doctorized »

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 all

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?
User avatar
spikey
Enthusiast
Enthusiast
Posts: 581
Joined: Wed Sep 22, 2010 1:17 pm
Location: United Kingdom

Re: Create monthly table from sqlite?

Post by spikey »

Something like:

Code: Select all

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;
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Create monthly table from sqlite?

Post by doctorized »

spikey wrote:Something like:

Code: Select all

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.
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: Create monthly table from sqlite?

Post by the.weavster »

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: Select all

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())
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Create monthly table from sqlite?

Post by doctorized »

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
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: Create monthly table from sqlite?

Post by mk-soft »

Perhaps...

Code: Select all

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 / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Create monthly table from sqlite?

Post by doctorized »

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: Select all

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)
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: Create monthly table from sqlite?

Post by the.weavster »

Code: Select all

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: Select all

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
User avatar
Sicro
Enthusiast
Enthusiast
Posts: 538
Joined: Wed Jun 25, 2014 5:25 pm
Location: Germany
Contact:

Re: Create monthly table from sqlite?

Post by Sicro »

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: Select all

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 :: PB-CodeArchiv-Rebirth :: Pleasant-Dark (syntax color scheme) :: RegEx-Engine (compiles RegExes to NFA/DFA)
Manjaro Xfce x64 (Main system) :: Windows 10 Home (VirtualBox) :: Newest PureBasic version
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Create monthly table from sqlite?

Post by doctorized »

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: Select all

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?
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: Create monthly table from sqlite?

Post by the.weavster »

I don't understand this bit:

Code: Select all

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: Select all

      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.
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Create monthly table from sqlite?

Post by doctorized »

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!!
Post Reply