PHP to SQL
-
Can anyone help me convert the following php code to sql. I need a sql query to do exactly what the below php code is doing:
$monthrange = 24;$month=0;
while ($month <= $monthrange) {
$k = "select '2015-08-01'::date - interval '".$month." month' as mydate";
#echo $k;
$result = pg_query($dbh,$k);
$row = pg_fetch_array($result);
$statdate = $row['mydate'];
echo $statdate . '
';
$q = "select Id from tblreps where Id != 0";
$reps_data = $pdo->prepare($q);
$reps_data->execute();
$reps = $reps_data->fetchAll();
foreach($reps as $rep){
$query = "execute rept.spUsageReport ".$rep['Id'].", '".$statdate."'";
$statement = $pdo->prepare($query);
$statement->execute();
$rows = $statement->fetchAll();
#echo $rep['Id'];
foreach($rows as $row){
$qry = "insert into usage (rep,client_name,subscribed,lapsed,q1,q2,q3,q4,quarter_total,lg30,logged_in_90,system_usage_30,previous_usage_30,daypointchange30,";
$qry .= "system_usage_90,previous_usage_90,daypointchange90,client,statdate,qry,kitusage) values (";
$qry .= $rep['Id'] .",'".$row['Client']."',".$row['Subscribed'].",".$row['Lapsed'].",".$row['Q1'].",".$row['Q2'].",".$row['Q3'].",".$row['Q4'].",".$row['Quarter Total'].",'".$row['logged_in_30day']."','".$row['logged_in_90day']."','".$row['system_usage_30day_percent']."','".$row['previous_usage30day']."','".$row['30daypointchange']."','".$row['system_usage_90day']."','".$row['previous_usage_90day']."','".$row['90daypointchange']."',".$row['ClientNumber'].",'".$statdate."',$1,$2)";
pg_query_params($dbh,$qry,array($query,$row['system_usage_30day']));
}
}
$month++;
}
Table Reps:
Any help/advice will be appreciated.
Thanks
-
I have managed to create a months table and cross joined it with the reps table
DECLARE @tblMonths TABLE(
dtMonthStart date,
intDaysInMonth tinyint
)
DECLARE
@startDate date = '2014-01-01',
@endDate date = CAST(SYSUTCDATETIME() AS date),
@datePoint date,
@daysInMonth tinyint
SET @datePoint = DATEADD(M, -2, @startDate)
WHILE @datePoint < @endDate
BEGIN
IF DATEADD(M, 1, @datePoint) > SYSUTCDATETIME()
BEGIN
SET @daysInMonth = DATEPART(D, CAST(SYSUTCDATETIME() AS date)) - 1
END
ELSE
BEGIN
SET @daysInMonth = DATEPART(D, DATEADD(D, -1, DATEADD(M, 1, @datePoint)))
END
INSERT INTO @tblMonths (dtMonthStart, intDaysInMonth)
VALUES (
@datePoint,
@daysInMonth
)
SET @datePoint = DATEADD(M, 1, @datePoint)
END
;WITH cteAllReps AS (
SELECT id
FROM dbo.tblReps
),
cteRepsCrossMonths AS (
SELECT *
FROM cteAllReps
CROSS JOIN @tblMonths
)
SELECT * FROM cteRepsCrossMonths
Think I need a cursor which will execute each and every id and date returned by the above table into a stored procedure.. Not very experienced with cursors, any help or advice would be great!
Thanks
-
While I wait for your replies, this is as far as I have got till now with my cursor. If you point out anything please do correct me
DECLARE @id INT, @dtMonthStart DATE, @queryString varchar(8000)DECLARE crReps CURSOR FOR
SELECT id, dtMonthStart
FROM #Report
OPEN crReps
FETCH NEXT FROM crReps
INTO @id, @dtMonthStart
WHILE @@FETCH_STATUS = 0
BEGIN
SET @queryString = 'execute rept.spUsageReport @id, @dtMonthStart'
EXECUTE(@queryString)
END
FETCH NEXT FROM crReps
INTO @id, @dtMonthStart
CLOSE crReps;
DEALLOCATE crReps;
-
It would be easier if you tell us what you're doing. You could certainly execute a stored procedure for each row or you could tell us what are you trying to accomplish with rept.spUsageReport and we could help you do it in a single shot instead of going row by row.
Be careful when you run this code as it creates and drops a table which you might have in your system.
This uses a CTE to create a tally table which replaces the loop[/url]
It also uses date routines to calculate the start of the month. You can find more examples in here[/url]
Finally, it uses a method to concatenate results from a query which is explained in here[/url].
CREATE TABLE dbo.tblReps(
id int
);
INSERT INTO tblReps VALUES(1),(2),(3),(4),(5);
DECLARE
@startDate date = '2014-01-01';
-- Get result as shown on your query
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E3(n) AS(
SELECT a.n FROM E a, E b, E c
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E3
)
SELECT id,
DATEADD( MM, DATEDIFF( MM, 0, @startDate) + t.n - 3, CAST( '1900' AS date)) AS dtMonthStart,
DAY( DATEADD( DD, - 1, DATEADD( MM, DATEDIFF( MM, 0, @startDate) + t.n - 2, 0))) AS intDaysInMonth
FROM tblReps r
CROSS
JOIN cteTally t
WHERE DATEADD( MM, t.n - 3, @startDate) <= CAST(SYSUTCDATETIME() AS date)
ORDER BY id;
--Generate the EXECUTE statements and run the stored procedure.
DECLARE @SQL nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E3(n) AS(
SELECT a.n FROM E a, E b, E c
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E3
)
SELECT @SQL = (SELECT 'EXEC rept.spUsageReport ' + CAST( id AS varchar(10)) + ', ' +
QUOTENAME( CONVERT( char(8), DATEADD( MM, DATEDIFF( MM, 0, @startDate) + t.n - 3, 0), 112), '''') + ';' + CHAR(10)
FROM tblReps r
CROSS
JOIN cteTally t
WHERE DATEADD( MM, t.n - 3, @startDate) <= CAST(SYSUTCDATETIME() AS date)
ORDER BY id
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)');
PRINT @SQL;
EXEC sp_executesql @SQL;
GO
DROP TABLE tblReps
-
Hi Luis,
Thanks for your reply!
What I am trying to achieve is:
Send in each rep id and month (starting from given date by user to previous 24 months) into the stored procedure rept.spUsageReport
For example
exec rept.spUsageReport 1, '2014-06-01'
exec rept.spUsageReport 1, '2014-05-01'
exec rept.spUsageReport 1, '2014-04-01'
... till 24 months
exec rept.spUsageReport 2, '2014-06-01'
exec rept.spUsageReport 2, '2014-05-01'
exec rept.spUsageReport 2, '2014-04-01'
... till 24 months
exec rept.spUsageReport 3, '2014-06-01'
exec rept.spUsageReport 3, '2014-05-01'
exec rept.spUsageReport 3, '2014-04-01'
till previous 24 months from the given date '2014-06-01' for all Reps
and the results of all the above execution statements to be stored in 1 table. Is that possible?
Thank you for your help!
-
That's possible with minor changes to the code that I posted. Based on your OP, it could look like this:
CREATE TABLE dbo.tblReps(
id int
);
INSERT INTO tblReps VALUES(1),(2),(3),(4),(5);
DECLARE
@startDate date = '2014-06-01';
--Generate the EXECUTE statements and run the stored procedure.
DECLARE @SQL nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
cteTally(n) AS(
SELECT TOP 24 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E2
)
SELECT @SQL = (SELECT 'INSERT INTO Sometableusage (rep,client_name,subscribed,lapsed,q1,q2,q3,q4,quarter_total,lg30,logged_in_90,system_usage_30,previous_usage_30,daypointchange30,'
+ 'system_usage_90,previous_usage_90,daypointchange90,client,statdate,qry,kitusage)' + CHAR(10)
+ 'EXEC rept.spUsageReport ' + CAST( id AS varchar(10)) + ', ' +
QUOTENAME( CONVERT( char(8), DATEADD( MM, DATEDIFF( MM, 0, @startDate) - t.n + 1, 0), 112), '''') + ';' + CHAR(10)
FROM tblReps r
CROSS
JOIN cteTally t
ORDER BY id
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)');
PRINT SUBSTRING( @SQL, 1, 4000);
PRINT SUBSTRING( @SQL, 4001, 8000);
PRINT SUBSTRING( @SQL, 8001, 8000);
PRINT SUBSTRING( @SQL, 12001, 8000);
PRINT SUBSTRING( @SQL, 16001, 8000); --Add more if needed to debug or comment all if not needed
EXEC sp_executesql @SQL;
GO
DROP TABLE tblReps
However, the code from the stored procedure can be adapted to create a process that will run for all reps and all months at once instead of going one by one. That should be faster depending on the volume of the information.
-
Brilliant, thank you so much for your help. Really don't want to touch the stored procedure because that is another puzzle.. :crazy:
-
Ok, be sure to understand what's going on in here and ask any questions that you might have. You can't blame me if something goes wrong and you're not sure why or how to solve it.
-
Hi,
I do understand most of the query you wrote but some bits i dont get. Could you please explain what you are doing here
WITHE(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
-
When I run the above query I get the following error:
EXEC rept.spUsageReport_v3 8, '20121201';INSERT INTO tblUsage_all (clientNumber,client,subscribed,Lapsed,Q1,Q2,Q3,Q4,[Quater Total],logged_in_30day,logged_in_90day,system_usage_30day,system_usage_30day_percent,previous_usage30day,30daypointchange,syst
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '30'.
Msg 102, Level 15, State 1, Line 3
I think its exceeding the limit of 8000 dynamic sql.. can this be solved by using a cursor instead?
-
Note 30days.. is not valid column name anyway.
8000 limit doesn't apply to sp_executesql if only you are not quering linked server, OPENQUERY may be.
-
Let's go step by step.
The following code generates 10 rows. I don't care about the contents, so it's a simple column with zeros.
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)Then I use a cross join (using old syntax) to transform those 10 rows into 100 (10 x 10 = 100)
SELECT a.n FROM E a, E b
Finally, I take only 24 rows and assign a number so I can use them to add (or substract) months.
SELECT TOP 24 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E2
There's an issue that might happen with sp_executesql. From Books Online (BOL)
The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).
So you might be limited by memory. A cursor can be a good approach as you're still executing the procedure row by row. However, you shouldn't use the default options for that.
DECLARE@startDate date = '2014-06-01';
DECLARE @SQL nvarchar(300);
DECLARE Statements CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
cteTally(n) AS(
SELECT TOP 24 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E2
)
SELECT 'INSERT INTO Sometableusage (rep,client_name,subscribed,lapsed,q1,q2,q3,q4,quarter_total,lg30,logged_in_90,system_usage_30,previous_usage_30,daypointchange30,'
+ 'system_usage_90,previous_usage_90,daypointchange90,client,statdate,qry,kitusage)' + CHAR(10)
+ 'EXEC rept.spUsageReport ' + CAST( id AS varchar(10)) + ', ' +
QUOTENAME( CONVERT( char(8), DATEADD( MM, DATEDIFF( MM, 0, @startDate) - t.n + 1, 0), 112), '''') + ';'
FROM tblReps r
CROSS
JOIN cteTally t
ORDER BY id, n;
OPEN Statements;
FETCH NEXT FROM Statements INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SQL;
--EXEC sp_executesql @SQL;
FETCH NEXT FROM Statements INTO @SQL;
END
CLOSE Statements;
DEALLOCATE Statements;
-
Thanks Luis, that explanation was very helpful!
I tried using a cursor but i get following errors which don't make any sense to me.. Would you be able to have a look at my cursor and see if you can point out something
DECLARE @tblMonths TABLE(
dtMonthStart date,
intDaysInMonth tinyint
)
DECLARE
@startDate date = '2014-01-01',
@endDate date = CAST(SYSUTCDATETIME() AS date),
@datePoint date,
@daysInMonth tinyint
SET @datePoint = DATEADD(M, -2, @startDate)
WHILE @datePoint < @endDate
BEGIN
IF DATEADD(M, 1, @datePoint) > SYSUTCDATETIME()
BEGIN
SET @daysInMonth = DATEPART(D, CAST(SYSUTCDATETIME() AS date)) - 1
END
ELSE
BEGIN
SET @daysInMonth = DATEPART(D, DATEADD(D, -1, DATEADD(M, 1, @datePoint)))
END
INSERT INTO @tblMonths (dtMonthStart, intDaysInMonth)
VALUES (
@datePoint,
@daysInMonth
)
SET @datePoint = DATEADD(M, 1, @datePoint)
END
--SELECT * FROM @tblMonths
;WITH cteAllReps AS (
SELECT id
FROM dbo.tblReps
),
cteRepsCrossMonths AS (
SELECT *
FROM cteAllReps
CROSS JOIN @tblMonths
)
SELECT *
INTO #Report
FROM cteRepsCrossMonths
--SELECT * FROM cteRepsCrossMonths
DECLARE @id INT, @dtMonthStart DATE, @queryString varchar(8000)
DECLARE crReps CURSOR FOR
SELECT id, dtMonthStart
FROM #Report
OPEN crReps
FETCH NEXT FROM crReps
INTO @id, @dtMonthStart
WHILE @@FETCH_STATUS = 0
BEGIN
SET @queryString = 'execute rept.spUsageReport_v3 @id, @dtMonthStart'
EXECUTE(@queryString)
END
FETCH NEXT FROM crReps
INTO @id, @dtMonthStart
CLOSE crReps;
DEALLOCATE crReps;
and the errors i get
(299 row(s) affected)Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@id".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@id".
-
Variable lose scope inside the EXECUTE(), you would need to send them as parameters using sp_executesql or concatenate them in the string as I did.
Generally speaking, using parameters with sp_executesql is better, but in this case it shouldn't make a difference.
-
okay, will give that a shot! Thanks again
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply