I'm probably a bit late but maybe this can help someone else.
First create a stored proceedure in your SQL server that has a start date (can be start + end date) but for our company we just want a start date:
Here is some simple SQl code to show this - outlined in bold the iimportant bits (im not here to teach sql lol)
USE [MyMainDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_Midnight_Census]
(
@vStartDate Date
)
AS
BEGIN
SET NOCOUNT ON;
-- Step 1 is to create a temporary table to store our date range…
create table #dateTable (thedate datetime)
--Step 2 is create some variables for the start and end date.
declare @dtEnd date, @hour int, @days int
set @days = DATEDIFF(DAY, @vStartDate, CURRENT_TIMESTAMP)
set @dtEnd = dateadd(day, @days, @vStartDate)
set @hour = 00
--Step 3 is to insert our dates into the temporary table… we do this with a bit of SQL Server magic that selects sequential numbers from a master table and then adds the same number of days to our base date.
--I have also added 00 hours to the date (@hours) because for this dataset we want midnight patients. For midday etc @hour would be set to 12
insert into #dateTable
select DATEADD(hour, @hour, CONVERT(datetime, dateadd(day, number, @vStartDate))) as 'thedate'
from
(select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @vStartDate) < @dtEnd
--Final step is to join this date table with the MyDATA transfer table.
--NOTE: dischg_dtm = null so my join is in actual fact slightly wrong
select d.thedate, t.ward, count(*) as occupancy
from [server\HOSP2020].[MyDATA ].[dbo].[tablename] t
join #dateTable d on d.thedate between ward_admit and ward_disch
where t.ward_id like 'FP%'
group by d.thedate, t.ward
order by d.thedate, t.ward
drop table #datetable
END
* Now I want to create my Power BI query/new paramter to handle this SQL connection
- Open Power BI
- Open the Document to link the data to
- Click on: Get data > Blank Query
- Click on Manage Parameters > New Parameter
- Under Name type "Somethign Descriptive"
- Type server\name into “Current Value”
- Select – OK
- Select Advanced Properties
- Replace anything here with
let
SQLSource = (vStartDate as date) =>
let
Source = Sql.Database("server\Name", "ClinEpiReporting", [Query="exec sp_Midnight_Census '"& Date.ToText(vStartDate) & "' #(lf)#(lf)#(lf) #(lf)"])
in
Source
in
SQLSource
- Click Done
- Select Query and enter a start date and then click “Invoke”.
- It may show a warning
- Click “Edit Permission”
- It may show a warning to run the query in Native Mode. Click “Run”.
- It will load all the data.
- Click “Close and Apply”.
- This should get you going, we found it relatively easy to create a stored proceedure using google. Everything outlined here is pretty much how we start off - of course our stored proceedure has a lot more in it and the one here is a simngle table example, not something useful to you, but ive outlined in bold text the main parts which let you make it a start date paramter, which I've found really useful in Power BI. Hope it helps.
