SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need urgent help with query


Need urgent help with query

Author
Message
kanchan 58240
kanchan 58240
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 10
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,
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 10342
Can you post up a sample data script please?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25845 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kanchan 58240
kanchan 58240
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 10
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)



gasbod
gasbod
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 124
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25845 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
gasbod
gasbod
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 124
Yeah Good call that.
kanchan 58240
kanchan 58240
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 10
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.
gasbod
gasbod
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 124
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
kanchan 58240
kanchan 58240
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 10
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)


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search