PSEQUEL SUM PER DATE RANGE CODE

+ ''' BETWEEN ArrivalDate AND DepartureDate THEN ''1'' ELSE '''' END' FROM n SELECT TOP (DATEDIFF(DAY, (SELECT MinDate FROM dr), (SELECT MaxDate FROM dr)) + 1)ĭ = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY )-1, Assuming your table stores DATE and not DATETIME (or even worse, VARCHAR): USE tempdb ĭECLARE NVARCHAR(MAX) = N'SELECT Person_Id' I'm sure bluefeet will show up shortly and put me to shame, but in the meantime you can play with this dynamic SQL query. I'm old school, and find CASE easier to work out in my head than PIVOT. ) p results are the same (see SQL Fiddle With Demo): PERSON_ID | ARRIVALDATE | DEPARTUREDATE | | | | | | | Set = 'SELECT person_id, arrivaldate, departuredate, ' + + ' from Select = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120)) The dynamic version will generate the list of values to transform to columns: DECLARE AS AS NVARCHAR(MAX) Results (See SQL Fiddle With Demo): PERSON_ID | ARRIVALDATE | DEPARTUREDATE | | | | | | | On d.datelist between b.arrivaldate and b.departuredate Select b.person_id, b.arrivaldate, b.departuredate,Ĭonvert(CHAR(10), datelist, 120) PivotDate Select min(arrivaldate) datelist, max(departuredate) maxdate first into into a #temp table the list of dates that you want to turn to columns My answer will include both a Static and dynamic version because sometimes it is easier to understand it using a static version.Ī Static Pivot is when you hard-code all of the values that you want to transform into columns. If you are capturing the values in a different column you will also need to change the value of 3 in the Cells reference, to the column that captures the values.You can use the PIVOT function to perform this query.

PSEQUEL SUM PER DATE RANGE CODE

In this example the dates are captured in rows 8 to 14 (this example assumes that the dates and values are captured in the same row range, therefore, the row range for both dates and values need to match) and column 3, therefore, if you are capturing the values in different rows you will need to change the From and To i values in the VBA code (this will be the same as for dates, therefore, if you have already changed the i values for the dates no further action is required for the values row range). Values: Select the range of values that you want to sum that are associated with the specified day by changing the i values that represent the row range and the value of 3 in the Cells reference that represents the column reference. If you are capturing the dates in a different column you will also need to change the value of 2 in the Cells reference, to the column that captures the dates. In this example the dates are captured in rows 8 to 14 and column 2, therefore, if you are capturing the dates in different rows you will need to change the From and To i values in the VBA code. Output Range: Select the output range by changing the cell reference ('F7') in the VBA code to any cell in the worksheet that doesn't conflict with the formula.ĭate: Select the range that captures the dates that you want to test against by changing the i values that represent the row range and the value of 2 in the Cells reference that represents the column reference. In this example we have specified for the For Loop to loop through rows 8 to 14 in column 3 to find all of the values associated with the dates that fall into the 15th day. This is especially important for the For Loop to loop through specified rows and columns. Values: If using the exact VBA code you will need to capture the values that you want to sum that are associated with the specified day in range ('C8:C14'), which is the column next to the dates. In this example we have specified for the For Loop to loop through rows 8 to 14 in column 2 to find all of the dates associated with the 15th day. Worksheet Name: Have a worksheet named Analysis.ĭates: If using the exact VBA code you will need to capture the dates that you are testing against in range ('B8:B14').

Psequel sum per date range