Databases
StackOverflow2013
Postsdbo
POFilter based on multiple date ranges?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFilter based on multiple date ranges?
    primarykey
    data
    text
    <p>The user clicks on a month and then this stored procedure is executed. It checks for the total booked time and what groups have been filtered.</p> <pre><code> | Job Group | Month Booked | Time (hrs) | Cleaning Jan 7 </code></pre> <p>I have the following SQL:</p> <pre><code>SELECT tsks.grouping_ref, ttg.description AS grouping_desc, SUM(ts.booked_time) AS booked_time_total, DATENAME(MONTH, ts.start_dtm) + ' ' + DATENAME(YEAR, ts.start_dtm) AS month_name, @month_ref AS month_ref FROM timesheets ts JOIN timesheet_categories cat ON ts.timesheet_cat_ref = cat.timesheet_cat_ref JOIN timesheet_tasks tsks ON ts.task_ref = tsks.task_ref JOIN timesheet_task_groupings ttg ON tsks.grouping_ref = ttg.grouping_ref WHERE ts.status IN(1, 2) --Booked and approved AND cat.is_leave_category = 0 --Ignore leave AND DATEPART(YEAR, ts.start_dtm) = @Year AND DATEPART(MONTH, ts.start_dtm) = @Month GROUP BY tsks.grouping_ref, ttg.description, DATENAME(MONTH, ts.start_dtm), DATENAME(YEAR, ts.start_dtm) ORDER BY grouping_desc </code></pre> <p>I want to filter based on multiple date ranges.</p> <p>I thought about adding this:</p> <pre><code>AND ((ts.start_dtm BETWEEN '2011-12-28' AND '2012-01-01') OR (ts.start_dtm BETWEEN '2012-01-02' AND '2012-01-29')) </code></pre> <p>But then realized it wouldn't matter what month the user clicked it would still show all the records as it will carry out the OR statement.</p> <p>What I need is something that's based on the month_ref, eg: </p> <pre><code>CASE WHEN @month_ref = 81201 THEN AND (ts.start_dtm BETWEEN '2011-12-28' AND '2012-01-01') END </code></pre> <p>But the case statement needs to go just after the WHERE clause.</p> <p>I have about 12 accounting months for 2012 which I need to add as case statements so that when the user clicks on March, it will fire the correct filter.</p> <p>In the database <code>ts.start_dtm</code> looks like this:</p> <pre><code>2011-04-01 00:00:00.000 </code></pre> <p>Hope that was enough information for my first post?</p> <p>I'm stuck writing the case statement and where to put it, been trying for hours now.</p> <p>Hope you can help :)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USSOLDIER-OF-FORTUNE
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFilter based on multiple date ranges?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload