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
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: 25853 Visits: 17509
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.

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
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.
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: 25853 Visits: 17509
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.

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)
R. Brush
R. Brush
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 352
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
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 a lot R. Brush.

You are simply great. The query worked as required.
sumith1andonly1
sumith1andonly1
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 346
hi ,
pls check this query


select cardno,min(datetime1),max(datetime1),action
from view1
group by cardno,datetime1,action
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7209 Visits: 6431
Why obfuscate such simple logic with a view?


SELECT CARDNO, [Date]=LEFT(b.strDate, 10)
,InTime=SUBSTRING(b.strDate, 12, 5)
,OutTime=SUBSTRING(c.strDate, 12, 5)
FROM table2 a
CROSS APPLY
(
SELECT CONVERT(VARCHAR(19), a.DateTime1, 20)
) b (strDate)
CROSS APPLY
(
SELECT TOP 1 CONVERT(VARCHAR(19), d.DateTime1, 20)
FROM table1 d
WHERE a.CARDNO = d.CARDNO AND d.CHANNEL_NO = 2
ORDER BY Datetime1 DESC
) c (StrDate)




Note: I didn't notice the second page of posts when I posted this, so I'm not sure this solution is still valid. Give it a try and let me know.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
guruprasad.marathe
guruprasad.marathe
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 3
Below is my query which is giving MIN(InTime) & MAX(OutTime) for one day of one employee. Now I want to show total effective hours of employee by calculating all the In & Out timings difference of employee for one day.

Any help on this will be appreciated.

WITH cte_tables (zcardno, zdate, zs_datetime, zchannel_no) AS
( SELECT cardno, DATEADD(dd, DATEDIFF(dd, 0, s_datetime), 0), s_datetime, [channel no] as action
FROM transactions where s_datetime >= '2013-03-01'
UNION ALL SELECT cardno, DATEADD(dd, DATEDIFF(dd, 0, s_datetime), 0), s_datetime, Action
FROM custom_transactions where s_datetime >= '2013-03-01'),
cte_minmax (acardno, adate, aMinS_datetime, aMaxS_datetime) AS
(SELECT zcardno, zdate, MIN(zs_datetime), NULL FROM cte_tables
WHERE zchannel_no = 2 GROUP BY zcardno, zdate UNION ALL SELECT zcardno, zdate, NULL, MAX(zs_datetime)
FROM cte_tables WHERE zchannel_no = 1 GROUP BY zcardno, zdate)
SELECT m.name, c.acardno AS cardno, REPLACE(CONVERT(VarChar(50), c.adate, 103),'/','-') AS [date],
LEFT(CONVERT(varchar,MAX(c.aMinS_datetime),108),5) AS InTime,
LEFT(CONVERT(varchar,MAX(c.aMaxS_datetime),108),5) AS OutTime FROM cte_minmax c
inner join master m on c.acardno = m.cardno where acardno in ('14109393') and adate between '2013-06-01' and '2013-06-08'
GROUP BY m.name, c.acardno, c.adate order by c.acardno
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