removing duplicate date value in union query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

removing duplicate date value in union query

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-05-04 : 08:38:11

hi

i have 2 tables. the first table has field called date, name, workingHour. the second has a field date

the second table with the field date store populated dates from 2010-01-01 to 2012-01-01

the first table basically store value on a person name working for number of hour on a particular date.

This is my query:
SELECT Date, Name, WorkingHours from tableA
where year(Date) = YEAR(getdate()) and Month(Date) = Month(getdate())
union
Select Date, '','','' ,'','' from tableB
where year(Date) = YEAR(getdate()) and Month(Date) = Month(getdate())

When i run this query it gave me duplicate dates such as
01/05/2010, PersonA, 5
01/05/2010, '', ''
02/05/2010 PersonA, 8
02/05/2010,'' ''

But at the same time i also want to output those dates the PersonA did not work on the month of May. Presume PersonA works only 2 days in the month of May which is 01/05/2010 and 02/05/2010

So the result would be
01/05/2010, PersonA, 5
02/05/2010 PersonA, 8
03/05/2010,'', ''
04/05/2010,'', ''
05/05/2010,'', ''
06/05/2010,'', ''
.
.
.
31/05/2010,'', ''

How do i output these value? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-04 : 08:50:21

Here is a start

;WITH cteDates(theDate)
AS (
	SELECT		DATEADD(DAY, v.Number, d.theStart) AS theDate
	FROM		(
				SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS theStart,
					DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) AS theEnd
			) AS d
	INNER JOIN	master..spt_values AS v ON v.Type = 'P'
				AND v.Number <= DATEDIFF(DAY, d.theStart, d.theEnd)
)
SELECT		d.theDate AS [Date],
		a.Name,
		a.WorkingHours
FROM		cteDates AS d
LEFT JOIN	TableA AS a ON a.[Date] = d.theDate

N 56°04'39.26"
E 12°55'05.63"

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-05-04 : 09:01:59

The reason i have tableB populated with dates from 01/01/2010 to 31/12/2050 is because i want to do away with DateAdd, datediff. And also it would be easier if i want a user to choose a particular month of a year. That is why i ended up with
SELECT Date, Name, WorkingHours from tableA
where year(Date) = YEAR(getdate()) and Month(Date) = Month(getdate())
union
Select Date, '','','' ,'','' from tableB
where year(Date) = YEAR(getdate()) and Month(Date) = Month(getdate())

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-04 : 09:07:12

[code]
SELECT B.Date, ISNULL(A.Name, '') AS Name, ISNULL(A.WorkingHours, '') AS WorkingHours
FROM tableB AS B
LEFT OUTER JOIN
tableA AS A
ON A.Date = B.Date
WHERE B.Date >= DATEADD(MONTH, DATEDIFF(MONTH, '20100101', GETDATE()), '20100101')
AND B.Date < DATEADD(MONTH, DATEDIFF(MONTH, '20100101', GETDATE()) + 1, '20100101');
[/code]

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-05-04 : 11:01:18

Thanks it works

How do i create temp tables based on userid? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:15:23

do you mean appending userid to temp table name?

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

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-05-04 : 22:42:11

hi

I was thinking about using temps table in a stored proc, but because of is scope, i ended up using global table ##

I have many users in my company and each users has their own userid and password stored in the DB.

Many of these users are required to create a reports for analysis based on the the month and year they select. So i created a stored proc that has these parameters, userid, month, and year. And in this stored proc i have a global temp table. How do i create each global temp table based on their userid? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:38:25

you need to use dynamic sql. use USER_ID() function to get id for user

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

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-05-05 : 09:50:33

hi

Is there a way to get rid off the global table with this query? It is a stored proc that has a parameter of staffid. Thanks

SELECT dbo.Attendence.Attendence_Date, dbo.Staff.Staff_Name, dbo.Product.Product_Title, dbo.Work.WorkType, dbo.Attendence.AHours,
dbo.Attendence.Remarks into ##tempo
FROM dbo.Staff INNER JOIN
dbo.Attendence ON dbo.Staff.Staff_ID = dbo.Attendence.Staff_ID INNER JOIN
dbo.Product ON dbo.Attendence.Product_ID = dbo.Product.Product_ID INNER JOIN
dbo.Work ON dbo.Attendence.WorkID = dbo.Work.WorkID where Attendence.Staff_ID = @Staff_id

Select StoreDates.StoreDate, isNull(##tempo.Staff_Name,'') as Staff_Name, isNull(##tempo.Product_Title,'')as Product_Title, isNull(##tempo.WorkType,'') as WorkType, isNull(##tempo.AHours,'') as AHours, isNull(##tempo.Remarks,'') as Remarks
from dbo.StoreDates Left Outer Join ##tempo on ##tempo.Attendence_Date = StoreDates.StoreDate
WHERE StoreDates.StoreDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')
AND StoreDates.StoreDate < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '1900010

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-05 : 10:00:04

You can use CTE like this:


WITH tempoCTE AS
(
   SELECT A.Attendence_Date, S.Staff_Name, P.Product_Title, W.WorkType, A.AHours, A.Remarks
     FROM dbo.Staff AS S
          INNER JOIN
          dbo.Attendence AS A
          ON dbo.Staff.Staff_ID = dbo.Attendence.Staff_ID
          INNER JOIN
          dbo.Product AS P
          ON dbo.Attendence.Product_ID = dbo.Product.Product_ID
          INNER JOIN
          dbo.Work AS W
          ON dbo.Attendence.WorkID = dbo.Work.WorkID
    WHERE Attendence.Staff_ID = @Staff_id 
)
SELECT SD.StoreDate, ISNULL(T.Staff_Name, '') AS Staff_Name, ISNULL(T.Product_Title, '') AS Product_Title,
       ISNULL(T.WorkType, '') AS WorkType, ISNULL(T.AHours, '') AS AHours, ISNULL(T.Remarks, '') AS Remarks 
  FROM dbo.StoreDates AS SD
       LEFT OUTER JOIN
       tempoCTE AS T
       ON T.Attendence_Date = SD.StoreDate
 WHERE SD.StoreDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')
   AND SD.StoreDate < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '1900010')

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-05-18 : 08:01:10

hi

How do i pivot this query? Thanks a lot

WITH tempoCTE AS
(
SELECT A.Attendence_Date, S.Staff_Name, P.Product_Title, W.WorkType, A.AHours, A.Remarks
FROM dbo.Staff AS S
INNER JOIN
dbo.Attendence AS A
ON dbo.Staff.Staff_ID = dbo.Attendence.Staff_ID
INNER JOIN
dbo.Product AS P
ON dbo.Attendence.Product_ID = dbo.Product.Product_ID
INNER JOIN
dbo.Work AS W
ON dbo.Attendence.WorkID = dbo.Work.WorkID
)
SELECT SD.StoreDate, ISNULL(T.Staff_Name, '') AS Staff_Name, ISNULL(T.Product_Title, '') AS Product_Title,
ISNULL(T.WorkType, '') AS WorkType, ISNULL(T.AHours, '') AS AHours, ISNULL(T.Remarks, '') AS Remarks
FROM dbo.StoreDates AS SD
LEFT OUTER JOIN
tempoCTE AS T
ON T.Attendence_Date = SD.StoreDate
WHERE SD.StoreDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')
AND SD.StoreDate < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '1900010')

The result should be this:

Staff_Name Product_Title WorkType 01/05/2010 02/05/2010 03/5/2010 04/05/2010...
John Bread Day 12 12 12 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources