Updating DLL refs when calling F# code from a C# project

This might be useful some other day. I had a situation where everything worked fine in the F# project but would crash with a FileNotFound FSharp.core exception when calling from the C# project. The problem was the I had used a build of FParsec that relied on an old version of FSharp.core. This was taken care of in the F# app.config file, but not in the app.config file of the C# project. So I just made sure the to add the same snippet to the C# project and everything works again. Snippet:

C# F# Excel parsing

Working on some new additions to MKC-shift planner I decided I would write an Excel Parser in F# to work with the main C# project. Most methods of the parser are in "Parse.fs"

module FSpart.Parse

open FSpart.Enums
open FSpart.Types
open Converters

open Microsoft.Office.Interop.Excel


let cellToString (range :Microsoft.Office.Interop.Excel.Range) =
    range.Value2.ToString()

let xlApp = 
    new ApplicationClass(Visible = true)

let workbook path (xlApp:Microsoft.Office.Interop.Excel.ApplicationClass) =
    xlApp.Workbooks.Open(path)

let sheetFromPath path sheetName (xlApp:Microsoft.Office.Interop.Excel.ApplicationClass) =
    let xlWorkbook = xlApp.Workbooks.Open(path)
    let xlWorksheet = xlWorkbook.Worksheets.[sheetName] :?> Microsoft.Office.Interop.Excel.Worksheet
    xlWorksheet
   
let sheetFromWorkbook (workbook:Microsoft.Office.Interop.Excel.Workbook) sheetname =
    workbook.Worksheets.[sheetname] :?> Microsoft.Office.Interop.Excel.Worksheet

... More methods are here

let ExcelExit =
    xlApp.Quit()
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject xlApp

To make this accesible to the main c# project I added "ExcelParser.fs"

module FSpart.Excelparser


type ExcelParser =
    static member Parse(path)=
        let Excel = Parse.xlApp
        let workbook = Parse.workbook path Excel
        let sheet = Parse.sheetFromWorkbook workbook 1
        let LongShiftPlan = Parse.longShiftAssignments sheet
        Parse.ExcelExit |> ignore
        LongShiftPlan

A reference to the F# project was added to the c# project and the parser can be called via.

FSpart.Excelparser.ExcelParser.Parse(path);

This Works, but there is a caveat. The last line in Parse.fs:

System.Runtime.InteropServices.Marshal.FinalReleaseComObject xlApp 

causes an error:
"COM object that has been separated from its underlying RCW cannot be used."
Omitting the line makes my program run, but it leaves those Excel processes running somewhere in the background. Any suggestions on avoiding this ?
SOLVED:
Mathias Brandewinder(@brandewinder) was kind enough to point me towards this SO post that includes lots of useful information. By following the "never use 2 dots on COM objects" rule the snippet below shows the changes made to ExcelParser, enough to kill of the Excel squatters.

module FSpart.Excelparser


type ExcelParser =
    static member Parse(path)=
        let Excel = Parse.xlApp
        let workbook = Parse.workbook path Excel
        let sheet = Parse.sheetFromWorkbook workbook 1
        let LongShiftPlan = Parse.longShiftAssignments sheet

        //Cleanup
        Parse.ExcelExit |> ignore
        System.GC.Collect()
        System.GC.WaitForPendingFinalizers()
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject sheet |>ignore
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject workbook |> ignore
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject Excel |> ignore


        LongShiftPla

Thanks Mathias.

F# fibonacci numbers

It's very warm. Here is an F# fibonacci sequence. I am not sure it's really good functional fu to use mutable values.

let sumUp =
    seq {let fib = ref 0
         let fib1 = ref 1
        while true do 
        yield !fib + !fib1
        let oldfib = !fib 
        fib:=!fib1
        fib1:=oldfib + !fib1}
        
(Seq.take 20 sumUp) |> Seq.iter (fun x -> printfn "%d" x)

SqlDataConnection and wasted time

Today I was trying to connect to an Azure SQL-database using F# type providers. The guide given here is great, but does not mention how to get the connection string to a database from Azure. There are some strings given inside the Azure portal, but none of them seem to match the format suggested in the guide. I spend a long time trying to construct a connection string and it just doesn't work. The connection works fine if I only specify the server, but when I also specify the database I get errors like:

Warning : SQM1012: Unable to extract table 'dbo.Board' from SqlServer. Invalid object name 'syscomments'

The table "dbo.Board" exists in the database, so somehow the connection is pointing to the right place. My code looks like this:

open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq
　

type dbSchema = SqlDataConnection<"Server=tcp:k9qd5a64zq.database.windows.net,1433;Database=timetobeat562;User ID=johanvts@k9qd5a64zq;Password=MYPASSWORD;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;">

let db = dbSchema.GetDataContext()
db.DataContext.Log <- System.Console.Out

printfn "%s" db.DataContext.Connection.DataSource

[]
let main argv =
    printfn "%A" argv
    System.Console.ReadLine() |> ignore
    0 // return an integer exit code

Any help would be greatly appriciated.

Rewriting another Query

I spend a lot of my time rewriting queries, so today I though I would document my process. We start with this query: 

Select A.*
,1 as Vægtet
,'Betalende' as rtype
From
(
SELECT AA.DT, CASE WHEN AA.afdeling='HK/Hovedstaden' AND Fsektor = 'HK/Privat' THEN aa.Asektor else aa.afdeling END as afdeling, AA.KTG_ID, aa.nvn, aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr,
(SUM(aa.blb_1mdr) * (aa.antal_medlemmer_1mdr)) - (SUM(aa.blb_ult) * (aa.antal_medlemmer_ult))as kr

FROM

(

SELECT convert(nvarchar(10),max(a.dt),105) as DT, a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END as hovedopdeling, a.ERHVERVSAKTIVE, a.Betalende, 
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END as afd_sortering,


SUM(a.antal_medlemmer_1mdr) as antal_medlemmer_1mdr, SUM(a.antal_medlemmer_2mdr) as antal_medlemmer_2mdr, SUM(a.antal_medlemmer_ult) as antal_medlemmer_ult,

SUM(a.blb_1mdr) as blb_1mdr, SUM(a.blb_2mdr) as blb_2mdr, SUM(a.blb_ult) as blb_ult

FROM

(


SELECT a.DT, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, b.blb as blb_1mdr, 0 as blb_2mdr, 0 as blb_ult, COUNT(*) as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select flag, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=1 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by ktg_id, flag,afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id  --and a.FLAG=b.flag
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
  
where DT=(select max(dt) from lr..medlem) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.DT, a.KTG_ID, a.AAR, a.MAANED, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb


UNION ALL


SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, b.blb as blb_2mdr, 0 as blb_ult, 0 as antal_medlemmer_1mdr, COUNT(*) as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=2 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id ) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id 
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
  
where a.dt=(select dt from lr.dbo.tKalender_statistik where flag=1) and a.MEDL_FORB=1 -- and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE,a.AAR, a.MAANED,  d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb

UNION ALL

SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, 0 as blb_2mdr, b.blb as blb_ult, 0 as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, COUNT(*) as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb,  afd_sektor_id from [LR].[dbo].[tKontingentBlb] where dt = (SELECT  Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id 
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
  
where a.dt=(SELECT  Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and a.MEDL_FORB=1 -- and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling,a.ERHVERVSAKTIVE ,a.AAR, a.MAANED,  d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb

) A

group by a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END, a.ERHVERVSAKTIVE, a.Betalende, 
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END

) AA

where aa.asektor <> '?' and aa.afdeling not in ('HK/Trafik og Jernbane', 'HK/Post og kommunikation')

GROUP BY AA.DT, AA.afdeling, AA.KTG_ID, aa.nvn,aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr
) A 
--Cross join (SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]) B
--left join (SELECT [dt], [ktg_id], [afd_sektor_id], [Forbund],[Afdeling],[Sektor],[Fuldtid_Forbund],[Fuldtid_Afdeling],[Fuldtid_Sektor] FROM [LR].[dbo].[factKontigent_Blb] Where dt =(SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]))
--C on A.KTG_ID = C.ktg_id and A.AFD_SEKTOR_ID = C.[afd_sektor_id]


union all

Select A.DT ,CASE WHEN A.afdeling='HK/Hovedstaden' THEN a.Asektor else a.afdeling END as afdeling,A.KTG_ID,A.nvn ,A.AFD_SEKTOR_ID,A.Asektor,A.Fsektor,A.hovedopdeling,A.ERHVERVSAKTIVE,A.Betalende,A.afd_sortering
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end * A.antal_medlemmer_1mdr as antal_medlemmer_1mdr
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end * A.antal_medlemmer_2mdr as antal_medlemmer_2mdr
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end * A.antal_medlemmer_ult as antal_medlemmer_ult
,A.blb_1mdr
,A.kr
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end as Vægtetmedlem
,'Fuldtidsvægtede' as rtype
From
(
SELECT AA.DT, AA.afdeling, AA.KTG_ID, aa.nvn, aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr,
(SUM(aa.blb_1mdr) * (aa.antal_medlemmer_1mdr)) - (SUM(aa.blb_ult) * (aa.antal_medlemmer_ult))as kr

FROM

(

SELECT convert(nvarchar(10),max(a.dt),105) as DT, a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END as hovedopdeling, a.ERHVERVSAKTIVE, a.Betalende, 
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END as afd_sortering,


SUM(a.antal_medlemmer_1mdr) as antal_medlemmer_1mdr, SUM(a.antal_medlemmer_2mdr) as antal_medlemmer_2mdr, SUM(a.antal_medlemmer_ult) as antal_medlemmer_ult,

SUM(a.blb_1mdr) as blb_1mdr, SUM(a.blb_2mdr) as blb_2mdr, SUM(a.blb_ult) as blb_ult

FROM

(


SELECT a.DT, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, b.blb as blb_1mdr, 0 as blb_2mdr, 0 as blb_ult, COUNT(*) as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select flag, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=1 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by ktg_id, flag,afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id  --and a.FLAG=b.flag
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
  
where DT=(select max(dt) from lr..medlem) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.DT, a.KTG_ID, a.AAR, a.MAANED, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb


UNION ALL


SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, b.blb as blb_2mdr, 0 as blb_ult, 0 as antal_medlemmer_1mdr, COUNT(*) as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=2 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id ) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id 
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
  
where a.dt=(select dt from lr.dbo.tKalender_statistik where flag=1) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE,a.AAR, a.MAANED,  d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb

UNION ALL

SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, 0 as blb_2mdr, b.blb as blb_ult, 0 as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, COUNT(*) as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb,  afd_sektor_id from [LR].[dbo].[tKontingentBlb] where dt = (SELECT  Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id 
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
  
where a.dt=(SELECT  Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling,a.ERHVERVSAKTIVE ,a.AAR, a.MAANED,  d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb

) A

group by a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END, a.ERHVERVSAKTIVE, a.Betalende, 
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END

) AA

where  aa.asektor <> '?' and aa.afdeling not in ('HK/Trafik og Jernbane', 'HK/Post og kommunikation')

GROUP BY AA.DT, AA.afdeling, AA.KTG_ID, aa.nvn,aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr
) A 
--Cross join (SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]) B
left join (SELECT [dt], [ktg_id], [afd_sektor_id], [Forbund],[Afdeling],[Sektor],[Fuldtid_Forbund],[Fuldtid_Afdeling],[Fuldtid_Sektor] FROM [LR].[dbo].[factKontigent_Blb] Where dt =(SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]))
C on A.KTG_ID = C.ktg_id and A.AFD_SEKTOR_ID = C.[afd_sektor_id]
Where antal_medlemmer_1mdr>0 or antal_medlemmer_2mdr > 0 or antal_medlemmer_ult > 0

This query is central to a few dashboards but I needed to make some changes and I would also like it to load faster, currently it takes 39 seconds. I skimmed the entire query, but to make changes I just start from the top. Open a new query windows and make a copy so I can compare my changes query results to the current ones.

Starting at the top the first 4 lines simply add two columns with fixed values "1, 'Betalende'", so I remove that outer query and add it to the first inner query starting at line 6. I also removed the mathching ending on line 75.

Next I looked at the many unioned queries starting at line 26, they seem very similar. Looking closer they select the same thing, except the last six values where they are used to sum one thing at a time, and then the outer queries collapses the rows using sum. It seems this would be better handled by a single select statement with conditional sums. I started writing my conditional sums, one thing that helped a lot is to always look at the "raw" data, that's just select * without the aggregates and looking at  the rows. In the end i had constructed this:

SELECT max(a.dt)
       , a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling
       , a.ERHVERVSAKTIVE, case when sum(b.blb) > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende
    , sum( CASE WHEN flag = 0 THEN b.blb ELSE 0 END) as blb_1mdr
    , sum( CASE WHEN flag = 1 THEN b.blb ELSE 0 END) as blb_2mdr
    , sum( CASE WHEN a.Dt = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN b.blb ELSE 0 END) as blb_ult
    , COUNT(DISTINCT CASE WHEN flag = 0 THEN PRS_ID ELSE NULL END) as antal_medlemmer_1mdr
    , COUNT( DISTINCT CASE WHEN flag = 1 THEN PRS_ID ELSE NULL END) as antal_medlemmer_2mdr
    , COUNT( DISTINCT CASE WHEN a.Dt = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN PRS_ID ELSE NULL END) as antal_medlemmer_ult
    , CASE WHEN COUNT(distinct case when flag = 0 then Beskrivelse else null end) = 0 THEN 0 ELSE SUM( CASE WHEN FLAG = 0 THEN fuld_vægt ELSE 0 END)/COUNT(distinct case when flag = 0 then Beskrivelse else null end)END
    , CASE WHEN COUNT(distinct case when flag = 1 then Beskrivelse else null end) = 0 THEN 0 ELSE SUM( CASE WHEN FLAG = 1 THEN fuld_vægt ELSE 0 END)/COUNT(distinct case when flag = 1 then Beskrivelse else null end) END
    , CASE WHEN COUNT(distinct case when a.DT = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) then Beskrivelse else null end) = 0 THEN 0 ELSE SUM( CASE WHEN a.DT = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN fuld_vægt ELSE 0 END)/COUNT(distinct case when a.DT = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) then Beskrivelse else null end) END
    from lr.dbo.Medlem a
    left join [LR].[dbo].[tKontingentBlb] b on a.KTG_ID=b.ktg_id and a.afd_sektor_id  = b.afd_sektor_id and a.DT = b.dt
    left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
    left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
    left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
    left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
    OUTER APPLY (SELECT top 1 CASE WHEN sum([Fuldtid_Forbund]+[Fuldtid_Afdeling]+[Fuldtid_Sektor]) = 0 THEN 0 ELSE 
     sum(CAST([Forbund]+[Afdeling]+[Sektor] AS FLOAT)/([Fuldtid_Forbund]+[Fuldtid_Afdeling]+[Fuldtid_Sektor])) END as fuld_vægt
    FROM [LR].[dbo].[factKontigent_Blb] 
    where ktg_id= A.KTG_ID and [afd_sektor_id]= A.AFD_SEKTOR_ID and 
    dt = a.DT ) ktg_fact 
    where (a.DT>=DATEADD(M,-1,GETDATE()) OR a.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105)) and a.MEDL_FORB=1  and beskrivelse in ('Forbund', 'Afdeling','Sektor') and a.dt = b.dt
    group by    a.KTG_ID,c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor,fuld_vægt
    order by ktg_id,afd_sektor_id,afdeling

While this is much more compact, it's still not very readable and it turns out the wrong results. The summing simply is too complicated and too many strange constrains across the rows apply. Time to start over. In my second approach i first select the data i am looking for a into a temporary table and a CTE and the abuse the PIVOT command to get the summations as needed. Notice that the aggragation in my last select are necessary for the use of more than one PIVOT statement:

/*
Datagrundlag til medlemsdelen af det fælles dashboard
Author : Johan Sivertsen

==================================
Kontingentoplysninger: Samme som viewet factkontingentblb men her valgt kun for de tre relevante måneder
*/
SELECT     Kontigent.*, Fuldtid.Forbund AS [Fuldtid_Forbund], Fuldtid.Afdeling AS [Fuldtid_Afdeling], Fuldtid.Sektor AS [Fuldtid_Sektor], 
                      Fuldtid.[AkasseAdm] AS [Fuldtid_AkasseAdm] into #kont_fact
FROM         (SELECT     dt, aar, maaned, ktg_id, afd_sektor_id, sum(isnull([Forbund], 0)) AS [Forbund], sum(isnull([Afdeling], 0)) AS [Afdeling], sum(isnull([Sektor], 0)) 
                                              AS [Sektor], Sum(isnull([AkasseAdm], 0)) AS [AkasseAdm]
                       FROM          (SELECT     Kontingent.dt, Kontingent.aar, Kontingent.maaned, Kontingent.ktg_id, Kontingent.afd_sektor_id, Kontingent.beskrivelse, 
                                                                      Kontingent.blb, fuldtid.fblb, CASE fuldtid.fblb WHEN 0 THEN 0 ELSE cast(Kontingent.blb AS FLOAT) / cast(fuldtid.fblb AS FLOAT) 
                                                                      END AS fa
                                               FROM          (SELECT     tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, blb AS fblb, beskrivelse, afd_sektor_id
                                                                       FROM          dbo.tKontingentBlb_alle
                                                                       WHERE      ((tKontingentBlb_alle.ktg_id = 1110) AND beskrivelse IN ('Sektor', 'Afdeling', 'Forbund')) OR
                                                                                              ((tKontingentBlb_alle.ktg_id = 2110) AND beskrivelse IN ('AkasseAdm'))
                                                                       GROUP BY tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, tKontingentBlb_alle.blb, tKontingentBlb_alle.afd_sektor_id, 
                                                                                              tKontingentBlb_alle.beskrivelse) AS fuldtid INNER JOIN
                                                                      tKontingentBlb_alle AS Kontingent ON fuldtid.aar = Kontingent.aar AND fuldtid.maaned = Kontingent.maaned AND 
                                                                      fuldtid.afd_sektor_id = Kontingent.afd_sektor_id AND fuldtid.beskrivelse = Kontingent.beskrivelse) AS A PIVOT (max(blb) FOR 
                                              beskrivelse IN ([Forbund], [Afdeling], [Sektor], [AkasseAdm])) AS pvt
                       GROUP BY dt, aar, maaned, ktg_id, afd_sektor_id) AS Kontigent INNER JOIN
                          (SELECT     dt, aar, maaned, ktg_id, afd_sektor_id, sum(isnull([Forbund], 0)) AS [Forbund], sum(isnull([Afdeling], 0)) AS [Afdeling], sum(isnull([Sektor], 
                                                   0)) AS [Sektor], Sum(isnull([AkasseAdm], 0)) AS [AkasseAdm]
                            FROM          (SELECT     Kontingent.dt, Kontingent.aar, Kontingent.maaned, Kontingent.ktg_id, Kontingent.afd_sektor_id, Kontingent.beskrivelse, 
                                                                           Kontingent.blb, fuldtid.fblb, CASE fuldtid.fblb WHEN 0 THEN 0 ELSE cast(Kontingent.blb AS FLOAT) / cast(fuldtid.fblb AS FLOAT) 
                                                                           END AS fa
                                                    FROM          (SELECT     tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, blb AS fblb, beskrivelse, afd_sektor_id
                                                                            FROM          dbo.tKontingentBlb_alle
                                                                            WHERE      ((tKontingentBlb_alle.ktg_id = 1110) AND beskrivelse IN ('Sektor', 'Afdeling', 'Forbund')) OR
                                                                                                   ((tKontingentBlb_alle.ktg_id = 2110) AND beskrivelse IN ('AkasseAdm'))
                                                                            GROUP BY tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, tKontingentBlb_alle.blb, tKontingentBlb_alle.afd_sektor_id, 
                                                                                                   tKontingentBlb_alle.beskrivelse) AS fuldtid INNER JOIN
                                                                           tKontingentBlb_alle AS Kontingent ON fuldtid.aar = Kontingent.aar AND fuldtid.maaned = Kontingent.maaned AND 
                                                                           fuldtid.afd_sektor_id = Kontingent.afd_sektor_id AND fuldtid.beskrivelse = Kontingent.beskrivelse) AS A PIVOT (max(fblb) FOR 
                                                   beskrivelse IN ([Forbund], [Afdeling], [Sektor], [AkasseAdm])) AS pvt
WHERE (DT>=DATEADD(M,-1,GETDATE()) OR DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105))
GROUP BY dt, aar, maaned, ktg_id, afd_sektor_id) AS Fuldtid ON Kontigent.dt = Fuldtid.dt AND Kontigent.ktg_id = Fuldtid.ktg_id AND 
                      Kontigent.afd_sektor_id = Fuldtid.afd_sektor_id;

/*
====================== # Medlem ===============
Danner medlemsoplysninger - Bruger WITH da dette er den tungeste af de to midlertidige tabeller
*/
WITH medlem AS(
SELECT medlem.dt,medlem.prs_id, medlem.KTG_ID, afd.afdeling, medlem.AFD_SEKTOR_ID, Asek.Asektor, Fsek.Fsektor
FROM LR.dbo.medlem medlem
 left join lr.dbo.tAfdeling afd on medlem.krds_id = afd.krds_id
 left join lr.dbo.tAsektor Asek on medlem.afd_sektor_id = Asek.afd_sektor_id
 left join lr.dbo.tFsektor Fsek on medlem.FORB_SEKTOR_ID = Fsek.forb_sektor_id
WHERE (medlem.DT>=DATEADD(M,-1,GETDATE()) OR medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105)) --medlemmer fra de to seneste md samt ult. sidste år
      and medlem.MEDL_Forb = 1  )                       --medlemmer af forbundet)




/*Det hele klistres sammen med en lækker triple PIVOT, den første pivot opbruger egentlig den midlertidige tabel, 
men hvis man undlader at kalde den(SourceTable) ved navn virker det. Grupperingen er nødvendig da PIVOT ikke ved
at de tre "id" nøgler egentlig alle peger samme opdeling af dt.
*/


SELECT afdeling, CASE WHEN Fsektor = 'HK/Privat' and afdeling = 'HK/Hovedstaden' THEN Asektor ELSE afdeling END as Afdeling_Privat,Fsektor,ktg_id,nvn,hovedopdeling, ---row
  isnull(MAX([0]),0) as antal_dm,
  isnull(MAX([1]),0) as antal_sm,
  isnull(MAX([2]),0) as antal_ult,
  isnull(MAX([3]),0) as ktg_dm,
  isnull(MAX([4]),0) as ktg_sm,
  isnull(MAX([5]),0) as ktg_ult,
  Coalesce(MAX([6]),MAX([7]),MAX([8])) as fuldktg_dm,
  Coalesce(MAX([7]),MAX([6]),MAX([8])) as fuldktg_sm,
  Coalesce(MAX([8]),MAX([7]),MAX([6])) as fuldktg_ult,
  ISNULL(MAX([9]),0) as fb_dm,
  ISNULL(MAX([10]),0) as fb_sm,
  ISNULL(MAX([11]),0) as fb_ult,
  Coalesce(MAX([12]),MAX([9]),1) as fb_fuld_dm, --The coalesce avoid deviding with 0
  Coalesce(MAX([13]),MAX([10]),1) as fb_fuld_sm,
  Coalesce(MAX([14]),MAX([11]),1) as fb_fuld_ult,
  CASE WHEN MAX([3])>0 THEN 1 ELSE 0 END AS Betalende
FROM(
SELECT  CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 2 WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 1 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 0 ELSE NULL END as antal_id,
  CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 5  WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 4 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 3 ELSE NULL END as blb_id,
  CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 8  WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 7 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 6 ELSE NULL END as fuld_id,
  CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 11  WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 10 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 9 ELSE NULL END as fb_id,
  CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 14  WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 13 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 12 ELSE NULL END as fb_fuld_id,
  medlem.prs_id,medlem.afdeling,medlem.Asektor,medlem.Fsektor,medlem.KTG_ID,kont_description.nvn,kont_description.hovedopdeling,
  forbund+kont.afdeling+sektor as beløb,
  Fuldtid_forbund+fuldtid_afdeling+fuldtid_sektor as Fuldt_beløb,
  Forbund as fb_beløb,
  Fuldtid_Forbund as fb_fuldt_beløb
 FROM medlem
 Left join #kont_fact as kont on medlem.KTG_ID=kont.ktg_id and medlem.AFD_SEKTOR_ID = kont.afd_sektor_id and medlem.DT = kont.dt
 Left join tKontingent as kont_description on medlem.KTG_ID = kont_description.ktg_id) AS SourceTable
 PIVOT
 (
 COUNT(SourceTable.prs_id)
 FOR antal_id IN([0],[1],[2])
 ) AS PivotTable
 PIVOT
 (
 SUM(beløb)
 FOR blb_id IN([3],[4],[5])
 ) AS PivotTable
 PIVOT
 (
 SUM(Fuldt_beløb)
 FOR fuld_id IN([6],[7],[8])
 ) AS PivotTable
 PIVOT
 (
 SUM(fb_beløb)
 FOR fb_id IN ([9],[10],[11])
 ) AS PivotTable
 PIVOT
 (
 SUM(fb_fuldt_beløb)
 FOR fb_fuld_id IN ([12],[13],[14])
 ) AS PivotTable
 GROUP BY afdeling, CASE WHEN Fsektor = 'HK/Privat' and afdeling = 'HK/Hovedstaden' THEN Asektor ELSE afdeling END,Fsektor,ktg_id,nvn,hovedopdeling

drop table #kont_fact

While this query is far from elegant it is still more compact and readable than what we started with, but it requires an understanding of the PIVOT function, and how to abuse it to pivot multiple colums. This is achieved by constructing multiple ID's and using aggregation. Not pretty, but it works. Best of all it runs in around 14 seconds, mostly because it does not rely on the factKontingentblb view, but just selects the necessary subsection into a tmp. table.

Since last time - Ugly fonts on SSRS charts

Since finishing my thesis I have moved away from working with C++ to a temporary job with SQL and the microsoft BI stack while I await a decision on my Ph.D. application. So I will try to write a bit about SSRS and SQL here the next few months. I also try to catch up on some F# in my spare time so hopefully I will also write a bit about that.

Today I had to deal with a old nemesis: SSRS 2008. When drawing graphs the font sometimes becomes terribly ugly. The problem is that SSRS cannot use anti-aliasing when you set the chart background to "No color", simply because it appearently has to render the chart before knowing about the color of the background. If you set a background color the text becomes smooth again. You can then use if statements to make the background conditional, ex. make it different when printing and viewing online using Globals!RenderFormat.

Also yesterday I learned that the VB 2005 expressions you write in SSRS 2008 have two different "OR" statements: Or and OrElse. The last one is short-curcuiting, but the other one isn't. Why would you ever need one that isn't? I really cannot come up with a case for VB 2005 regular Or.

ssh agent

Ok, I am writing a bit again. Currently in the processe of applying for a Ph.D and wasted some time trying to add a key to ssh-agent using ssh-add 'key'. Turns out the agent needs to be runnig first and it can be hard to start through an ssh. But "eval $(ssh-agent)" did the trick in the end.