Toughest Query I Have Ever Had

Question

0.00/5 (No votes)

See more:

Hi Guys,

During development i came to a situation where i am not able to solve below query.
I am frustrated since morning please help.

Table:

CSS

Id InvId DTL_Status  dtTime
1   11  SC          4/15/2015 18:48
2   12  SC          4/15/2015 18:48
3   13  SC          4/12/2015 18:48
4   14  SC          4/15/2015 18:48
5   11  NI          4/15/2015 18:48
6   12  NI          4/15/2015 18:48
7   13  NI          4/15/2015 18:48
8   15  NI          4/15/2015 18:48
9   11  SR          4/15/2015 18:48
10  12  SR          4/15/2015 18:48
11  13  SR          4/15/2015 18:48
12  11  SC          4/14/2015 18:48

SC= Scanned Cases
NI=New Invoices
SR= Cases which are rejected

ID=Primary Key
InvId= Invoice Id

1.I want to find cases which are Scanned today
2. cases which went to SC and on same day same cases which went to NI.

Output espected
1 (O/P).

HTML

Id InvId DTL_Status  dtTime
1   11  SC          4/15/2015 18:48
2   12  SC          4/15/2015 18:48
3   13  SC          4/12/2015 18:48

2 (O/P).

CSS

Id InvId DTL_Status  dtTime
5   11  NI          4/15/2015 18:48
6   12  NI          4/15/2015 18:48
7   13  NI          4/15/2015 18:48

Thanks in Advance. :)

Posted 15-Apr-15 3:45am

sunil mali

Add a Solution

Comments

Deepu S Nair 15-Apr-15 9:56am

so what you tried so far?

sunil mali 15-Apr-15 10:06am

select * from Location where Convert(varchar(20),dtTime,106)=Convert(varchar(20),getdate(),106)
and DTL_Status in('SC','NI')

Maciej Los 15-Apr-15 13:45pm

Why do you convert dtTime to varchar data type? It should be datetime data type!

2 solutions

Solution 1

Please, read my comment to the question.

SQL

SELECT <Field_list>
FROM Location
WHERE dtTime >= DATEADD(dd, 0, DATEDIFF(dd,0, dtTime)) AND dtTime <= DATEADD(ss, -1, DATEDIFF(dd,0, DATEADD(dd, 1, dtTime)))
AND DTL_Status IN('SC','NI')

Where is the trick? You have to set time between 00:00:00AM and 11:59:59PM of current date. It is possible by using DATEADD[^]and DATEDIFF[^] functions.

SQL

DECLARE @dtTime DATETIME = GETDATE()
SELECT @dtTime, DATEADD(dd, 0, DATEDIFF(dd,0, @dtTime)), DATEADD(ss, -1, DATEDIFF(dd,0, DATEADD(dd, 1, @dtTime)))

Returns:

2015-04-15 19:55:26.850	2015-04-15 00:00:00.000	2015-04-15 23:59:59.000

More about: How to remove time part of datetime?[^]

Posted 15-Apr-15 8:01am

Maciej Los

Comments

sunil mali 16-Apr-15 2:59am

I read your comment.. My requirement is as mentioned by Jorgen..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2015-04-15T08:49:00

Solution 2

If I have understood you correctly this is what you need:

SQL

SELECT  l2.*
FROM    Location l1
JOIN    location l2
    ON  l1.InvId = l2.InvId 
    AND CONVERT(DATE, l1.dtTime) = CONVERT(DATE, l2.dtTime)
WHERE   CONVERT(DATE, l1.dtTime) = CONVERT(DATE, getdate())
    AND l1.DTL_Status = 'SC'
    AND l2.DTL_Status = 'NI'

Note that the construct with CONVERT(DATE, getdate()) works with SQLServer 2008 and forward, if your server is older than that you need to use the trick that Maciej has shown.

Posted 15-Apr-15 8:49am

Jörgen Andersson

Comments

Maciej Los 15-Apr-15 15:14pm

Good advice, +5! I focused on MS SQL SERVER 2005, because most of users still use it.

Maciej Los 15-Apr-15 15:15pm

I'd suggest some change AND l1.DTL_Status = 'SC' ~~AND~~ OR l2.DTL_Status = 'NI'. Status can't be SC and NI in the same time ;)
Am i right?

Jörgen Andersson 15-Apr-15 15:28pm

It's two different tables, l1 and l2.
If I understood the OP correctly he wanted "cases which went to SC", that's l1. And "and on same day same cases which went to NI", that's l2 and then joined on date and InvID

Maciej Los 15-Apr-15 15:31pm

Second time this day i'm blind...

Jörgen Andersson 15-Apr-15 16:16pm

I've had my share of those days too, so I can relate. :laugh:

sunil mali 16-Apr-15 2:49am

Yes Jorgen you are right
Thanks you understood my problem correctly.

sunil mali 16-Apr-15 3:01am

Thanks Jorgen & Maciej for your precious time..