问:
Help with a SQL Query
I have a question on a SQL query and im wondering where to start. Thoughts so far include creating a table in memory with a range of dates, and joining on to it to get the sum of hours entered for a particular day.

Just to give an idea of the background of this question here is a little information.

The database is structured like so:

The result im trying to achieve with the query im currently writing needs to look like this:

The Query I have written so far:

```<code>DECLARE @Deleted AS BIT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME

SET @Deleted = 0
SET @WeekStartDate = '2011/05/01 00:00'
SET @WeekEndDate = '2011/05/07 00:00'

SELECT
[JobSheet].[JobSheetDate],
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
'???' AS 'Mon',
'???' AS 'Tue',
'???' AS 'Wed',
'???' AS 'Thu',
'???' AS 'Fri',
'???' AS 'Sat',
'???' AS 'Sun'

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    [JobSheet].[JobSheetDate],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode]
</code>```

And this outputs the result set below:

As you can see the result set is almost there, I just need to total the number of hours worked on that particular day, grouping on ContractID, JobID, ProjectID, CostRateCode so I can get the number of hours for each cost rate on each unique project.

Is this possible with the current table design? or by using a temporary calendar table for the dates between the two passed in to the query?

EDIT:
Updated Query:

```<code>SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @RequestedByID AS BIGINT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME
DECLARE @WaitingForUserID AS BIGINT
DECLARE @WaitingForUserTypeID AS BIGINT
DECLARE @WaitingForTypeUser AS VARCHAR(50)
DECLARE @WaitingForTypeUserType AS VARCHAR(50)

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT
[JobSheet].[JobSheetDate],
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 1
THEN SUM(JobSheetLineHours.Hours )
END
AS MON,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 2
THEN SUM(JobSheetLineHours.Hours )
END
AS TUE,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 3
THEN SUM(JobSheetLineHours.Hours )
END
AS WED,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 4
THEN SUM(JobSheetLineHours.Hours )
END
AS THU,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 5
THEN SUM(JobSheetLineHours.Hours )
END
AS FRI,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 6
THEN SUM(JobSheetLineHours.Hours )
END
AS SAT,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 7
THEN SUM(JobSheetLineHours.Hours )
END
AS SUN

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    [JobSheet].[JobSheetDate],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode],
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
    WHEN 1
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 2
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 3
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 4
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 5
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 6
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 7
    THEN     JobSheetLineHours.Hours
    END
</code>```

Updated Result Set:

What I'm trying to do now is remove the JobSheetDate from the result set so the Total hours for each day are not all on seperate rows eg. The values for the same contract, project etc are put in the same row, but in the Mon, Tues, Wed etc column, rather than on multiple rows with the hours for one day populated, and the rest of the days null (see screenshot)

EDIT 2:
Updated Query:

```<code>SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT
--[JobSheet].[JobSheetDate],
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 1
THEN JobSheetLineHours.Hours
END )
AS MON,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 2
THEN JobSheetLineHours.Hours
END )
AS TUE,
SUM(CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 3
THEN JobSheetLineHours.Hours
END)
AS WED,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 4
THEN JobSheetLineHours.Hours
END)
AS THU,
SUM(CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 5
THEN JobSheetLineHours.Hours
END )
AS FRI,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 6
THEN JobSheetLineHours.Hours
END )
AS SAT,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 7
THEN JobSheetLineHours.Hours
END )
AS SUN

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    --[JobSheet].[JobSheetDate],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode],
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
    WHEN 1
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 2
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 3
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 4
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 5
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 6
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 7
    THEN     JobSheetLineHours.Hours
    END
</code>```

Results:

FINAL Update with finished Query:

```<code>SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Monday
     WHEN 1
     THEN [JobSheetLineHours].[Hours]
     END )
AS MON,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Tuesday
     WHEN 2
     THEN [JobSheetLineHours].[Hours]
     END )
AS TUE,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Wednesday
     WHEN 3
     THEN [JobSheetLineHours].[Hours]
     END )
AS WED,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Thursday
     WHEN 4
     THEN [JobSheetLineHours].[Hours]
     END )
AS THU,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Friday
     WHEN 5
     THEN [JobSheetLineHours].[Hours]
     END )
AS FRI,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Saturday
     WHEN 6
     THEN [JobSheetLineHours].[Hours]
     END )
AS SAT,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Sunday
     WHEN 7
     THEN [JobSheetLineHours].[Hours]
     END )
AS SUN

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode]
</code>```

答:
It is perfectly acceptable (and often optimal) to create a concrete calendar table of days.

The table would be small and light, you could JOIN to it easily and group by any date combo you wanted. You could add a column to denote work-days versus off-days too if you like.

Otherwise it is awkward to get "zero hour" days because you're asking for an aggregation from data which doesn't exist.

Title: Help with a SQL Query

Body:
I have a question on a SQL query and im wondering where to start. Thoughts so far include creating a table in memory with a range of dates, and joining on to it to get the sum of hours entered for a particular day.

Just to give an idea of the background of this question here is a little information.

The database is structured like so:

The result im trying to achieve with the query im currently writing needs to look like this:

The Query I have written so far:

DECLARE @Deleted AS BIT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME

SET @Deleted = 0
SET @WeekStartDate = '2011/05/01 00:00'
SET @WeekEndDate = '2011/05/07 00:00'

SELECT
[JobSheet].[JobSheetDate],
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
'???' AS 'Mon',
'???' AS 'Tue',
'???' AS 'Wed',
'???' AS 'Thu',
'???' AS 'Fri',
'???' AS 'Sat',
'???' AS 'Sun'

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    [JobSheet].[JobSheetDate],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode]

And this outputs the result set below:

As you can see the result set is almost there, I just need to total the number of hours worked on that particular day, grouping on ContractID, JobID, ProjectID, CostRateCode so I can get the number of hours for each cost rate on each unique project.

Is this possible with the current table design? or by using a temporary calendar table for the dates between the two passed in to the query?

EDIT:
Updated Query:

SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @RequestedByID AS BIGINT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME
DECLARE @WaitingForUserID AS BIGINT
DECLARE @WaitingForUserTypeID AS BIGINT
DECLARE @WaitingForTypeUser AS VARCHAR(50)
DECLARE @WaitingForTypeUserType AS VARCHAR(50)

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT
[JobSheet].[JobSheetDate],
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 1
THEN SUM(JobSheetLineHours.Hours )
END
AS MON,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 2
THEN SUM(JobSheetLineHours.Hours )
END
AS TUE,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 3
THEN SUM(JobSheetLineHours.Hours )
END
AS WED,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 4
THEN SUM(JobSheetLineHours.Hours )
END
AS THU,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 5
THEN SUM(JobSheetLineHours.Hours )
END
AS FRI,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 6
THEN SUM(JobSheetLineHours.Hours )
END
AS SAT,
CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 7
THEN SUM(JobSheetLineHours.Hours )
END
AS SUN

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    [JobSheet].[JobSheetDate],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode],
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
    WHEN 1
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 2
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 3
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 4
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 5
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 6
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 7
    THEN     JobSheetLineHours.Hours
    END

Updated Result Set:

What I'm trying to do now is remove the JobSheetDate from the result set so the Total hours for each day are not all on seperate rows eg. The values for the same contract, project etc are put in the same row, but in the Mon, Tues, Wed etc column, rather than on multiple rows with the hours for one day populated, and the rest of the days null (see screenshot)

EDIT 2:
Updated Query:

SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT
--[JobSheet].[JobSheetDate],
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 1
THEN JobSheetLineHours.Hours
END )
AS MON,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 2
THEN JobSheetLineHours.Hours
END )
AS TUE,
SUM(CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 3
THEN JobSheetLineHours.Hours
END)
AS WED,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 4
THEN JobSheetLineHours.Hours
END)
AS THU,
SUM(CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 5
THEN JobSheetLineHours.Hours
END )
AS FRI,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 6
THEN JobSheetLineHours.Hours
END )
AS SAT,
SUM( CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
WHEN 7
THEN JobSheetLineHours.Hours
END )
AS SUN

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    --[JobSheet].[JobSheetDate],
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode],
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate)
    WHEN 1
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 2
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 3
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 4
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 5
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 6
    THEN     JobSheetLineHours.Hours
    END,
    CASE DATEPART(WEEKDAY, JobSheet.JobSheetDate) WHEN 7
    THEN     JobSheetLineHours.Hours
    END

Results:

FINAL Update with finished Query:

SET DATEFIRST 1 -- Set the first day of week to monday
GO

DECLARE @Deleted AS BIT
DECLARE @WeekStartDate AS DATETIME
DECLARE @WeekEndDate AS DATETIME

SET @Deleted = 0
SET @WeekStartDate = '2009/05/01 00:00'
SET @WeekEndDate = '2012/05/07 00:00'

SELECT
[JobSheet].[ContractID],
[JobSheet].[ContractCode],
[JobSheet].[ContractTitle],
[JobSheet].[ProjectID],
[JobSheet].[ProjectCode],
[JobSheet].[ProjectTitle],
[JobSheet].[JobID],
[JobSheet].[JobCode],
[JobSheet].[JobTitle],
[JobSheet].[SageDatabaseID],
[JobSheetLineHours].[CostRateCode],
SUM([JobSheetLineHours].[Hours]) AS TotalCostRateHours,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Monday
     WHEN 1
     THEN [JobSheetLineHours].[Hours]
     END )
AS MON,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Tuesday
     WHEN 2
     THEN [JobSheetLineHours].[Hours]
     END )
AS TUE,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Wednesday
     WHEN 3
     THEN [JobSheetLineHours].[Hours]
     END )
AS WED,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Thursday
     WHEN 4
     THEN [JobSheetLineHours].[Hours]
     END )
AS THU,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Friday
     WHEN 5
     THEN [JobSheetLineHours].[Hours]
     END )
AS FRI,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Saturday
     WHEN 6
     THEN [JobSheetLineHours].[Hours]
     END )
AS SAT,
SUM( CASE DATEPART(WEEKDAY, [JobSheet].[JobSheetDate]) -- Get Total Value for Sunday
     WHEN 7
     THEN [JobSheetLineHours].[Hours]
     END )
AS SUN

FROM [JobSheet]

INNER JOIN [JobSheetLine]
ON  [JobSheetLine].[JobSheetID]
=   [JobSheet].[JobSheetID]

INNER JOIN [JobSheetLineHours]
ON  [JobSheetLineHours].[JobSheetLineID]
=   [JobSheetLine].[JobSheetLineID]

WHERE
    [JobSheet].[Deleted]=@Deleted
AND [JobSheet].[JobSheetDate] >= @WeekStartDate
AND [JobSheet].[JobSheetDate] <= @WeekEndDate
AND [JobSheetLine].[Deleted]=@Deleted
AND [JobSheetLineHours].[Deleted]=@Deleted

GROUP BY
    [JobSheet].[ContractID],
    [JobSheet].[ContractCode],
    [JobSheet].[ContractTitle],
    [JobSheet].[ProjectID],
    [JobSheet].[ProjectCode],
    [JobSheet].[ProjectTitle],
    [JobSheet].[JobID],
    [JobSheet].[JobCode],
    [JobSheet].[JobTitle],
    [JobSheet].[SageDatabaseID],
    [JobSheetLineHours].[CostRateCode]


来源: stackexchange

问:
PyTorch: The number of sizes provided (0) must be greater or equal to the number of dimensions in the tensor (1)
I'm trying to convert a CPU model to GPU using Pytorch, but I'm running into issues. I'm running this on Colab and I'm sure that Pytorch detects a GPU. This is a deep Q network (RL).

I declare my network as: Q = Q_Network(input_size, hidden_size, output_size).to(device)

I ran into an issue when I tried to pass arguments through the network (It expected type cuda but got type cpu) so I add .to(device):

batch = np.array(shuffled_memory[i:i+batch_size])
b_pobs = np.array(batch[:, 0].tolist(), dtype=np.float32).reshape(batch_size, -1)
b_pact = np.array(batch[:, 1].tolist(), dtype=np.int32)
b_reward = np.array(batch[:, 2].tolist(), dtype=np.int32)
b_obs = np.array(batch[:, 3].tolist(), dtype=np.float32).reshape(batch_size, -1)
b_done = np.array(batch[:, 4].tolist(), dtype=np.bool)

q = Q(torch.from_numpy(b_pobs).to(device))
q_ = Q_ast(torch.from_numpy(b_obs).to(device))

maxq = torch.max(q_.data,axis=1)
target = copy.deepcopy(q.data)

for j in range(batch_size):
    print(target[j, b_pact[j]].shape) # torch.Size([])
    target[j, b_pact[j]] = b_reward[j]+gamma*maxq[j]*(not b_done[j]) #I run into issues here

```

Here is the error:

RuntimeError: expand(torch.cuda.FloatTensor{[50]}, size=[]): the number of sizes provided (0) must be greater or equal to the number of dimensions in the tensor (1)

答:
target[j, b_pact[j]] is a single element of the tensor (a scalar, hence size of torch.Size([])). If you want to assign anything to it, the right hand side can only be a scalar. That is not the case, as one of the terms is a tensor with 1 dimension (a vector), namely your maxq[j].

When specifying a dimension dim (axis is treated as a synonym) to torch.max, it will return a named tuple of (values, indices), where values contains the maximum values and indices the location of each of the maximum values (equivalent to argmax).

maxq[j] is not indexing into the maximum values, but rather the tuple of (values, indices). If you only want the values you can use one of the following to get the values out of the tuple (all of them are equivalent, you can use whichever you prefer):

# Destructure/unpack and ignore the indices
maxq, _ = torch.max(q_.data,axis=1)

# Access first element of the tuple
maxq = torch.max(q_.data,axis=1)[0]

# Access `values` of the named tuple
maxq  = torch.max(q_.data,axis=1).values
```

ContentLicense: CC BY-SA 4.0

Body:
I'm trying to convert a CPU model to GPU using Pytorch, but I'm running into issues. I'm running this on Colab and I'm sure that Pytorch detects a GPU. This is a deep Q network (RL).

I declare my network as: Q = Q_Network(input_size, hidden_size, output_size).to(device)

I ran into an issue when I tried to pass arguments through the network (It expected type cuda but got type cpu) so I add .to(device):

batch = np.array(shuffled_memory[i:i+batch_size])
b_pobs = np.array(batch[:, 0].tolist(), dtype=np.float32).reshape(batch_size, -1)
b_pact = np.array(batch[:, 1].tolist(), dtype=np.int32)
b_reward = np.array(batch[:, 2].tolist(), dtype=np.int32)
b_obs = np.array(batch[:, 3].tolist(), dtype=np.float32).reshape(batch_size, -1)
b_done = np.array(batch[:, 4].tolist(), dtype=np.bool)

q = Q(torch.from_numpy(b_pobs).to(device))
q_ = Q_ast(torch.from_numpy(b_obs).to(device))

maxq = torch.max(q_.data,axis=1)
target = copy.deepcopy(q.data)

for j in range(batch_size):
    print(target[j, b_pact[j]].shape) # torch.Size([])
    target[j, b_pact[j]] = b_reward[j]+gamma*maxq[j]*(not b_done[j]) #I run into issues here

Here is the error:

RuntimeError: expand(torch.cuda.FloatTensor{[50]}, size=[]): the number of sizes provided (0) must be greater or equal to the number of dimensions in the tensor (1)


问:
I leave a question regarding DRF multi-image processing
```<code>class PostImageSerializer(serializers.ModelSerializer):
    class Meta:
        model = PostImage
        fields = "image"

class PostSerializer(serializers.ModelSerializer):
    images = PostImageSerializer(many=True, read_only=True)
    class Meta:
        model = Post
        fields = (
            "id",
            "author",
            "content",
            "images",
        )
</code>```

I'd like to get the results the following response from the above communication codes.

(위 코드로 GET 통신에서 아래와 같은 응답 결과를 얻고 싶은데요.)

```<code>{
  "id": 53,
  "author": 1,
  "post": "awesome",
  "images": {
     "image": "abc.png",
     "image": "def.jpg",
  }
}
</code>```

The images field does not apply and appears like below.

(images 필드가 적용되지 않고 아래와 같이 나오는 상황입니다.)

```<code>{
  "id": 53,
  "author": 1,
  "post": "awesome",
}
</code>```

Which part should I fix? I also attach the code for the model.

(어떤 부분을 고쳐야할까요? 모델 단 코드도 같이 첨부합니다.)

```<code>class Post(models.Model):
    author = models.ForeignKey("User", on_delete=models.CASCADE)
    post = models.CharField(max_length=100)

class PostImage(models.Model):
    post = ForeignKey("Post", on_delete=models.CASCADE)
    image = models.ImageField(upload_to="image", blank=True)
</code>```

答:
Edit models.py

(models.py를 수정하시면 됩니다.)

```<code>class Post(models.Model):
    author = models.ForeignKey("User", on_delete=models.CASCADE)
    post = models.CharField(max_length=100)

class PostImage(models.Model):
    post = ForeignKey("Post", on_delete=models.CASCADE, related_name="images")
    image = models.ImageField(upload_to="image", blank=True)
</code>```

Body:
class PostImageSerializer(serializers.ModelSerializer):
    class Meta:
        model = PostImage
        fields = "image"

class PostSerializer(serializers.ModelSerializer):
    images = PostImageSerializer(many=True, read_only=True)
    class Meta:
        model = Post
        fields = (
            "id",
            "author",
            "content",
            "images",
        )

I'd like to get the results the following response from the above communication codes.

(위 코드로 GET 통신에서 아래와 같은 응답 결과를 얻고 싶은데요.)

{
  "id": 53,
  "author": 1,
  "post": "awesome",
  "images": {
     "image": "abc.png",
     "image": "def.jpg",
  }
}

The images field does not apply and appears like below.

(images 필드가 적용되지 않고 아래와 같이 나오는 상황입니다.)

{
  "id": 53,
  "author": 1,
  "post": "awesome",
}

Which part should I fix? I also attach the code for the model.

(어떤 부분을 고쳐야할까요? 모델 단 코드도 같이 첨부합니다.)

class Post(models.Model):
    author = models.ForeignKey("User", on_delete=models.CASCADE)
    post = models.CharField(max_length=100)

class PostImage(models.Model):
    post = ForeignKey("Post", on_delete=models.CASCADE)
    image = models.ImageField(upload_to="image", blank=True)

Body:
Edit models.py

(models.py를 수정하시면 됩니다.)

class Post(models.Model):
    author = models.ForeignKey("User", on_delete=models.CASCADE)
    post = models.CharField(max_length=100)

class PostImage(models.Model):
    post = ForeignKey("Post", on_delete=models.CASCADE, related_name="images")
    image = models.ImageField(upload_to="image", blank=True)


问:
How to parse Firebase Dynamic Link parameter values and put it in a variable?
I'm building an app that can receive Firebase's Dynamic Links and redirects it into a certain UIViewController. I've followed the steps on this video, but there's something that I still don't understand. So after I followed the steps above, I print my link on the debug area to something like this:

Incoming link parameter is https://play.google.com/store/apps/details?id=com.xxx.xxx&referrer=utm_source%3DXXX%26utm_medium%3Dxxx%26utm_campaign%3DXXX

Parameter id has a value of com.xxx.xxx

Parameter referrer has a value of utm_source=XXX&utm_medium=xxx&utm_campaign=XXX

This works perfectly, but what I want to do is I want to get the values of utm_source, utm_medium and utm_campaign and store it into a variable. I'm still uncertain on how do I manage to achieve this. Here is my code:

```<code>func handleIncomingDynamicLink(_ dynamicLink: DynamicLink) {
        guard let url = dynamicLink.url else {
            print("Dynamic Link has no URL")
            return
        }

        print("Incoming link parameter is \(url.absoluteString)")
        guard let components = URLComponents(url: url, resolvingAgainstBaseURL: false), let queryItems = components.queryItems else { return }
        for queryItem in queryItems {
            print("Parameter \(queryItem.name) has a value of \(queryItem.value ?? "")")
        }
    }
</code>```

So how do I manage to do this? Feel free to ask if you need any more information. Thank you.

答:
I don't know if this is the best answer or the right code design but fortunately I managed to solve by changing handleIncomingDynamicLink function to something like this:

```<code>func handleIncomingDynamicLink(_ dynamicLink: DynamicLink) {
        guard let url = dynamicLink.url else {
            print("Dynamic Link has no URL")
            return
        }

        print("Incoming link parameter is \(url.absoluteString)")
        guard let components = URLComponents(url: url, resolvingAgainstBaseURL: false), let queryItems = components.queryItems else { return }
        for queryItem in queryItems {
            if queryItem.name == "referrer" {
                print("Referrer found")

                let referrerString = "https://xxx.page.link/details?\(queryItem.value ?? "")"
                let referrerUrl = URL(string: referrerString)

                guard let queryItemComponents = URLComponents(url: referrerUrl!, resolvingAgainstBaseURL: false), let itemComponents = queryItemComponents.queryItems else {
                    print("Referrer string couldn't be casted as a URL")
                    return
                }

                for item in itemComponents {
                    print("Parameter \(item.name) has a value of \(item.value ?? "")")
                }

            }
        }
    }
</code>```

So basically what I did was I take the referrer value of the main URL and make it a "custom URL" so that I could parse the values by using the URLComponents function. The output is exactly what I expected to be.

Incoming link parameter is https://play.google.com/store/apps/details?id=com.xxx.xxx&referrer=utm_source%3Dxxx%26utm_medium%3Dxxx%26utm_campaign%3Dxxx

Referrer found

Parameter utm_source has a value of xxx

Parameter utm_medium has a value of xxx

Parameter utm_campaign has a value of xxx

Body:
I'm building an app that can receive Firebase's Dynamic Links and redirects it into a certain UIViewController. I've followed the steps on this video, but there's something that I still don't understand. So after I followed the steps above, I print my link on the debug area to something like this:

Incoming link parameter is https://play.google.com/store/apps/details?id=com.xxx.xxx&referrer=utm_source%3DXXX%26utm_medium%3Dxxx%26utm_campaign%3DXXX

Parameter id has a value of com.xxx.xxx

Parameter referrer has a value of utm_source=XXX&utm_medium=xxx&utm_campaign=XXX

This works perfectly, but what I want to do is I want to get the values of utm_source, utm_medium and utm_campaign and store it into a variable. I'm still uncertain on how do I manage to achieve this. Here is my code:

func handleIncomingDynamicLink(_ dynamicLink: DynamicLink) {
        guard let url = dynamicLink.url else {
            print("Dynamic Link has no URL")
            return
        }

        print("Incoming link parameter is \(url.absoluteString)")
        guard let components = URLComponents(url: url, resolvingAgainstBaseURL: false), let queryItems = components.queryItems else { return }
        for queryItem in queryItems {
            print("Parameter \(queryItem.name) has a value of \(queryItem.value ?? "")")
        }
    }

So how do I manage to do this? Feel free to ask if you need any more information. Thank you.

Body:
I don't know if this is the best answer or the right code design but fortunately I managed to solve by changing handleIncomingDynamicLink function to something like this:

func handleIncomingDynamicLink(_ dynamicLink: DynamicLink) {
        guard let url = dynamicLink.url else {
            print("Dynamic Link has no URL")
            return
        }

        print("Incoming link parameter is \(url.absoluteString)")
        guard let components = URLComponents(url: url, resolvingAgainstBaseURL: false), let queryItems = components.queryItems else { return }
        for queryItem in queryItems {
            if queryItem.name == "referrer" {
                print("Referrer found")

                let referrerString = "https://xxx.page.link/details?\(queryItem.value ?? "")"
                let referrerUrl = URL(string: referrerString)

                guard let queryItemComponents = URLComponents(url: referrerUrl!, resolvingAgainstBaseURL: false), let itemComponents = queryItemComponents.queryItems else {
                    print("Referrer string couldn't be casted as a URL")
                    return
                }

                for item in itemComponents {
                    print("Parameter \(item.name) has a value of \(item.value ?? "")")
                }

            }
        }
    }

So basically what I did was I take the referrer value of the main URL and make it a "custom URL" so that I could parse the values by using the URLComponents function. The output is exactly what I expected to be.

Incoming link parameter is https://play.google.com/store/apps/details?id=com.xxx.xxx&referrer=utm_source%3Dxxx%26utm_medium%3Dxxx%26utm_campaign%3Dxxx

Referrer found

Parameter utm_source has a value of xxx

Parameter utm_medium has a value of xxx

Parameter utm_campaign has a value of xxx


问:
Android when activity loses focus and is brought back by notification, sound will no longer play
I have a timer running and at various points play short audio clips(dings or voices) all less than 4 seconds.  This is one if statement in the timer that can play a sound:

```<code>Random rand = new Random();
if (rand.nextInt(2) == 0) {
    restSoundsH[rand.nextInt(rests.length)].start();
} else {
    restSoundsT[rand.nextInt(rests.length)].start();
}
</code>```

The restSounds are a private array loaded in the onCreate method and have 4-12 sounds each:

```<code>            restSoundsH = new MediaPlayer[rests.length];
            restSoundsT = new MediaPlayer[rests.length];
            for(int i = 0;i < rests.length;i++){
                restSoundsH[i] = MediaPlayer.create(this, getResources().getIdentifier(rests[i] + "h", "raw", getPackageName()));
                restSoundsT[i] = MediaPlayer.create(this, getResources().getIdentifier(rests[i] + "t", "raw", getPackageName()));
            }
</code>```

When the activity is created I create a notification like this:

```<code>//create notification
        Intent intent = getPackageManager()
                .getLaunchIntentForPackage(getPackageName())
                .setPackage(null)
                .setFlags(Intent.FLAG_ACTIVITY_NEW_TASK | Intent.FLAG_ACTIVITY_RESET_TASK_IF_NEEDED);

        PendingIntent pendingIntent = PendingIntent.getActivity(this, 0, intent, 0);

        nm = (NotificationManager) getSystemService(Context.NOTIFICATION_SERVICE);
        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {
            NotificationChannel notificationChannel =
                    new NotificationChannel(NOTIFICATION_CHANNEL_ID, NOTIFICATION_CHANNEL_ID, NotificationManager.IMPORTANCE_LOW);
            notificationChannel.setDescription("Main Channel");
            notificationChannel.setSound(null,null);
            nm.createNotificationChannel(notificationChannel);
        }
        notificationBuilder = new NotificationCompat.Builder(this, NOTIFICATION_CHANNEL_ID);

        notificationBuilder
                .setWhen(System.currentTimeMillis())
                .setSmallIcon(R.drawable.ic_notification)
                .setContentText("Remaining: ")
                .setContentInfo("Info")
                .setOngoing(true)
                .setContentIntent(pendingIntent);

        nm.notify(NOTIFICATION_ID, notificationBuilder.build());
</code>```

The notification is updated every second.
I want the app to run even when it loses focus(home button etc) or phone is slept which it does.

The problem is, when it loses focus and the notification is clicked on or the phone is slept and then brought back the sound no longer works. Its strange in the fact that only certain sounds at first. If I cancel the activity and start it again no sounds work. It is almost like it is certain Media arrays that get blocked.

EDIT: This only happens on actual phones(tested on 3 different), works fine on emulators.

What am I doing wrong. Let me know if you need to see other code.

Thanks

答:
```<code>new NotificationChannel(NOTIFICATION_CHANNEL_ID, NOTIFICATION_CHANNEL_ID, NotificationManager.IMPORTANCE_LOW);
</code>```

You are creating a notification channel with importance of "IMPORTANCE_LOW".

No sound will be played when the channel set to "IMPORTANCE_LOW".

Try to initialize the channel with "IMPORTANCE_DEFAULT" or higher importance.

for more details, please check the android developer documents.
https://developer.android.com/training/notify-user/channels#importance

Body:
I have a timer running and at various points play short audio clips(dings or voices) all less than 4 seconds.  This is one if statement in the timer that can play a sound:

Random rand = new Random();
if (rand.nextInt(2) == 0) {
    restSoundsH[rand.nextInt(rests.length)].start();
} else {
    restSoundsT[rand.nextInt(rests.length)].start();
}

The restSounds are a private array loaded in the onCreate method and have 4-12 sounds each:

            restSoundsH = new MediaPlayer[rests.length];
            restSoundsT = new MediaPlayer[rests.length];
            for(int i = 0;i < rests.length;i++){
                restSoundsH[i] = MediaPlayer.create(this, getResources().getIdentifier(rests[i] + "h", "raw", getPackageName()));
                restSoundsT[i] = MediaPlayer.create(this, getResources().getIdentifier(rests[i] + "t", "raw", getPackageName()));
            }

When the activity is created I create a notification like this:

//create notification
        Intent intent = getPackageManager()
                .getLaunchIntentForPackage(getPackageName())
                .setPackage(null)
                .setFlags(Intent.FLAG_ACTIVITY_NEW_TASK | Intent.FLAG_ACTIVITY_RESET_TASK_IF_NEEDED);

        PendingIntent pendingIntent = PendingIntent.getActivity(this, 0, intent, 0);

        nm = (NotificationManager) getSystemService(Context.NOTIFICATION_SERVICE);
        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {
            NotificationChannel notificationChannel =
                    new NotificationChannel(NOTIFICATION_CHANNEL_ID, NOTIFICATION_CHANNEL_ID, NotificationManager.IMPORTANCE_LOW);
            notificationChannel.setDescription("Main Channel");
            notificationChannel.setSound(null,null);
            nm.createNotificationChannel(notificationChannel);
        }
        notificationBuilder = new NotificationCompat.Builder(this, NOTIFICATION_CHANNEL_ID);

        notificationBuilder
                .setWhen(System.currentTimeMillis())
                .setSmallIcon(R.drawable.ic_notification)
                .setContentText("Remaining: ")
                .setContentInfo("Info")
                .setOngoing(true)
                .setContentIntent(pendingIntent);

        nm.notify(NOTIFICATION_ID, notificationBuilder.build());

The notification is updated every second.
I want the app to run even when it loses focus(home button etc) or phone is slept which it does.

The problem is, when it loses focus and the notification is clicked on or the phone is slept and then brought back the sound no longer works. Its strange in the fact that only certain sounds at first. If I cancel the activity and start it again no sounds work. It is almost like it is certain Media arrays that get blocked.

EDIT: This only happens on actual phones(tested on 3 different), works fine on emulators.

What am I doing wrong. Let me know if you need to see other code.

Thanks

Body:
new NotificationChannel(NOTIFICATION_CHANNEL_ID, NOTIFICATION_CHANNEL_ID, NotificationManager.IMPORTANCE_LOW);

You are creating a notification channel with importance of "IMPORTANCE_LOW".

No sound will be played when the channel set to "IMPORTANCE_LOW".

Try to initialize the channel with "IMPORTANCE_DEFAULT" or higher importance.

for more details, please check the android developer documents.
https://developer.android.com/training/notify-user/channels#importance


问:
Azure Batch and Connecting to Key Vault
I have a C# console application running on a batch node that is trying to connect to a key vault. The pool, nodes and key vault are linked to the same subnet however when I try and get a secret the call timeouts - I think this maybe a network issue. Is there any network security permissions I need to set on the v-net or sub-net or node or pool that may resolve the timeout?

Or any step by steps instructions for getting a batch pool and nodes communicating on a subnet...

答: Do you have a virtual NIC on the VM hosting the C# app? They can have a Network Security Group (NSG), too, that can restrict access to network traffic. Another thing you should consider is adding a Service Principal to the VM and allowing access to KV. https://learn.microsoft.com/en-us/azure/batch/batch-aad-auth. This won't solve your problem if it IS a network issue.

Title: Azure Batch and Connecting to Key Vault


问:
Xcode 10.2.1 Command PhaseScriptExecution failed with a nonzero exit code
```<code>Showing Recent Messages:-1: mkdir -p /Users/spritzindia/Library/Developer/Xcode/DerivedData/Contigo-atftiouzrdopcmcpprphpilawwzm/Build/Products/Debug-iphonesimulator/Contigo.app/Frameworks

Showing Recent Messages:-1: rsync --delete -av --filter P .*.?????? --filter "- CVS/" --filter "- .svn/" --filter "- .git/" --filter "- .hg/" --filter "- Headers" --filter "- PrivateHeaders" --filter "- Modules" "/Users/pothi/Library/Developer/Xcode/DerivedData/Contigo-atftiouzrdopcmcpprphpilawwzm/Build/Products/Debug-iphonesimulator/IQKeyboardManagerSwift/IQKeyboardManagerSwift.framework" "/Users/pothi/Library/Developer/Xcode/DerivedData/Contigo-atftiouzrdopcmcpprphpilawwzm/Build/Products/Debug-iphonesimulator/Contigo.app/Frameworks"
</code>```

Command PhaseScriptExecution failed with a nonzero exit code

I deleted derived data.
i have tried :

keychain access -> right click on login -> lock & unlock again ->
  clear Xcode project

Restarted machine, no use

How do I resolve "Command PhaseScriptExecution failed with a nonzero exit code" error when trying to archive project.

And I am using those librarys

```<code>pod 'IQKeyboardManagerSwift'
pod 'SDWebImage', '~> 4.0'
pod 'KRPullLoader'
pod 'Paytm-Payments'
</code>```

答:
For Xcode 11, React Native development environment.
I usually have this problem when a dependency is not updated.

You can try following these steps, this usually works for me:

1- Delete your Podfile.lock (I like to use the command '-rm -rf Podfile.lock' on the terminal for this)

2- Delete your Pods folder (I like to use the command '-rm -rf Pods' in the terminal for this)

3- Delete your .xcworkspace

4- Pod install

5- Clear your project into XCode> Product> Clean Build Folder

Body:
Showing Recent Messages:-1: mkdir -p /Users/spritzindia/Library/Developer/Xcode/DerivedData/Contigo-atftiouzrdopcmcpprphpilawwzm/Build/Products/Debug-iphonesimulator/Contigo.app/Frameworks

Showing Recent Messages:-1: rsync --delete -av --filter P .*.?????? --filter "- CVS/" --filter "- .svn/" --filter "- .git/" --filter "- .hg/" --filter "- Headers" --filter "- PrivateHeaders" --filter "- Modules" "/Users/pothi/Library/Developer/Xcode/DerivedData/Contigo-atftiouzrdopcmcpprphpilawwzm/Build/Products/Debug-iphonesimulator/IQKeyboardManagerSwift/IQKeyboardManagerSwift.framework" "/Users/pothi/Library/Developer/Xcode/DerivedData/Contigo-atftiouzrdopcmcpprphpilawwzm/Build/Products/Debug-iphonesimulator/Contigo.app/Frameworks"

Command PhaseScriptExecution failed with a nonzero exit code

I deleted derived data.
i have tried :

keychain access -> right click on login -> lock & unlock again ->
  clear Xcode project

Restarted machine, no use

How do I resolve "Command PhaseScriptExecution failed with a nonzero exit code" error when trying to archive project.

And I am using those librarys

pod 'IQKeyboardManagerSwift'
pod 'SDWebImage', '~> 4.0'
pod 'KRPullLoader'
pod 'Paytm-Payments'


问:
Need a delay to wait for GPS
Using the iPhone and objective C, is there a way to stall or perform a timing loop to allow for the GPS to catch up and return a valid set of coordinates?

Currently, the application runs too quickly and the GPS cannot supply the coordinates fast enough...

答: Assuming your GPS polling is running in a different thread to the User Interface, you can call the static NSThread functions sleepForTimeInterval or sleepUntilDate from the thread that is waiting for the GPS data.</p>

ContentLicense: CC BY-SA 3.0

Title: Need a delay to wait for GPS


问:
Display FileName of a Image Python imshow
Please,

I'm trying to display the filename of a jpeg but i could not find how to.
I want to display the image + the filename of the image.

```<code>img_dir = "paintings/"
data_path = os.path.join(img_dir,'*g')
files = glob.glob(data_path)
data = []
for f1 in files:
    img = imageio.imread(f1)
    data.append(img)

plt.imshow(data[40])
</code>```

Thanks

答: You can try plt.title(filename):</p>

Body:
Please,

I'm trying to display the filename of a jpeg but i could not find how to.
I want to display the image + the filename of the image.

img_dir = "paintings/"
data_path = os.path.join(img_dir,'*g')
files = glob.glob(data_path)
data = []
for f1 in files:
    img = imageio.imread(f1)
    data.append(img)

plt.imshow(data[40])

Thanks

Body: You can try plt.title(filename):


问:
Randomly downsample rows of a dataframe 1.Conserving the distribution of column labels OR 2.Same number of samples per each label?
I have a dataset that is very large and in the ' Label' column, it has 3 different class labels namely 'benign', 'webdos' and 'udp-lag'. I want to reduce the dataset by randomly selecting the data from the dataset while keeping the distribution of the class labels to an equal or similar number.

how can I effectively do it using python or any other equivalent python libraries?

Link to the small dataset

答:
Pandas has a built in function to sample data. You only need to give the frequency of class/labels as weights:

```<code>df['counts'] = df['target'].map(df['target'].value_counts())
df_downsample = df.sample(frac=0.4, weights='counts', axis=0)
</code>```

You can either use argument frac to decide what fraction of original data frame to sample (here, for e.g. it returns 40% of original dataframe rows), or instead, REPLACE it with argument n for number of rows you desire in your down sampled dataframe (e.g. df_downsample = df.sample(n=10, weights='counts', axis=0))

EDIT: per OP's comment, if you need a fixed number of instances n per each class, you can use groupby:

```<code>df_downsample  = df.groupby('target', group_keys=False).apply(lambda df: df.sample(n))
</code>```

Body:
Pandas has a built in function to sample data. You only need to give the frequency of class/labels as weights:

df['counts'] = df['target'].map(df['target'].value_counts())
df_downsample = df.sample(frac=0.4, weights='counts', axis=0)

You can either use argument frac to decide what fraction of original data frame to sample (here, for e.g. it returns 40% of original dataframe rows), or instead, REPLACE it with argument n for number of rows you desire in your down sampled dataframe (e.g. df_downsample = df.sample(n=10, weights='counts', axis=0))

EDIT: per OP's comment, if you need a fixed number of instances n per each class, you can use groupby:

df_downsample  = df.groupby('target', group_keys=False).apply(lambda df: df.sample(n))


问:
Change value of arraylist field where object exists
I need some help cause I got stuck. I have an ArrayList called orderList with type ItemOrdered(int quantity,Item item). I want to set a new value for item's quantity where the given item exists into ArrayList. I tried everything but didn't work.</p>

```<code>//place new order from buyer class
    public void placeOrder(int quantity, Item item) {
        //ItemOrdered newitemordered = new ItemOrdered(quantity,item );
        ShoppingCart shoppingCart = new ShoppingCart();
        shoppingCart.addItemOrdered(quantity,item);

    }
</code>```

```<code>//call placeOrder method
buyer.placeOrder(quantity,eshop.ItemListPen.get(x));
</code>```

```<code>import java.util.ArrayList;

public class ShoppingCart {
    public ArrayList<ItemOrdered> orderList = new ArrayList<ItemOrdered>();

    public void addItemOrdered(int quantity,Item item) {
        if(item.getStock() >= quantity && (!item.equals(orderList))){
            ItemOrdered newitemordered = new ItemOrdered(quantity,item);
            orderList.add(new ItemOrdered(quantity,item));
        }else if(item.getStock() < quantity){
            System.out.println("Sorry,this quantity is not available in stock.");
        }else{

            orderList.get(item).setQuantity(quantity);
            System.out.println("nothing");
        }

    }

}
</code>```

```<code>public class ItemOrdered {
    static  Item item;
    private  int quantity;
    public ItemOrdered(int quantity, Item item){
        this.quantity=quantity;
        this.item=item;
    }

    public void setQuantity(int x){
        quantity=quantity + x;
    }

}

</code>```

```<code>

</code>```

答:
It looks like you’re always going to enter the if block of your if-else if-else when the stock is greater than or equal to quantity because the second condition, (!item.equals(orderList)), will be true. This condition should check to see if the list contains the item, not that the list and the item are equal. The next piece of the puzzle is that the objects in orderList are ItemOrdered objects and not Item objects. That being the case, simply doing !orderList.contains(item) will also always return true.

You could solve this by having a list containing Item objects as part of your ShoppingCart, or adding a new method to loop through the orderList and check item against each Item in the list.

Option #1 - Adding a new List of Item objects to ShoppingCart:

```<code>public class ShoppingCart {
    public ArrayList<ItemOrdered> orderList = new ArrayList<ItemOrdered>();

    // This could also be public if you needed for some reason, but you could also achieve that by adding getter and setter methods
    // depending on version of Java, you may need to include Item in <> when creating the new list: new ArrayList<Item>()
    private ArrayList<Item> itemsInCart = new ArrayList<>();

    public void addItemOrdered(int quantity,Item item) {
        if(item.getStock() >= quantity && !itemsInCart.contains(item)){
            ItemOrdered newitemordered = new ItemOrdered(quantity,item); // this could be removed, and you just create the new ItemOrdered within .add() on the next line
            orderList.add(newitemordered);
            itemsInCart.add(item); // have to update the new list of items
        }else if(item.getStock() < quantity){
            System.out.println("Sorry,this quantity is not available in stock.");
        }else{
            orderList.get(item).setQuantity(quantity);
            System.out.println("nothing");
        }

    }

}
</code>```

Option #2 - Looping through the orderList:

```<code>public class ShoppingCart {
    public ArrayList<ItemOrdered> orderList = new ArrayList<ItemOrdered>();

    public void addItemOrdered(int quantity,Item item) {
        if(item.getStock() >= quantity && !isItemInCart(item)){
            orderList.add(new ItemOrdered(quantity,item));
        }else if(item.getStock() < quantity){
            System.out.println("Sorry,this quantity is not available in stock.");
        }else{
            orderList.get(item).setQuantity(quantity);
            System.out.println("nothing");
        }

    }

    private boolean isItemInCart(Item item){
        for(ItemOrdered itemOrdered : orderList){
            if(itemOrdered.getItem().equals(item)){
                return true;
            }
        }
        // Item is not in cart
        return false;
    }

}
</code>```

```<code>public class ItemOrdered {
    static  Item item;
    private  int quantity;
    public ItemOrdered(int quantity, Item item){
        this.quantity=quantity;
        this.item=item;
    }

    public void setQuantity(int x){
        quantity=quantity + x;
    }

    public Item getItem(){
        return this.item;
    }

}
</code>```

Notes on the different options:

Option 1:

Simple

Have to maintain a second list

Option 2:

Requires more work up front

You don't have to maintain two lists containing similar data

Looping could be expensive if the orderList is exceedingly large (probably not a huge deal for most practical cases)

You may want to override the .equals() method of Item to define exactly what's being compared to determine if two items are equal

Body:
I need some help cause I got stuck. I have an ArrayList called orderList with type ItemOrdered(int quantity,Item item). I want to set a new value for item's quantity where the given item exists into ArrayList. I tried everything but didn't work.

//place new order from buyer class
    public void placeOrder(int quantity, Item item) {
        //ItemOrdered newitemordered = new ItemOrdered(quantity,item );
        ShoppingCart shoppingCart = new ShoppingCart();
        shoppingCart.addItemOrdered(quantity,item);

    }

//call placeOrder method
buyer.placeOrder(quantity,eshop.ItemListPen.get(x));

import java.util.ArrayList;

public class ShoppingCart {
    public ArrayList<ItemOrdered> orderList = new ArrayList<ItemOrdered>();

    public void addItemOrdered(int quantity,Item item) {
        if(item.getStock() >= quantity && (!item.equals(orderList))){
            ItemOrdered newitemordered = new ItemOrdered(quantity,item);
            orderList.add(new ItemOrdered(quantity,item));
        }else if(item.getStock() < quantity){
            System.out.println("Sorry,this quantity is not available in stock.");
        }else{

            orderList.get(item).setQuantity(quantity);
            System.out.println("nothing");
        }

    }

}

public class ItemOrdered {
    static  Item item;
    private  int quantity;
    public ItemOrdered(int quantity, Item item){
        this.quantity=quantity;
        this.item=item;
    }

    public void setQuantity(int x){
        quantity=quantity + x;
    }

}

Body:
It looks like you’re always going to enter the if block of your if-else if-else when the stock is greater than or equal to quantity because the second condition, (!item.equals(orderList)), will be true. This condition should check to see if the list contains the item, not that the list and the item are equal. The next piece of the puzzle is that the objects in orderList are ItemOrdered objects and not Item objects. That being the case, simply doing !orderList.contains(item) will also always return true.

You could solve this by having a list containing Item objects as part of your ShoppingCart, or adding a new method to loop through the orderList and check item against each Item in the list.

Option #1 - Adding a new List of Item objects to ShoppingCart:

public class ShoppingCart {
    public ArrayList<ItemOrdered> orderList = new ArrayList<ItemOrdered>();

    // This could also be public if you needed for some reason, but you could also achieve that by adding getter and setter methods
    // depending on version of Java, you may need to include Item in <> when creating the new list: new ArrayList<Item>()
    private ArrayList<Item> itemsInCart = new ArrayList<>();

    public void addItemOrdered(int quantity,Item item) {
        if(item.getStock() >= quantity && !itemsInCart.contains(item)){
            ItemOrdered newitemordered = new ItemOrdered(quantity,item); // this could be removed, and you just create the new ItemOrdered within .add() on the next line
            orderList.add(newitemordered);
            itemsInCart.add(item); // have to update the new list of items
        }else if(item.getStock() < quantity){
            System.out.println("Sorry,this quantity is not available in stock.");
        }else{
            orderList.get(item).setQuantity(quantity);
            System.out.println("nothing");
        }

    }

}

Option #2 - Looping through the orderList:

public class ShoppingCart {
    public ArrayList<ItemOrdered> orderList = new ArrayList<ItemOrdered>();

    public void addItemOrdered(int quantity,Item item) {
        if(item.getStock() >= quantity && !isItemInCart(item)){
            orderList.add(new ItemOrdered(quantity,item));
        }else if(item.getStock() < quantity){
            System.out.println("Sorry,this quantity is not available in stock.");
        }else{
            orderList.get(item).setQuantity(quantity);
            System.out.println("nothing");
        }

    }

    private boolean isItemInCart(Item item){
        for(ItemOrdered itemOrdered : orderList){
            if(itemOrdered.getItem().equals(item)){
                return true;
            }
        }
        // Item is not in cart
        return false;
    }

}

public class ItemOrdered {
    static  Item item;
    private  int quantity;
    public ItemOrdered(int quantity, Item item){
        this.quantity=quantity;
        this.item=item;
    }

    public void setQuantity(int x){
        quantity=quantity + x;
    }

    public Item getItem(){
        return this.item;
    }

}

Notes on the different options:

Option 1:

Simple

Have to maintain a second list

Option 2:

Requires more work up front

You don't have to maintain two lists containing similar data

Looping could be expensive if the orderList is exceedingly large (probably not a huge deal for most practical cases)

You may want to override the .equals() method of Item to define exactly what's being compared to determine if two items are equal


问:
What is the functionality of this regex?
I am recently learning regex and i am not quite sure how the following regex works:

```<code>str.replaceAll("(\\w)(\\w*)", "$2$1ay");
</code>```

This allows us to do the following:

```<code>input string: "Hello World !"
return string: "elloHay orldWay !"
</code>```

From what I know: w is supposed to match all word characters including 0-9 and underscore and $ matches stuff at the end of string.

答:
In the replaceAll method, the first parameter can be a regex. It matches all words in the string with the regex and changes them to the second parameter.

In simple cases replaceAll works like this:

```<code>str = "I,am,a,person"
str.replaceAll(",", " ") // I am a person
</code>```

It matched all the commas and replaced them with a space.

In your case, the match is every alphabetic character(\w), followed by a stream of alphabetic characters(\w*).

The () around \w is to group them. So you have two groups, the first letter and the remaining part. If you use regex101 or some similar website you can see a visualization of this.

Your replacement is $2 -> Second group, followed by $1(remaining part), followed by ay.

Hope this clears it up for you.

Body:
I am recently learning regex and i am not quite sure how the following regex works:

str.replaceAll("(\\w)(\\w*)", "$2$1ay");

This allows us to do the following:

input string: "Hello World !"
return string: "elloHay orldWay !"

From what I know: w is supposed to match all word characters including 0-9 and underscore and $ matches stuff at the end of string.

Body:
In the replaceAll method, the first parameter can be a regex. It matches all words in the string with the regex and changes them to the second parameter.

In simple cases replaceAll works like this:

str = "I,am,a,person"
str.replaceAll(",", " ") // I am a person

It matched all the commas and replaced them with a space.

In your case, the match is every alphabetic character(\w), followed by a stream of alphabetic characters(\w*).

The () around \w is to group them. So you have two groups, the first letter and the remaining part. If you use regex101 or some similar website you can see a visualization of this.

Your replacement is $2 -> Second group, followed by $1(remaining part), followed by ay.

Hope this clears it up for you.


问:
How can conditional rendering based on privilege
I am not able to show / hide the delete icon based on following code. If the privilege == "ADMIN" then display the delete icon, but if the privilege == "PLAYER" then hide it.

Then below code is not working,

```<code>const [deleteIcon, setDeleteIcon] = useState({ show: false});

 useEffect(() => {
    const fetchData = async () => {
      try {
       const res = await Axios.get('http://localhost:8000/service/players');
       setPlayerList(res.data.players);
       setSearchResults(res.data.players);
       const privilege = localStorage.getItem('Privilege');
       console.log("Privilege logging in Front End:"+privilege);
       showDeleteIcon(privilege);
      } catch (e) {
       console.log(e);
     }
   }
  fetchData();
 }, []);

  const showDeleteIcon = (privilege) =>{
    if(privilege === "ADMIN"){
      setDeleteIcon({show:true})
    }else{
      setDeleteIcon({show:false})
    }
  }

   { deleteIcon &&(
    <span className="deletePlayerIcon">
       <img src="images/deleteIcon.png"/>
    </span>
    )}
</code>```

答:
On your conditional rendering statement, you will need to go 1 level deeper into the deleteIcon state object:

```<code>{deleteIcon.show && (
  <span className="deletePlayerIcon">
   <img src="images/deleteIcon.png"/>
  </span>
)}
</code>```

Body:
I am not able to show / hide the delete icon based on following code. If the privilege == "ADMIN" then display the delete icon, but if the privilege == "PLAYER" then hide it.

Then below code is not working,

const [deleteIcon, setDeleteIcon] = useState({ show: false});

 useEffect(() => {
    const fetchData = async () => {
      try {
       const res = await Axios.get('http://localhost:8000/service/players');
       setPlayerList(res.data.players);
       setSearchResults(res.data.players);
       const privilege = localStorage.getItem('Privilege');
       console.log("Privilege logging in Front End:"+privilege);
       showDeleteIcon(privilege);
      } catch (e) {
       console.log(e);
     }
   }
  fetchData();
 }, []);

  const showDeleteIcon = (privilege) =>{
    if(privilege === "ADMIN"){
      setDeleteIcon({show:true})
    }else{
      setDeleteIcon({show:false})
    }
  }

   { deleteIcon &&(

    )}

Body:
On your conditional rendering statement, you will need to go 1 level deeper into the deleteIcon state object:

{deleteIcon.show && (
  <span className="deletePlayerIcon">
   <img src="images/deleteIcon.png"/>
  </span>
)}


问:
automatically partition Hive tables based on S3 directory names
I have data stored in S3 like:

```<code>/bucket/date=20140701/file1
/bucket/date=20140701/file2
...
/bucket/date=20140701/fileN

/bucket/date=20140702/file1
/bucket/date=20140702/file2
...
/bucket/date=20140702/fileN
...
</code>```

My understanding is that if I pull in that data via Hive, it will automatically interpret date as a partition. My table creation looks like:

```<code>CREATE EXTERNAL TABLE search_input(
   col 1 STRING,
   col 2 STRING,
   ...

)
PARTITIONED BY(date STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 's3n://bucket/';
</code>```

However Hive doesn't recognize any data. Any queries I run return with 0 results. If I instead just grab one of the dates via:

```<code>CREATE EXTERNAL TABLE search_input_20140701(
   col 1 STRING,
   col 2 STRING,
   ...

)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 's3n://bucket/date=20140701';
</code>```

I can query data just fine.

Why doesn't Hive recognize the nested directories with the "date=date_str" partition?
Is there a better way to have Hive run a query over multiple sub-directories and slice it based on a datetime string?

答:
I had faced the same issue and realized that hive does not have partitions metadata with it. So we need to add that metadata using ALTER TABLE ADD PARTITION query. It becomes tedious, if you have few hundred partitions to create same queries with different values.

ALTER TABLE
 ADD PARTITION(<partitioned column name>=<partition value>);

Once you run above query for all available partitions. You should see the results in hive queries.

Body:
I have data stored in S3 like:

/bucket/date=20140701/file1
/bucket/date=20140701/file2
...
/bucket/date=20140701/fileN

/bucket/date=20140702/file1
/bucket/date=20140702/file2
...
/bucket/date=20140702/fileN
...

My understanding is that if I pull in that data via Hive, it will automatically interpret date as a partition. My table creation looks like:

CREATE EXTERNAL TABLE search_input(
   col 1 STRING,
   col 2 STRING,
   ...

)
PARTITIONED BY(date STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 's3n://bucket/';

However Hive doesn't recognize any data. Any queries I run return with 0 results. If I instead just grab one of the dates via:

CREATE EXTERNAL TABLE search_input_20140701(
   col 1 STRING,
   col 2 STRING,
   ...

)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 's3n://bucket/date=20140701';

I can query data just fine.

Why doesn't Hive recognize the nested directories with the "date=date_str" partition?
Is there a better way to have Hive run a query over multiple sub-directories and slice it based on a datetime string?

Body:
I had faced the same issue and realized that hive does not have partitions metadata with it. So we need to add that metadata using ALTER TABLE ADD PARTITION query. It becomes tedious, if you have few hundred partitions to create same queries with different values.

ALTER TABLE <table name> ADD PARTITION(<partitioned column name>=<partition value>);

Once you run above query for all available partitions. You should see the results in hive queries.


问:
How do I test if a variable does not equal either of two values?
I want to write an if/else statement that tests if the value of a text input does NOT equal either one of two different values. Like this (excuse my pseudo-English code):

```
var test = $("#test").val();
if (test does not equal A or B){
    do stuff;
}
else {
    do other stuff;
}
```

How do I write the condition for the if statement on line 2?

答:
This can be done with a switch statement as well. The order of the conditional is reversed but this really doesn't make a difference (and it's slightly simpler anyways). </p>

```<code>switch(test) {
    case A:
    case B:
        do other stuff;
        break;
    default:
        do stuff;
}
</code>```

Body:
I want to write an if/else statement that tests if the value of a text input does NOT equal either one of two different values. Like this (excuse my pseudo-English code):

var test = $("#test").val();
if (test does not equal A or B){
    do stuff;
}
else {
    do other stuff;
}

How do I write the condition for the if statement on line 2?

Body:
This can be done with a switch statement as well. The order of the conditional is reversed but this really doesn't make a difference (and it's slightly simpler anyways).

switch(test) {
    case A:
    case B:
        do other stuff;
        break;
    default:
        do stuff;
}


问:
org.springframework.web.HttpRequestMethodNotSupportedException: Request method 'GET' not supported
I am studying spring boot and spring jpa .
I was able to successfully read and write into the database, but the problem occurs while updating the data.

```<code>There was an unexpected error (type=Method Not Allowed, status=405).
Request method 'GET' not supported
org.springframework.web.HttpRequestMethodNotSupportedException: Request method 'GET' not supported
</code>```

I am trying to update data of table : Person ( Id, Name , Age) using a html form. It accepts the data, and using spring jpa  I am trying to update it in MySQL database.

I am getting the following at console when trying to update the data, after submitting the form

```<code>> WARN 5176 --- [nio-8080-exec-1]
> .w.s.m.s.DefaultHandlerExceptionResolver : Resolved
> [org.springframework.web.HttpRequestMethodNotSupportedException:
> Request method 'GET' not supported]
</code>```

My code is here :
updateperson form :

```<code> <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Title</title>
    </head>
    <body>
    <h1>Enter new details</h1>
    <form action="updatePerson" method="post">
        Id   : <input type="number" name="id"><br>
        Name :<input type = "text" name = "name"><br>
        Age : <input type = "number" name = "age"><br>
        <input type="submit">
    </form>
    </body>
    </html>
</code>```

PersonRepository.java

```<code>package com.example.demo.repository;

import com.example.demo.model.Person;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PersonRepository extends CrudRepository<Person , Integer> {
    List<Person> findByName(String name);
    List<Person> findByAge(int age);
    List<Person> findAll();
}
</code>```

PersonController.java

```<code>    package com.example.demo.controller;

import com.example.demo.model.Person;
import com.example.demo.repository.PersonRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Controller
public class PersonController {
    @Autowired
    PersonRepository personRepository;

    @GetMapping("update")
    public String updatePerson(){
        System.out.println("update to updateperson html");
        return "updateperson";
    }
    @PutMapping("/updatePerson")
    public String updatePerson(Person person){
        System.out.println("UPDATING TO" + person.getAge()+" : "+person.getName() + " : "+person.getPid());
        personRepository.save(person);
        return "updateperson";
    }

}
</code>```

Person.java

```<code>package com.example.demo.model;

import org.springframework.stereotype.Component;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Person {
    @Id
    @GeneratedValue
    int pid;

    String name;
    int age;

     public Person() {
    }

    public Person(int pid, String name, int age) {
        this.pid = pid;
        this.name = name;
        this.age = age;
    }

//getter and setters
}
</code>```

The URL to which I'm sending the request:

```<code>localhost:8080/updatePerson?id=4&name=personame&age=34
</code>```

答:
You are sending a GET request while you have added a @PutMapping in your updatePerson method. If you want to access updatePerson then you should try to hit that endpoint with a PUT method.

Use GET method when accessing localhost:8080/update and use PUT method when accessing localhost:8080/updatePerson

Body:
I am studying spring boot and spring jpa .
I was able to successfully read and write into the database, but the problem occurs while updating the data.

There was an unexpected error (type=Method Not Allowed, status=405).
Request method 'GET' not supported
org.springframework.web.HttpRequestMethodNotSupportedException: Request method 'GET' not supported

I am trying to update data of table : Person ( Id, Name , Age) using a html form. It accepts the data, and using spring jpa  I am trying to update it in MySQL database.

I am getting the following at console when trying to update the data, after submitting the form

> WARN 5176 --- [nio-8080-exec-1]
> .w.s.m.s.DefaultHandlerExceptionResolver : Resolved
> [org.springframework.web.HttpRequestMethodNotSupportedException:
> Request method 'GET' not supported]

My code is here :
updateperson form :

 <!DOCTYPE html>

        <meta charset="UTF-8">
        <title>Title</title>

Enter new details

    <form action="updatePerson" method="post">
        Id   : <input type="number" name="id">

        Name :<input type = "text" name = "name">

        Age : <input type = "number" name = "age">

        <input type="submit">
    </form>

PersonRepository.java

package com.example.demo.repository;

import com.example.demo.model.Person;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PersonRepository extends CrudRepository<Person , Integer> {
    List<Person> findByName(String name);
    List<Person> findByAge(int age);
    List<Person> findAll();
}

PersonController.java

    package com.example.demo.controller;

import com.example.demo.model.Person;
import com.example.demo.repository.PersonRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Controller
public class PersonController {
    @Autowired
    PersonRepository personRepository;

    @GetMapping("update")
    public String updatePerson(){
        System.out.println("update to updateperson html");
        return "updateperson";
    }
    @PutMapping("/updatePerson")
    public String updatePerson(Person person){
        System.out.println("UPDATING TO" + person.getAge()+" : "+person.getName() + " : "+person.getPid());
        personRepository.save(person);
        return "updateperson";
    }

}

Person.java

package com.example.demo.model;

import org.springframework.stereotype.Component;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Person {
    @Id
    @GeneratedValue
    int pid;

    String name;
    int age;

     public Person() {
    }

    public Person(int pid, String name, int age) {
        this.pid = pid;
        this.name = name;
        this.age = age;
    }

//getter and setters
}

The URL to which I'm sending the request:

localhost:8080/updatePerson?id=4&name=personame&age=34


答:
I found that you're storing an object into deleteIcon. So you have to do like this</p>

```<code>   { deleteIcon.show &&(
    <span className="deletePlayerIcon">
       <img src="images/deleteIcon.png"/>
    </span>
    )}

</code>```

Title: How can conditional rendering based on privilege

Body:
I found that you're storing an object into deleteIcon. So you have to do like this

   { deleteIcon.show &&(
    <span className="deletePlayerIcon">
       <img src="images/deleteIcon.png"/>
    </span>
    )}


问:
pip install rpy2==3.2.7 error with code 1
Please anyone help me, i got these when pip install rpy2==3.2.7 :

Collecting wheel
  Using cached https://files.pythonhosted.org/packages/8c/23/848298cccf8e40f5bbb59009b32848a4c38f4e7f3364297ab3c3e2e2cd14/wheel-0.34.2-py2.py3-none-any.whl
Installing collected packages: wheel
Successfully installed wheel-0.34.2

(venv) C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50>pip install rpy2==3.2.7
Collecting rpy2==3.2.7
  Using cached https://files.pythonhosted.org/packages/39/c0/61120f9dae06b4887426d229b68a7a5f0ca1f9cb3986319bb9484819a28d/rpy2-3.2.7.tar.gz
    Complete output from command python setup.py egg_info:
    Command "python setup.py egg_info" failed with error code 1 in C:\Users\billpebert\AppData\Local\Temp\pip-wheel-4l7yazyi\cffi\
    Traceback (most recent call last):
      File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\installer.py", line 128, in fetch_build_egg
        subprocess.check_call(cmd)
      File "C:\Users\billpebert\AppData\Local\Programs\Python\Python38-32\lib\subprocess.py", line 364, in check_call
        raise CalledProcessError(retcode, cmd)
    subprocess.CalledProcessError: Command '['C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\Scripts\python.exe', '-m', 'pip', '--disable-pip-versio
n-check', 'wheel', '--no-deps', '-w', 'C:\Users\BILLPE~1\AppData\Local\Temp\tmpvdyfy70j', '--quiet', 'cffi>=1.13.1']' returned non-zero exit status 1.

```<code>During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Users\billpebert\AppData\Local\Temp\pip-install-22ktwibt\rpy2\setup.py", line 147, in <module>
    setup(
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\__init__.py", line 143, in setup
    _install_setup_requires(attrs)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\__init__.py", line 138, in _install_setup_requires
    dist.fetch_build_eggs(dist.setup_requires)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\dist.py", line 695, in fetch_build_eggs
    resolved_dists = pkg_resources.working_set.resolve(
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\pkg_resources\__init__.py", line 781, in resolve
    dist = best[req.key] = env.best_match(
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\pkg_resources\__init__.py", line 1066, in best_match
    return self.obtain(req, installer)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\pkg_resources\__init__.py", line 1078, in obtain
    return installer(requirement)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\dist.py", line 754, in fetch_build_egg
    return fetch_build_egg(self, req)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\installer.py", line 130, in fetch_build_egg
    raise DistutilsError(str(e))
distutils.errors.DistutilsError: Command '['C:\\Users\\billpebert\\PycharmProjects\\KlasifikasiKualitasUdaraC50\\venv\\Scripts\\python.exe', '-m', 'pip', '--disable-pip-vers
</code>```

ion-check', 'wheel', '--no-deps', '-w', 'C:\Users\BILLPE~1\AppData\Local\Temp\tmpvdyfy70j', '--quiet', 'cffi>=1.13.1']' returned non-zero exit status 1.

```<code>----------------------------------------
</code>```

Command "python setup.py egg_info" failed with error code 1 in C:\Users\billpebert\AppData\Local\Temp\pip-install-22ktwibt\rpy2\

答: Fixes for Windows only appeared with rpy2-3.3.2. If you pip install rpy2 you will get the latest release (currrently 3.3.3).

Body:
Please anyone help me, i got these when pip install rpy2==3.2.7 :

Collecting wheel
  Using cached https://files.pythonhosted.org/packages/8c/23/848298cccf8e40f5bbb59009b32848a4c38f4e7f3364297ab3c3e2e2cd14/wheel-0.34.2-py2.py3-none-any.whl
Installing collected packages: wheel
Successfully installed wheel-0.34.2

(venv) C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50>pip install rpy2==3.2.7
Collecting rpy2==3.2.7
  Using cached https://files.pythonhosted.org/packages/39/c0/61120f9dae06b4887426d229b68a7a5f0ca1f9cb3986319bb9484819a28d/rpy2-3.2.7.tar.gz
    Complete output from command python setup.py egg_info:
    Command "python setup.py egg_info" failed with error code 1 in C:\Users\billpebert\AppData\Local\Temp\pip-wheel-4l7yazyi\cffi\
    Traceback (most recent call last):
      File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\installer.py", line 128, in fetch_build_egg
        subprocess.check_call(cmd)
      File "C:\Users\billpebert\AppData\Local\Programs\Python\Python38-32\lib\subprocess.py", line 364, in check_call
        raise CalledProcessError(retcode, cmd)
    subprocess.CalledProcessError: Command '['C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\Scripts\python.exe', '-m', 'pip', '--disable-pip-versio
n-check', 'wheel', '--no-deps', '-w', 'C:\Users\BILLPE~1\AppData\Local\Temp\tmpvdyfy70j', '--quiet', 'cffi>=1.13.1']' returned non-zero exit status 1.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Users\billpebert\AppData\Local\Temp\pip-install-22ktwibt\rpy2\setup.py", line 147, in <module>
    setup(
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\__init__.py", line 143, in setup
    _install_setup_requires(attrs)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\__init__.py", line 138, in _install_setup_requires
    dist.fetch_build_eggs(dist.setup_requires)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\dist.py", line 695, in fetch_build_eggs
    resolved_dists = pkg_resources.working_set.resolve(
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\pkg_resources\__init__.py", line 781, in resolve
    dist = best[req.key] = env.best_match(
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\pkg_resources\__init__.py", line 1066, in best_match
    return self.obtain(req, installer)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\pkg_resources\__init__.py", line 1078, in obtain
    return installer(requirement)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\dist.py", line 754, in fetch_build_egg
    return fetch_build_egg(self, req)
  File "C:\Users\billpebert\PycharmProjects\KlasifikasiKualitasUdaraC50\venv\lib\site-packages\setuptools\installer.py", line 130, in fetch_build_egg
    raise DistutilsError(str(e))
distutils.errors.DistutilsError: Command '['C:\\Users\\billpebert\\PycharmProjects\\KlasifikasiKualitasUdaraC50\\venv\\Scripts\\python.exe', '-m', 'pip', '--disable-pip-vers

ion-check', 'wheel', '--no-deps', '-w', 'C:\Users\BILLPE~1\AppData\Local\Temp\tmpvdyfy70j', '--quiet', 'cffi>=1.13.1']' returned non-zero exit status 1.

----------------------------------------

Command "python setup.py egg_info" failed with error code 1 in C:\Users\billpebert\AppData\Local\Temp\pip-install-22ktwibt\rpy2\


问:
How to filter dataframe against reference with varying conditions
I would like to filter for grouped rows based on multiple conditions (some arbitrary number of comparisons that may contain one or two conditions) like so:

We begin with a test dataframe, a reference table containing relevant conditions, and test for the presence of all conditions within a group.

```<code>library(tidyverse)

test_case = tribble(
  ~id, ~value,
  1, 'A',
  1, 'B',
  2, 'C',
  3, 'C',
  3, 'D',
  4, 'E',
  4, 'F'
)

reference_table = tribble(
  ~var1, ~var2,
  'A', 'NA',
  'B', 'NA',
  'C', 'D',
  'D', 'E',
  'E', 'F'
)

</code>```

I expect to filter test_case against reference_table such that groups in test_case with values
1) satisfying var1 only when var2 is NA are present or 2) satisfying both conditions when both var1 and var2 are defined. So in this contrived example, the group containing id 2 will not be present in the filtered test_case because while it contains value C, it requires both C and D to be present. On the other hand, id 3 meets filter requirements because it contains both C and D, matching the third row in the reference table.

```<code>filtered_test_case = tribble(
  ~id, ~value,
  1, 'A',
  1, 'B',
  3, 'C',
  3, 'D',
  4, 'E',
  4, 'F'
)
</code>```

I've attempted something like

```<code>test_case %>%
  group_by(id) %>%
  filter(all(value %in% reference_table$var1, value %in% reference_table$var2, na.rm = TRUE))
</code>```

which does not perform the way I expected. The idea was to demand one or both (where valid) conditions match. I think I can understand why after applying

```<code>test_case %>%
  group_by(id) %>%
  mutate(var1_check = if_else(value %in% reference_table$var1, TRUE, FALSE)) %>%
  mutate(var2_check = if_else(value %in% reference_table$var2, TRUE, FALSE))

</code>```

I think I can see a convoluted way of applying case_when logic to achieve my goal, but I'm looking for a succinct way of doing so.

答:
First, the NA's in reference_table are not real NAs they are string "NA"'s. Let's change that :

```<code>reference_table[reference_table == 'NA'] <- NA
</code>```

After that, we can write a function that will select a group if any row in reference_table is completely present in the group value.

```<code>select_rows <- function(ref_tab, value) {
   any(apply(ref_tab, 1, function(x) all(na.omit(x) %in% value)))
}
</code>```

We can then apply this function for each group.

```<code>library(dplyr)

test_case %>%
   group_by(id) %>%
   filter(select_rows(reference_table, value))

#    id value
#  <dbl> <chr>
#1     1 A
#2     1 B
#3     3 C
#4     3 D
#5     4 E
#6     4 F
</code>```

Body:
I would like to filter for grouped rows based on multiple conditions (some arbitrary number of comparisons that may contain one or two conditions) like so:

We begin with a test dataframe, a reference table containing relevant conditions, and test for the presence of all conditions within a group.

library(tidyverse)

test_case = tribble(
  ~id, ~value,
  1, 'A',
  1, 'B',
  2, 'C',
  3, 'C',
  3, 'D',
  4, 'E',
  4, 'F'
)

reference_table = tribble(
  ~var1, ~var2,
  'A', 'NA',
  'B', 'NA',
  'C', 'D',
  'D', 'E',
  'E', 'F'
)

I expect to filter test_case against reference_table such that groups in test_case with values
1) satisfying var1 only when var2 is NA are present or 2) satisfying both conditions when both var1 and var2 are defined. So in this contrived example, the group containing id 2 will not be present in the filtered test_case because while it contains value C, it requires both C and D to be present. On the other hand, id 3 meets filter requirements because it contains both C and D, matching the third row in the reference table.

filtered_test_case = tribble(
  ~id, ~value,
  1, 'A',
  1, 'B',
  3, 'C',
  3, 'D',
  4, 'E',
  4, 'F'
)

I've attempted something like

test_case %>%
  group_by(id) %>%
  filter(all(value %in% reference_table$var1, value %in% reference_table$var2, na.rm = TRUE))

which does not perform the way I expected. The idea was to demand one or both (where valid) conditions match. I think I can understand why after applying

test_case %>%
  group_by(id) %>%
  mutate(var1_check = if_else(value %in% reference_table$var1, TRUE, FALSE)) %>%
  mutate(var2_check = if_else(value %in% reference_table$var2, TRUE, FALSE))

I think I can see a convoluted way of applying case_when logic to achieve my goal, but I'm looking for a succinct way of doing so.

Body:
First, the NA's in reference_table are not real NAs they are string "NA"'s. Let's change that :

reference_table[reference_table == 'NA'] <- NA

After that, we can write a function that will select a group if any row in reference_table is completely present in the group value.

select_rows <- function(ref_tab, value) {
   any(apply(ref_tab, 1, function(x) all(na.omit(x) %in% value)))
}

We can then apply this function for each group.

library(dplyr)

test_case %>%
   group_by(id) %>%
   filter(select_rows(reference_table, value))

#    id value
#  <dbl> <chr>
#1     1 A
#2     1 B
#3     3 C
#4     3 D
#5     4 E
#6     4 F


问:
Pear: Includes are successful but cannot find functions
EDIT: Solution...email is a function within Validate which is a class so you need to access it using:

Validate::email("anemail@email.com");

or

$val = new Validate();
$val->email("anemail@email.com");

Thanks red eyes

====================== original question ==============================

Hi,

I'm using Pear and have installed the package Validate to try it out.

Using the code:

```<code><?php
require_once 'Validate.php';
email("anemail@email.com");
?>
</code>```

I get the error.

```<code>PHP Fatal error:  Call to undefined function email() in /home/benb/temp/pear.php on line 4
</code>```

If I alter the code to include a non existent extension like require_once 'Validate2.php'; it gives me the error to say it can't find it.
Also, if I view Validate.php I can see the email function within it.

So it seems as though it can find the extension but not the functions within it.
Can anyone help?
Thanks

答:
It's possible to post Validate.php ?
I think Validate.php it's PHP5 ;)

So it's

```<code>$val = new validate();
$val->email();
</code>```

Body:
EDIT: Solution...email is a function within Validate which is a class so you need to access it using:

Validate::email("anemail@email.com");

or

$val = new Validate();
$val->email("anemail@email.com");

Thanks red eyes

====================== original question ==============================

Hi,

I'm using Pear and have installed the package Validate to try it out.

Using the code:

<?php
require_once 'Validate.php';
email("anemail@email.com");
?>

I get the error.

PHP Fatal error:  Call to undefined function email() in /home/benb/temp/pear.php on line 4

If I alter the code to include a non existent extension like require_once 'Validate2.php'; it gives me the error to say it can't find it.
Also, if I view Validate.php I can see the email function within it.

So it seems as though it can find the extension but not the functions within it.
Can anyone help?
Thanks

Body:
It's possible to post Validate.php ?
I think Validate.php it's PHP5 ;)

So it's

$val = new validate();
$val->email();