SQL QUERY PROBLEM..
-
Try this one... select productname,sum(issueqty) QTY from issuance where issuedate >= '5/1/2010' and issuedate <= '5/31/2010' and productid in ('777' ,'778') group by productname order by productname
Thanks
-
yes all record of productid=777 and productid=778
You want the records that have number "777" or "778". You are asking for the records that have *both* numbers. The resulting list will have records with both records containing 777
AND778, but you'll need to ask for the records that contain 777OR778 :)I are Troll :suss:
-
yes all record of productid=777 and productid=778
In high school, to drive home the difference between 'AND' and 'OR' in a logical statement, the teacher asked all of the students in room 201 and 202 to please stand up. When people stood, he asked if there body was split, thereby allowing them to be in two rooms at the same time.
-
Try this one... select productname,sum(issueqty) QTY from issuance where issuedate >= '5/1/2010' and issuedate <= '5/31/2010' and productid in ('777' ,'778') group by productname order by productname
Actually he shouldn't use that type of date format since it can fail. Better is to use the SQL standard: yyyyMMdd
select productname,sum(issueqty) QTY from issuance
where issuedate >= '20100501' and issuedate <= '20100531'
and productid in ('777' ,'778')
group by productname
order by productname -
Actually he shouldn't use that type of date format since it can fail. Better is to use the SQL standard: yyyyMMdd
select productname,sum(issueqty) QTY from issuance
where issuedate >= '20100501' and issuedate <= '20100531'
and productid in ('777' ,'778')
group by productname
order by productnameTom Deketelaere wrote:
that type of date format since it can fail.
Can you elaborate? What would allow or cause it to fail?
I know the language. I've read a book. - _Madmatt
-
In high school, to drive home the difference between 'AND' and 'OR' in a logical statement, the teacher asked all of the students in room 201 and 202 to please stand up. When people stood, he asked if there body was split, thereby allowing them to be in two rooms at the same time.
and that is when the class decided to tear down the wall between rooms 201 and 202 I presume. :laugh:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
-
Tom Deketelaere wrote:
that type of date format since it can fail.
Can you elaborate? What would allow or cause it to fail?
I know the language. I've read a book. - _Madmatt
In the UK, the date '5/31/2010' would fail if you localize the database because we format dates as dd/mm/yyyy. Using yyyymmdd format or explicitly using CONVERT is much safer.
-
Tom Deketelaere wrote:
that type of date format since it can fail.
Can you elaborate? What would allow or cause it to fail?
I know the language. I've read a book. - _Madmatt
At a guess, regional settings: 5/1/2010 can be interpreted as 01-May-2010 or 05-Jan-2010 The second date, 5/31/2010, is presumed to mean '31-May-2010' and provides context for the first date. I encounter this issue on a regular basis; especially when 'constructing' datetime stamps on the fly. For example, our mill production day starts at 7:00 AM. For for month to date information for a given date, I have to build the date as: '01-' (month of given date) '-' (year of given date). Hope that helps. Tim
-
In the UK, the date '5/31/2010' would fail if you localize the database because we format dates as dd/mm/yyyy. Using yyyymmdd format or explicitly using CONVERT is much safer.
I guess that falls under the DAH!! category. Obviously if you are using dates they must be in the correct format :rolleyes: Using universal time will ensure these issues do not arise. '20100501' is just as likely to be mistyped and cause issues.
I know the language. I've read a book. - _Madmatt
-
At a guess, regional settings: 5/1/2010 can be interpreted as 01-May-2010 or 05-Jan-2010 The second date, 5/31/2010, is presumed to mean '31-May-2010' and provides context for the first date. I encounter this issue on a regular basis; especially when 'constructing' datetime stamps on the fly. For example, our mill production day starts at 7:00 AM. For for month to date information for a given date, I have to build the date as: '01-' (month of given date) '-' (year of given date). Hope that helps. Tim
The implication I read was the OP said it would fail because of SQL server issue. Of course you must use the proper date format. That's user error not a SQL Server issue
I know the language. I've read a book. - _Madmatt
-
The implication I read was the OP said it would fail because of SQL server issue. Of course you must use the proper date format. That's user error not a SQL Server issue
I know the language. I've read a book. - _Madmatt
No I mend for the exact reason Tim talks about. Sorry if that wasn't entirely clear :) I encounter this a lot. Very often ours clients pc will be set to the correct regional settings (Belgium settings) but there server (installed by hardware guys) is almost always set to the English settings, so without using the SQL standard format things can go very wrong and act very weird.
-
and that is when the class decided to tear down the wall between rooms 201 and 202 I presume. :laugh:
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
Not to be picky here but wouldn't that just merge the 2 rooms into 1 room and therefor they once again weren't in 201 AND 202 but only in 201 OR 202 (or another number given to the now twice as big a room :) )
-
Not to be picky here but wouldn't that just merge the 2 rooms into 1 room and therefor they once again weren't in 201 AND 202 but only in 201 OR 202 (or another number given to the now twice as big a room :) )
Of course not. Without the wall, they would be in both rooms at the same time. Unless you insist on renumbering everything, or otherwise denormalize the school. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
-
Of course not. Without the wall, they would be in both rooms at the same time. Unless you insist on renumbering everything, or otherwise denormalize the school. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
Well if you keep the numbering that would mean 201 is one half of the room and 202 another half of the room. One could argue that in order to be in both rooms at the same time you would have to be standing in the exact middle of the room (where the wall was). I don't know why but I'm in a picky mood today, must be because of the slow day at work ;P
-
Well if you keep the numbering that would mean 201 is one half of the room and 202 another half of the room. One could argue that in order to be in both rooms at the same time you would have to be standing in the exact middle of the room (where the wall was). I don't know why but I'm in a picky mood today, must be because of the slow day at work ;P