Databases
StackOverflow2013
Postsdbo
POConvert Date Range to Individual Days
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConvert Date Range to Individual Days
    primarykey
    data
    text
    <p>A table called <code>VolumeRequest</code> stores the volume requests by accounts for a date range.</p> <pre><code>AccountId StartDate EndDate DailyVolume 670 2013-07-01 00:00:00.000 2013-07-31 00:00:00.000 10 670 2013-07-01 00:00:00.000 2013-07-31 00:00:00.000 1050 670 2013-07-10 00:00:00.000 2013-07-10 00:00:00.000 -350 670 2013-07-24 00:00:00.000 2013-07-26 00:00:00.000 -350 673 2013-06-01 00:00:00.000 2013-07-31 00:00:00.000 233 </code></pre> <p>I need to display the requests on daily basis where volume is summed by day by account for a given date range like for month of July the report is like below. The date start and end dates of the volume requests need to be trimmed for the given report dates</p> <pre><code>AccountId Date Volume 670 2013-07-01 00:00:00.000 1060 670 2013-07-02 00:00:00.000 1060 . . 670 2013-07-10 00:00:00.000 710 . . 670 2013-07-24 00:00:00.000 710 670 2013-07-25 00:00:00.000 710 670 2013-07-26 00:00:00.000 710 . . 670 2013-07-31 00:00:00.000 1060 673 2013-07-01 00:00:00.000 233 . . 673 2013-07-31 00:00:00.000 233 </code></pre> <p>Right now I am using table Variables and loops to achieve it which I know is not a good way to code. </p> <pre><code>DECLARE @sDate DATETIME, @eDate DATETIME , @volume DECIMAL (10, 4), rstartdate DATETIME, @renddate DATETIME , @loopcount INT SET @sdate = '4/1/2013' SET @edate = '4/30/2013' DECLARE @VolumeRequest TABLE ( ID INT IDENTITY (1, 1) PRIMARY KEY, Aid INT, Startdate DATETIME, Enddate DATETIME, volume DECIMAL (14, 4) ) DECLARE @DailyRequest TABLE ( ID INT IDENTITY (1, 1) PRIMARY KEY, Accountid INT, ReadDate DATETIME, Volume DECIMAL (14, 4) ) INSERT INTO @VolumeRequest SELECT Accountid, ( CASE WHEN @sdate &gt; startdate THEN @sdate ELSE startdate END ), ( CASE WHEN @edate &lt; enddate THEN @edate ELSE enddate END ), dailyvolume FROM VolumeRequest WHERE Startdate &lt;= @edate AND Enddate &gt;= @sdate AND isnull (deprecated, 0) != 1 --loop to breakdown the volume requests into daily requests SET @loopcount = 1 WHILE @loopcount &lt;= (SELECT MAX(ID) FROM @VolumeRequest) BEGIN SELECT @volume = volume, @rstartdate = Startdate, @renddate = Enddate FROM @VolumeRequest WHERE ID = @loopcount WHILE @rstartdate &lt;= @renddate BEGIN INSERT INTO @DailyRequest SELECT @currentaid, @rstartdate, @volume SET @rstartdate = DATEADD(day, 1, @rstartdate) END SET @LoopCount = @LoopCount + 1 END </code></pre> <p>I am looking for ways which don't involve loops or cursors. I found a <a href="https://stackoverflow.com/questions/5363003/sql-query-to-convert-date-ranges-to-per-day-records">Similar Question</a>. The answers there didn't help me. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USRam
    plurals
    1. PL
      singulars
      1. LTLinked
    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. POConvert Date Range to Individual Days
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFrom where does the 710 as the volume for the 10th come?
      singulars
      1. POConvert Date Range to Individual Days
      1. USDan Bracuk
    2. CO@DanBracuk as there is a volume request for `670` account for 10th with -350 as volume
      singulars
      1. POConvert Date Range to Individual Days
      1. USRam
 

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