Generalising a date-related query

Databases
1

I have this query at the moment:

$sql = "SELECT
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2000’ AND prints.sale_price > 0) as year1,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2001’ AND prints.sale_price > 0) as year2,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2002’ AND prints.sale_price > 0) as year3,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2003’ AND prints.sale_price > 0) as year4,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2004’ AND prints.sale_price > 0) as year5,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2005’ AND prints.sale_price > 0) as year6,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2006’ AND prints.sale_price > 0) as year7,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2007’ AND prints.sale_price > 0) as year8,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2008’ AND prints.sale_price > 0) as year9,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2009’ AND prints.sale_price > 0) as year10,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2010’ AND prints.sale_price > 0) as year11,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2011’ AND prints.sale_price > 0) as year12
FROM prints
GROUP BY year1, year2, year3, year4 ";

I’d like generalize the query by changing the year to current year -5, current year -4… current year, current year +1 etc…

What’s the suggestions for doing this?

Thanks

David

2 
SELECT y.theyear
     , SUM(prints.print_key) 
  FROM ( SELECT YEAR(CURRENT_DATE) AS theyear
         UNION ALL
         SELECT YEAR(CURRENT_DATE) - 1
         UNION ALL
         SELECT YEAR(CURRENT_DATE) + 1
         UNION ALL
         SELECT YEAR(CURRENT_DATE) - 2
         UNION ALL
         SELECT YEAR(CURRENT_DATE) + 2
         UNION ALL
         ... 
       ) AS y
LEFT OUTER
  JOIN prints
    ON YEAR(prints.date_printed) = y.theyear
   AND prints.sale_price > 0
GROUP
    BY y.theyear
3

ah, lovely

while the moderators are moving this thread, would you please do a SHOW CREATE TABLE for the prints table

4

Excellent, many thanks Rudy…

5

oops. I should have said. It’s mySQL.

David

6

create a table of currency conversion factors

CREATE TABLE currencies
( currency VARCHAR(37) NOT NULL PRIMARY KEY
, conversion DECIMAL(13,6) NOT NULL
);
INSERT INTO currencies VALUES
( ‘US dollar’ , 1.0427 )
,( ‘UK pound’ , 1.5849 )
,( ‘Euro’ , 1.3365 )

then join this table to your table in the query and perform the multiplication

7

which database system is this?

(we have only two forums for databases, one is for mysql and the other is for everything else)

8

I have this query at the moment:

$sql = "SELECT
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2000’ AND prints.sale_price > 0) as year1,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2001’ AND prints.sale_price > 0) as year2,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2002’ AND prints.sale_price > 0) as year3,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2003’ AND prints.sale_price > 0) as year4,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2004’ AND prints.sale_price > 0) as year5,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2005’ AND prints.sale_price > 0) as year6,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2006’ AND prints.sale_price > 0) as year7,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2007’ AND prints.sale_price > 0) as year8,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2008’ AND prints.sale_price > 0) as year9,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2009’ AND prints.sale_price > 0) as year10,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2010’ AND prints.sale_price > 0) as year11,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2011’ AND prints.sale_price > 0) as year12
FROM prints
GROUP BY year1, year2, year3, year4 ";

I have another question posted in the forum about how to generalize and improve this query.

This is a second question:

I’m currently adding apples, oranges and legos, as prints.sale price can be $US, $UK, and €Euros. I need to find a way to convert the currencies on the fly to give me more accurate annual totals.

Many Thanks for great ideas!

David

9

OK, thanks Rudy, I abused your code and came up with this ugly duckling below that worked. However, I wonder if there is a way to iterate (loop) this into a single statement?.

$sql = "SELECT
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 10) AND prints.sale_price > 0) as year1,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN(prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 9) AND prints.sale_price > 0) as year2,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 8) AND prints.sale_price > 0) as year3,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 7) AND prints.sale_price > 0) as year4,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 6) AND prints.sale_price > 0) as year5,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 5) AND prints.sale_price > 0) as year6,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 4) AND prints.sale_price > 0) as year7,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 3) AND prints.sale_price > 0) as year8,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 2) AND prints.sale_price > 0) as year9,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year - 1) AND prints.sale_price > 0) as year10,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = $year AND prints.sale_price > 0) as year11,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_printed,1,4) = ($year + 1) AND prints.sale_price > 0) as year12



FROM prints 
GROUP BY year1, year2, year3, year4 ";
10

That’s a good idea, as even if I’m now able to get currencies in real-time, I don’t really want to do that for every query…

But I’m actually asking something different now. I have managed to get the correct totals - see post #30.

What I’d like to do now, is to find a way to make a loop so that all of that code can be consolidated…

11

that is correct, the part about making a table for currencies so that you can join to it

see also post #4

:slight_smile:

12

whoa, that’s monstrous!! :mad:

can you please descibe in words what that query is supposed to do?

also, could you please run the SHOW CREATE TABLE query for your table

13

yep, that’s them, was concerned they got lost in my chaos…

I’m not usually so disorganized (well a bit these days)… I have a very ill wife, and we’ve had a major electricity outage in the area and there are family members all over the place…

David

14

sorry, i can’t help you with refactoring, i have no idea what that means

15

It will take me some time to understand what you’ve given me Rudy…

The attachment shows what I’m trying to come up with:

This is my prints table…

CREATE TABLE `prints` (
  `print_key` int(10) NOT NULL AUTO_INCREMENT,
  `edition_id` int(11) NOT NULL DEFAULT '0',
  `print_id` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `negative_id` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `date_printed` date DEFAULT NULL,
  `paper_type` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `status` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `date_of_disposition` date DEFAULT NULL,
  `frame_type` varchar(15) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Examples: Wood, Metal',
  `frame_width` int(10) DEFAULT NULL,
  `frame_height` int(10) DEFAULT NULL,
  `frame_size_units` varchar(10) COLLATE utf8_bin DEFAULT '' COMMENT 'mm, cm, inches',
  `glass_type` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `studio_location` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `contact_id` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `sale_price` int(10) DEFAULT NULL,
  `currency` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `notes` longtext COLLATE utf8_bin,
  `printNumber` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `uid` int(25) NOT NULL,
  PRIMARY KEY (`print_key`),
  KEY `Prints_edition_serial_no_idx` (`edition_id`),
  KEY `Prints_customer_serial_no_idx` (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1850 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
16

I ended up using this gentleman’s site:

It was very easy to install, having a class file and a small piece of code. It’s free too!

17

mysql doesn’t do loops, sorry

18

That sounds great, but I’m out of my depth a bit… I know how to go to a service, but how do you pull in a data element from them, and what would my site query look like…?

Thanks!

19

i thought you already did – in post #9 and again in post #21

20

excellent, and i do hope you meant what you said about “directly”

forcing your query through a meatgrinder (e.g. separate columns for the years) is inefficient

you want the simplest and most direct retrieval and i can help you with that

so you’ll need a table for that

need help with that too?

i gather that none of this actually works yet, right? i mean, it looks like you have 1850 rows in the prints table but i’m wondering if it’s real data…