CODEWITHSUNDEEP
sql-server
iha
iha

Reputation: 157

C# pass repeated parameter to DataTable SQL Statement

I have this query in my DataTable SQL Statement, now I confuse, how to pass date parameter to this query, the date is only 2, startdate and enddate, but the date is repeated several times.

SELECT        
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR
                                                         shift = 'E' OR
                                                         shift = 'F' OR
                                                         shift = 'G' OR
                                                         shift = 'H' OR
                                                         shift = 'J' OR
                                                         shift = 'P') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS P,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_8
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR
                                                         shift = 'N' OR
                                                         shift = 'O' OR
                                                         shift = 'S' OR
                                                         shift = 'T') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS S,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_7
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'B' OR
                                                         shift = 'M' OR
                                                         shift = 'Q') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS M,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_6
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'C') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS C,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_5
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'D') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS D,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_4
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'I') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS I,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_3
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'K') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS K,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_2
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'L') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) AS L,
                             (SELECT        COUNT(shift) AS Expr1
                               FROM            dbo.JadwalKerja AS JadwalKerja_1
                               WHERE        (nip = dbo.JadwalDinas_DaftarKaryawan.NIP) AND (shift = 'R') AND (date >= CONVERT(DATETIME, '2015-12-01 00:00:00', 102)) AND (date <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102))) 
                         AS R
FROM            dbo.JadwalDinas_DaftarKaryawan
ORDER BY Nama

Upvotes: 2

Views: 63

Answers (1)

Horaciux
Horaciux

Reputation: 6477

You can declare variables to hold those values as this:

DECLARE @sDate datetime, @eDate datetime

SET @sDate = '2015-12-01 00:00:00'
SET @eDate = '2015-12-31 00:00:00'

Then replace CONVERT(DATETIME, '2015-12-01 00:00:00', 102) with @sDate and CONVERT(DATETIME, '2015-12-31 00:00:00', 102) with @eDate

You can create a stored procedure also containing all this query and reciving those variables as parameters

CREATE PROCEDURE mySP  @sDate datetime, @eDate datetime
AS
--Your query here

Upvotes: 3

Related Questions