SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Select Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

manosgovind
Starting Member

India
12 Posts

Posted - 01/04/2013 :  00:42:45  Show Profile  Reply with Quote
Hi there,

I have three Select Queries each of which returns two rows. I associate this in a single Query as follows...

SELECT t1.Today, t2.Yest,t3.Earlier FROM
(SELECT Sum(EstimateMins) as Today from ProductionTbl
WHERE CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10),GETDATE(), 103)
union
Select ISNULL(Sum(EstimateMins),0) as Today_Comp from ProductionTbl EP
LEFT JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID
WHERE (CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10),GETDATE(), 103))
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed') )as t1 ,

(SELECT Sum(EstimateMins) as Yest from ProductionTbl
where CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103)
union
Select ISNULL( Sum(EstimateMins) ,0)as Yest_Comp from ProductionTbl EP
inner JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID
WHERE ( CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103))
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed')) as t2,

(SELECT Sum(EstimateMins) as Earlier from ProductionTbl
where CONVERT(varchar(10),requiredBy,103) < CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103)
union
Select ISNULL( Sum(EstimateMins) ,0)as Yest_Comp from ProductionTbl EP
inner JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID
WHERE CONVERT(varchar(10),requiredBy,103) < CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103)
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed')
)as t3
group by t1.Today, t2.Yest,t3.Earlier
order by t1.Today desc

When running this It returns 6 rows(4 rows return null values) . Iwant to get only 2 rows()

PLease help me to solve this.. Other way of wrting query for this output is also welcomed..


bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/04/2013 :  02:10:49  Show Profile  Reply with Quote
Can you post the result of above query and also expected output?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/04/2013 :  02:33:30  Show Profile  Reply with Quote
there's lot of redundancy in posted code

you need only this


SELECT Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Today, 
Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) AND requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)  THEN EstimateMins ELSE 0 END) as Yest,
Sum(CASE WHEN requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Earlier
from ProductionTbl 
UNION ALL
SELECT Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Today_Comp, 
Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) AND requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)  THEN EstimateMins ELSE 0 END) as Yest_comp,
Sum(CASE WHEN requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Earlier_Comp
from ProductionTbl 
INNER JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID 
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed')
WHERE requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)


If you want to identify rows as Comp and total values then return one more category column too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/04/2013 :  02:34:24  Show Profile  Reply with Quote
also see this to understand the date logic used

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manosgovind
Starting Member

India
12 Posts

Posted - 01/04/2013 :  22:46:18  Show Profile  Reply with Quote
Thanks for ur reply visakh..

The Field "Requiredby" is a varchar field and I get an Exception as "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I used Cast funtion to convert it into datetime, but still the error occurs..


Edited by - manosgovind on 01/04/2013 22:47:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/05/2013 :  01:03:50  Show Profile  Reply with Quote
quote:
Originally posted by manosgovind

Thanks for ur reply visakh..

The Field "Requiredby" is a varchar field and I get an Exception as "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I used Cast funtion to convert it into datetime, but still the error occurs..




the reason is simple. you don't have date values in unambiguos format. That's why its important to use proper datatype for all columns.
The best option is to make field datetime or date if possible.

Otherwise read this

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

and make sure all the values in field is in unambiguos format or consistent format before you apply casting over it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manosgovind
Starting Member

India
12 Posts

Posted - 01/05/2013 :  01:34:10  Show Profile  Reply with Quote
hi visakh..

It Works. Thanks for the information. The blog was very helpful to me.

Now i want to get the records in which the required by date is with in a time limit of the particular day.
For Ex. Get all the records of requireddate is with in 2012-01-04 02.00PM.

Thanks in Advance


Go to Top of Page

manosgovind
Starting Member

India
12 Posts

Posted - 01/05/2013 :  01:37:01  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Can you post the result of above query and also expected output?

--
Chandu



Thanks Chandu for the Interest. It worked
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/05/2013 :  02:33:44  Show Profile  Reply with Quote
quote:
Originally posted by manosgovind

hi visakh..

It Works. Thanks for the information. The blog was very helpful to me.

Now i want to get the records in which the required by date is with in a time limit of the particular day.
For Ex. Get all the records of requireddate is with in 2012-01-04 02.00PM.

Thanks in Advance





are you looking at only records that happened in that single day upto 2 PM or do you want all records that happened from earlier days as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manosgovind
Starting Member

India
12 Posts

Posted - 01/05/2013 :  03:20:06  Show Profile  Reply with Quote
Actually the Requirement is , by the end of every day , A report should be genereated which shows the total works hours and Completed work hours for that particular day. There are 2 Shifts/ day 1st one ends by 2pm and 2nd one ends by 10 pm.
So we want to show the completed hours from morning 6 Am to 2pm and 2pm to 10 pm sepaerately. This for that particular day

Hope u r clear with this description

Thanks in Advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/05/2013 :  04:38:20  Show Profile  Reply with Quote
ok then you can use something like

SELECT SUM(CASE WHEN Datefield <= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN TotalHours ELSE 0 END) AS TotalHours1stShift,
SUM(CASE WHEN Datefield > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN TotalHours ELSE 0 END) AS TotalHours2ndShift,
SUM(CASE WHEN Datefield <= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN CompletedHours ELSE 0 END) AS CompletedHours1stShift,
SUM(CASE WHEN Datefield > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN CompletedHours ELSE 0 END) AS CompletedHours2ndShift
from Table
WHERE DateField >= DATEADD(dd,DATEDIF(dd,0,GETDATE()),0)
AND DateField < DATEADD(dd,DATEDIF(dd,0,GETDATE()),1)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manosgovind
Starting Member

India
12 Posts

Posted - 01/07/2013 :  04:43:52  Show Profile  Reply with Quote
ok Visakh.. but the time in the date field is in 12 hr format.

The data may look like this 2012-12-01 03:00:00 PM.

could this be a problem?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2013 :  09:39:59  Show Profile  Reply with Quote
then that means datatype is not datetime. a field of type datetime will not store time part in 12 hr format.
If you've values in 12 hr format you need to first convert them to datetime datatype using CONVERT() function




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manosgovind
Starting Member

India
12 Posts

Posted - 01/08/2013 :  04:16:55  Show Profile  Reply with Quote
ok Visakh.

It works... Once again Thanks for the replies.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/08/2013 :  04:20:56  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000