| Author |
 Topic  |
|
|
manosgovind
Starting Member
India
9 Posts |
 Posted - 01/04/2013 : 00:42:45
|
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
1411 Posts |
Posted - 01/04/2013 : 02:10:49
|
Can you post the result of above query and also expected output?
--
Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/04/2013 : 02:33:30
|
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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
|
|
manosgovind
Starting Member
India
9 Posts |
Posted - 01/04/2013 : 22:46:18
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/05/2013 : 01:03:50
|
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/
|
|
|
|
manosgovind
Starting Member
India
9 Posts |
Posted - 01/05/2013 : 01:34:10
|
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
|
|
|
|
manosgovind
Starting Member
India
9 Posts |
Posted - 01/05/2013 : 01:37:01
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/05/2013 : 02:33:44
|
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/
|
|
|
|
manosgovind
Starting Member
India
9 Posts |
Posted - 01/05/2013 : 03:20:06
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/05/2013 : 04:38:20
|
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/
|
|
|
|
manosgovind
Starting Member
India
9 Posts |
Posted - 01/07/2013 : 04:43:52
|
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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/07/2013 : 09:39:59
|
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/
|
|
|
|
manosgovind
Starting Member
India
9 Posts |
Posted - 01/08/2013 : 04:16:55
|
ok Visakh.
It works... Once again Thanks for the replies. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/08/2013 : 04:20:56
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Multiple Select Query
