Click here to Skip to main content
14,930,169 members
Home / Discussions / Database
   

Database

 
QuestionSqlServer (and ADO.NET) mystery... Pin
Super Lloyd27-Apr-21 19:19
MemberSuper Lloyd27-Apr-21 19:19 
AnswerRe: SqlServer (and ADO.NET) mystery... Pin
Richard Deeming27-Apr-21 22:11
mveRichard Deeming27-Apr-21 22:11 
GeneralRe: SqlServer (and ADO.NET) mystery... Pin
Super Lloyd27-Apr-21 22:42
MemberSuper Lloyd27-Apr-21 22:42 
AnswerRe: SqlServer (and ADO.NET) mystery... Pin
Mycroft Holmes28-Apr-21 11:46
professionalMycroft Holmes28-Apr-21 11:46 
QuestionInterval schedule Pin
Member 1462398929-Apr-21 2:56
MemberMember 1462398929-Apr-21 2:56 
AnswerRe: Interval schedule Pin
Victor Nijegorodov27-Apr-21 20:45
MemberVictor Nijegorodov27-Apr-21 20:45 
AnswerRe: Interval schedule Pin
Richard Deeming27-Apr-21 22:08
mveRichard Deeming27-Apr-21 22:08 
GeneralRe: Interval schedule Pin
Member 1462398929-Apr-21 2:38
MemberMember 1462398929-Apr-21 2:38 
This is EXACTLY what I needed. This was far too complex for me to comprehend, so I started to look into why it works and breaking down everything in piece. No comments in anything for the query below, but should be able to figure it out for someone looking at the same thing:

SQL
SELECT 
	DATEDIFF(HOUR, '1:00', '23:00')

SELECT 
	DATEDIFF(HOUR, '1:00', '23:00') / 4

SELECT 
	(1 + DATEDIFF(HOUR, '1:00', '23:00')) / 4

SELECT
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N  
FROM 
	sys.all_columns

SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4) 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N 
FROM 
    sys.all_columns

SELECT 
	DATEADD(HOUR, 5 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 4 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 3 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 2 * 4, '1:00') As ScheduleTime
UNION
SELECT 
	DATEADD(HOUR, 1 * 4, '1:00') As ScheduleTime

SELECT
    ',' + CAST(T.ScheduleTime As char(5))
FROM
    /* Tally table: */
    (
        SELECT TOP ((1 + DateDiff(hour, '1:00', '23:00')) / 4) 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
        FROM 
            sys.all_columns
    ) As N
    /* Generated schedule: */
    CROSS APPLY 
    (
        SELECT DateAdd(hour, N.n * 4, '1:00') As ScheduleTime
    ) As T
FOR XML PATH('')


I had to make an adjustment to the final query though to make sure I was getting a schedule for a 24 hour period instead from the start time to midnight:
SQL
WITH cteSource As
(
	SELECT CAST('13:00' As time) As StartTime, 4 As Hours
	UNION SELECT CAST('01:00' As time) As StartTime, 7 As Hours
)
SELECT
    StartTime,
    Hours
    ,STUFF(T.ScheduleTime, 1, 1, '') As ScheduleTime
FROM
    cteSource As S
    CROSS APPLY
    (
        SELECT
			
            ',' + CAST(T.ScheduleTime As char(5))
        FROM
            /* Tally table: */
            (

                SELECT TOP (24 / S.Hours) 
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As n
                FROM 
                    sys.all_columns
			
            ) As N
            /* Generated schedule: */
            CROSS APPLY 
            (
                
				SELECT DateAdd(hour, N.n * S.Hours, S.StartTime) As ScheduleTime

            ) As T
        FOR XML PATH('')
    ) As T (ScheduleTime)


modified 29-Apr-21 15:03pm.

QuestionRetail inventory management DB Design Pin
vineet123456712-Apr-21 23:42
Membervineet123456712-Apr-21 23:42 
AnswerRe: Retail inventory management DB Design Pin
Victor Nijegorodov13-Apr-21 0:54
MemberVictor Nijegorodov13-Apr-21 0:54 
AnswerRe: Retail inventory management DB Design Pin
RedDk13-Apr-21 12:10
MemberRedDk13-Apr-21 12:10 
QuestionRe: Retail inventory management DB Design Pin
Eddy Vluggen13-Apr-21 13:00
professionalEddy Vluggen13-Apr-21 13:00 
AnswerRe: Retail inventory management DB Design Pin
Mycroft Holmes14-Apr-21 13:32
professionalMycroft Holmes14-Apr-21 13:32 
QuestionConvert date, did I go too far Pin
jkirkerx8-Apr-21 12:50
professionaljkirkerx8-Apr-21 12:50 
AnswerRe: Convert date, did I go too far Pin
Victor Nijegorodov8-Apr-21 20:22
MemberVictor Nijegorodov8-Apr-21 20:22 
AnswerRe: Convert date, did I go too far Pin
Richard Deeming8-Apr-21 22:03
mveRichard Deeming8-Apr-21 22:03 
GeneralRe: Convert date, did I go too far Pin
jkirkerx9-Apr-21 6:14
professionaljkirkerx9-Apr-21 6:14 
GeneralRe: Convert date, did I go too far Pin
Mycroft Holmes9-Apr-21 12:21
professionalMycroft Holmes9-Apr-21 12:21 
GeneralRe: Convert date, did I go too far Pin
jkirkerx9-Apr-21 14:20
professionaljkirkerx9-Apr-21 14:20 
GeneralRe: Convert date, did I go too far Pin
Richard Deeming11-Apr-21 21:10
mveRichard Deeming11-Apr-21 21:10 
AnswerRe: Convert date, did I go too far, Sort of solved Pin
jkirkerx10-Apr-21 14:06
professionaljkirkerx10-Apr-21 14:06 
QuestionExcel Spreadsheet To Database Migration Assistance Pin
Glen McHale6-Apr-21 23:55
MemberGlen McHale6-Apr-21 23:55 
SuggestionRe: Excel Spreadsheet To Database Migration Assistance Pin
CHill607-Apr-21 0:57
mveCHill607-Apr-21 0:57 
AnswerRe: Excel Spreadsheet To Database Migration Assistance Pin
Mycroft Holmes7-Apr-21 12:54
professionalMycroft Holmes7-Apr-21 12:54 
QuestionSQLite query Pin
David Crow23-Mar-21 10:02
MemberDavid Crow23-Mar-21 10:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.