Marriage report by day of the week

[JEM]

I have a report for marriages by day of the week.

 

SELECT 
    CASE DAYOFWEEK(marrdatetr) 
         WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END AS Marriage_Day,
    COUNT(*) AS Number_of_Marriages
FROM tng_families
WHERE marrdatetr != '0000-00-00'
AND marrdatetr IS NOT NULL
GROUP BY DAYOFWEEK(marrdatetr)
ORDER BY DAYOFWEEK(marrdatetr);

 

This is the result. Can't figure out why the numbering if off.

[Katryne]

Could be dates where you have only month_year and not the very day.

[Philip Roy]

I suggest you paste it into ChatGPT and ask. I just did and got a lengthy response with possible causes. Too long to list here, but some useful ideas

IMPORTANT - Do not paste actual TNG code into AI…it’s a breach of the copyright for TNG. As this is generic SQL  it’s ok to paste it in.

[ajnsmits]

 

Katryne is right.
I've updated the query, and in my records, there are 9 entries that only have a year listed for the marriage.

 

SELECT 
	CASE DAYOFWEEK(marrdatetr) 
    	WHEN 1 THEN 'Sunday' 
    	WHEN 2 THEN 'Monday' 
    	WHEN 3 THEN 'Tuesday' 
    	WHEN 4 THEN 'Wednesday' 
    	WHEN 5 THEN 'Thursday' 
    	WHEN 6 THEN 'Friday' 
    	WHEN 7 THEN 'Saturday' 
    	ELSE 'unkown'
	END AS Marriage_Day,
COUNT(*) AS Number_of_Marriages
FROM tng_families
WHERE marrdatetr != '0000-00-00' 
AND marrdatetr IS NOT NULL
GROUP BY DAYOFWEEK(marrdatetr)
ORDER BY CASE WHEN Marriage_Day = 'unkown' THEN 1 ELSE 0 END,
MIN(DAYOFWEEK(`marrdatetr`)) ASC; 

André

[Rob Severijns]

If you're looking for reports or examples you can tweak have a look here:

Category:Henny Savenije - Reports - TNG_Wiki