Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# FINAL STEP needed (UPDATE temp table)

Posted on 2011-03-22
Medium Priority
213 Views
Hello experts,

NEED HELP for the FINAL step to UPDATE the temp table (SEE STEP 3 below)

STEP1: I'm constructing a temp table and: filling DEFAULT values (DONE)
STEP2: Getting the select statement to bring the actual data (DONE)
STEP3: Need help to update the table based on selected values (HELP NEEDED)
STEP3: Select * from temp Table (DONE)

Here is the code
``````DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Sun varchar(10)
DECLARE @Mon varchar(10)
DECLARE @Tue varchar (10)
DECLARE @Wed varchar (10)
DECLARE @Thu varchar (10)
DECLARE @Fri varchar (10)
DECLARE @Sat varchar (10)

SET @StartDate = '03/20/2011'
SET @EndDate = '03/26/2011'
-- THIS WOULD PROVIDE THE DAY TO BE MATCHED
-- @Sun is 20
---@Mon is 21 etc.
SET @Sun = CAST(DAY(@StartDate) AS VARCHAR(2))
SET @Mon = CAST(DAY(@StartDate + 1) AS VARCHAR(2))
SET @Tue = CAST(DAY(@StartDate + 2) AS VARCHAR(2))
SET @Wed = CAST(DAY(@StartDate + 3) AS VARCHAR(2))
SET @Thu = CAST(DAY(@StartDate + 4) AS VARCHAR(2))
SET @Fri = CAST(DAY(@StartDate + 5) AS VARCHAR(2))
SET @Sat = CAST(DAY(@EndDate) AS VARCHAR(2))

SELECT 'Time' As 'Time','Sun - ' + @Sun AS Sun,
'Mon - ' + @Mon AS Mon,
'Tue - ' + @Tue AS Tue,
'Wed - ' + @Wed AS Wed,
'Thu - ' + @Thu AS Thu,
'Fri - ' + @Fri AS Fri,
'Sat - ' + @Sat AS Sat

CREATE TABLE #Time
(TimeId int,
NextTime varchar(5),
Sun int,
Mon int,
Tue int,
Wed int,
Thu int,
Fri int,
Sat int)

INSERT INTO #Time
(1,'8 am', 0,0,0,0,0,0,0),
(2,'9 am', 0,0,0,0,0,0,0,
(3,'10 am', 0,0,0,0,0,0,0),
(4,'11 am', 0,0,0,0,0,0,0),
(5,'12 pm', 0,0,0,0,0,0,0),
(6,'1 pm', 0,0,0,0,0,0,0),
(7,'2 pm', 0,0,0,0,0,0,0),
(8,'3 pm', 0,0,0,0,0,0,0),
(9,'4 pm', 0,0,0,0,0,0,0),
(10,'5 pm', 0,0,0,0,0,0,0),
(11,'6 pm', 0,0,0,0,0,0,0

-- THIS WOULD DELIVER the following:
---------------------------------------------
--  Count  |  NextTimeOnly  |  NextDateOnly  |
--   2     |    11:00AM     |    03/22/2011  |
--   1     |    12:00PM     |    03/22/2011  |

SELECT		COUNT([NEXTSCHEDULE]) AS 'Count',
SUBSTRING(CONVERT(CHAR(19),[NEXTSCHEDULE],100),13,19) AS 'NextTimeOnly',
CONVERT(VARCHAR(10),[NEXTSCHEDULE],101) AS 'NextDateOnly'
FROM		[dbo].[SCHEDULE]
GROUP BY	[NEXTSCHEDULEDTIME]
HAVING		[NEXTSCHEDULE] BETWEEN @StartDate AND @EndDate

--- NEED TO MATCH DAY '22' and '23' vs @Sun or @Mon or @Tue, etc. values (above)
--- FIND THE TIME and UPDATE #Time table by inserting the Count int

SELECT * FROM #Time

DROP TABLE #Time
``````
0
Question by:smano
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2

LVL 1

Author Comment

ID: 35194681
Actually here is the INSERT statement for the above

``````INSERT INTO #Time
SELECT 1,'8 am', 0,0,0,0,0,0,0 UNION
SELECT 2,'9 am', 0,0,0,0,0,0,0 UNION
SELECT 3,'10 am', 0,0,0,0,0,0,0 UNION
SELECT 4,'11 am', 0,0,0,0,0,0,0 UNION
SELECT 5,'12 pm', 0,0,0,0,0,0,0 UNION
SELECT 6,'1 pm', 0,0,0,0,0,0,0 UNION
SELECT 7,'2 pm', 0,0,0,0,0,0,0 UNION
SELECT 8,'3 pm', 0,0,0,0,0,0,0 UNION
SELECT 9,'4 pm', 0,0,0,0,0,0,0 UNION
SELECT 10,'5 pm', 0,0,0,0,0,0,0 UNION
SELECT 11,'6 pm', 0,0,0,0,0,0,0
``````
0

LVL 41

Expert Comment

ID: 35195105
Can you post the result of this query?
``````SELECT		COUNT([NEXTSCHEDULE]) AS 'Count',
SUBSTRING(CONVERT(CHAR(19),[NEXTSCHEDULE],100),13,19) AS 'NextTimeOnly',
CONVERT(VARCHAR(10),[NEXTSCHEDULE],101) AS 'NextDateOnly'
FROM		[dbo].[SCHEDULE]
GROUP BY	[NEXTSCHEDULEDTIME]
HAVING		[NEXTSCHEDULE] BETWEEN @StartDate AND @EndDate
``````
0

LVL 41

Expert Comment

ID: 35195135
Ignore my comment as I could see the result of your query.
0

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35195218
Hope this solves the problem.
``````;WITH CTE
AS (SELECT *
FROM (  SELECT COUNT([NEXTSCHEDULE])                                 AS Cnt,
SUBSTRING(CONVERT(CHAR(19),[NEXTSCHEDULE],100),13,19) AS NextTime,
LEFT(DATENAME(DW,[NEXTSCHEDULE]),3)                   AS DayPartofWeek
FROM [dbo].[SCHEDULE]
WHERE [NEXTSCHEDULE] BETWEEN @StartDate AND @EndDate
GROUP BY SUBSTRING(CONVERT(CHAR(19),[NEXTSCHEDULE],100),13,19),
LEFT(DATENAME(DW,[NEXTSCHEDULE]),3)) t1
PIVOT
(MAX(Cnt)
FOR DayPartOfWeek IN ( [Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat] ) ) p)
UPDATE t1
SET t1.Sun = c1.Sun,
t1.Mon = c1.Mon,
t1.Tue = c1.Tue,
t1.Wed = c1.Wed,
t1.Thu = c1.Thu,
t1.Fri = c1.Fri,
t1.Sat = c1.Sat
FROM #Time AS t1
JOIN CTE c1
ON t1.NextTime = c1.NextTime
``````
``````DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Sun varchar(10)
DECLARE @Mon varchar(10)
DECLARE @Tue varchar (10)
DECLARE @Wed varchar (10)
DECLARE @Thu varchar (10)
DECLARE @Fri varchar (10)
DECLARE @Sat varchar (10)

SET @StartDate = '03/20/2011'
SET @EndDate = '03/26/2011'
-- THIS WOULD PROVIDE THE DAY TO BE MATCHED
-- @Sun is 20
---@Mon is 21 etc.
SET @Sun = CAST(DAY(@StartDate) AS VARCHAR(2))
SET @Mon = CAST(DAY(@StartDate + 1) AS VARCHAR(2))
SET @Tue = CAST(DAY(@StartDate + 2) AS VARCHAR(2))
SET @Wed = CAST(DAY(@StartDate + 3) AS VARCHAR(2))
SET @Thu = CAST(DAY(@StartDate + 4) AS VARCHAR(2))
SET @Fri = CAST(DAY(@StartDate + 5) AS VARCHAR(2))
SET @Sat = CAST(DAY(@EndDate) AS VARCHAR(2))

SELECT 'Time' As 'Time','Sun - ' + @Sun AS Sun,
'Mon - ' + @Mon AS Mon,
'Tue - ' + @Tue AS Tue,
'Wed - ' + @Wed AS Wed,
'Thu - ' + @Thu AS Thu,
'Fri - ' + @Fri AS Fri,
'Sat - ' + @Sat AS Sat

CREATE TABLE #Time
(TimeId int,
NextTime varchar(7),
Sun int,
Mon int,
Tue int,
Wed int,
Thu int,
Fri int,
Sat int)

INSERT INTO #Time
SELECT 1,'8:00AM', 0,0,0,0,0,0,0 UNION
SELECT 2,'9:00AM', 0,0,0,0,0,0,0 UNION
SELECT 3,'10:00AM', 0,0,0,0,0,0,0 UNION
SELECT 4,'11:00AM', 0,0,0,0,0,0,0 UNION
SELECT 5,'12:00PM', 0,0,0,0,0,0,0 UNION
SELECT 6,'1:00PM', 0,0,0,0,0,0,0 UNION
SELECT 7,'2:00PM', 0,0,0,0,0,0,0 UNION
SELECT 8,'3:00PM', 0,0,0,0,0,0,0 UNION
SELECT 9,'4:00PM', 0,0,0,0,0,0,0 UNION
SELECT 10,'5:00PM', 0,0,0,0,0,0,0 UNION
SELECT 11,'6:00PM', 0,0,0,0,0,0,0

-- THIS WOULD DELIVER the following:
---------------------------------------------
--  Count  |  NextTimeOnly  |  NextDateOnly  |
--   2     |    11:00AM     |    03/22/2011  |
--   1     |    12:00PM     |    03/22/2011  |

declare @Result table(Cnt int,NextTimeOnly varchar(10),NextDateOnly varchar(10))
insert @Result values (2,'11:00AM','03/22/2011'),(1,'12:00AM','03/23/2011')

;WITH CTE
AS (SELECT *
FROM (  SELECT COUNT([NEXTSCHEDULE])                                 AS Cnt,
SUBSTRING(CONVERT(CHAR(19),[NEXTSCHEDULE],100),13,19) AS NextTime,
LEFT(DATENAME(DW,[NEXTSCHEDULE]),3)                   AS DayPartofWeek
FROM [dbo].[SCHEDULE]
WHERE [NEXTSCHEDULE] BETWEEN @StartDate AND @EndDate
GROUP BY SUBSTRING(CONVERT(CHAR(19),[NEXTSCHEDULE],100),13,19),
LEFT(DATENAME(DW,[NEXTSCHEDULE]),3)) t1
PIVOT
(MAX(Cnt)
FOR DayPartOfWeek IN ( [Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat] ) ) p)
UPDATE t1
SET t1.Sun = c1.Sun,
t1.Mon = c1.Mon,
t1.Tue = c1.Tue,
t1.Wed = c1.Wed,
t1.Thu = c1.Thu,
t1.Fri = c1.Fri,
t1.Sat = c1.Sat
FROM #Time AS t1
JOIN CTE c1
ON t1.NextTime = c1.NextTime

--- NEED TO MATCH DAY '22' and '23' vs @Sun or @Mon or @Tue, etc. values (above)
--- FIND THE TIME and UPDATE #Time table by inserting the Count int

SELECT * FROM #Time

DROP TABLE #Time
``````
0

LVL 1

Author Comment

ID: 35200391
Interesting, I'll take it thanks! I got rid of the above @ Result table as its no longer needed and modifed the Update to eliminate inserted NULLs like this:
UPDATE t1
SET t1.Sun = ISNULL(c1.Sun,0),
t1.Mon = ISNULL(c1.Mon,0),
t1.Tue = ISNULL(c1.Tue,0),
t1.Wed = ISNULL(c1.Wed,0),
t1.Thu = ISNULL(c1.Thu,0),
t1.Fri = ISNULL(c1.Fri,0),
t1.Sat = ISNULL(c1.Sat,0)
FROM #Time AS t1
JOIN CTE c1
ON t1.NextTimeOnly = c1.NextTimeOnly
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month8 days, left to enroll

#### 610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.