July 25, 2013 at 2:34 am
Hello friends,
One table (table1)
cardno, datetime1, channel_no
another table (table 2)
cardno, datetime1, channel_no
values in channel_no can be either 1 or 2.
I have 1 view for above table (view1)
What o/p I want is
cardno, datetime, min(datetime1) of channel_no 1 , max(datetime1) of channel_no 2
Application is kind of reading first in(1) and last out(2) timings ffrom datetime1
Please help me.
Thanks,
July 25, 2013 at 2:56 am
Can you post up a sample data script please?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 25, 2013 at 7:58 am
Hi and welcome to the forums. It is very unclear what you are trying to here. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2013 at 10:30 pm
Thanks for the reply.
Here is the sql
CREATE TABLE table1(
[CARDNO] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateTime1] [datetime] NULL,
[CHANNEL_NO] [smallint] NULL
) ON [PRIMARY]
CREATE TABLE table2(
[CARDNO] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTime1] [datetime] NOT NULL,
[action] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE VIEW view1 AS
SELECT CARDNO AS cardno, datetime1, channel_no as action FROM table1
UNION ALL
SELECT CARDNO AS cardno, datetime1, action FROM table2
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 10:45:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 12:45:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 13:10:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 16:10:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('11111' ,'2013-02-04 16:30:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222' ,'2013-02-04 11:45:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 13:00:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 13:45:00.000',1)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 16:10:00.000',2)
INSERT INTO table1 ([CARDNO] ,[DateTime1],[channel_no]) VALUES('22222','2013-02-04 16:30:00.000',1)
INSERT INTO table2 ([CARDNO] ,[DateTime1],[action]) VALUES('11111' ,'2013-02-04 08:45:00.000',1)
INSERT INTO table2 ([CARDNO] ,[DateTime1],[action]) VALUES('11111' ,'2013-02-04 18:30:00.000',1)
July 26, 2013 at 3:58 am
Create VIEW V2 as
SELECT
cardno
, MIN(datetime1) AS MINdatetime1
, MAX(datetime1) AS maxdatetime1
,[action]
FROM (
Select * FROM view1)A
GROUP BY cardno, [ACTION]
I think this is what you are after.
you could take it a step more and remove your first view and combine it in this.
July 26, 2013 at 7:25 am
Or you could make this a bit simpler by eliminating the unnecessary subselect.
select cardno
, MIN(datetime1) AS MINdatetime1
, MAX(datetime1) AS maxdatetime1
,[action]
from view1
GROUP BY cardno, [ACTION]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2013 at 7:32 am
Yeah Good call that.
July 28, 2013 at 10:33 pm
Hi,
Thanks for the reply.
However, the select query gives the results as min of dateime1 and max of datetime1. The o/p which I want is like
cardno, date, intime, outtime
1111, 04-02-2013, 08:45, 16:10
2222, 04-02-2013, 11:45, 16:10
i.e. datewise.
Please help.
July 29, 2013 at 2:04 am
select cardno
,CAST(datetime1 AS DATE)
, MIN(cast(datetime1 AS time)) AS MINdatetime1
, MAX(Cast(datetime1 AS time)) AS maxdatetime1
from view1
GROUP BY cardno, CAST(datetime1 AS DATE)
Give that a try.
J
July 29, 2013 at 2:32 am
Hi,
Thanks for all your replies. Here is the query worked as required.
select t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,
(select SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,min(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='1'
)as InTime,
(select SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,max(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='2'
)as OutTime
from view1 t where t.cardno in ('111111','222222') and group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)
July 29, 2013 at 7:20 am
kanchan 58240 (7/29/2013)
Hi,Thanks for all your replies. Here is the query worked as required.
select t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,
(select SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,min(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='1'
)as InTime,
(select SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,max(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='2'
)as OutTime
from view1 t where t.cardno in ('111111','222222') and group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)
Thanks for posting your solution. However this is not correct. It can't possibly work because you have
AND GROUP BY
Perhaps if you post your actual code for your solution we can offer a better alternative than multiple subselects each with lots of string manipulation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2013 at 10:16 pm
Hi Thanks for the reply.
Yes. from the query we need to remove 'and'.
The column datetime1 in both the table is of type datetime.
The values which are present are like '2013-02-04 10:45:00.000', '2013-02-10 18:45:00.000', '2013-04-01 09:20:00.000', etc. From this value, I want to retrieve in the form of hours and minute.
The end result values will be
10:45, 18:45, 09:20, etc.
Could you please help me out to get the result in the above form?
Thanks in advance.
July 30, 2013 at 7:43 am
kanchan 58240 (7/29/2013)
Hi Thanks for the reply.Yes. from the query we need to remove 'and'.
The column datetime1 in both the table is of type datetime.
The values which are present are like '2013-02-04 10:45:00.000', '2013-02-10 18:45:00.000', '2013-04-01 09:20:00.000', etc. From this value, I want to retrieve in the form of hours and minute.
The end result values will be
10:45, 18:45, 09:20, etc.
Could you please help me out to get the result in the above form?
Thanks in advance.
I formatted this a bit so it is more legible. I also removed all of the date formatting. Honestly, your date formatting should be left to the front end instead of doing it in sql.
select t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,
(
select min(st.datetime1)
from view1 st
where t.cardno = st.cardno
and dateadd(dd, -datediff(dd, t.datetime1, 1), 1) = dateadd(dd, -datediff(dd, st.datetime1, 1), 1)
and st.action = '1'
)as InTime,
(
select max(st.datetime1)
from view1 st
where t.cardno = st.cardno
and dateadd(dd, -datediff(dd, t.datetime1, 1), 1) = dateadd(dd, -datediff(dd, st.datetime1, 1), 1)
and st.action = '2'
)as OutTime
from view1 t
--where t.cardno in ('111111','222222')
group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2013 at 8:09 am
Use CTEs to eliminate subselects and make your code more readable (the view is also eliminated by the first cte):
WITH cte_tables (zCardNo, zDate, zDatetime1, zChannel_no)
AS ( SELECT cardno, CONVERT(DATE, datetime1), datetime1, channel_no
FROM TABLE1
UNION ALL
SELECT cardno, CONVERT(DATE, datetime1), datetime1, Action
FROM TABLE2
)
, cte_minmax (aCardNo, aDate, aMinDatetime1, aMaxDatetime1)
AS (
SELECT zCardNo, zDate, MIN(zDatetime1), NULL
FROM cte_tables
WHERE zChannel_no = 1
GROUP BY zCardNo, zDate
UNION ALL
SELECT zCardNo, zDate, NULL, MAX(zDatetime1)
FROM cte_tables
WHERE zChannel_no = 2
GROUP BY zCardNo, zDate
)
SELECT aCardNo AS cardno
, REPLACE(CONVERT(VarChar(50), aDate, 103),'/','-') AS [date]
, LEFT(CONVERT(varchar,MAX(aMinDatetime1),108),5) AS intime
, LEFT(CONVERT(varchar,MAX(aMaxDatetime1),108),5) AS outtime
FROM cte_minmax
GROUP BY aCardNo, aDate
August 6, 2013 at 12:02 am
Hi
Thanks a lot R. Brush.
You are simply great. The query worked as required.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply