SQL help

[Pho3nix]

Hi all,

Having a slow morning and need some help.

I have a list of projects in table A.
I have a list of dates, with the columns down as Year, Month.

Now I would like to have a new row via a join of some sort between the two tables to give me something like :

[table="width: 500"]
[tr]
[td]Project[/td]
[td]Year[/td]
[td]Month[/td]
[/tr]
[tr]
[td]A[/td]
[td]2015[/td]
[td]1[/td]
[/tr]
[tr]
[td]A[/td]
[td]2015[/td]
[td]2[/td]
[/tr]
[/table]

Can anyone assist ?
The tables have nothing in common so having some amnesia about how to do such

 

[Messugga]

Implicit join? Select * from table1, table2

Syntax on this is a tad off but it's close

 

[Elax]

Select * from A Cross Join B perhaps

 

[Baxteen]

if there is nothing linking the tables what is the point?

are the dates related to the projects at all?

is there no other table linking to the projects and to the dates?

what are you trying to achieve here? I like brain puzzles but I need a bit more info here.

 

[prod]

if there is nothing linking the tables what is the point?

are the dates related to the projects at all?

is there no other table linking to the projects and to the dates?

what are you trying to achieve here? I like brain puzzles but I need a bit more info here.

Yea...if they have nothing in common how will SQL know what to join on, makes no sense :wtf:

 

[Pho3nix]

if there is nothing linking the tables what is the point?

are the dates related to the projects at all?

is there no other table linking to the projects and to the dates?

what are you trying to achieve here? I like brain puzzles but I need a bit more info here.

There are some projects which didn't have costs in certain months. I can write a script that gives me a sum of the budgeted Work of the projects per month, per year but the months where the project wasn't active isn't there making reports interesting to write.

So my thought process this morning was get a base list of all the projects for all the months and just update the ones that the second script can find.
Code isn't working though.

Please see below :

CREATE TABLE #TableValues
(
	ProjectId int,
	DateYear varchar(max),
	DateMonth varchar(max), 
	DateFirstDayOfMonth varchar(max),
	BudgettedWork varchar(max)
)
	INSERT INTO #TableValues
	SELECT DISTINCT 
		P.Id,
		CAST(d.Year AS varchar) as yearPulled, 
		CAST(d.Month as varchar) as monthPulled,
		CAST(d.FirstDayOfMonth as varchar), 
		0 
 FROM dbo.dimProject P
 cross join dimdate d
 WHERE d.Year = YEAR(GETDATE())
 ORDER BY 2,3,1;

	WITH CTE(Id,YearDate,MonthDate, DateFirstDayOfMonth,Work)
		AS
		(
		 SELECT 
				T.ProjectId, 
				d.Year, 
				d.Month, 
				d.FirstDayOfMonth, 
				SUM(T.BudgetWork) 
				FROM 
				dbo.FactProjectResourceTime T 
				INNER JOIN dbo.DimDate d on d.Date = T.TimeByDay 
				GROUP BY t.ProjectId,d.Year,d.Month,d.FirstDayOfMonth 
				--ORDER BY T.ProjectId,d.Year,d.Month
		)
	UPDATE #TableValues
	SET BudgettedWork = CTE.Work
        FROM CTE
	WHERE #TableValues.ProjectId = CTE.Id AND #TableValues.DateYear = CTE.YearDate AND CTE.DateFirstDayOfMonth = #TableValues.DateFirstDayOfMonth


DROP TABLE #TableValues

 

[Batista]

Why not join on something like "a" = "a"? I do it all the time but our table structures have good structures so dont need to do it often...

Select a.*,b.*
from tableA a
join tableB b on "a"="a"
where blah blah

 

[Baxteen]

glad you got it working.

that bit of code looks way more complicated that I understood your question.

but using N normal form table design would have made the process much easier.

again I do understand, sometimes we work on DB's that were written by someone else with no ability to plan.

 

[Pho3nix]

glad you got it working.

that bit of code looks way more complicated that I understood your question.

but using N normal form table design would have made the process much easier.

again I do understand, sometimes we work on DB's that were written by someone else with no ability to plan.

Unfortunately I'm not the architect of this particular warehouse.

Code isn't working at the moment though. Can't reference the CTE for some reason.. Having a coffee now which should hopefully jump start my juices.

 

[Pho3nix]

Managed to fix the script.
An oversight.

Updated code found above.

 

[Baxteen]

glad you sorted it out man.

 

[AntiGanda]

Hi all,

Having a slow morning and need some help.

I have a list of projects in table A.
I have a list of dates, with the columns down as Year, Month.

Now I would like to have a new row via a join of some sort between the two tables to give me something like :

[table="width: 500"]
[tr]
[td]Project[/td]
[td]Year[/td]
[td]Month[/td]
[/tr]
[tr]
[td]A[/td]
[td]2015[/td]
[td]1[/td]
[/tr]
[tr]
[td]A[/td]
[td]2015[/td]
[td]2[/td]
[/tr]
[/table]

Can anyone assist ?
The tables have nothing in common so having some amnesia about how to do such

Am I reading correctly or is this one table that needs to be listed using 'order by'??

 

[skimread]

definitively cross join

Select * from TableA Cross Join TableB

 

[Pho3nix]

Am I reading correctly or is this one table that needs to be listed using 'order by'??

Look at the code