SQL Help

GazChap · **Posted:** Tue Jun 19, 2012 12:33

I'm sure the answer to this is crazy simple, but my brain just isn't working today.

I've got two tables. Let's call them holidays and availability.

In the holidays table is the general holiday info, and a column "setdates", which can be either 0, 1 or 2:

0: There are no set dates for this holiday, the arrival day can vary
1: There are explicit set dates for this holiday.
2: There can be both (more on this below)

In the availability table are date ranges and the availability of them, joined to the holidays table by the ID of the holiday, in a 1:M relationship.

The table has a start date column, an end date column, and the availability column, which can be either 0, 1, 2 or 4:

0: No availability for this date range, i.e. all places are booked.
1: Places are available for this date range.
2: Call for availability (in practice, rarely used)
4: Essentially the same as 0, but with a semantic difference that is irrelevant to this question.

For holidays that are set dates, the availability value could be any of these. For variable dates, only 4 is used, and simply means that you can't go on the holiday over that date range.

The search form I'm doing has a box for the start date and a box for the end date, so what I need is a query that does the following:

Returns any holidays that have dates in the table that fall within the date range specified, and are marked as available (availability:1)
Returns any holidays that are variable (setdates:1 or 2) and DOESN'T have dates in the availability table.

First one is dead easy, just:

Code:

SELECT `holidays`.* FROM `holidays` LEFT JOIN `holidays_availability` ON `holidays`.`id`=`holidays_availability`.`holidayid` WHERE `holidays_availability`.`startdate` >= '2012-10-10' AND `holidays_availability`.`enddate` <= '2012-10-19' AND `holidays_availability`.`availability`=1

It's adding on the second bit that I'm struggling with.

I've probably not explained that well at all.

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

I'm not much of a help, but don't forget that you can nest queries so that the criteria of one query can include a select statement within it.

If that helps.

Bamba · **Joined:** 25th Jul, 2010 **Posts:** 11128

What's a 'ride' in this context? You mention it throughout but never define it.

GazChap · **Posted:** Tue Jun 19, 2012 12:42

Bamba wrote:

What's a 'ride' in this context? You mention it throughout but never define it.

Sorry, should have been "holiday."

Bamba · **Joined:** 25th Jul, 2010 **Posts:** 11128

Okay, cool. I wonder if I'm over-simplifying here but, in Oracle SQL grammar, I'd do the following:

Code:

select *
from holidays
where setdates in (1,2)
and id not in
(
   select holidayid
   from holidays_availability
)   

Grim... · **Posted:** Tue Jun 19, 2012 13:11

Yeah, you need to use a subquery.

Here's how to do them in MySQL: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Grim... · **Posted:** Tue Jun 19, 2012 13:15

I can't figure out where I need to put the 'IS NULL' from your description, so (if I've understood correctly) you need either:

Code:

SELECT h.* 
FROM   holidays h 
       LEFT JOIN holidays_availability a 
              ON h.id = a.holidayid 
WHERE  ( a.startdate >= '2012-10-10' 
         AND a.enddate <= '2012-10-19' 
         AND a.availability = 1 ) 
        OR ( a.id IS NULL 
             AND h.id IN (SELECT id 
                          FROM   holidays 
                          WHERE  startdate <= '2012-10-10' 
                                 AND enddate >= '2012-10-19' 
                                 AND ( setdates = 1 
                                        OR setdates = 2 )) ) 

or

Code:

SELECT h.* 
FROM   holidays h 
       LEFT JOIN holidays_availability a 
              ON h.id = a.holidayid 
WHERE  ( a.startdate >= '2012-10-10' 
         AND a.enddate <= '2012-10-19' 
         AND a.availability = 1 ) 
        OR ( h.id IN (SELECT h2.id 
                      FROM   holidays h2 
                             LEFT JOIN holidays_availability a2 
                                    ON h2.id = a2.holidayid 
                                       AND a2.startdate >= '2012-10-10' 
                                       AND a2.enddate <= '2012-10-19' 
                      WHERE  a2.id IS NULL 
                             AND h2.startdate <= '2012-10-10' 
                             AND h2.enddate >= '2012-10-19' 
                             AND ( h2.setdates = 1 
                                    OR h2.setdates = 2 )) ) 

GazChap · **Posted:** Tue Jun 19, 2012 14:57

There's no "startdate" or "enddate" field in the holidays table, which I assume is why I can't get either of those to work :/

Grim... · **Posted:** Tue Jun 19, 2012 15:05

Oh right - try this then:

Code:

SELECT h.* 
FROM   holidays h 
       LEFT JOIN holidays_availability a 
              ON h.id = a.holidayid AND a.startdate >= '2012-10-10' AND a.enddate <= '2012-10-19' 
WHERE  ( a.availability = 1 ) 
        OR ( a.id IS NULL 
             AND ( h.setdates = 1 OR h.setdates = 2 ) )

You can swap the WHERE for

Code:

WHERE  ( a.availability = 1 ) 
        OR ( a.id IS NULL AND h.setdates != 0 )

if it speeds it up, although it's not as futureproof.

GazChap · **Posted:** Tue Jun 19, 2012 15:40

That looks like it's bringing back the right subset of records, but need to wait for confirmation.

Cheers. Dunno why I always forget about subqueries.

Grim... · **Posted:** Tue Jun 19, 2012 15:43

You didn't need one in the end, though.

GazChap · **Posted:** Tue Jun 19, 2012 15:44

True. I didn't realise you could stick ANDs into join definitions though, will have to remember that one.

Grim... · **Posted:** Tue Jun 19, 2012 15:46

You can use sub-queries in joins, too

GazChap · **Posted:** Tue Jun 19, 2012 15:56

Mind asplode.

ElephantBanjoGnome · **Joined:** 22nd Dec, 2010 **Posts:** 8175

Instead of using >= and <=, you can just use WHERE date BETWEEN date1 AND date2.

GazChap · **Posted:** Tue Jun 19, 2012 16:06

Yeah, I'm an odd one with that. One of SQL's strengths is it's "English-like" structure, and thus readability, but my eye tends to gravitate towards operators like >= and so on, so I tend to use those instead of keywords like BETWEEN. :shrug:

Grim... · **Posted:** Tue Jun 19, 2012 16:55

ElephantBanjoGnome wrote:

Instead of using >= and <=, you can just use WHERE date BETWEEN date1 AND date2.

Not really in this situation, because you don't have 'date', you have 'datestart' and 'dateend'.

I mean, you could, but

Code:

a.startdate BETWEEN '2012-10-10' AND '2012-10-19' AND a.enddate BETWEEN '2012-10-10' AND '2012-10-19'

looks a bit daft.

Code:

'2012-10-10' BETWEEN a.startdate AND a.enddate AND '2012-10-19' BETWEEN a.startdate AND a.enddate

is perhaps easier to read, but (a tiny bit) more intensive.

Picky folk would probably say that you should cast your strings into dates if you're using BETWEEN, too.

GazChap · **Posted:** Tue Jun 19, 2012 17:07

Here's another one for ya. Again, I'm sure I'm missing something dead simple but I stayed up watching Shawshank the other night and my brain hasn't recovered yet (thank God I didn't watch Primer.)

Say you've got a calendar widget, that shows a month at a time, with arrows to move between months, as you typically see.

Each day in the viewed month can be either Available or Unavailable.

It's available if no records are in the holiday availability table that include that date (so, for today, it would be available if there were no date ranges including 2012-06-19)

However, short of doing a query for each day in the viewed month, is there a more efficient way of retrieving all records from the availability table that include any date within the viewed month?

Grim... · **Posted:** Tue Jun 19, 2012 17:10

Er... Between?

Code:

SELECT COUNT(id) FROM table WHERE date BETWEEN '2012-10-01' AND '2012-11-01'

would cover October. If you don't get a 0, it's unavailable.

GazChap · **Posted:** Tue Jun 19, 2012 17:15

Don't think that'd work, as there's no "date" column, but there is a startdate and enddate.

Take this example table:

Code:

id      startdate      enddate
1       2012-05-27     2012-06-03
2       2012-06-05     2012-06-10
3       2012-06-20     2012-06-23
4       2012-06-29     2012-07-10

So, for June, I'd need something that would pick up on the fact that the first record includes part of June, so would need to mark June 1-3 as unavailable.

//edit: Could perhaps do an OR with startdate and enddate being checked for the BETWEEN range though.

//edit edit: Yup, that does it. Total brain lock today, but apparently writing things down helps me figure it out

Malc · **Posted:** Tue Jun 19, 2012 17:43

wouldn't it be easier, just to have a table that had an entry for each day? And run your query against that?

Malc

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Are they stored as dates? If they are, you can use DATEPART to pull the month out.

Grim... · **Posted:** Tue Jun 19, 2012 18:18

Craster wrote:

Are they stored as dates? If they are, you can use DATEPART to pull the month out.

No need though, is there? And you'd have to check the year anyway.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Grim... wrote:

Craster wrote:

Are they stored as dates? If they are, you can use DATEPART to pull the month out.

No need though, is there? And you'd have to check the year anyway.

It's neater. Your example would become

Code:

SELECT COUNT(id) FROM [table] WHERE datepart(month,[date])=10

GazChap · **Posted:** Tue Jun 19, 2012 20:47

They are being stored as dates, yeah. It seems to be working at the moment though, which is good enough for me - sod neat

Grim... · **Posted:** Tue Jun 19, 2012 22:19

Craster wrote:

Grim... wrote:

Craster wrote:

Are they stored as dates? If they are, you can use DATEPART to pull the month out.

No need though, is there? And you'd have to check the year anyway.

It's neater. Your example would become

Code:

SELECT COUNT(id) FROM [table] WHERE datepart(month,[date])=10

You'd need

Code:

SELECT COUNT(id) FROM [table] WHERE datepart(month,[date])=10 AND datepart(year,[date])=2012

But yeah, it's neater, but it'll get less neat if the date range stretches over two months. Anyway, neat doesn't count for shit if it isn't as fast

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Saves you having to work out the last day of the month to provide it as a parameter, too.

Grim... · **Posted:** Tue Jun 19, 2012 22:34

Well, validation of the date will be done client side, not in the server, it doesn't care.

If you put '2012-06-31' into a date field MySQL will take it to mean the 1st of July.

I think.

Hang on.

[edit] Nope, it doesn't, that comes out as 'null'. But comparatively it still works, as '2012-06-31' is still before '2012-07-02' and after '2012-06-30'.
[Disclaimer] I've been drinking
[edit] Hang on, he's comparing from the first to the first anyway!

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

I am trying to do a select of rows where all the rows have the same statusid.

In the below data, I want to show all the columns, but group by storecode, storetype and parceltype. After this grouping, only those rows where ALL the rows have statusid 12 should be selected. So in the below example, I would want results showing ids 123 because they have the same storecode, storetype and parceltype, and all the statusids are 12.
It would NOT show rows 4,5,6 because even though the storecode, storetype and parceltype are the same, at least one row is not a 12.

Any ideas?

Code:

id   storecode   storetype   barcode   parcel type   Qty   statusid
1   2808   1   977205514400803   a   1   12
2   2808   1   501755814202603   a   1   12
3   2808   1   501079143200801   a   4   12
4   2808   1   977205716700502   b   1   1
5   2808   1   977147896704101   b   2   12
6   2808   1   977204239505501   b   1   1
7   2808   1   977146904306807   c   1   4
8   2808   1   977205251500914   c   1   5
9   5565   2   977135674397201   a   2   6
10   5565   2   977001306122004   a   1   4
11   5565   2   977175266500777   a   2   5
12   5565   2   977026371620802   a   2   4
13   5565   2   977003389229301   a   2   4

Grim... · **Posted:** Mon Feb 02, 2015 17:03

I figure I've misunderstood, but

Code:

WHERE statusid = 12

?

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Grim... wrote:

I figure I've misunderstood, but

Code:

WHERE statusid = 12

?

See, that would return row 5, but I need to exclude row 5 because the rest of that group (row 4,5 and 6) isn't all 12s.

Grim... · **Posted:** Mon Feb 02, 2015 17:07

Hmm, okay. You can use a self-referencing join for that (ie. you Inner Join the table to itself), but how do you know what you need? I mean, how do you know that you want statusid 12? Is it because that's the first one?

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Grim... wrote:

Hmm, okay.

How do you know what you need? I mean, how do you know that you want statusid 12? Is it because that's the first one?

All the rows are assigned a statusid by importing external data. Status 12 is the hardcoded value representing missing data, so I am trying to find a parcel where ALL rows for that parcel for that storecode and storetype have status 12 as it means the whole parcel is missing.

if just one line has another status id than 12 then the whole parcel is not missing.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

It means half a parcel is missing?

Grim... · **Posted:** Mon Feb 02, 2015 17:15

Maybe this?

Code:

SELECT * FROM tablename t
INNER JOIN tablename t2 ON t2.storecode = t.storecode AND t2.storetype = t.storetype AND t.id < t2.id
WHERE t.parceltype = 12 AND t2.parceltype = 12

It's not going to be especially fast

Grim... · **Posted:** Mon Feb 02, 2015 17:17

Hmm, in your original data the storetypes are all different, so you'll need to take that bit out.

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

It's part of the news management system I write.

They're not true parcels in the sense of the word, but a store will bundle up magazines to return to a wholesaler. We hold that information on a line by line basis, and update those lines with a status from the wholesaler as information about each line is received.

Status 12 in this case would be 'we haven't received that yet'. Rather than a report of all lines that weren't received, I'm trying to identify groups of bundles that all have the 'not received yet' status. That way we can see where entire bundles have gone missing rather than just individual lines, as it indicates a failing at a different part of the returns process.

Grim... that still didn't work sadly. It's OK though, it's nothing mission critical, more of a flight of fancy of my own.

Grim... · **Posted:** Tue Feb 03, 2015 13:18

It didn't? Did you take the storetype out?

What did you get back?

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Grim... wrote:

It didn't? Did you take the storetype out?

What did you get back?

I did take it out, and it returned rows where the statusid was 12 for all results, but when i then said, ok show me all rows for that storecode, storetype and parcel type there were other rows in there with other status ids.

Grim... · **Posted:** Tue Feb 03, 2015 14:02

Ah, I read the original table headers wrong. The "Code" tag doesn't seem very good at doing tables.

Code:

SELECT * FROM tablename t
INNER JOIN tablename t2 ON t2.storecode = t.storecode AND t2.storetype = t.storetype AND t.id < t2.id
WHERE t.statusid = 12 AND t2.statusid = 12

Give that a shot.

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Hang on, that's the same query as before. I realised you'd put parcel type instead of statusid in the original one, so already changed that.

So, results all have statusid 12, but if I then select all rows for that store, storetype and parceltype there are other rows in that parcel with a different statusid.

I'm thinking I'm going to have to count the number of rows in each parcel and divide by 12 to see if it's a whole number at this rate

Grim... · **Posted:** Tue Feb 03, 2015 14:20

Mr Russell wrote:

So, results all have statusid 12, but if I then select all rows for that store, storetype and parceltype there are other rows in that parcel with a different statusid.

AHHH I get you now! You want the results only if every entry for that store, storetype and parceltype are 12?

You want something like this:

Code:

SELECT * FROM tablename t
WHERE t.id NOT IN (SELECT id FROM tablename t2
   WHERE storecode = t.storecode AND storetype = t.storetype  AND parceltype = t.parceltype AND statusid != 12
)

You'll probably need to fiddle it a bit, but it should be enough to get you going.

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Gngh. It's so close, and I can see exactly that it should work, but it's not. Don't worry about it, because it's only driving me mad, and you can't even see the actual problem from there!

Hearthly · **Joined:** 11th May, 2008 **Posts:** 13382

Our SQL guy says this.

Needs SQL2008 or later, for earlier you'd have to use a temporary table. Apparently.

----------

Code:

WITH cte_grouping (storecode, storetype,[parcel type], total, statusid, barcode)
as
(
SELECT  Distinct a.Storecode,a.storetype,a.[Parcel type],a.qty as 'Total',a.statusid, A.barcode FROM test a
INNER OUTER JOIN test b ON b.storecode = a.storecode AND b.storetype = a.storetype AND a.id < b.id
WHERE a.statusid = 12 AND b.statusid = 12
)
 
select storecode, storetype, [parcel type], statusid, sum(total) as 'Total' from cte_grouping
group by storecode, storetype, [parcel type], statusid

-------------

This will work must be ran as a batch.

Please tell him off for the horrible table design and having a space in his column name [Parcel Type] bad form….

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Thanks for the help Hearthly. It returns rows with status id 12, but when I then run a query for that storecode, storetype and parceltype it returns other rows with different statusids.

(And obviously i don't really have a space, this data is the sanitised part of a much larger table.)

Hearthly · **Joined:** 11th May, 2008 **Posts:** 13382

As I understand it with CTEs (Common Table Expressions) the results of the query only exist at the moment in time in memory, it's not something you can then subsequently query again as it'll just go back to the original table (which I think is what you're saying seems to be happening) - any results that you want from the CTE you have to batch together and run at the same time.

It you want persistent results that you can run further queries against, you need to use a temporary table.

I'm not 100% clear on exactly what result you're wanting to get though? You won't be able to group by barcode because that's a unique value for every row (not unique in the table, but unique in the row - so the end result is you can't group by barcode and store in the same query).

<< This is my best understanding of it, I defer to people with more knowledge than me likes.

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Indeed, so imagine storecode 1 sends back three unique barcodes in parcel A, and a further three (different) unique barcodes in parcel B.

The statusid is then updated for each of the 6 rows. The status id of all the rows in parcel A becomes 12. The statusid of just one row in parcel B is 12 and the others are 1. So data is now as follows:

storecode barcode parceltype statusid
1 12342 A 12
1 22347 A 12
1 62376 A 12
1 72315 B 12
1 82364 B 1
1 92333 B 1

So I want a result set that shows me the first three rows only because when I group by storecode, all the unique incidences of that storecode, storetype and parceltype all have rows with statusid 12. The bottom three rows would not be presented back to me because even though one of the rows has status 12, not all of them do.

So in essence, show me all rows in the table where the status id is 12, but exclude those parcels where there are any rows that are not statusid 12.

Bamba · **Joined:** 25th Jul, 2010 **Posts:** 11128

What about something like:

Code:

select distinct(parceltype)
from parcels p1
where p1.statusid = 12
and where not exists (
   select 1
   from parcels p2
   where p1.parceltype = p2.parceltype
   and p2.statusid <> 12
)

I might well have fucked up the syntax there but essentially 'give me a list of unique parceltypes where the status id = 12 and there doesn't exist any that aren't 12 for that same parceltype'.

ETA: And obviously just take out the 'distinct' if you actually want to see all three rows and now just a list of the parcels your criteria applies to.

Grim... · **Posted:** Thu Feb 05, 2015 12:46

That's pretty much what I said, and it feels right, but apparently didn't work.

:shrug:

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

Bamba, after a few tests that seems spot on, thanks so much!

Grim..., you're right, it's practically the same, but there's an extra reference to the statusid outside the where clause, and bamba has used exists instead of IN (not sure if that makes a difference?)

Anyway, thank you to everyone who tried at this. I'm going to go and read me a book with SQL for Dummies in the title and see if I can absorb more knowledge about JOINS and subqueries into my face.

Really appreciate this, guys, cheers.

Be Excellent To Each Other

SQL Help
Gnngh

Who is online

Be Excellent To Each Other

SQL Help Gnngh

Who is online

SQL Help
Gnngh