locked
How to Perform this Query ? RRS feed

  • Question

  • Tables : LPDetail, LPType, PStats, KDate, Div, Band

    Table : LPDetail
    PDID (Primary Key)
    PTID
    DivID
    BID
    CheckDate

    Table : LPType
    PTID (Primary Key)

    Table : PStats
    PSID (Primary Key)

    Table : KDate
    KDID
    PDID - FRom LPDetail
    PSID - From PStats

    Table : Div
    DivID

    Table : Band
    BID

    Now I have a page where there are 3 dates : (start and end date) and (check Date)..Then I have a Status combobox, div and band.

    So first type of possibility with (Start and end date) : User picks these two dates, then pick status , div and band. (All the comboxboxes are multiselect)....User can select one entry or multile entries.....

    2) When only check date is selected and everything else stays the same ?

    My Question : How can I perform the above task ?
    2) Can be done is single Query ? How ?

     

    Thanks for all the help.

     

    Harsimrat

    Friday, June 1, 2007 7:07 PM

Answers

  • For your first problem -- passing in more than one item picks -- give a look to this article by MVP Erland Sommarskog:

     

    http://www.sommarskog.se/arrays-in-sql.html

     

    For the second problem, the short answer is, "No", you do not need separate queries if you have variant date selection criteria; however, separate queries might end up being a viable option for one reason or another.  I still do not have a good feel for the data so I really cannot say about this one.

     

    Another option for the first problem -- especially if you need to send multiple fields per item as a key -- might be to pass this argument as an XML string.

     

     If your keys for multiple entries are single column integers or strings you might give thought to passing a single comma separated "string list" that contains the keys -- a string something like '14,16,18,19'.  This could then be processed by something similar to Jens Suessmeyers SPLIT function.  An example of Jens' split function can be found here:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

    Friday, June 1, 2007 9:02 PM

All replies

  • Harsimrat:

     

    I need more information.  First, I can certainly see that you are describing some input data:

     

    • DivID
    • BID
    • Status
    • Check Date
    • Begin Date
    • End Date

    However, I do not understand how "Status" relates to any of these tables.  Please clarify this.  The next problem has to do with the "Begin Date" and "End Date".  Since "Check Date" is probably compared to the "CheckDate" field it does not seem likely that "Begin Date" and "End Date" will get compared to "CheckDate".  Unfortunately, this is the only date that I see in any of your tables.  Now, there is a "KDate" table, but there are no dates in this table.  Did you leave out the date from this table?

     

    Finally, you stated no objective as far as what you are trying to accomplish in the database.  It looks to me like you are implying a whole series of activities.  Please give more description of what you are trying to accomplish.

     

     

    Kent

    Friday, June 1, 2007 8:39 PM
  • Hello Kent,

     

    This is like a reporting functionality :

     

    (Begin and End Date) and Check Date are both compared to "checkDate" as well. Reason being when user only selects "checkdate" that means for that particular date and when User selects Begin and End Date that means checkDate that falls in that range.

     

    If you see KDate table there PSID from PStats table is available. 

     

    Now For Example : User picks (begin and end date) then pick up a status(its a multiselect box) can be one status or multiples. Then User picks div and band (can be one or multiples agian).

     

    2nd Case : User picks only check date and rest everything stays the same as Case 1.

     

    Now based on the above what results get returned need to be shown to the User ?

     

    1st Problem : Multiselect boxes, when user picks more than one item. How to incorporate that in the query.

    2nd Problem : For two different types of dates do I need two queries or one ?

     

    Hopefully this explains little more.

     

    Let me know if you need more explanation.

     

    Thanks for helping.

     

    Harsimrat

     

    Friday, June 1, 2007 8:52 PM
  • For your first problem -- passing in more than one item picks -- give a look to this article by MVP Erland Sommarskog:

     

    http://www.sommarskog.se/arrays-in-sql.html

     

    For the second problem, the short answer is, "No", you do not need separate queries if you have variant date selection criteria; however, separate queries might end up being a viable option for one reason or another.  I still do not have a good feel for the data so I really cannot say about this one.

     

    Another option for the first problem -- especially if you need to send multiple fields per item as a key -- might be to pass this argument as an XML string.

     

     If your keys for multiple entries are single column integers or strings you might give thought to passing a single comma separated "string list" that contains the keys -- a string something like '14,16,18,19'.  This could then be processed by something similar to Jens Suessmeyers SPLIT function.  An example of Jens' split function can be found here:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

    Friday, June 1, 2007 9:02 PM