More SQL Questions..

[Raithlin]

Yes a sub-query or nested (not sure about the difference), after the rows from this month are selected, I would like to set the number of rows to be selected to be equal to a value a get from a "count()" and this value might need to be rounded of to a to one significant number or whole number ie.40.5 = 41 etc.

Dunno about a sub-query for this specific instance, but you can put it into a stored proc, and assign the count to a variable, which can then be used in a SELECT TOP @counter * FROM statement.

You can use the CEILING() keyword to round the value up if needed.

 

[bin3]

Not ideal, but in some cases HAVING works very, very well.

SELECT bla, count(*) FROM Table WHERE 'Something' = 'SomeThingElse' GROUP BY WhatEVER
HAVING count(*) > 10

HAVING is a bit like having an additional where clause on your query, but is very handy to do filtering on top of the query. Be aware that the whole dataset first needs to get generated before the having clause will be applied, so you don't want to select a million records ...

 

[Pho3nix]

Will look into the HAVING statement.

SELECT * from Test_MarvalTable 
WHERE 14<Time_Spent<30 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

Offtopic : Whats wrong with the above statements syntax tho?

 

[bin3]

Will look into the HAVING statement.

SELECT * from Test_MarvalTable 
WHERE 14<Time_Spent<30 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

Offtopic : Whats wrong with the above statements syntax tho?

At a quick glance: I think it's the tertiary operator -- This is SQL not Math ...

I would try

SELECT * from Test_MarvalTable
WHERE
Time_Spent > 14 AND
Time_Spent < 30 AND
MONTH(Date_Worked) = MONTH(GETDATE()) AND
YEAR(Date_Worked)=YEAR(GETDATE())

Otherwise you can probably replace the tertiary with BETWEEN, i.e. WHERE Time_Spent BETWEEN 14 AND 30 ...

 

[Pho3nix]

Declare @vSQL varchar(1000), @numrows int
SET @numrows = 0
SELECT * FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent<240 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
SET @numrows = CEILING((COUNT(*)/100)*1)
SET @vSQL = 'SELECT TOP ' + convert(varchar, @numrows) + ' * SELECT * FROM [Test_Database].[dbo].[Test_Table] WHERE Time_Spent<240 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())'
EXECUTE @vSQL

Ok, this is the query I'm now trying to run to get a better understanding of the variables in sql but somewhere init there is an error with the Set @vSQL. can anyone assist. It seems to be running the first Select Statement but not the second. Help Please.

I did get a working query to get the work done earlier working, this is just so I can get more info and experience about SQL.

ERROR :

The name 'SELECT TOP 0 * SELECT * FROM [Test_Database].[dbo].[Test_MarvalTable] WHERE Time_Spent<240 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())' is not a valid identifier.

 

[Pho3nix]

Ok guys, this works in SQL but not in SSIS, is there a way to set this to a variable and run it from there?

SELECT TOP 1 PERCENT * FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent BETWEEN 0 AND 15 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

SELECT TOP 5 PERCENT * FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent BETWEEN 15 AND 30 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

SELECT TOP 10 PERCENT * FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent BETWEEN 29 AND 60 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

SELECT TOP 25 PERCENT * FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent BETWEEN 60 AND 120 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

SELECT TOP 50 PERCENT* FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent BETWEEN 120 AND 239 AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO

SELECT * FROM [Test_Database].[dbo].[Test_Table]
WHERE Time_Spent>239 OR Name = 'A' AND MONTH(Date_Worked)=MONTH(GETDATE()) AND YEAR(Date_Worked)=YEAR(GETDATE())
GO