Dynamic SQL

Question

Dynamic SQL

bhaveshp.dba

SSC Eights!

Points: 834
More actions
November 8, 2011 at 9:02 am

#262515

Hello All,
Please any can help me how to use dynamic sql. I don't know how to use dynamic sql .
Here I only used 3 conditon to insert record.but
I want to insert 15 more condition based on [ApptTimeStampFromID],[ApptTimeStampToID], [TotalDuration] and where clause
How can i use dynamic sql for different conditon?
Please help me
INSERT INTO t_ApptDuration_Monthly ([Year],[Month],[FirstOfMonth],[ApptTimeStampFromID],[ApptTimeStampToID],
[TotalDuration],[VisitType],[NumberOfAppts] ,[FacilityID],[Drno],[CreatedDateTime] )
SELECT YEAR(appt.appointment_date) as [Year], Month(appt.appointment_date) as [Month],
CONVERT(datetime,convert(char(4),Year(appt.appointment_date)) + '-' + convert(char(2),Month(appt.appointment_date)) + '-01') as FirstOFMonth,
1, 2,Sum(CASE WHEN dbo.f_ConvertToDate(appt.arrival_time) < dbo.f_ConvertToDate(appt.appointment_time) THEN 0 ELSE
DATEDIFF(MI, dbo.f_ConvertToDate(appt.appointment_time),dbo.f_ConvertToDate(appt.arrival_time)) END)
TotalDuration,
pc.ProcedureName as VisitType,COUNT(appt.appointment_key) As NumOfAppts,
f.FacilityID,appt.resource_id as Drno,
GetDate()as CurrentDate
FROM Facilities AS f INNER JOIN
FacilityCodeXref AS facXref ON f.FacilityID = facXref.FacilityID INNER JOIN
sch_app_slot AS appt ON facXref.FacilityCode = appt.faccode
left outer join finhdr h on appt.appointment_key=h.appointment_key
inner join findet d on h.invno=d.invno and h.patkey=d.patkey and h.entryno=d.entryno
inner join ProcedureCodeXref pc on d.proccode=pc.proccode
WHERE pc.ProcedureName<>'Surgery' and
appt.appointment_date BETWEEN @startDate and @endDate
AND appt.appointment_status = 0 and appt.arrival_time is not nullGROUP BY f.FacilityID, pc.ProcedureName, appt.resource_id, YEAR(appt.appointment_date), Month(appt.appointment_date)
Union All
SELECT YEAR(appt.appointment_date) as [Year], Month(appt.appointment_date) as [Month],
CONVERT(datetime,convert(char(4),Year(appt.appointment_date)) + '-' + convert(char(2),Month(appt.appointment_date)) + '-01') as FirstOFMonth,
1,3,
Sum(CASE WHEN dbo.f_ConvertToDate(appt.exam_start_time) < dbo.f_ConvertToDate(appt.appointment_time) THEN 0 ELSE
DATEDIFF(MI, dbo.f_ConvertToDate(appt.appointment_time),dbo.f_ConvertToDate(appt.exam_start_time)) END)as
TotalDuration,pc.ProcedureName as VisitType,COUNT(appt.appointment_key) As NumOfAppts,
f.FacilityID,appt.resource_id as Drno,
GetDate()as CurrentDate
FROM Facilities AS f INNER JOIN
FacilityCodeXref AS facXref ON f.FacilityID = facXref.FacilityID INNER JOIN
sch_app_slot AS appt ON facXref.FacilityCode = appt.faccode
left outer join finhdr h on appt.appointment_key=h.appointment_key
inner join findet d on h.invno=d.invno and h.patkey=d.patkey and h.entryno=d.entryno
inner join ProcedureCodeXref pc on d.proccode=pc.proccode
WHERE pc.ProcedureName<>'Surgery' and
appt.appointment_date BETWEEN @startDate and @endDate
AND appt.appointment_status = 0 and appt.exam_start_time is not nullGROUP BY f.FacilityID, pc.ProcedureName, appt.resource_id, YEAR(appt.appointment_date), Month(appt.appointment_date)
Union All
SELECT YEAR(appt.appointment_date) as [Year], Month(appt.appointment_date) as [Month],
CONVERT(datetime,convert(char(4),Year(appt.appointment_date)) + '-' + convert(char(2),Month(appt.appointment_date)) + '-01') as FirstOFMonth,
2,3,
Sum(CASE WHEN dbo.f_ConvertToDate(appt.exam_end_time) < dbo.f_ConvertToDate(appt.appointment_time) THEN 0 ELSE
DATEDIFF(MI, dbo.f_ConvertToDate(appt.appointment_time),dbo.f_ConvertToDate(appt.exam_end_time)) END) as
TotalDuration,
pc.ProcedureName as VisitType,COUNT(appt.appointment_key) As NumOfAppts,
f.FacilityID,appt.resource_id as Drno,
GetDate()as CurrentDate
FROM Facilities AS f INNER JOIN
FacilityCodeXref AS facXref ON f.FacilityID = facXref.FacilityID INNER JOIN
sch_app_slot AS appt ON facXref.FacilityCode = appt.faccode
left outer join finhdr h on appt.appointment_key=h.appointment_key
inner join findet d on h.invno=d.invno and h.patkey=d.patkey and h.entryno=d.entryno
inner join ProcedureCodeXref pc on d.proccode=pc.proccode
WHERE pc.ProcedureName<>'Surgery' and
appt.appointment_date BETWEEN @startDate and @endDate
AND appt.appointment_status = 0 and appt.exam_end_time is not nullGROUP BY f.FacilityID, pc.ProcedureName, appt.resource_id, YEAR(appt.appointment_date), Month(appt.appointment_date)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

bhaveshp.dba SSC Eights! Points: 834 More actions · Answer 1

bhaveshp.dba (11/8/2011)
Hello All,
Please any can help me how to use dynamic sql. I don't know how to use dynamic sql .
Here I only used 3 conditon to insert record.but
I want to insert 15 more condition based on [ApptTimeStampFromID],[ApptTimeStampToID], [TotalDuration] and where clause
How can i use dynamic sql for different conditon?
Please help me
INSERT INTO t_ApptDuration_Monthly ([Year],[Month],[FirstOfMonth],[ApptTimeStampFromID],[ApptTimeStampToID],
[TotalDuration],[VisitType],[NumberOfAppts] ,[FacilityID],[Drno],[CreatedDateTime] )
SELECT YEAR(appt.appointment_date) as [Year], Month(appt.appointment_date) as [Month],
CONVERT(datetime,convert(char(4),Year(appt.appointment_date)) + '-' + convert(char(2),Month(appt.appointment_date)) + '-01') as FirstOFMonth,
1, 2,Sum(CASE WHEN dbo.f_ConvertToDate(appt.arrival_time) < dbo.f_ConvertToDate(appt.appointment_time) THEN 0 ELSE
DATEDIFF(MI, dbo.f_ConvertToDate(appt.appointment_time),dbo.f_ConvertToDate(appt.arrival_time)) END)
TotalDuration,
pc.ProcedureName as VisitType,COUNT(appt.appointment_key) As NumOfAppts,
f.FacilityID,appt.resource_id as Drno,
GetDate()as CurrentDate
FROM Facilities AS f INNER JOIN
FacilityCodeXref AS facXref ON f.FacilityID = facXref.FacilityID INNER JOIN
sch_app_slot AS appt ON facXref.FacilityCode = appt.faccode
left outer join finhdr h on appt.appointment_key=h.appointment_key
inner join findet d on h.invno=d.invno and h.patkey=d.patkey and h.entryno=d.entryno
inner join ProcedureCodeXref pc on d.proccode=pc.proccode
WHERE pc.ProcedureName<>'Surgery' and
appt.appointment_date BETWEEN @startDate and @endDate
AND appt.appointment_status = 0 and appt.arrival_time is not nullGROUP BY f.FacilityID, pc.ProcedureName, appt.resource_id, YEAR(appt.appointment_date), Month(appt.appointment_date)
Union All
SELECT YEAR(appt.appointment_date) as [Year], Month(appt.appointment_date) as [Month],
CONVERT(datetime,convert(char(4),Year(appt.appointment_date)) + '-' + convert(char(2),Month(appt.appointment_date)) + '-01') as FirstOFMonth,
1,3,
Sum(CASE WHEN dbo.f_ConvertToDate(appt.exam_start_time) < dbo.f_ConvertToDate(appt.appointment_time) THEN 0 ELSE
DATEDIFF(MI, dbo.f_ConvertToDate(appt.appointment_time),dbo.f_ConvertToDate(appt.exam_start_time)) END)as
TotalDuration,pc.ProcedureName as VisitType,COUNT(appt.appointment_key) As NumOfAppts,
f.FacilityID,appt.resource_id as Drno,
GetDate()as CurrentDate
FROM Facilities AS f INNER JOIN
FacilityCodeXref AS facXref ON f.FacilityID = facXref.FacilityID INNER JOIN
sch_app_slot AS appt ON facXref.FacilityCode = appt.faccode
left outer join finhdr h on appt.appointment_key=h.appointment_key
inner join findet d on h.invno=d.invno and h.patkey=d.patkey and h.entryno=d.entryno
inner join ProcedureCodeXref pc on d.proccode=pc.proccode
WHERE pc.ProcedureName<>'Surgery' and
appt.appointment_date BETWEEN @startDate and @endDate
AND appt.appointment_status = 0 and appt.exam_start_time is not nullGROUP BY f.FacilityID, pc.ProcedureName, appt.resource_id, YEAR(appt.appointment_date), Month(appt.appointment_date)
Union All
SELECT YEAR(appt.appointment_date) as [Year], Month(appt.appointment_date) as [Month],
CONVERT(datetime,convert(char(4),Year(appt.appointment_date)) + '-' + convert(char(2),Month(appt.appointment_date)) + '-01') as FirstOFMonth,
2,3,
Sum(CASE WHEN dbo.f_ConvertToDate(appt.exam_end_time) < dbo.f_ConvertToDate(appt.appointment_time) THEN 0 ELSE
DATEDIFF(MI, dbo.f_ConvertToDate(appt.appointment_time),dbo.f_ConvertToDate(appt.exam_end_time)) END) as
TotalDuration,
pc.ProcedureName as VisitType,COUNT(appt.appointment_key) As NumOfAppts,
f.FacilityID,appt.resource_id as Drno,
GetDate()as CurrentDate
FROM Facilities AS f INNER JOIN
FacilityCodeXref AS facXref ON f.FacilityID = facXref.FacilityID INNER JOIN
sch_app_slot AS appt ON facXref.FacilityCode = appt.faccode
left outer join finhdr h on appt.appointment_key=h.appointment_key
inner join findet d on h.invno=d.invno and h.patkey=d.patkey and h.entryno=d.entryno
inner join ProcedureCodeXref pc on d.proccode=pc.proccode
WHERE pc.ProcedureName<>'Surgery' and
appt.appointment_date BETWEEN @startDate and @endDate
AND appt.appointment_status = 0 and appt.exam_end_time is not nullGROUP BY f.FacilityID, pc.ProcedureName, appt.resource_id, YEAR(appt.appointment_date), Month(appt.appointment_date)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

strSELECT -- SELECT columns

strFROM -- From Table(s) with JOIN

strWHERE -- Filters

strGROUP_BY -- if Any

strHAVING -- if Any

strORDER_BY -- If Any

strSQL= strSELECT + strFROM + strWHERE + strGROUP_BY + strHAVING + strORDER_BY

This is typically helpful if 2 or more blocks (say SELECT & FROM) are common and only filter changes.