Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
=================== ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
===========================================================You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay => (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth => (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ===========================================================>|||BTW and FWIW, I added this exact example to the article.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)

Component not showing "Show Advanced Editor" in menu

Hi,
I've developed a couple of components now and I know I've seen this problem before but I can't remember how to solve it. My component is built, it has it's own (blank at the moment) UI and it seems to work fine except that there is no right click option to look at the advanced editor. The code is almost identical to another component I wrote that works just fine.
Any ideas anyone?
Thanks
Charlie.The component has more than one input. This apparenty silently disables Advanced Editor support.

complex query!

Hi,
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ' Please help
Query:
----
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status = 'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
---Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status => 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status => 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>

complex query!

Hi,
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ? Please help
Query:
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegr oups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ? Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
>
|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegr oups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ? Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
>

complex query!

Hi,
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ' Please help
Query:
----
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
---Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>