Solved

FINAL STEP needed (UPDATE temp table)

Posted on 2011-03-22
5
196 Views
Last Modified: 2013-11-05
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

Open in new window

0
Comment
Question by:smano
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:smano
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

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
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

Open in new window

0
 
LVL 40

Expert Comment

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

Accepted Solution

by:
Sharath earned 500 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

Open in new window

Your whole query.
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

Open in new window

0
 
LVL 1

Author Comment

by:smano
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard 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.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now