Please help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help with Query

Author

Topic

zoodie
Starting Member

8 Posts

Posted - 2010-03-19 : 17:54:21

Hi, im really struggling with this query as it involves year range from ...'Academic year starts from beginning of September to the end
of August on the following year.'

can anyone pls help and show me how to write the code for this year range..?

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-19 : 17:56:04

WHERE SomeColumn >= '09/01/2009' AND SomeColumn < '09/01/2010'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zoodie
Starting Member

8 Posts

Posted - 2010-03-19 : 18:01:59

this is the whole query..

" For a given borrower, produce the total number of borrowed items in a specific academic year. Academic year starts from beginning of September to the end of August on the following year. The total number of items should include current borrowings, if the end date is less than the date of running the query (i.e. if the query runs in the current academic year, the total items should include all items that are currently on loan). The user should be prompted to
enter the borrower number and the target academic year in the format YY-YY(e.g. 09-10) "

thank you tara for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-19 : 20:03:21

You'll need to provide the columns of your tables and some sample data to make it more clear. Your homework/test should have some additional detail to provide to us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zoodie
Starting Member

8 Posts

Posted - 2010-03-19 : 20:09:36

Is there anyway i can send you my database and my pdf coursework so tht you can read it and see if you could help..? I think i hav got almost everything else working apart from this one query labelled 'A2' in my coursework.
I really really appreciate your help and do apologise. Did not mean to post same topic twice

baja_yu
Starting Member

24 Posts

Posted - 2010-03-19 : 20:29:43

Send screen shots as images, using www.imageshack.us or Photobucket etc.

zoodie
Starting Member

8 Posts

Posted - 2010-03-19 : 20:50:21

Here have a look at these..

http://img230.imageshack.us/gal.php?g=schemanew.jpg

baja_yu
Starting Member

24 Posts

Posted - 2010-03-19 : 22:49:00

I'm not sure if you are coding this somewhere, the assignment says to give the user the option to enter borrower ID and years, so assume you have three string type variables: strUserID, strStartYear, strEndYear.

SELECT COUNT(ItemID) AS Count FROM Loan WHERE MemberID = strUserID AND IssueDate >= '09/01/' + strStartYear AND IssueDate < '09/01/' + strEndYear

The syntax isn't completely correct, but I think you'll figure out what I was trying to say. And I'm assuming you're on MS SQL server, if your on Oracle or MySQL you'll have to replace + with the CONCAT function. Also, I either didn't understand a part of the assignemnt correctly or the DB structure, because I think this will work the same on all date ranges, regardless if it's a past or current academic year (reffering to this segment "... if the end date is less than the date of running the query...").

P.S: You shouldn't play games while working on your homework (Need For Speed ;) )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 00:58:16

quote:
Originally posted by baja_yu

I'm not sure if you are coding this somewhere, the assignment says to give the user the option to enter borrower ID and years, so assume you have three string type variables: strUserID, strStartYear, strEndYear.

SELECT COUNT(ItemID) AS Count FROM Loan WHERE MemberID = strUserID AND IssueDate >= '09/01/' + strStartYear AND IssueDate < '09/01/' + strEndYear

The syntax isn't completely correct, but I think you'll figure out what I was trying to say. And I'm assuming you're on MS SQL server, if your on Oracle or MySQL you'll have to replace + with the CONCAT function. Also, I either didn't understand a part of the assignemnt correctly or the DB structure, because I think this will work the same on all date ranges, regardless if it's a past or current academic year (reffering to this segment "... if the end date is less than the date of running the query...").

P.S: You shouldn't play games while working on your homework (Need For Speed ;) )

What was purpose of making parameters as string for passing date values? always use proper datatypes for your variables/parameters/fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

baja_yu
Starting Member

24 Posts

Posted - 2010-03-20 : 04:57:58

The point was that it was 6AM my time. It's good that it turned out that way and there were no leprechauns in it :)

Anyway it should give him a general idea of what to do. Since his assignment say that the user enters years, he'll have to do some date manipulations (DateAdd etc.) to get the periods properly set up. I guess it all depends on how and where he's implementing this.

zoodie
Starting Member

8 Posts

Posted - 2010-03-20 : 07:49:35

thank you for your help baja yu..

this is wht i came up with from your suggestion..

SELECT COUNT(ItemId) AS TotalItems
FROM Loan
WHERE MemberId=EntermemberId And yearStart=EnterStartYear AND yearEnd=EnterEndYear AND IssueDate>='09/01/'+yearStart And IssueDate<'09/01/'+yearEnd;

doesnt work properly, im guessing cause ive kept 'yearStart' and 'yearEnd' as 'Text' and 'IssueDate' as datatype date/time ? i tried changing the yearStart and yearEnd fields datatypes to 'Number' bt then the query returns an error saying query too complicated or incorrect.. :S

ps. need for speed was a small break from the stress this query was givin me..lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 11:44:03

thats why i told to make fields/parameters that store date values as of datetime type and YearStart,YearEnd to be of type int
then you can do


SELECT COUNT(ItemId) AS TotalItems
FROM Loan
WHERE MemberId=EntermemberId And yearStart=EnterStartYear AND yearEnd=EnterEndYear AND IssueDate>=DATEADD(mm,8,DATEADD(yy,yearStart-1900,0)) And IssueDate<DATEADD(mm,8,DATEADD(yy,yearEnd-1900,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zoodie
Starting Member

8 Posts

Posted - 2010-03-20 : 12:56:35

thank you for ur help

Did wht u told me to do..(changed yearStart yearEnd to int, IssueDate was already date/time type.

this is the error msg i get whn i run the query..

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-20 : 14:26:51

I did a quick Google search on your error and found that you are using Access which is not the same thing as SQL Server. They are similar, but there are going to be syntax differences. I've never worked with Access before, so I'm unable to convert visakh's solution into Access.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zoodie
Starting Member

8 Posts

Posted - 2010-03-20 : 17:10:47

yes, i forgot to mention im using access 2003 and tht i am not permitted to use query builder.. have to code it using SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources