August 16, 2012 at 7:08 am
Hello
I want to take data extract into .csv format. I already try from select my result set and save as .csv file.
but people complain about that is not in real csv format.
so please suggest me any other way for csv data file dump.
August 16, 2012 at 7:22 am
Have you tried using the Import/Export wizard?
August 16, 2012 at 7:24 am
Lynn Pettis (8/16/2012)
Have you tried using the Import/Export wizard?
No, because i am exporting my results from query
August 16, 2012 at 7:27 am
You can do this. On the screen titled "Specify Table Copy or Query", choose "Write a query to specify the data to transfer". In the next screen, paste your query.
August 16, 2012 at 7:29 am
You can use the wizard to run a query to export to a flat file.
You can also run the query to file, but to save it as CSV, you would have to put in a ',' in between each column other wise it will export it as one cell, not multiple cells.
August 16, 2012 at 8:06 am
anthony.green (8/16/2012)
You can use the wizard to run a query to export to a flat file.You can also run the query to file, but to save it as CSV, you would have to put in a ',' in between each column other wise it will export it as one cell, not multiple cells.
I am using temp table as #temp1 in my query
but export wizard don't allow that
so how to handle that?
August 16, 2012 at 8:10 am
select column1,',',column2,',',column3 from #temp
then export query results to file using ssms
August 16, 2012 at 8:14 am
Here is the problem we face, we can't see what you see. You are using a temporary table, great. Are you exporting directly from the table using a query? Based on what little you have provided, probably. Well, how is the data getting INTO the temporary table? Are you running a query to populate this table or is the data just magically appearing in the table for you? If you are using a query, use that query in the Import/Export wizard to create your csv file.
August 16, 2012 at 8:22 am
Lynn Pettis (8/16/2012)
Here is the problem we face, we can't see what you see. You are using a temporary table, great. Are you exporting directly from the table using a query? Based on what little you have provided, probably. Well, how is the data getting INTO the temporary table? Are you running a query to populate this table or is the data just magically appearing in the table for you? If you are using a query, use that query in the Import/Export wizard to create your csv file.
in my task, i need to load into temp table by year wise and then join to actual table. so i am using temp table
and thats why i having some trouble
August 16, 2012 at 8:23 am
please provide your query
August 16, 2012 at 8:29 am
anthony.green (8/16/2012)
please provide your query
declare @CurrentSchoolYear int,
@dtBeginDateIA3 datetime,
@dtEndDateIA3 datetime,
@dtBeginDateIA2 datetime,
@dtEndDateIA2 datetime,
@dtBeginDateIA1 datetime,
@dtEndDateIA1 datetime,
@dtBeginDateIA datetime,
@dtEndDateIA datetime
select @CurrentSchoolYear = iCurrentSchoolYearCode from tblSysVariables
SELECT @dtBeginDateIA3 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear
SELECT @dtEndDateIA3 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear
SELECT @dtBeginDateIA2 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-1
SELECT @dtEndDateIA2 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-1
SELECT @dtBeginDateIA1 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-2
SELECT @dtEndDateIA1 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-2
SELECT @dtBeginDateIA = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-3
SELECT @dtEndDateIA = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-3
CREATE TABLE #TempTable1 (cStudentID char(9), iSchoolCode int, cGradeCode varchar(04),iSuspensionTypeId int,iSuspNumOfDays int, iSchoolYearCode int )
insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA.cStudentID,IA.iSchoolCode,IA.cGradeCode,IA.iSuspensionTypeId,IA.iSuspNumOfDays,@CurrentSchoolYear-3 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA AND @dtEndDateIA
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA
insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA1.cStudentID,IA1.iSchoolCode,IA1.cGradeCode,IA1.iSuspensionTypeId,IA1.iSuspNumOfDays,@CurrentSchoolYear-2 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA1 AND @dtEndDateIA1
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA1
insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA2.cStudentID,IA2.iSchoolCode,IA2.cGradeCode,IA2.iSuspensionTypeId,IA2.iSuspNumOfDays,@CurrentSchoolYear-1 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA2 AND @dtEndDateIA2
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA2
insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA3.cStudentID,IA3.iSchoolCode,IA3.cGradeCode,IA3.iSuspensionTypeId,IA3.iSuspNumOfDays,@CurrentSchoolYear from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA3 AND @dtEndDateIA3
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA3
; WITH Students AS
(
SELECT DISTINCT
cStudentId
FROM tblStudentSchoolHistory
WHERE iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode IN (SELECT iSchoolCode FROM tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
)
SELECT DISTINCT
ISNULL(S.cStudentId,'') AS Student_PPS_ID
,CAST(a.iSchoolYearCode AS VARCHAR) + '/' + CAST(a.iSchoolYearCode + 1 AS VARCHAR) AS School_Year
,CASE WHEN IA.iSchoolCode IS NOT NULL THEN CAST(IA.iSchoolCode AS VARCHAR) ELSE '' END AS School_Code
,a.cGradeCode as Grade_level
,a.iSuspensionTypeId AS Suspension_Type_Code
,a.iSuspNumOfDays AS Days_Suspended
FROM Students S
JOIN tblStudentSchoolHistory IA
on s.cStudentId = IA.cStudentId
LEFT JOIN #TempTable1 a
on s.cStudentId = a.cStudentId
AND IA.iSchoolYearCode = a.iSchoolYearCode
--AND IA.cGradeCode = a.cGradeCode
AND IA.iSchoolCode = a.iSchoolCode
WHERE
a.cStudentID IS NOT NULL AND
IA.iSchoolCode IN (SELECT iSchoolCode FROM tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
AND IA.iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2,@CurrentSchoolYear-3)
ORDER BY Student_PPS_ID,School_Year
August 16, 2012 at 8:34 am
Make your inserts into your tempoary table a sub query
something like this
declare @CurrentSchoolYear int,
@dtBeginDateIA3 datetime,
@dtEndDateIA3 datetime,
@dtBeginDateIA2 datetime,
@dtEndDateIA2 datetime,
@dtBeginDateIA1 datetime,
@dtEndDateIA1 datetime,
@dtBeginDateIA datetime,
@dtEndDateIA datetime
select @CurrentSchoolYear = iCurrentSchoolYearCode from tblSysVariables
SELECT @dtBeginDateIA3 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear
SELECT @dtEndDateIA3 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear
SELECT @dtBeginDateIA2 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-1
SELECT @dtEndDateIA2 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-1
SELECT @dtBeginDateIA1 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-2
SELECT @dtEndDateIA1 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-2
SELECT @dtBeginDateIA = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-3
SELECT @dtEndDateIA = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-3
--CREATE TABLE #TempTable1 (cStudentID char(9), iSchoolCode int, cGradeCode varchar(04),iSuspensionTypeId int,iSuspNumOfDays int, iSchoolYearCode int )
; WITH Students AS
(
SELECT DISTINCT
cStudentId
FROM tblStudentSchoolHistory
WHERE iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode IN (SELECT iSchoolCode FROM tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
)
SELECT DISTINCT
ISNULL(S.cStudentId,'') AS Student_PPS_ID
,CAST(a.iSchoolYearCode AS VARCHAR) + '/' + CAST(a.iSchoolYearCode + 1 AS VARCHAR) AS School_Year
,CASE WHEN IA.iSchoolCode IS NOT NULL THEN CAST(IA.iSchoolCode AS VARCHAR) ELSE '' END AS School_Code
,a.cGradeCode as Grade_level
,a.iSuspensionTypeId AS Suspension_Type_Code
,a.iSuspNumOfDays AS Days_Suspended
FROM Students S
JOIN tblStudentSchoolHistory IA
on s.cStudentId = IA.cStudentId
LEFT JOIN
(
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA.cStudentID,IA.iSchoolCode,IA.cGradeCode,IA.iSuspensionTypeId,IA.iSuspNumOfDays,@CurrentSchoolYear-3 as iSchoolYearCode from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA AND @dtEndDateIA
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA
UNION
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA1.cStudentID,IA1.iSchoolCode,IA1.cGradeCode,IA1.iSuspensionTypeId,IA1.iSuspNumOfDays,@CurrentSchoolYear-2 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA1 AND @dtEndDateIA1
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA1
UNION
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA2.cStudentID,IA2.iSchoolCode,IA2.cGradeCode,IA2.iSuspensionTypeId,IA2.iSuspNumOfDays,@CurrentSchoolYear-1 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA2 AND @dtEndDateIA2
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA2
UNION
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA3.cStudentID,IA3.iSchoolCode,IA3.cGradeCode,IA3.iSuspensionTypeId,IA3.iSuspNumOfDays,@CurrentSchoolYear from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA3 AND @dtEndDateIA3
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA3
) as a
on s.cStudentId = a.cStudentId
AND IA.iSchoolYearCode = a.iSchoolYearCode
--AND IA.cGradeCode = a.cGradeCode
AND IA.iSchoolCode = a.iSchoolCode
WHERE
a.cStudentID IS NOT NULL AND
IA.iSchoolCode IN (SELECT iSchoolCode FROM tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
AND IA.iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2,@CurrentSchoolYear-3)
ORDER BY Student_PPS_ID,School_Year
August 16, 2012 at 8:38 am
anthony.green (8/16/2012)
Make your inserts into your tempoary table a sub querysomething like this
declare @CurrentSchoolYear int,
@dtBeginDateIA3 datetime,
@dtEndDateIA3 datetime,
@dtBeginDateIA2 datetime,
@dtEndDateIA2 datetime,
@dtBeginDateIA1 datetime,
@dtEndDateIA1 datetime,
@dtBeginDateIA datetime,
@dtEndDateIA datetime
select @CurrentSchoolYear = iCurrentSchoolYearCode from tblSysVariables
SELECT @dtBeginDateIA3 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear
SELECT @dtEndDateIA3 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear
SELECT @dtBeginDateIA2 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-1
SELECT @dtEndDateIA2 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-1
SELECT @dtBeginDateIA1 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-2
SELECT @dtEndDateIA1 = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-2
SELECT @dtBeginDateIA = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 4 AND ischoolyearcode = @CurrentSchoolYear-3
SELECT @dtEndDateIA = cd.dtCalendarDay
FROM tblCalendarDay cd WITH (NOLOCK) INNER JOIN tblCalendarDayCategory cdc WITH (NOLOCK) ON cd.iCalendarDayId = cdc.iCalendarDayId
WHERE cdc.iCalendarDayCategoryId = 5 AND ischoolyearcode = @CurrentSchoolYear-3
--CREATE TABLE #TempTable1 (cStudentID char(9), iSchoolCode int, cGradeCode varchar(04),iSuspensionTypeId int,iSuspNumOfDays int, iSchoolYearCode int )
; WITH Students AS
(
SELECT DISTINCT
cStudentId
FROM tblStudentSchoolHistory
WHERE iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode IN (SELECT iSchoolCode FROM tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
)
SELECT DISTINCT
ISNULL(S.cStudentId,'') AS Student_PPS_ID
,CAST(a.iSchoolYearCode AS VARCHAR) + '/' + CAST(a.iSchoolYearCode + 1 AS VARCHAR) AS School_Year
,CASE WHEN IA.iSchoolCode IS NOT NULL THEN CAST(IA.iSchoolCode AS VARCHAR) ELSE '' END AS School_Code
,a.cGradeCode as Grade_level
,a.iSuspensionTypeId AS Suspension_Type_Code
,a.iSuspNumOfDays AS Days_Suspended
FROM Students S
JOIN tblStudentSchoolHistory IA
on s.cStudentId = IA.cStudentId
LEFT JOIN
(
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA.cStudentID,IA.iSchoolCode,IA.cGradeCode,IA.iSuspensionTypeId,IA.iSuspNumOfDays,@CurrentSchoolYear-3 as iSchoolYearCode from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA AND @dtEndDateIA
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA
UNION
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA1.cStudentID,IA1.iSchoolCode,IA1.cGradeCode,IA1.iSuspensionTypeId,IA1.iSuspNumOfDays,@CurrentSchoolYear-2 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA1 AND @dtEndDateIA1
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA1
UNION
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA2.cStudentID,IA2.iSchoolCode,IA2.cGradeCode,IA2.iSuspensionTypeId,IA2.iSuspNumOfDays,@CurrentSchoolYear-1 from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA2 AND @dtEndDateIA2
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA2
UNION
--insert into #TempTable1 (cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,iSuspNumOfDays,iSchoolYearCode)
select IA3.cStudentID,IA3.iSchoolCode,IA3.cGradeCode,IA3.iSuspensionTypeId,IA3.iSuspNumOfDays,@CurrentSchoolYear from (
SELECT cStudentID,iSchoolCode,cGradeCode,iSuspensionTypeId,COUNT(cStudentID) as NumIncidents,SUM(iSuspNumOfDays) as iSuspNumOfDays
FROM tblIncidentActor
WHERE iSuspensionTypeId IN (1,2,10,11,12,13)
AND dtSuspBeginDate BETWEEN @dtBeginDateIA3 AND @dtEndDateIA3
GROUP BY cStudentID,iSuspensionTypeId,iSchoolCode,cGradeCode) IA3
) as a
on s.cStudentId = a.cStudentId
AND IA.iSchoolYearCode = a.iSchoolYearCode
--AND IA.cGradeCode = a.cGradeCode
AND IA.iSchoolCode = a.iSchoolCode
WHERE
a.cStudentID IS NOT NULL AND
IA.iSchoolCode IN (SELECT iSchoolCode FROM tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
AND IA.iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2,@CurrentSchoolYear-3)
ORDER BY Student_PPS_ID,School_Year
sweet
Thanks Appreciate
August 16, 2012 at 11:47 am
Please help me too this
How i dump into csv file using Export Wizard
declare @CurrentSchoolYear int
select @CurrentSchoolYear = iCurrentSchoolYearCode from [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblSysVariables
; WITH Students AS
(
SELECT DISTINCT
cStudentId
FROM [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblStudentSchoolHistory
WHERE iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode IN (SELECT iSchoolCode FROM [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
)
SELECT DISTINCT
A.iSchoolCode
,A.vcCourseId
,A.vcSection
,cast(A.vcTermCode AS VARCHAR) AS vcTermCode
,A.iSchoolYearCode
,A.cStudentId
,A.ADD_DATE as ADD_DATE
,A.DROP_DATE DROP_DATE
,cast(A.PrimaryEmployeeID AS VARCHAR) AS PrimaryEmployeeID
,cast(A.SecondaryEmployeeID AS VARCHAR) AS SecondaryEmployeeID
into #x1
FROM (
SELECT DISTINCT
Organization.LocalId as [iSchoolCode]
,Course.Code as [vcCourseId]
,CAST(Section.SectionNumber AS VARCHAR) as [vcSection]
,CAST(Term.Code AS VARCHAR) AS vcTermCode
,Calendar.SchoolYear as [iSchoolYearCode]
, Student.LocalId as [cStudentId]
--,case when PrimaryTeacher.SectionId is not null THEN PrimaryTeacher.LocalId
-- WHEN SecondaryTeacher.SectionId is not null THEN SecondaryTeacher.LocalId
--else '' end as 'Employee ID'
,CASE WHEN SegmentEnrollment.addDate IS NOT NULL THEN CONVERT(CHAR(10), SegmentEnrollment.addDate, 101) ELSE '' END AS [ADD_DATE]
,CASE WHEN SegmentEnrollment.DropDate IS NOT NULL THEN CONVERT(CHAR(10), SegmentEnrollment.DropDate, 101) ELSE '' END AS [DROP_DATE]
, CASE WHEN PrimaryTeacher.SectionId is not null THEN PrimaryTeacher.LocalId
ELSE '' END AS [PrimaryEmployeeID]
, CASE WHEN SecondaryTeacher.SectionId is not null THEN SecondaryTeacher.LocalId
ELSE '' END AS [SecondaryEmployeeID]
FROM Organization
JOIN dbo.School WITH (NOLOCK)ON Organization.OrganizationId = School.OrganizationId
JOIN dbo.SchoolEnrollment WITH (NOLOCK) ON SchoolEnrollment.CalendarId=School.CurrentCalendarId
AND SchoolEnrollment.Status IN ('A','C','P')
AND SchoolEnrollment.IsCurrent = 1
JOIN dbo.Student WITH (NOLOCK)ON SchoolEnrollment.StudentId=Student.StudentId
JOIN dbo.SegmentEnrollment WITH (NOLOCK)ON SegmentEnrollment.CalendarId = School.CurrentCalendarId
AND SegmentEnrollment.StudentId=SchoolEnrollment.StudentId
JOIN dbo.Segment WITH (NOLOCK)ON Segment.SegmentId=SegmentEnrollment.SegmentId
JOIN dbo.Section WITH (NOLOCK)ON Section.SectionId=Segment.SectionId
JOIN dbo.Course WITH (NOLOCK)ON Section.CourseId=Course.CourseId
JOIN dbo.Calendar WITH (NOLOCK)ON School.CurrentCalendarId = Calendar.CalendarId
JOIN dbo.meetingtime with (NOLOCK) ON meetingTime.MeetingPatternId=Segment.MeetingPatternId
JOIN dbo.Term with (NOLOCK) ON Term.termid=meetingTime.TermId
LEFT JOIN (select TS.SectionId,T.TeacherId,T.LocalId ,T.FirstName
,T.LastName from dbo.TeacherSection TS left join info.Teacher T
on TS.TeacherId = T.TeacherId
where TS.IsPrimaryInstructor = 1) PrimaryTeacher
on PrimaryTeacher.SectionId=Section.SectionId
LEFT JOIN (select TS.SectionId,T.TeacherId,T.LocalId,T.FirstName
,T.LastName from dbo.TeacherSection TS left join info.Teacher T
on TS.TeacherId = T.TeacherId
where TS.IsPrimaryInstructor = 0) SecondaryTeacher
on SecondaryTeacher.SectionId=Section.SectionId
WHERE Calendar.SchoolYear = @CurrentSchoolYear
UNION
SELECT DISTINCT
IA.iSchoolCode,
ISNULL(ScheduleHistory.vcCourseId ,'') AS vcCourseId,
ISNULL(ScheduleHistory.vcSection ,'') AS vcSection,
ST.vcTermCode,
IA.iSchoolYearCode,
s.cStudentId ,
'' AS [ADD_DATE],
'' AS [DROP_DATE],
E.vcEmployeeUniqueID AS PrimaryEmployeeID,
'' AS SecondaryEmployeeID
FROM Students S
JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblStudentSchoolHistory IA
ON s.cStudentId = IA.cStudentId and
IA.iSchoolYearCode in (@CurrentSchoolYear-1,@CurrentSchoolYear-2)
LEFT JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblStudentScheduleHistory SSH
ON SSH.cStudentId = S.cStudentId
AND SSH.iSchoolYearCode = IA.iSchoolYearCode
LEFT JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblScheduleHistory ScheduleHistory
ON ScheduleHistory.iScheduleId = SSH.iScheduleId
AND ScheduleHistory.iSchoolCode = IA.iSchoolCode
AND ScheduleHistory.iSchoolYearCode = IA.iSchoolYearCode
LEFT JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblScheduleTermCode ST
ON ScheduleHistory.iTermCode = ST.iTermCode
LEFT JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblEmployee E
ON ScheduleHistory.iTeacheruserid = E.iuserid
where SSH.cStudentId is not null
) A
where
A.iSchoolCode IN (SELECT iSchoolCode FROM [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblSchoolCategoryHistory
WHERE iSchoolCategoryCode = 10
AND iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
AND iSchoolCode BETWEEN 100 AND 490
and iSchoolCode NOT IN (335,336,446,472,474,475,477))
AND
A.iSchoolYearCode in (@CurrentSchoolYear,@CurrentSchoolYear-1,@CurrentSchoolYear-2)
order by A.cStudentId,A.iSchoolYearCode,A.vcCourseId
--select * from #x1
update #x1 set PrimaryEmployeeID = '000000000'
where iSchoolYearCode = 2009
and vcCourseId = '006502'
and iSchoolCode = 116
and PrimaryEmployeeID = '000009665'
update #x1 set PrimaryEmployeeID = '000000000'
where iSchoolYearCode = 2009
and iSchoolCode = 279
and PrimaryEmployeeID = '000014257'
update #x1 set vcCourseID = case
WHEN vcCourseID = '006679' THEN '006604'
WHEN vcCourseID = '006949' THEN '006902'
WHEN vcCourseID = '006584' THEN '006504'
else vcCourseID end
where iSchoolYearCode in (2009,2010)
and iSchoolCode = 147
and PrimaryEmployeeID = '000002290'
and vcCourseID in ('006679','006949','006584')
update #x1 set vcCourseID = case
WHEN vcCourseID = '006179' THEN '000198'
WHEN vcCourseID = '006956' THEN '006953'
WHEN vcCourseID = '006680' THEN '006605'
WHEN vcCourseID = '006373' THEN '006305'
else vcCourseID end
where iSchoolYearCode in (2009,2010)
and iSchoolCode = 147
and PrimaryEmployeeID = '000004118'
and vcCourseID in ('006179','006956','006680','006373')
update #x1 set vcCourseID = case
WHEN vcCourseID = '006179' THEN '000198'
WHEN vcCourseID = '006956' THEN '006953'
WHEN vcCourseID = '006680' THEN '006605'
WHEN vcCourseID = '006373' THEN '006305'
else vcCourseID end
where iSchoolYearCode in (2009,2010)
and iSchoolCode = 147
and PrimaryEmployeeID = '000005922'
and vcCourseID in ('006179','006956','006680','006373')
update #x1 set vcCourseID = case
WHEN vcCourseID = '006179' THEN '000198'
WHEN vcCourseID = '006585' THEN '0006505'
else vcCourseID end
where iSchoolYearCode in (2009,2010)
and iSchoolCode = 147
and PrimaryEmployeeID = '000007762'
and vcCourseID in ('006179','006585')
--select * from #x1
select iSchoolCode as [School_ID]
, ISNULL(vcCourseID,'') AS Course_ID
, ISNULL(vcSection,'') AS Section_ID
, ISNULL(vcTermCode,'') AS TermCode
,CAST(iSchoolYearCode AS VARCHAR) + '/' + CAST(iSchoolYearCode + 1 AS VARCHAR) AS School_Year
, CASE WHEN E.vcHRJobTitleCode IN ('2302','8503','3309','2612','9801','2301',
'3660','2110','3809','3658','2118','2178',
'3668','3107','8251','2706','2168','2147',
'2148','3656','3310','3223','2613','2303',
'8505','3661','3666','3667','3657')
THEN '' ELSE
B.PrimaryEmployeeID END AS Primary_teacher_ID
, ISNULL(cStudentID,'') AS Student_PPS_ID
,Add_Date
,DROP_DATE
--,PrimaryEmployeeID
, CASE WHEN F.vcHRJobTitleCode IN ('2302','8503','3309','2612','9801','2301',
'3660','2110','3809','3658','2118','2178',
'3668','3107','8251','2706','2168','2147',
'2148','3656','3310','3223','2613','2303',
'8505','3661','3666','3667','3657')
THEN '' ELSE
B.SecondaryEmployeeID END AS SecondaryEmployeeID
--,SecondaryEmployeeID
FROM #x1 B LEFT JOIN (select E.iUserID, E.vcEmployeeUniqueID,EJ.vcHRJobTitleCode from [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblEmployee E JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblEmployeeJobTitle EJ
ON E.iJobTitleID = EJ.iJobTitleID)E ON B.PrimaryEmployeeID = E.vcEmployeeUniqueID
LEFT JOIN (select E.iUserID, E.vcEmployeeUniqueID,EJ.vcHRJobTitleCode from [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblEmployee E JOIN [STARS-DAYB-SRV.PGHBOE.LOC].STARS.dbo.tblEmployeeJobTitle EJ
ON E.iJobTitleID = EJ.iJobTitleID)F ON B.SecondaryEmployeeID = F.vcEmployeeUniqueID
order by Student_PPS_ID,School_Year,Course_ID
August 17, 2012 at 1:38 am
run query in ssms, results to grid, right click top left hand blank thing to select the whole result set including headers, then right click, save results as
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply