Excel file fetch data from sql server in 1 second refresh

1.      

2.       Connect with sql server

3.      

4.       Refresh connection property

5.        Go to developer tool à click on visual stio

Click on this work book code

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:00:01"), "SaveThis"

End Sub

6.       Add new module code id

Sub SaveThis()

Application.DisplayAlerts = False

ThisWorkbook.RefreshAll

Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:00:01"), "SaveThis"

End Sub

Run use it

Mahesh arya

Mahesh arya

sql server Find Days between date

SET DATEFIRST 1

DECLARE

    @start_date DATETIME,

    @end_date DATETIME

SET @start_date = DATEADD(MONTH,-1,CONVERT(DATETIME,CONVERT(VARCHAR(4),YEAR(GETDATE()))+'-'+CONVERT(VARCHAR(4),MONTH(GETDATE()))+'-24'))

SET @end_date = CONVERT(DATETIME,CONVERT(VARCHAR(4),YEAR(GETDATE()))+'-'+CONVERT(VARCHAR(4),MONTH(GETDATE()))+'-23')

;WITH Days_Of_The_Week AS (

    SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL

    SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL

    SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL

    SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL

    SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL

    SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL

    SELECT 7 AS day_number, 'Sunday' AS day_name

)

SELECT

    day_name,

    DATEDIFF(wk, @start_date, @end_date) -

        CASE WHEN DATEPART(weekday, @start_date) > day_number THEN 1 ELSE 0 END -

        CASE WHEN DATEPART(weekday, @end_date)   < day_number THEN 1 ELSE 0 END

FROM

    Days_Of_The_Week