February 6, 2015 at 2:48 am
Hi everyone,
I am very new to SQL coming from a SAS background.
I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.
For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.
I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.
I appreciate this is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.
I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).
Any help or feedback would be massively appreciated!
Thanks
Simon
SELECT
CC.cc_name as Academy,
SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre,
MD2.m_reference as Course,
MD2.m_name as Course_Name,
CAST(MD2.m_start AS DATE) as Start_Date,
MD2.m_id as Module_ID,
CS.s_studentreference as Student_ID,
CP.p_forenames as Forenames,
CP.p_surname as Surname,
CAST(CP.p_dob AS DATE) as DOB,
ROUND(ABS(DATEDIFF(month,CP.p_dob,'31-AUG-14') / 12),0,1) AS Age_31AUG14,
case
when ROUND(ABS(DATEDIFF(month,CP.p_dob,'31-AUG-14') / 12),0,1)
BETWEEN '10' AND '18' then '16-18'
when ROUND(ABS(DATEDIFF(month,CP.p_dob,'31-AUG-14') / 12),0,1)
BETWEEN '19' AND '23' then '19-23'
when ROUND(ABS(DATEDIFF(month,CP.p_dob,'31-AUG-14') / 12),0,1)
BETWEEN '24' AND '100' then '24+'
End as Age_Range,
/*
ABS(DATEDIFF(month,CP.p_dob,'31-AUG-14') / 12) AS Duration3,
DATEDIFF(month,CP.p_dob,'31-AUG-14') AS Duration2,
*/
SA.sa_status as Status,
VC.vc_name as Status_Desc,
SA.sa_reference as App_Ref,
CAST(SA.sa_start AS DATE) as Date_Rec,
1 as Count,
SA.sa_id as Student_App_ID,
case when MD.m_mode = '01' then 'FT'
when MD.m_mode = '02' then 'PT'
else 'XX' end as Mode,
SUBSTRING(MD2.m_reference,1,3) as APP,
case when SUBSTRING(MD2.m_reference,1,3) = 'HND' then 'Y'
when SUBSTRING(MD2.m_reference,1,3) = '#HN' then 'Y'
else 'N'
end as HND_FLAG,
case when SUBSTRING(MD2.m_reference,1,3) = 'APP' then 'Y'
else 'N'
end as APP_FLAG,
MA.ma_id as Module_App_ID,
CONVERT (time, SYSDATETIME()) as DateTime,
CONVERT (date, SYSDATETIME()) as ExtractDate
into MISTEST.dbo.TEST2
from
ULIVE.DBO.CAPD_COSTCENTRE CC with (nolock)
inner join ULIVE.DBO.CAPD_COSTCENTRE CC2 with (nolock) on CC.cc_id=CC2.cc_parent
inner join ULIVE.DBO.CAPD_MODULE MD with (nolock) on CC2.cc_id=MD.m_modulecostcentre
inner join ULIVE.DBO.CAPD_MODULE MD2 with (nolock) on MD.m_id=MD2.m_id
inner join ULIVE.DBO.CAPD_MODULEAPPLICATION MA with (nolock) on MD2.m_id=MA.ma_module
inner join ULIVE.DBO.CAPD_STUDENTAPPLICATION SA with (nolock)on MA.ma_studentapplication=SA.sa_id
left outer join ULIVE.DBO.CAPS_VALID_CODES VC with (nolock)on VC.vc_code=SA.sa_status and VC.vc_domain='applicstatus'
inner join ULIVE.DBO.CAPD_PERSON CP with (nolock) on SA.sa_student=CP.p_id
left outer join ULIVE.DBO.CAPD_STUDENT CS with (nolock) on CS.s_id=CP.p_id,
February 6, 2015 at 2:52 am
Firstly, why do you want to duplicate data in the table? Why create a new row when a column changes? Why not just change the column value?
Second, ditch the nolock hints. They're not good practice and most users don't appreciate reports that can be wrong as a result of the hint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2015 at 2:59 am
Hi,
Thanks for responding. I suppose in my mind I'd like to be able to see the changes historically. So if I want to report on how many applications were in the state of x on a particular date I can. If I overwrite the status every time I can't do that.
Otherwise I think what I have done is pretty much there. Minus the nolock - thanks for the advice. This was copied from someone who helped implement some SQL which feeds a third party software and the nolock was in their SQL.
Thanks
February 6, 2015 at 3:29 am
The you would have something like a status history table which records the changes, rather than duplicating the entire row for one column change.
As it currently is, you've got a pretty space-inefficient design, you'd need a timestamp as part of the primary key as the rest of the row gets duplicated and you'd have to write relatively complex T-SQL to get the current version of the row out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2015 at 3:32 am
OK thanks - I'll have to have a read up on how to create the history table.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply